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


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.


'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


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


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 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 are very useful to store temporary data during execution of your code. You can store different types of data:

Type Name Examples Value Examples Comment
Boolean bProcessed True, False Can only have value False (0, Zero) or True (-1)
Byte btChar &H0F, 253 Value range 0 - 255
Currency ccyEUR 123.01 Value range -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date dtBirthday #12/31/1980# Value range 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.
Decimal decDist 123789.12 With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001
Double dPi 3.14159265 -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Integer intAge 123 Value range -32,768 to 32,767
Long lIndex 17167 Value range -2,147,483,648 to 2,147,483,647
LongLong llBigNum 123152367765 Only available for 64 bit systems: value range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
LongPtr lptr 131313123 Signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647 on 32-bit systems; and signed 64-bit (8-byte) numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Object obj A Range Use the Set keyword to set the object reference to the variable
Type Name Examples Value Examples Comment
Single sngNum 1.2345 Value range -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values
String sName “Bernd” Codes for String characters range from 0–255
User defined m_SystemState Any data type that you define by using the Type statement. User-defined data types can contain one or more elements of a data type, an array, or a previously defined user-defined type
Type Name Examples Value Examples Comment
Variant vA True, 123.01, #12/31/1980#, “Bernd” Value range -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values. Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double. An error occurs when Variant variables containing Currency, Decimal, and Double values exceed their respective ranges


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.


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
        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.


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


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


  • 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)


  • 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


See Program_optimization.

Sample Application

See sbGenerateTeams.