VBA Important Tips

Thanks to Nick Williams at Acuity Training for this guest post!

The lesson will go over the following tips:


1) Loop Through Code

2) Variable Scope

3) Error Checking

4) Immediate Window


VBA Tip #1 – Loop Through Code

A common event in programming is having a section of code which needs to be executed one or more times, this is achieved using a Loop.  There are two types of loops available in VBA:

For … Next Loop – use this when you know how many times the code needs to be executed.

Do … Loop – use this when you need to test how many times the code needs to be executed.

For … Next Loops

When you know the number of times a piece of code needs to be executed, use the For … Next loop, this uses a counter to determine how many times the code has looped at any given time.  The following example shows how a For … Next Loop could be used to enter a value in a set of cells:

Image 1

The value of  i is incremented by 1 each time the Next line of code is executed.

Do Loops

Most occasions in programming we are not sure how many times a piece of code needs to be executed, and in most cases we would test this against a condition, ie. repeat until all records have been formatted, since we cannot determine how many records there may be at any given time, this would be a good example of performing a Do … Loop.  The Do … Loop has several formats it can take on, the following table explains the variations of the loop:

Image 2

The following example shows how a Do … Loop could be used to format a set of cells, the variation is a Do Until and the condition is until the Active Cell does not contain a value:

Image 3

Image 4

VBA Tip #2 – Variable Scope

Variables are constantly used in any programming language to capture and temporarily store values; VBA is not exception to this.  However, the importance of using variables is ensuring you set the scope for the variable correctly.  The Life of a variable is known as its scope:

Image 5

The following code example displays how we would use Procedure, Module and Global scope variables:

Image 6

Image 7

VBA Tip #3 – Error Checking

Once you have tried and tested your programs, the next step is to apply some Error handling which will make as many checks as possible during initialization to ensure that run time errors do not occur later.  If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box.  This may be acceptable, but a more user friendly message may be more desirable. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method.

There are generally three types of Errors:

1.   Compiler errors, such as undeclared variables that prevent your code from compiling

2.   User data entry error such as a user entering a negative value where only a positive number is acceptable

3.   Run Time errors, these occur when VBA cannot correctly execute a program statement.

The more checking you do before the real work of your application begins, the more stable your application will be.

Image 8

Image 9


VBA Tip #4 – Immediate WIndow

The Immediate Window within the VBA Editor is used as a scratchpad window where statements, methods and Sub procedures can be evaluated immediately.  Very useful when you want to see the results of a line or two of code, but do not want to run the entire procedure.

Image 10

Image 11

Hope you enjoyed the post, and thanks again to Acuity Training for creating this lesson!

This entry was posted in Workbook/Reference Lessons. Bookmark the permalink.

Leave a Reply