Some notes about Visual Basic for Applications (VBA) in Excel

VBA Links

First steps

Enable Developer tools Tab

Enable developer tools tab in Excel

Start Macro Recorder Developer - Record Macro During the recording you can switch between absolute and relative cell address recording (cell A2 vs offset)

Start a macro Define a shortcut for the macro Developer - Insert - Button, assign Macro

Object browser Developer - Visual Basic - View - Object Browser

Immediate window



Data types

Visual Basic Data types

Dim bytAge as Byte

Enforce variable declaration

Option Explicit


If a method contains a static variable it will remmeber its value for the next time the method is called

Static intCountMe As Integer


If variables are declared with Public instead of Dim they can be accessed globally Public intAge as Integer


Variables that cannot be changed after they have be initialized. There are already a lot of constants defined, which usually start with vb or xl.

Const intAnswer=42

Cast data types


Free reference

Set foo= Nothing


Dim arrMyArray(0 To 5) As String

arrMyArray(0) = "Zero"
arrMyArray(1) = "One"
arrMyArray(2) = "Two"

Dim s As Variant

For Each s In arrMyArray
    Debug.Print s
Next s

More than one array dimension

Dim arrTwoDimensions(0 To 15, 0 To 15) As Boolean
arrTwoDimensions(0, 0) = False

Change Array size

Dim arrMyArray(1) As String
arrMyArray(0) = "Zero"

ReDim arrMyArray(2)
arrMyArray(0) = "Zero"
arrMyArray(1) = "One"

ReDim Preserve arrMyArray(5)
Debug.Print LBound(arrMyArray)
Debug.Print UBound(arrMyArray)

Show debug messages

Debug.Print myVariable

if statement

If intMyNumber = 1 Then
    Debug.Print "1"
ElseIf intMyNumber = 2 Then
    Debug.Print "2"
    Debug.Print "?"
End If

Case Statement

Select Case intMyNumber
Case 1
       Debug.Print "1"
   Case 2
        Debug.Print "2"
    Case 3 To 8
        Debug.Print "3..8"
    Case Else
        Debug.Print "?"
End Select


For loop

For i = 10 To 1 Step -1
    Debug.Print i
    If i = 5 Then
        Exit For
   End If
Next i

While loop

Do While i < 100
    i = i + 1
    Debug.Print i
While i < 100
    i = i + 1
    Debug.Print i


If want to access more than one attribute of an object you can use the with statement

With FOO
    .age = 3
    .enable = True
    .Name = "Foo"
End With


Sub Foo(ByVal a As Integer, ByRef b As Integer)
    a = -1 ' ByVal will not have an effect outside this method
    b = -2 ' ByRef will have an effect outside this method
End Sub


Function fact(start As Integer) As Integer
    If start > 0 Then
        fact = start * fact(start - 1)
        fact = 1
    End If      
End Function

Debug.Print fact(5) ‘ 120

Functions can not only be used in VBA code but also directly in Excel cells. So you could also insert in an Excel cell


Non fixed number of parameters

Sub Sort(inverse As Boolean, ParamArray data() As Variant)


You can add new classes view VBA - Insert - Class Module

VBA insert new class

In the Properties field, the name of the class can be defined.

VBA insert new method

If you want to add Functions or Property methods (getters and setters) use Insert - Add Procedure

Example for a simple class

Option Explicit

Private mName As String
Private mRange As Long
Private mMaxSpeed As Integer

' Initialize object
Private Sub Class_Initialize()
    mName = "Unnamed"
    mRange = 0
    mMaxSpeed = 0
End Sub

' Setter and Getter Range
Public Property Get Range() As Variant
    Range = mRange
End Property
Public Property Let Range(ByVal vNewValue As Variant)
    mRange = vNewValue
End Property

' Setter and Getter MaxSpeed
Public Property Get MaxSpeed() As Variant
    MaxSpeed = mMaxSpeed
End Property

Public Property Let MaxSpeed(ByVal vNewValue As Variant)
    mMaxSpeed = vNewValue
End Property

' Setter and Getter VehicleName
Public Property Get VehicleName() As Variant
    Name = mName
End Property

Public Property Let VehicleName(ByVal vNewValue As Variant)
    mName = vNewValue
End Property

' Output method
Public Sub output()
    Debug.Print mName
    Debug.Print mRange
    Debug.Print mMaxSpeed
End Sub

Example how to use this class

Dim car As New Vehicle
car.VehicleName = "Volkswagen"
car.MaxSpeed = 180
car.Range = 650
Set car = Nothing

Date and time

Dim t As Date
t = TimeSerial(Second:=59, Minute:=59, Hour:=23)
Debug.Print t
Debug.Print Hour(t)
Debug.Print Minute(t)
Debug.Print Second(t)

Dim d As Date
d = DateSerial(Day:=31, Month:=12, Year:=2013)
Debug.Print d
Debug.Print Day(d)
Debug.Print Month(d)
Debug.Print Year(d)

Random number

Rnd gives you a random number between 0 and 1, but may be used to get you random int numbers as well

Function Random(min As Integer, max As Integer) As Single
 If (min <= max) Then
    Random = Int((Rnd * (max - min + 1)) + min)
 End If
End Function

Error Handling

You can ignore any error (not recommended), jump with goto if an error occurs or raise your own errors

Sub MySub()
    Dim intMyNumber As Integer

    ' ignore possible errors
    On Error Resume Next
    ' Error: String vs Integer
    intMyNumber = "abc"
    ' enable error tracking again
    On Error GoTo 0

    ' On error jump to another place to handle it
    On Error GoTo MyError
    intMyNumber = "a42"
    On Error GoTo 0

    Debug.Print "This is the value of intMyNumber"
    Debug.Print intMyNumber

    ' Raise your own error
    Err.Raise 1003

    Exit Sub

' Jump here if you got an error

    Debug.Print "MyError was called after an error occured: ",
    Debug.Print Err.Number,
    Debug.Print " ",
    Debug.Print Err.Description,
    Debug.Print " ",
    Debug.Print Err.Source
    intMyNumber = 42
    Resume Next
End Sub

Excel VBA

Iterate through Excel cells

Dim rngMyRange As Range
Dim r As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("MySheetWithNumbers")
Set rngMyRange = ws.Range("B4:D8")
For Each r In rngMyRange
    Debug.Print r.Value2
Next r

With Cells(1, 1)
    .Value = "Hallo"
    .Interior.Color = vbYellow
End With

Cells(1, 1).Offset(1, 0).Value = "-------"

Manipulate cells

Range("a1").Copy Destination:=Range("b1")

ActiveSheet.UsedRange.Interior.Color = vbRed



Range("A1:E1").Font.Color = vbBlue
Range("A1:E1").Font.Color = RGB(255, 0, 0)

Range("A1:D1").Font.Size = 32

Disable Screen Updates

You may disable screen updates during larger changes to improve performance and avoid screen flickering

Application.ScreenUpdating = False
Application.ScreenUpdating = True

Dialog windows

Info dialog

Show an info dialog, ask the user to press a button und check which button was pressed

Dim strResult
Dim strMyMessageText As String
Dim strMyMessageTitle As String

strMyMessageTitle = "Say hello"
strMyMessageText = "Hello " &amp; _
                   "World!" &amp; Chr(10) &amp; _

strResult = MsgBox(strMyMessageText, vbYesNo, strMyMessageTitle)

If strResult = vbYes Then
    Debug.Print "Yes!"
    Debug.Print "No!"
End If

Input dialog

Show an input dialog

Dim strResult
Dim strMyMessageText As String
Dim strMyMessageTitle As String
Dim strMyMessageSugestion As String

strMyMessageTitle = "Write what you think"
strMyMessageText = "Please leave a message"
strMyMessageSugestion = "..."

strResult = InputBox(strMyMessageText, strMyMessageTitle, strMyMessageSugestion)

If strResult <> "" Then
    Debug.Print strResult
End If


An Application.InputBox works similar and offers an input validation (Type 1 enforces a numeric value)

strResult = Application.InputBox(strMyMessageText, strMyMessageTitle, strMyMessageSugestion, Type:=1)

If strResult Then
    Debug.Print strResult
End If

Excel dialogs

You can also use build in Excel dialogs

res = Application.Dialogs(xlDialogSaveAs).Show

User forms

Open the Excel Visual Basic Window and insert a new UserForm

Excel VBA insert UserForm

Now you can “paint" the gui with all required elements. You can test it by pressing F5 while it is selected in the Visual Basic Window or you can add a button to your Excel sheet Excel - Developer - Insert - Form Button

Add a macro like this to the new button

Sub Button1_Click()
End Sub


You have to add the code to the Excel Object which will generate the Event (e.g. the Excel sheet). The name of the function will choose the event you want to monitor. In your function you may want to temporary disable any further events to prevent a chain reaction.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 MsgBox "Changed value:" &amp; Target.Value
 Application.EnableEvents = True
End Sub

Excel VBA Worksheet Object Events

Call windows API methods

API methods can only called within modules. So you have to use one or create one (in the object tree in the VBA editor, right click insert module). Otherwise you will get:

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.

All API methods you want to use have to be declared.

#If VBA7 Then
 Declare PtrSafe Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal puffer As String, size As Long) As Long
 Declare         Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal puffer As String, size As Long) As Long
#End If
Option Explicit

Sub ComputerName()
  Dim lngReturnValue As Long
  Dim strName As String * 32
  lngReturnValue = GetComputerName(strName, 32)
  If (lngReturnValue) Then
    Debug.Print (strName)
    Debug.Print ("Failed to get computer name")
  End If
End Sub

See also 32-bit vs 64-bit VBA in Office 2010 Microsoft WIN32 API for VBA