VBA Notes

Introduction

My recent programming focuses on Visual Studio, .NET (still 4.8) and C#. My recent excursions in VBA ( Visual Basic for Applications ) were necessary to implement some Word and Excel automation. In this article I will report on some of the aspects that are either noteworthy or useful but hard to uncover. It will not be comprehensive, nor objective. I'm reporting how I look at it, biased by C-like languages and personal preferences.

VBA, the Language

Visual Basic is an ancient language; it became available in 1991 and culminated in VB6 (1998). It later somehow got reincarnated inside the .NET Framework, where it got coined VB.NET. VB6 also is the foundation of the current VBA language, as found in e.g. the Office 365 suite.

Some characteristics:

1. It is case-insensitive, and its editor harmlessly turns most everything in PascalCase.

2. It is line oriented, has an unusual comment starter, no semi-colon line terminator, and no braces; instead, most block structuring keywords (Sub, For, Do…) have a matching closer End Keyword.

3. It offers explicit data types and does encourage their use: starting every module with Option Explicit makes it refuse all undeclared variables, which would otherwise get type Variant. Variants are larger, slower, make code easier to write but harder to debug. And they are contagious—any expression involving one becomes a Variant itself.

• Advice: use explicit declarations, e.g. Dim identifier As type.

• Warning: types are small (Integer = 2B, Long = 4B), advice: don't use Integer

• Missing: a single line declaration + initialization (which Const obviously does offer).

• Missing: a char type, use String.

• Advice: use the explicit-string variant of functions: Left() returns a Variant, Left$() returns a String.

4. It guarantees initial values for variables: 0 for numbers, false for Booleans, "" for Strings, Nothing for Objects, Empty for Variants.

5. It offers very good string support: all strings are UTF-16, all string functions understand UTF-16, so Len(string) counts characters, not bytes.

• Strong advice: don't use Asc() and Chr(), they are fossils that use 8-bit ANSI encoding. You want AscW() and its cousin ChrW().

• Trick: String looks like a value type (it refuses to be a LHS in a Set statement), but it is nullable, not by comparing/assigning Nothing, but by comparing/assigning vbNullString.

• Good to know: in Win32 these strings are type BSTR.

6. It has normal loop constructs, with an optional break (Exit Keyword), but no continue statement.

• Typical solution: use if boolexpr then goto NEXT_whatever
and a label NEXT_whatever: right before the end of the loop.

7. It offers typical If … Then, ElseIf, Else, EndIf flow control, as well as a pretty good Select Case, which breaks on every case, and has a default: Case Else

• Warning: logical operators And and Or have no short-circuitry behavior, they always evaluate both sides. AndAlso and OrElse are not provided.

• Goody: with the ternary operator (similar to ?: in C%#) one often can avoid a five-line If/Else/EndIf construct: IIf(boolexpr, exprTrue, exprFalso) ; alo without short-circuiting!

8. It offers bitwise operators And, Or and Xor.

9. It offers modules, and some scoping with Public and Private.

10. It has both Sub and Function procedures, where a Sub is like a void method, a Function any other method. To specify the return value, assign to the Function name, as that is a local variable one gets for free; assigning to it does not exit the function.

• Calling a Sub expects no parentheses, they are allowed when there is only one argument is passed (as they then get interpreted as part of the expression, not as delimiters). Turning a Sub into a Function typically requires adapting all calling points.

• Optional parameters need to be flagged with the Optional keyword.

11. It has classes, with some limitations. They sit in special "class modules", and don't offer a way to implement static data inside the class. Work-arounds do exist.

• Warning: when a variable is typed as some Object, it only accepts assignments using the Set statement, as in Set range = paragraph.Range (example from the Word document model)

• Warning: garbage collection is based on reference counting.You can help by setting objects to Nothing. Be aware that circular links may result in surviving islands, hence circular links should be broken explicitly.

12. It offers some GUI functionality. One can define Forms and have them shown either modal or modeless.

• Warning: Inside Word, the FormBorderStyle is fixed; no way to create a ToolBox-like form.

13. VBA will run in a single thread, the same thread its host (e.g. Word) uses most of the time. As a result:

• A modal dialog will halt all VBA and Word action until it gets closed.

• And a modeless Form will hesitate to do anything while VBA is spinning the CPU cycles. Hence:

• Advice: in a modeless Form inserting DoEvents is often needed to allow the form to actually obey your instructions.

• Trick: in a modeless Form having a button that launches a (tiny) modal dialog may offer a pause, so the form can be read.

VBE, the Editor/Debugger

1. Annoying: one cannot start entering a line of code, then suddenly click elsewhere to copy some identifier or expression, as the partial line gets parsed and rejected right away.

• So either copy first, or start with a quote, i.e. enter a comment line, and uncomment before hitting enter.

2. Annoying: putting aside some code is not straightforward as there is no block comment, no multi-line /* …*/ C-like languages offer, no #if false … #endif.

• I tend to use Notepad as a scratchpad!

3. Annoying: the editor does not show line numbers. Line numbering would ease scrolling large files, and better tracing/error reporting.

• Use small procedures, and small (hence many) modules; which also is not ideal…

4. Very annoying: sometimes VBE does not like your code, but does not automatically tell you where the friction points are; and when it does it only shows one.

• Advice: Use menu Debug/Compile, that will show the first friction point.

5. You can get to see a stack trace by hitting CTRL/L. It is very modest in the information it gives, and does not resize!

Some Tooling

DumpString is a little subroutine that outputs a String as a series of hexadecimal numbers. There are two ways to call it:

DumpString "Here is ", "Abc"	Here is 41 62 63
DumpString "Abc"	41 62 63

 

The code has three parts:

1. The parameter juggler: technically optional parameters must be at the righthand side, but functionally we want the first parameter to be optional. So when only one parameter is given, we pass its value on to the second parameter. Note the use of vbNullString to detect absence of the "txt" parameter.

2. The loop iterating over the characters in "txt" and converting them to their hex representations (either X2 or X4 format).

3. Calling a Log method to actually output them (to a ListBox on a form, to a file, whatever)

' the message is optional, the last parameter will be dumped as hex numbers
Sub DumpString(msg As String, Optional txt As String = vbNullString)
    Dim buf As String, i As Long, code As Long
    If txt = vbNullString Then
        txt = msg
        msg = ""
    End If
    buf = msg
    For i = 1 To Len(txt)
        code = AscW(Mid$(txt, i, 1))
        If code < &H100 Then
            buf = buf & " " & Right$("0" & Hex$(code), 2)
        Else
            buf = buf & " " & Right$("000" & Hex$(code), 4)
        End If
    Next i
    Log buf & txt
End Sub

Epilogue

VBA may not think like C, but it still gets real work done. Once you learn its quirks and tame its softer edges, it becomes a reliable tool for shaping Word and Excel into something that actually obeys you. If these notes help bridge the gap between strict code and everyday Office tasks, then they’ve done their job.