Monthly Archives: September 2013

Collections

In this blog post we will be discussing collections and how they can be used in VBA. Sub carParts()     ‘A collection is an object that has the ability     ‘to store other objects.     ‘     ‘Collections have 4 methods:     ‘Add: Used to add an item to the collection     ‘Remove: Used to […]

Working With Arrays 2

In this blog post we will be expanding on the capabilities of arrays in VBA. One of the headaches with arrays is that they are static blocks of memory and are not designed to change in size.  If we want to take an array that can hold 10 items and modify it so that it […]

Working With Arrays 1

In this blog post we introduce arrays and give you some examples of them in action. You can think of an array as a row of boxes with a number on each, 0 to n.  When we first declare an array we must at least state its type and may also state its size (we […]

Artithmetic Operators

In this blog post we will be examining Operators and how they can help you to construct if statements in VBA. An expression is a single or collection of variables and operators that ultimately evaluate to True or False.  Here we will list all the arithmetic operators with example code and introduce some operators you’ll […]

The If Statement

In this blog post we discuss the If…Then…Else statements that will allow you to add conditional logic to your posts. The Simple If Statement Sub standardExpressions() Dim a As Integer a = 10 If a = 10 Then Debug.Print “a = 10” ‘The If statement is a very simple statement that asks a ‘straightforward question […]

Compilation Explained

In this blog post we will examine compilation and the effect it has on the code you write. Compilation is the act of converting our human readable code ( VBA) into code the computer understands.  It may also be that your code is compiled into an intermediary format often called object code.  Either way, this […]

Visual Basic Editor Options

In this blog post we take a look at some of the options available in the Visual Basic Editor (press ALT + F11 to open). VBA has a concise set of options and tools which you can set to change behaviour of the editor and debugger.  All are useful tools to help make coding easier […]

Immediate Window

In this blog post we look at how you can use the immediate window whilst coding. The immediate window is a fantastic tool for testing and debugging code. Here are a few simple commands (open the immediate window and type these in directly, then press return): Debug.print “Hello World” Print “Foobarbar” ? “bar foo foo […]

Debugging

In this blog post we will discuss debugging and provide you with some simple tips for debugging your code. In VBA when we write code, it often doesn’t work how we expect it to or we think it is working fine but need to be sure before handing it over to a client. For this […]

Writing A Custom Function

In this blog post we will create a custom function that returns someone’s age based upon their date of birth. To write a custom function we need to consider the following: A returned value is needed, so we must use a function. The value returned will be somebody’s age, so we should return an Integer. […]

Built-In Functions

In this blog post we cover some commonly used built-in functions that VBA coders need to know. String Functions Len(s) – returns the length of String s. Left(s, n) – returns a substring of s that is n chars long from the left of the string s. Right(s, n) – returns a substring of s […]

Data Types In VBA

In this blog post we explain data types in VBA and provide detailed definitions for when they should be used. Firstly a word on VBA variable names; a variable may be named anything you wish as long as it conforms to VBA’s naming rules. Variable names must start with a letter, may contain number characters […]

Variables

In this blog post we cover declaring variables, naming conventions, scope and constants. When writing code in V.B.A. we often need to do calculations based on values that can change. An example would be working out the area of a circle. Take a look at the code below to see how we have used variables […]

How to Convert a Standalone Macro

In this blog post, we will be showing you how to convert a Macro into VBA code. A Macro is an object that includes a list of instructions, and these instructions translate to VBA code almost directly.  Rather than writing your code in VBA you could, and probably have already, put together a few Macros […]

Basic Tools for Writing Code

In this blog post, we provide you with some basic tools for writing VBA code. The VBA Editor incorporates a number of useful features which help you whilst you are writing code.  Here we’ll take a closer look at a few code writing features of the VBA editor. Line Continuation Character When we write code, […]

The VBA Editor Explained

In this blog post, we explain how the VBA editor works. There are four main areas of the editor that you need to know about.  The Code Window The Code Window is where all your VBA code will be written. It has syntax highlighting, which means keywords in VBA, – such as Function, CStr, Return […]

Opening The VBA Editor

In this blog post, we will show you how to open the VBA editor. The above image is the VBA Editor with three areas highlighted; the Project Explorer, Code Window and Immediate Window.  This is what is known as an Integrated Development Environment (which means everything you need to write programs and code are all […]

The Macro Designer

In this post, we will be explaining the Macro Designer. Macros have a very concise builder. There is no need for formatters, physical dimensions or other fancies; a macro is a doing object that is invisible to the user except in its expression, so the building really is as simple as it can get. Macro […]

Form Design View Explained

Form Object select button   Form Header   Form Detail   Form Footer     There are four main parts to any form. The Form Object select button selects the form window object.  The header, detail and footer are all children of the Form Object.  The Form Object links directly to tables and queries and […]

Sorting And Filtering Forms

In this post, we will be providing you with some techniques for sorting and filtering records in a form. Just like a datasheet, we can alter the order of the records see. You can sort a form by clicking into the field you wish to sort and then clicking either Ascending or Descending in the […]

Using The Like Operator In Queries

In this post, we will be demonstrating how you can use the LIKE operator in queries. Being able to filter queries is an important part of effective data retrieval. Although we often want to find exact matching values, sometimes we wish to search for a pattern within a string or a string which ends a […]

The Query Designer Explained

In this post, we will be explaining the constituent parts of the query designer.   To open the query designer navigate to the Create Tab of the Ribbon and click on the Query Design icon.  In the image below we can see the query designer for Access 2010. The query designers for Access 2007 and […]

Formatting A Datasheet

In this blog post we show you how to format a datasheet in MS Access. Although, generally, datasheets are boring grey forms that we can use to sort and filter data, we can actually format them to make their appearance more appealing. Open up a form in datasheet view. Background Colour and Alternate Rows Click […]

Sorting and Filtering A Record In Datasheet View

In this post, we will be showing you how to sort and filter a record in Datasheet view. Access has an array of filtering and sorting tools which are accessible in the Sort & Filter group on the Ribbon   Let’s put them to use. Filtering a Datasheet In order to perform any sorting or […]

Deleting A Record In Datasheet View

In this post, we are going to show you how to delete a record in datasheet view. Open up a form in MS Access in datasheet view.   Select the record you wish to delete by clicking in the circled part below. Click in the delete dropdown box in the Records group of the Ribbon. […]

Adding A Record In Datasheet View

In this post, we will be showing you how to add a record to a datasheet. Open up a form in datasheet view in MS Access. In the table below we have 3 fields – ItemID, Description and UnitPrice. What we don’t have is data! Adding A New Record The (1) in the above image […]

Indexes

In this post, we introduce the concept of indexes in MS Access. Open up a table in design view. You should see the Ribbon below. Click on the indexes button. The dialog box that opens up shows us all the indexes for a table.  In this case there is one index called Primary Key, which […]

How To Set A Primary Key

In this post, we demonstrate a couple of different ways to set the primary key in MS Access. A Primary Key is defined as “a unique key that can uniquely identify each row in a table”. The actual Key itself can be an auto-generated ID number, a known unique number (such as a social security […]

Field Data Types

In this post, we explain what the different data types in MS Access mean. Here are a list of data types in MS Access. Text (2013 Short Text) A String 0 to 255 characters long. Memo (2013 Long Text) A String up to 64,000 characters long. Number A Number holds numbers. You will be safe […]