Hidden features of VBA

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 EnumPublic 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 SubPrivate Function LongToByteArray(ByVal value As Long) As Byte()    Dim tl As TypedLong    Dim bl As ByteLong    tl.value = value    LSet bl = tl    LongToByteArray = bl.valueEnd Function

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

Public Sub Example()    Debug.Print &H8000    Debug.Print &O100000End 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 SubPublic Sub MySub(ByRef foo As Long)    foo = 5End 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 myStringEnd 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 myStringEnd SubPublic Sub Example2()    Dim myString As String    myString = "I liek pie."    myString = "I li" & "ke" & " pie" & "!"    MsgBox myStringEnd 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: StringBuilderOption ExplicitPrivate Const initialLength As Long = 32Private totalLength As Long  ' Length of the bufferPrivate curLength As Long    ' Length of the string value within the bufferPrivate buffer As String     ' The bufferPrivate Sub Class_Initialize()  ' We set the buffer up to it's initial size and the string value ""  totalLength = initialLength  buffer = Space(totalLength)  curLength = 0End SubPublic 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 = newLenEnd SubPublic Property Get Length() As Integer  Length = curLengthEnd PropertyPublic Property Get Text() As String  Text = Left(buffer, curLength)End PropertyPublic Sub Clear()  totalLength = initialLength  buffer = Space(totalLength)  curLength = 0End 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

VBA itself seems to be a hidden feature. Folks I know who've used Office products for years have no idea it's even a part of the suite.

I've posted this on multiple questions here, but the Object Browser is my secret weapon. If I need to ninja code something real quick, but am not familiar with the dll's, Object Browser saves my life. It makes it much easier to learn the class structures than MSDN.

The Locals Window is great for debugging as well. Put a pause in your code and it will show you all the variables, their names, and their current values and types within the current namespace.

And who could forget our good friend Immediate Window? Not only is it great for Debug.Print standard output, but you can enter in commands into it as well. Need to know what VariableX is?

?VariableX

Need to know what color that cell is?

?Application.ActiveCell.Interior.Color

In fact all those windows are great tools to be productive with VBA.