Hidden features of VBA

Which features of the VBA language are either poorly documented, or simply not often used?

This trick only works in Access VBA, Excel and others won't allow it. But you can make a Standard Module hidden from the object browser by prefixing the Module name with an underscore. The module will then only be visible if you change the object browser to show hidden objects.

This trick works with Enums in all vb6 based version of VBA. You can create a hidden member of an Enum by encasing it's name in brackets, then prefixing it with an underscore. Example:

Public Enum MyEnum
    meDefault = 0
    meThing1 = 1
    meThing2 = 2
    meThing3 = 3
    [_Min] = meDefault 
    [_Max] = meThing3 
End Enum

Public Function IsValidOption(ByVal myOption As MyEnum) As Boolean
    If myOption >= MyEnum.[_Min] Then IsValidOption myOption <= MyEnum.[_Max]
End Function

In Excel-VBA you can reference cells by enclosing them in brackets, the brackets also function as an evaluate command allowing you to evaluate formula syntax:

Public Sub Example()
    [A1] = "Foo"
    MsgBox [VLOOKUP(A1,A1,1,0)]
End Sub

Also you can pass around raw data without using MemCopy (RtlMoveMemory) by combining LSet with User Defined Types of the same size:

Public Sub Example()
    Dim b() As Byte
    b = LongToByteArray(8675309)
    MsgBox b(1)
End Sub

Private Function LongToByteArray(ByVal value As Long) As Byte()
    Dim tl As TypedLong
    Dim bl As ByteLong
    tl.value = value
    LSet bl = tl
    LongToByteArray = bl.value
End Function

Octal & Hex Literals are actually unsigned types, these will both output -32768:

Public Sub Example()
    Debug.Print &H8000
    Debug.Print &O100000
End Sub

As mentioned, passing a variable inside parenthesis causes it to be passed ByVal:

Sub PredictTheOutput()
    Dim i&, j&, k&
    i = 10: j = i: k = i
    MySub (i)
    MySub j
    MySub k + 20
    MsgBox Join(Array(i, j, k), vbNewLine), vbQuestion, "Did You Get It Right?"
End Sub

Public Sub MySub(ByRef foo As Long)
    foo = 5
End Sub

You can assign a string directly into a byte array and vice-versa:

Public Sub Example()
    Dim myString As String
    Dim myBytArr() As Byte
    myBytArr = "I am a string."
    myString = myBytArr
    MsgBox myString
End Sub

"Mid" is also an operator. Using it you overwrite specific portions of strings without VBA's notoriously slow string concatenation:

Public Sub Example1()
    ''// This takes about 47% of time Example2 does:
    Dim myString As String
    myString = "I liek pie."
    Mid(myString, 5, 2) = "ke"
    Mid(myString, 11, 1) = "!"
    MsgBox myString
End Sub

Public Sub Example2()
    Dim myString As String
    myString = "I liek pie."
    myString = "I li" & "ke" & " pie" & "!"
    MsgBox myString
End Sub

There is an important but almost always missed feature of the Mid() statement. That is where Mid() appears on the left hand side of an assignment as opposed to the Mid() function that appears in the right hand side or in an expression.

The rule is that if the if the target string is not a string literal, and this is the only reference to the target string, and the length of segment being inserted matches the length of the segment being replaced, then the string will be treated as mutable for the operation.

What does that mean? It means that if your building up a large report or a huge list of strings into a single string value, then exploiting this will make your string processing much faster.

Here is a simple class that benefits from this. It gives your VBA the same StringBuilder capability that .Net has.

' Class: StringBuilder

Option Explicit

Private Const initialLength As Long = 32

Private totalLength As Long  ' Length of the buffer
Private curLength As Long    ' Length of the string value within the buffer
Private buffer As String     ' The buffer

Private Sub Class_Initialize()
  ' We set the buffer up to it's initial size and the string value ""
  totalLength = initialLength
  buffer = Space(totalLength)
  curLength = 0
End Sub

Public Sub Append(Text As String)

  Dim incLen As Long ' The length that the value will be increased by
  Dim newLen As Long ' The length of the value after being appended
  incLen = Len(Text)
  newLen = curLength + incLen

  ' Will the new value fit in the remaining free space within the current buffer
  If newLen <= totalLength Then
    ' Buffer has room so just insert the new value
    Mid(buffer, curLength + 1, incLen) = Text
  Else
    ' Buffer does not have enough room so
    ' first calculate the new buffer size by doubling until its big enough
    ' then build the new buffer
    While totalLength < newLen
      totalLength = totalLength + totalLength
    Wend
    buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen)
  End If
  curLength = newLen
End Sub

Public Property Get Length() As Integer
  Length = curLength
End Property

Public Property Get Text() As String
  Text = Left(buffer, curLength)
End Property

Public Sub Clear()
  totalLength = initialLength
  buffer = Space(totalLength)
  curLength = 0
End Sub

And here is an example on how to use it:

  Dim i As Long
  Dim sb As StringBuilder
  Dim result As String
  Set sb = New StringBuilder
  For i = 1 to 100000
    sb.Append CStr( i)
  Next i
  result = sb.Text

Hidden features of VBA

Related

Recent Posts