“If you think you are too small to make a difference, try sleeping with a mosquito.” [Dalai Lama]

Abstract

Excel VBA is a programming language. You can write programs to enhance Excel standard worksheet functionality or to repeat specified actions, for example.

As with learning in general, you might want to look around for all possible material you can get hold of, sort/order/structure it to your needs and filter/focus on parts you profit from most. Some nice points to start at:

• (External link!) VBTutor (detailed)

• (External link!) TechBookReport (simple, short)

• Enter VBA Editor by pressing ALT + F11

• VBA Project Browser: Change module name with F4 but do not choose a name of a function or a sub

• Record a macro. You will get ‘spaghetti code’ but it’s an easy way to look for a function or for a command you don’t know or you can’t recall. Do not forget to clean this up!

My Second Program

Copy and steal good code wherever you can! Your first program should be just a copy, I suggest.

Good sites to look for code:

(External link!) Pearson Software Consulting

(External link!) Erlandsen Data Consulting - A good one for import/export of data

Logging with VBA (example for object orientation)

Example: =CONCATENATE(A1:A9) does not work. Create this functionality with your own code: TEXTJOIN

Use OPTION EXPLICIT at the start of each of your modules:

This forces you to declare of all variables explicitely. You will get warned about variables you misspelt, for example.

Comment!

'This function takes the input parameters x, y, and z, performs a
'blabla calculation and returns value "oops".
'Version 0.1
'Date        Programmer Change
'22-Nov-2008 Bernd      Create

I indicate the quality of my macros with my version numbers: 0.1 is the first version, module tested, 1.0 would be good for general usage, 0.01 would be a prototype with some obvious drawbacks or similar. You can increase these version to 0.2, 1.1, etc. each time you apply a change. So everybody can see at one glance whether he has the most recent version and to which extent he might want to rely on it.

Sub, Function, Parameters

Sub

A sub(routine) normally performs well defined actions. It is good for repetitive tasks.

Function

A function encapsulates a reasonable portion of functionality, has zero or more input parameters, returns a return value (output parameter), and cannot change worksheet contents (ok, it can, but it shouldn’t!).

Parameters

Parameters are arguments for a sub or a function. They enable you to program in a more general way, because you can use them to represent the variable (flexible) inputs to your code. When your code is called with explicit values as parameters, these parameters in your code get replaced by the call values.

Parameter types or forms are: ByRef, ByVal, Optional (Preset), Paramarray.

Variables

Variables are very useful to store temporary data during execution of your code. You can store different types of data:

Lifetime

A variable’s lifetime states when or how long the variable is valid, i.e. how long will it require system memory to retain its value for.

Example:

Sub ComputeFactorial()
    Dim lResult As Long
    Dim n As Long
    n = 4
    lResult = Fact(n)
    Debug.Print "The Factorial of " & n & " is " & lResult
End Sub

Function Fact(n As Long) As Long
    If n = 0 Then
        Fact = 0
    ElseIf n = 1 Then
        Fact = 1
    Else
        Fact = n * Fact(n - 1)
    End If
End Function

The variables lResult and n in the example above are alive as long as the subroutine ComputeFactorial is in execution. They start to live with their respective Dim statement and their lifetime ends when the execution of the sub reaches the End Sub command - their memory will be freed again.

If you declare a variable to be Static then the variable will live as long as the module it resides in. More precisely, a static variable will be reset (its memory gets freed again) when:

• The macro generates an untrapped run-time error.
• VBA is halted.
• You quit Excel.
• You change the module.

Static variables enable you to save runtime - see sbRandTrigen for a more complex example on this feature.

Scope

All variables and all constants have a scope which defines the area of code where the variables will be recognised. This scope for can be:

Procedure Level

This is also known as a local variable (local to a function or to a sub):

Function test() as Variant
Dim i as Integer 'Local Variable
...
End Function

Module Level

A private module level variable is visible to the module it is declared in:

Dim i as Integer 'Variable on module level
Function test() as Variant
...
End Function

Global Level

A public module level variable is visible to all modules in a project:

Public Dim i as Integer 'Global variable
Function test() as Variant
...
End Function

Input

Read from spreadsheet cells

Give a cell a name, for example:

Now you can read or refer to this name from within VBA with

Dim s as string
s = Range("City")
...
End Function

Please notice that you can define names for single cells or for cell ranges. They are valid for worksheets only or for the whole workbook.

Read from Files

(External link!) Erlandsen Data Consulting

Avoid Reading Manual Input

Output

• Write into worksheets

You can write text into cells with

Range("A1") = "Sample Text"

A more complex example shows you how to use the Worksheet_Change event to show aging information of your data.

• Write into a message box

Call Messagebox("Message", vkOkOnly, "Title")

• Write into the status bar

Application.Statusbar = "User information during runtime"

Clear status bar

Application.Statusbar = FALSE

• Use a user form

• Write into files

I prefer to define all application-dependent parameters in a special sheet Param and to define self-explanatory range names.

• Write into an array (temporary variable storage) or into a variant:

Dim i as Integer, a(1 to 99) as Double
For i=1 to 99: a(i) = Cell(1,i): Next i

Dim v as Variant
v = Range("A1:A99”)

• Write into the immediate window (see debugging below)

Debugging

• Run a macro step by step with F8, step across functions or subs with SHIFT + F8
• Define breakpoints
• Watch interesting values
• Use debug.print to print debugging information into immediate window. Open/show immediate window with CTRL + g. Show variable contents with ? <variable_name> in immediate window
• Analyse logfiles into which your programs have written trace information

Optimization

See Program_optimization.

Sample Application

See sbGenerateTeams.