Notes On 'Visual Basic For Applications' (VBA) In Excel

Purpose And Scope

I want to record some basics and pitfalls of using VBA in Excel, partially just for me, and mostly for Excel users with no prior programming experience.

 

Official overview doc for Excel VBA in particular and VBA in general.  There are also VB.NET docs, which are good for covering some of the gaps in the VBA docs, but sometimes VB.NET is different that VBA (ex: VB.NET has a Return statement but VBA does not).

Why Use VBA?

Using VBA in Excel lets you do complicated things simply.  Instead of having very long formulas and several cells to hold intermediate values, you can create a VBA function and then use that VBA function in an Excel cell formula like functions that are already built into Excel.  Example: you write a LargestPrimeFactor function in VBA and then can do stuff like `=LargestPrimeFactor(B38)`; if cell B38 holds the value 100, then the cell has the value 5 (because the prime factorization of 100 is 2 * 2 * 5 * 5).

VBA stands for "Visual Basic For Applications" and is a programming language with a lot of different syntax from Excel formulas.  Unfortunately, there is a lot of new stuff to learn, but conceptually there is a lot in common to all the complicated Excel cell formula stuff you've been doing.

How To Edit VBA In Excel

Supposedly you can launch the VBA editor with the keyboard shortcut Alt+F11, but that doesn't work for me.  You probably have to enable the Developer ribbon, and that gives you the Visual Basic button that launches the VBA editor.  In Windows, use the main menu at the top: File > Options > Customize Ribbon > checkmark 'Developer' in right list.  In Mac, it is similar.

VBA Is Not Case Sensitive

As far as I can tell so far, VBA doesn't seem to care about uppercase versus lower case for anything (other than actual text strings).  In this post, you will see a lot of stuff starting with a capital letter (ex: "Dim someVariable As Long"), and that is because the VBA editor likes to do the leading capital letter for a bunch of stuff and will change what you have written.  VBA will treat variables named "hi" and "HI" as the same.  I recommend having local variables as lower case, function as leading upper case, and not fighting the editor on anything.

 

You can type the line `dim myVar as long` and once you go to another line, the editor will auto-edit it to `Dim myVar As Long`.  You really don't need to type keywords with leading capital letters: 1) VBA doesn't care about capitalization and 2) the editor will change whatever keywords you type to have a leading capital anyway.

Some VBA Editor Tips

Once you start typing the name of an existing variable, function, or data type, you can press Ctrl+Space to get a dropdown of possible completions and Tab to accept the selected dropdown entry.  This really speeds things up when you want to use the variable `MeteorStrikeProbabilityForThisWeek`, but you only have to type `me`, Ctrl+Space, and then Tab.

You can also use Ctrl+Space right after a `.`, like `Application.WorksheetFunction.` to see all the valid members of that object.

VBA likes to have certain things like statements or procedure argument lists to be completely on one line, but sometimes that makes the line uncomfortably long.  You can use the underscore character `_` to continue something on the next line.  See below for without and with line continuations:

 

Function Fiddle1(arg1 As Long, arg2 As Long, arg3 As Long) As Long

    Fiddle1 = arg1 + arg2 + arg3

End Function 

 

Function Fiddle2 _

    ( arg1 As Long _

    , arg2 As Long _

    , arg3 As Long _

    ) As Long

    Fiddle2 _

        = arg1 _

        + arg2 _

        + arg3

End Function

Basic Structure Of A Procedure (Function And Subroutine)

Lots of other programming languages have functions that might or might not return the value.  In VBA, the terminology is that a procedure that returns a value is a function, and a procedure that does not return a value is a subroutine ("Sub").

 

Official docs Sub page.  Below is the structure of a subroutine, with stuff in curly braces being a stand in for what you actually put.

Sub {SomeSubroutineName} ( {Parameters} )

    {code goes here}

End Sub

 

We will go into more details and examples, but the big thing is that a subroutine has a name, zero or more input parameters, and some code that does stuff.  There is no return value.

Official docs Function page.  Here is a function...

Function {SomeFunctionName} ( {Parameters} ) As {SomeDataType}

    {code goes here}

    {SomeFunctionName} = {value you want to return}

End Sub

The `As {SomeDataType}` is saying the data type of the function's return value.

 

The last line of the function body doesn't have to be `SomeFunctionName = someValue`, but you will do it somewhere because that is how you set the return value of the function.  Some places will talk about how you can use a Return statement in Visual Basic, but I do not think that is available for VBA in Excel (list of Excel VBA statements).

Do not name your functions such they could be confused with Excel cells.  Excel can go to column XFD and row 1048576.  If you name a function "f1" or even "XFD1048576", Excel will autosuggest your function in cell formulas, but using it will get a #REF error because Excel will interpret it as a cell name.  "f" and "XFE1" are fine though because no cells exist by the same name.

Lets do an example of a silly function that takes two floating point numbers as inputs and returns/outputs the sum of those two numbers.  In VBA (and many other programming languages), a "double" is shorthand for a double-precision IEEE-754 floating point number and can handle values like `2.5`.

Function SillySum(val1 As Double, val2 As Double) As Double

    SillySum = val1 + val2

End Function

The `(val1 As Double, val2 As Double)` means that `val1` and `val2` are the input arguments to the function and that they are of type `Double`.  The `As Double` at the end of the first line it means the return value type is `Double`.

 

To call a function, you do something like `someVariable = SomeFunction(1, 2, 3)`.  The parens are required.  To call a subroutine, you don't use parens: `SomeSubroutine 1, 2, 3`.  Official doc.

Comments

You can start a code comment (text that is not interpreted as code to execute) with the single-quote character ('), and the rest of the line is ignored.  Example

Function LuckyNumber()

    LuckyNumber = 7 ' this text is a comment

End Function

Declaring And Using Variables In Functions/Subroutines

To declare a variable in a procedure (function or subroutine), you use the `Dim` keyword, supply a variable name, and an optional `As SomeDataType`, similar to what we did for input arguments for procedures.  Official doc on declaring variables. Example below...

 

Function SillySumAgain(val1 As Double, val2 As Double) As Double

    Dim mySillyVariable As Double

    mySillyVariable = val1

    mySillyVariable = mySillyVariable + val2

    SillySum = mySillyVariable

End Function

 

In the example above, we first declare that `mySillyVariable` will be a Double (double precision floating point number), then we use the variable in later lines.  The equals sign operator puts the value of the expression from the right hand side into the variable on the left hand side.  Notice that mySillyVariable can be on both sides of the equal sign, and that you can assign to it multiple times.

Of the available basic data types (there are more than just that list), you will probably mostly use...

• Boolean for whether something is true or false.
• Double for floating point numbers (ex: -2.5).
• Long for integers (or LongLong if you need values beyond ±2e9).  Don't use Integer since it can only handle -32,768 to 32,767.
• String for text.
  
• At some point, you will use arrays, dictionaries, and maybe collections to handle lots of related values.

Variables declared within procedures (including input arguments) are called local variables.  They only exist within the procedure they were declared.  If you have a local variable named `numDice` in one procedure, it is not affected by `numDice` local variables in other procedures and none of these `numDice` local variables are usable outside the procedures that declared them.

BUT, you can modify an input argument in one procedure and it will affect the variable in the procedure that called the current procedure.  The example below shows that passing fiddle1Var to Fiddle2 allows Fiddle2 to change fiddle1Var with lasting effect.

Sub Fiddle1()

  Dim fiddle1Var As Long

  fiddle1 = 10

  Fiddle2 fiddle1Var

  Debug.Print fiddle1Var ' WILL PRINT 11

End Sub

Sub Fiddle2(fiddle2Arg As Long)

  fiddle2Arg = fiddle2Arg + 1

End Sub

I recommend reading more on this topic, but one especially important thing is declaring procedure arguments with ByVal to help procedures get their own copies of inputs.

 

I suggest that local variable names be camelCase (first word is uncapitalized, following words are capitalized) and procedure names be PascalCase (each word capitalized).

I also suggest you put `Option Explicit` statement at the top of your VBA module (the top line of your editor thingy) to force variables to be declared before use.  It catches a lot of typos/mistakes.  You can go to [Main Menu > Tools > Options > Editor > checkmark Require Variable Declaration] to make the editor automatically add `Option Explicit` to the top of new VBA modules.

Using Excel Functions

 To use an Excel function (like how you do in Excel cell formulas), you need to preface the function with `Application.WorksheetFunction.`.  Ex: in an Excel cell, you might do `=fact(3)` to do factorial, and in VBA you would do `Application.WorksheetFunction.Fact(3)`.  Remember to use Ctrl+Space to see what is available.  Official doc.  List of functions.

 

If you are going to use an Excel function a lot, you might want to make a convenience function like the following...

Function ft(n As Double) As Double

    ft = Application.WorksheetFunction.Fact(n)

End Function

Which lets you do `numArrangements = ft(numObjects)` instead of `numArrangements = Application.WorksheetFunction.Fact(numObjects)`.

 

Arithmetic Operators

Operators like `+` and `*` do the natural thing.  I want to call out some special arithmetic operators (official operator summary).

• `^` is the exponentiation operator (doc) but you need to make sure you put a space before the `^`, otherwise it will be misinterpreted in a 64-bit environment.
• `\` is the integer division operator (doc).  `5 / 2` yields 2.5, but `5 \ 2` yields 2.

 

Using The Debugger

This 20min YouTube video seems to do a good job of covering the various debugger features for Excel VBA, and I think videos are the best medium for getting introduced to doing debugging and the various features.  You don't have to immediately understand and remember everything from the video, but I encourage you to work towards understanding at least these few key topics from the video:

• 01:50: the basics of running the debugger and stepping through your code.
• 02:25: looking at local variables with the locals window.
• 03:58: looking at all sorts of expressions and results in the watch window
• 08:45: breakpoints and step into/over/out; breakpoints are really, really useful
• 14:17: conditional breakpoints; a bit advanced, but often valuable
  

I want to emphasize that using the debugger is absolutely essential to quickly figuring out and fixing problems in your code.  Once you get comfortable using the debugger, trying to fix a problem with your code without the debugger feels like you're blind.

The first tip I want to add is that if you want to debug a procedure that takes input arguments, you might want to write a tiny subroutine with no input arguments that calls the procedure you actually want to debug.  The debugger is okay with being directly launched on a procedure with no input arguments.

The second tip I want to add is the Debug.Print subroutine.  Although debugging is great, you might want to see certain things about lots of different pieces of code, or draw connections between situations that are hard to isolate with a breakpoint.  For instance, you might just be curious what input values are being supplied to a particular procedure across hundreds of procedure calls.  With a Debug.Print, you can do the hundreds of calls and look at all the printed values at once in the Immediate Window (to enable, press Ctrl+G or menu: VBA Editor Window > View > Immediate Window).  Here's an example...

Sub DoSomething(someInput As Double)

    Debug.Print "DoSomething's someInput", someInput

    ' ... more code ...'

End Sub

The Debug.Print can take multiple arguments, and I chose to add some identifying text to help distinguish this printing from printings in other places.  You don't want a mess of numbers where you don't know which is what.

The third tip is the Debug.Assert subroutine.  It takes a boolean (true/false) expression as an input arg; if the expression evaluates to false, then execution is paused and dropped into the debugger where you can see what is going on and continue if you want.  This is most valuable for writing "checks" in your code that when violated means something has gone wrong.  Here's an example...

Function Factorial(n As Double) As Double

  Debug.Assert n >= 0

  Factorial = Application.WorksheetFunction.Fact(n)

End Function

 

The Excel Fact function can handle bad inputs by returning an error, but when you finally see the error, you have to track it down.  With the Assert, the moment that something calls Factorial with a negative number, you are alerted to it and you can go up the call stack to see how it happened.  It is great.

If-Then-Else Statements

Excel has a `IF(SomeCondition, ValueIfTrue, ValueIfFalse)` function.  In VBA, you can do `SomeVariable = IIF(SomeCondition, ValueIfTrue, ValueIfFalse)` (doc), which is pretty similar, but with even more flexibility, there are If statements, sometimes called If-Then-Else statements (doc).

The simplest usage is just an If-Then that does stuff if the provided condition evaluates to true...

If {condition} Then

    {body that only executes if the condition was true}

End If

But there is also If-Then-Else for also doing stuff if the condition was false ...

If {condition} Then

    {body that only executes if the condition was true}

Else

     {body that only executes if the condition was false}

End If

 

And you can use a If-Then-ElseIf to have multiple conditions ...

If {condition1} Then

  {body that only executes if condition1 was true}

ElseIf {condition2} Then

  {body that only executes if condition1 was false and condition2 was true}

ElseIf {condition3} Then

  {executes if condition1 and condition3 were false and condition3 was true}

Else

  {body that only executes if all of the above conditions were false}

End If

 

You can also nest If statements inside the bodies of other If statements...

 

If DrivingAtNight Then

    If WearingSunglasses Then

        Debug.Print "take the glasses off"

    Else

        Debug.Print "be vigilant"

    End If

Else    

    If WearingSunglasses Then

        Debug.Print "be vigilant"

    Else

        Debug.Print "think about wearing sunglasses"

    End If

End If

 

The conditions of these If statements are going to be often similar to the conditions you used in your Excel cell formulas (ex: `val1 >= val2 and val3 <> val4`).  Comparison operators doc.  Operators overview doc with a table of logical operator (and, or, not, xor).

 

At this point, with functions, variables, If statements, and access to Excel functions, there is so much you can do in terms of pure calculation.  With these things, instead of having 11 columns of intermediate steps to get one final value, and copy-paste-modifying it every time you want to use it in a new place, you can just call a reusable VBA function.

 

Exit Statement

You can exit a procedure early (before execution reaches the bottom of the procedures) by using the Exit statement.  Exit statements are usually used inside an If statement so that only certain conditions trigger an early exit.  Here is an example...

Function DoSomething() As Boolean

    DoSomething = false

    If Not TryStep1() Then

      Debug.Print "could not do step1, aborting"

      Exit

    End If

    If Not TryStep2() Then

      Debug.Print "could not do step2, aborting"

      Exit

    End If

    DoSomething = TryStep3()

End Sub

 

Conceptually, what is going on is that if we encounter an error in one of the steps, then there is no point in doing the following steps, so exit early.

Loops

Do Loops

Do loops (doc) let you repeat some code while a condition is true or until a condition is true (these are opposite and depend on which keyword you use). You can put the condition at the beginning of the loop to be evaluated right away, or you can put the condition at the bottom of the loop to make sure that the loop body executes at least once.

 

Basic structure with condition at top...

Do {While or Until} {condition}

  {statements}

Loop

And basic structure with condition at bottom (thus loop body executes at least once)...

Do

  {statements}

Loop {While or Until}

You can also use `Exit Do` to exit the Do loop immediately, usually inside an If statement.

For-Next Loops

For-Next loops (doc) are a good way to repeat some code a specified number of times.  Basic structure is...

For {someVar} = {start} To {end}

  {statements that may use someVar}

Next

 

The body of the For loop will execute multiple times with the loop control variable (someVar) eventually taking on all the values from start to end.

Some additional things that are possible:

• You can change use Step to choose a non-1 increment. Ex: `For someVar = 0 To 100 Step 10`.
• You can use an `Exit For` statement to exit the For loop early, usually inside an If statement.

For-Each Loops

There are also For-Each loops (doc) that let you loop every element in an array or collection.

For Each {someItem} In {someArray}

  {statements that may use someItem}

Next

 

Arrays

Arrays are a data type capable of holding multiple values, and you access values by providing an integer index. (Docs on declaring and using arrays.)

When declaring arrays, you tell VBA the LAST INDEX of the array dimension, not the length.  The following code declares arrays that have 10 items or 10 x 10 items, not 9.

 

Dim someArray1d(9) As Long

Dim someArray2d(9,9) As Long

 

By default, VBA array indices start at 0, but you can have them start at 1 or any integer of you choosing.  I recommend always going with the default of 0.

If you want to have array dimensions that are based on variables/run-time-values, then you need to do something like this...

Dim someArray() As Long

ReDim someArray(someVar1, someVar2)

 

And similarly, I have been using `someArray() As SomeDataType` for procedure array parameters and `As SomeDataType()` for procedure return types.  This works for arrays of any dimensionality, I believe.

 

To read or write to a value inside an array, use an integer index inside parens...

 

someVar = someArray1d(3)

someArray2d(someIndex1, someIndex2) = someValue

Note that `someVar = someArray` will do a (shallow?) array copy.  I don't currently know of a way to make two variables point to the same array without wrapping the array in a class and pointing to the class.

 

Unfortunately, VBA does not have a one-step way to get the length of an array, but it does have UBound and LBound to get the min and max indices, so you can do `UBound(someArray) - LBound(someArray) + 1` to get the length.  You might also do things like `For someVar = 0 To UBound(someArray)` where the upper bound is directly useful.  Sidenote: the Len function is for strings and will not tell you the number of items in an array.

If an Excel cell formula uses a function that returns an array, then Excel does a really nice thing of "spilling" the values to adjacent cells as needed. (Or maybe if you don't have Microsoft 365, you have to mess with Ctrl+Shift+Enter? Article.)

Side note: there are also "parameter arrays" (doc) where you can pass an arbitrary number of arguments to a procedure and those arguments get put in a single array for the procedure to use.

NOT-SO-BASIC STUFF BELOW HERE

Class Modules

You can make traditional user-defined classes with a class module (third party guide, vbaplanet article).

 

To create,  Main Menu → Insert → Class Module.

You can have public/private member variables, properties, and methods/functions.  Here is an example class module

Option Explicit

 

Public SomePublicMemberVariable As Long

Privage SomeExplicitlyPrivateMemberVariable As Long

SomeImplicitlyPrivateMemberVariable As Long

 

Public Property Get SomePublicProperty() As Long

     SomePublicProperty = someValue

End Property

Public Function SomePublicFunction As Long

    SomePublicFunction = someValue

End Function

 

Private Sub Class_Initialize()

    ' like a default constructor
    Debug.Print "hi"
End Sub

 

Private Sub Class_Terminate()

    ' like a default destructor
    Debug.Print "bye"
End Sub

Inside property and function definitions, you can refer to member variables/properties/functions without any prefix.  `Me` is like the self/this object in other languages.  I don't know of any way to do a non-default constructor.

Some usage example of a class module named Coord...

' construct new object

Dim coord1 As New Coord

coord1.x = 3 ' access member variable or property

someVar = coord1.Magnify(2) ' use member function

' just declare, not construct

Dim coord2 As Coord

Set coord2 = functionThatReturnsACoord()

Simple Composite Types

If all you need is a grouping of variables (like a struct in C), there are user-defined types with the Type statement.  Example:

Public Type SomeType

    SomeField As Long

    AnotherField As Double

End Type

Dim someVar as SomeType ' do not need `New`

someVar.SomeField = 1

Enums

VBA has the enum statement, like so...

Enum Style
  Invalid = -1

  Solid = 0

  Hollow = 1

  Bumpy ' implicitly 2
End Enum

Enum values can be implicit or have a constant expression, which gets evaluated to a Long.  You can use enums like `someVar = Style.Solid` (see vbaplanet page).  You should get intellisense suggestions for the enum values after you type in the enum type name and `.`.