Hidden features of VBA

Question

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

Answer 1

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