Understanding VBA

If you work with any of the Microsoft Office products then you probably have some interaction with VBA even if you haven't programmed it directly. VBA is short for Visual Basic for Applications, and is the code generated in the background when recording a macro in most of the Microsoft Office products. VBA has its advantages and disadvantages, but if you need to automate something in Excel, Word, Access, or Outlook then VBA is your quickest route to get there.

If you are familiar with programming than VBA will seem a bit off. Even if you are familiar with programming in Visual Basic VBA will have some oddities that may frustrate you to no end. It is defiantly possible to program Office functionality using another language, but this will require the additional step of creating a COM Add-In. VBA allows you to skip this step and reference your code directly from the application via either a custom Ribbon Tab, or shortcut combination.

As for some of the oddities, here are the ones that I have run across so far:

Functions return a value based on the name of the function not a variable values set within the function. This one threw me the first time I ran into it, but essentially if you have a function called "Foo" then you set "Foo" equal to the results of you function and call return. Here's a quick code sample:

Function Foo(ByVal x as Integer) as Integer

x = x + 1

Foo = x

Return

End Function

Variable based array dimensioning has to be done using the ReDim command and cannot be done during the initial dimensioning. This one took me some time to resolve as well because it doesn't make any sense. VBA allows you to set the array length with a static value easily:

Dim MyArray(10) as string

However you cannot use a variable:

Dim ArrayLen as Integer

ArrayLen = 10

Dim MyArray(ArrayLen) as String

If you do want to set the length with a variable you have to use the ReDim to assign the value:

Dim ArrayLen as Integer

ArrayLen = 10

Dim MyArray() as String

ReDim MyArray(ArrayLen) as String

Another one that has caught me several times is the switching back and forth between 0 and 1 as the initial count start. If I want to do something similar to a substring search I would expect that the starting location within the string would be 0 if I wanted to start the search from the beginning of the string, not the case. 1 is the value used for the start of the string in the InStr function in VBA.

InStr(0, MyString, MySearchString) ' Will Error out

InStr(1, MyString, MySearchString) ' Works

Thankfully Arrays start their count at 0, so it should match other programming languages you may have used. This may be prevalent in other functions within VBA, but this one catches me all the time.

Getting the variable length in VBA is also different. For the longest time I was using .NET form of variable.Length, and there are certain functions or variable types that can use that. However, if you want to get the length of a regular string variable you need to use the Len() function.

I'm sure there are many more of these, and I will continue to update this list as I run into them, please feel free to add any of the quirks you have found in the comments.