Enhancing a Recorded VBA Macro in Excel

I know. It is not MS Access. But it is VBA and I bet a lot of you use Excel as well. AND, it was fun to do.

This video series shows me recording a very basic VBA macro in Excel, and then modifying it to make it more complex. Excel is great for recording a macro, but you have to add things like loops manually. This series is mainly about the loop.

VBA Settings – Part 2

This time we will look at how to colour code our VBA programming. I like to colour my comment lines with green text. Variables are red in my code.

Here is one of my 3 minute videos showing how I do it.

VBA Settings – Part 1

There are a variety of settings in MS Access that you will customize over time. This one, inside the VBA editor, is one of the most important. Requiring Variable Declaration is a real time saver, and gets rid of lots of “bugs” before they even get started.

Using the Immediate Window

The Immediate Window in MS Access is a great way to test the use of a function before using that function in a query or in VBA code. You start using the Immediate Window by pressing Ctrl-G on your keyboard.

Examples used in the video are:
? 5*8
? now
? dlookup(“[CourseName]”,”Courses”,”CourseID=2″)
? dlookup(“[StartDate]”,”Courses”,”CourseName=’Basket Weaving 101′”)
? dlookup(“[CourseName]”,”Courses”,”StartDate=#Jan 26, 2015#”)

IF Statements In A Query


In a query with 2 columns based on 2 fields in a table, how can I create a 3rd column that lists the minimum of the other 2 columns?


Here is a simple list of sample data for this query.

 Field1      Field2      Smallest
   5            7            5
   9            3            3
   8            7            7
   1            2            1

Field1 and Field2 come from the table. Smallest is determined by the query.

You might think that there would be an “IF” command that we use in the query. You are almost correct. The command is an “IIF”, or in full, Immediate IF. It is formally called a function and you will find it as part of the VBA programming language.

In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition: the value that you want to test

value_if_true: the value that is returned if “condition” evaluates to TRUE

value_if_false: the value that is returned if “condition” evaluates to FALSE

Here is the query that we need:

Select Field1, Field2, iif(Field1 < Field2, Field1, Field2) as Smallest 
From Table1