Hidden features of VBA

Question

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

Lynching

@bbqchickenrobot I don't have much of a choice, really. In small doses it isn't that bad, anyway. – Lynching

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

Siding

Answer 2

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

Limassol

Answer 3

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.

Turgot

"Folks I know who've used Office products for years have no idea it's even a part of the suite." - thats what keeps part of my business alive, the fact that I get called to do this sort of work. If they learned how to do it (and/or got good at it) - I would be out of work. :-) – Valuer

Me as well. I feel VBA is going to turn into COBOL of yesteryear. VBA applications will stil be here and need to be updated but people will have moved on to sexier languages. – Turgot

Class structures on MSDN? First you have to goto "MyClass" to get a useless overview and seldom used examples, then "MyClass Methods" to get a list of methods then "MyClass Properties" to find the properties... then for hiearchy.... you know MSDN is usually my last resort. – Dunkirk

"but the Object Browser is my secret weapon" It is the secret weapon of .NET also. – Forging

@mandroid: definitely. There's a lot of work for skilled .NET developers who are willing to help port legacy VBA apps. There are many small to medium businesses who are utterly dependent upon business systems built on top of Office using VBA. – Meal

"Folks I know who've used Office products for years have no idea it's even a part of the suite." so true !! There's tons of features in MS Office, power users have only less than 10% of the total features. – Tent

Answer 4

It's not a feature, but a thing I have seen wrong so many times in VBA (and VB6): Parenthesis added on method calls where it will change semantics:

Sub Foo()

    Dim str As String

    str = "Hello"

    Bar (str)
    Debug.Print str 'prints "Hello" because str is evaluated and a copy is passed

    Bar str 'or Call Bar(str)
    Debug.Print str 'prints "Hello World"

End Sub

Sub Bar(ByRef param As String)

    param = param + " World"

End Sub

Merline

+1 I have yet to find a single example where the Call keyword is ever actually needed. Call foo(bar) can always be replaced with foo bar so Call appears to be redundant. Going by the VBA questions on SO, many people seem unaware that parentheses aren't need to call a Sub so we could call this a poorly understood feature – Holley

If Bar is a function and you use Bar(str) instead of Bar (str) the parenthesis don't override the ByRef keyword. – Cephalonia

@Kuyenda: The space is inserted automatically by the VBA-IDE when the code is compiled. How did you run the Bar(str) version? – Merline

THIS IS A FEATURE OF THE LANGUAGE. Adding parentheses allow you to pass the argument ByVal instead of ByRef. See "How to: Force an Argument to Be Passed by Value" at msdn.microsoft.com/en-us/library/chy4288y.aspx for more details. – Forging

@AMissico: Agreed, but the article you link to refers to VB.NET where the situation is far better than in VB6/VBA, because a method's paramaters always have to be enclosed in parenthesis. Therefore, when reading Bar((str)) vs. Bar(str) (incontrast to Bar(str) vs. Bar str in VB6/VBA) it is much more obvious that this is no longer a ByRef call. – Merline

@0xA3: I think the languages are different enough that both are obvious. Just like the use/non-use of Set. – Forging

Here VB6 reference, "The simplest way to turn a variable into an expression is to enclose it in parentheses." at msdn.microsoft.com/en-us/library/aa263527(VS.60).aspx – Forging

Answer 5

Possibly the least documented features in VBA are those you can only expose by selecting "Show Hidden Members" on the VBA Object Browser. Hidden members are those functions that are in VBA, but are unsupported. You can use them, but microsoft might eliminate them at any time. None of them has any documentation provided, but you can find some on the web. Possibly the most talked about of these hidden features provides access to pointers in VBA. For a decent writeup, check out; Not So Lightweight - Shlwapi.dll

Documented, but perhaps more obscure (in excel anyways) is using ExecuteExcel4Macro to access a hidden global namespace that belongs to the entire Excel application instance as opposed to a specific workbook.

Stogner

+1 for ExecuteExcel4Macro trick, I was wondering how you did that from code. – Nutting

Answer 6

Hidden Features

1. Although it is "Basic", you can use OOP - classes and objects
2. You can make API calls

Ulpian

To any who say that the OO in VBA is not OO, get over it. Interface inheritance is also inheritance - it's just a different kind. – Trueman

I love the (albeit limited) support for OO in VBA. Especially the ability to implement multiple interfaces. – Taction

These two features are hardly hidden, are they? – Jocelynjocelyne

the word hidden is subjective. I know alot of VBA users (not necessarily developers) who are unaware of these two features but still use the language alot. – Nutting

Answer 7

Dictionaries. VBA is practically worthless without them!

Reference the Microsoft Scripting Runtime, use Scripting.Dictionary for any sufficiently complicated task, and live happily ever after.

The Scripting Runtime also gives you the FileSystemObject, which also comes highly recommended.

Start here, then dig around a bit...

http://msdn.microsoft.com/en-us/library/aa164509%28office.10%29.aspx

Method

Answer 8

You can implement interfaces with the Implements keyword.

Lynching

Answer 9

Typing VBA. will bring up an intellisense listing of all the built-in functions and constants.

Berneta

Answer 10

• Save 4 whole keystrokes by typing debug.? xxx instead of debug.print xxx.
• Crash it by adding: enum foo: me=0: end enum to the top of a module containing any other code.

Bombsight

Answer 11

With a little work, you can iterate over custom collections like this:

' Write some text in Word first.'
Sub test()
    Dim c As New clsMyCollection
        c.AddItems ActiveDocument.Characters(1), _
            ActiveDocument.Characters(2), _
            ActiveDocument.Characters(3), _
            ActiveDocument.Characters(4)

    Dim el As Range
    For Each el In c
        Debug.Print el.Text
    Next
    Set c = Nothing
End Sub

Your custom collection code (in a class called clsMyCollection):

Option Explicit

Dim m_myCollection As Collection

Public Property Get NewEnum() As IUnknown
    ' This property allows you to enumerate
    ' this collection with the For...Each syntax
    ' Put the following line in the exported module
    ' file (.cls)!'
    'Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = m_myCollection.[_NewEnum]
End Property

Public Sub AddItems(ParamArray items() As Variant)

    Dim i As Variant

    On Error Resume Next
    For Each i In items
        m_myCollection.Add i
    Next
    On Error GoTo 0
End Sub

Private Sub Class_Initialize()
    Set m_myCollection = New Collection
End Sub

Lynching

Answer 12

Support for localized versions, which (at least in the previous century) supported expressions using localized values. Like Pravda for True and Fałszywy (not too sure, but at least it did have the funny L) for False in Polish... Actually the English version would be able to read macros in any language, and convert on the fly. Other localized versions would not handle that though.

FAIL.

Cellini

Answer 13

IsDate("13.50") returns True but IsDate("12.25.2010") returns False

This is because IsDate could be more precisely named IsDateTime. And because the period (.) is treated as a time separator and not a date separator. See here for a full explanation.

Predilection

Answer 14

The VBE (Visual Basic Extensibility) object model is a lesser known and/or under-utilized feature. It lets you write VBA code to manipulate VBA code, modules and projects. I once wrote an Excel project that would assemble other Excel projects from a group of module files.

The object model also works from VBScript and HTAs. I wrote an HTA at one time to help me keep track of a large number of Word, Excel and Access projects. Many of the projects would use common code modules, and it was easy for modules to "grow" in one system and then need to be migrated to other systems. My HTA would allow me to export all modules in a project, compare them to versions in a common folder and merge updated routines (using BeyondCompare), then reimport the updated modules.

The VBE object model works slightly differently between Word, Excel and Access, and unfortunately doesn't work with Outlook at all, but still provides a great capability for managing code.

Fishwife

Answer 15

VBA supports bitwise operators for comparing the binary digits (bits) of two values. For example, the expression 4 And 7 evaluates the bit values of 4 (0100) and 7 (0111) and returns 4 (the bit that is on in both numbers.) Similarly the expression 4 Or 8 evaluates the bit values in 4 (0100) and 8 (1000) and returns 12 (1100), i.e. the bits where either one is true.

Unfortunately, the bitwise operators have the same names at the logical comparison operators: And, Eqv, Imp, Not, Or, and Xor. This can lead to ambiguities, and even contradictory results.

As an example, open the Immediate Window (Ctrl+G) and enter: ? (2 And 4) This returns zero, since there are no bits in common between 2 (0010) and 4 (0100).

Nagel

Answer 16

Deftype Statements

This feature exists presumably for backwards-compatibility. Or to write hopelessly obfuscated spaghetti code. Your pick.

Predilection

Let's not be too harsh on VBA now. This is straight-up backwards compatibility stuff. Once upon a time, bytes mattered. (This is the earliest VBA ancestor with DEFxxx statements I can find in 60s of Googling: en.wikipedia.org/wiki/GW-BASIC) – Chantalchantalle

It looks like IBM BASICA and its earlier variants supported it too. See p.4-73 in retroarchive.org/dos/docs/basic_ref_1.pdf. Also check out p.4-68; I wish I'd known about DEF FN back then. Good times... – Chantalchantalle