February 19, 2013 in VBA
Identifiers in VBA
Visual Basic for Applications does not provide a mechanism for this. Reflecting its origins more in procedural than object-oriented programming, all entity definitions in VBA exist in the global scope. In practical terms: Any function or sub defined in a standard module can be called just by typing the name, no prefixes or qualifiers required. Likewise any type can be identified by the bare name. To resolve ambiguities when multiple entities have the same name, you may type the name of the library. For a member of a standard module you may also type the name of the module. But that’s as deep as it goes.
' Example 1. Normal usage : bare identifiers ' 1.A Declaring as type 'Recordset' with no additional qualifiers Dim rs As Recordset ' 1.B Calling a method myResult = SomeFunction(1, "Hello") ' Example 2. Qualified by library name Dim rs As DAO.Recordset myResult = MyProject.SomeFunction(1, "Hello") ' Example 3. Qualified by module name, with and without library name myResult = MyModule.SomeFunction(1, "Hello") myResult = MyProject.MyModule.SomeFunction(1, "Hello")
Why namespaces are helpful
I’ll leave the articles linked above to provide the more in-depth explanation of why namespaces are so useful. In a nutshell, though:
Concise and semantic identifiers
Without namespaces, you sometimes have to make long, cumbersome identifiers to keep them unique. Since long cumbersome names are a pain to type (even with autocomplete) and take up too much space, they get shortened to cryptic abbreviations. So you’re stuck with either
smRLngBSpecId, niether of which is terribly user friendly.
You’re also out of luck if you want to use a word that is already taken in a built-in global method, property, or constant such as Log or Left. Although you can use library or module names to qualify the identifiers as described above, doing say may break existing code that uses the bare identifiers for the built in members.
For example, I love working with arrays in VBA — when I have a few extra utilities I’ve built. For frequently used utility functions I want the names to be short, descriptive and intuitive, like Pop, Push, Concat, and Count. I also have some utility functions for working with strings, and at least one of them (Concat) already clashes with a name I want to use for a function that works on arrays.
Without qualifiers these short identifiers can lose their helpfulness by being too vague (not descriptive enough for another programmer) or worse ambiguous (can mean more than one specific thing). Namespaces provide concise context that clarifies the meaning for programmer and compiler alike:
Concat(a, b, c) ' It's not clear what this does Text.Concat(a, b, c) ' This obviously concatenates strings Arrays.Concat(a, b, c) ' This obviously concatenates arrays Arrays.Left(a, 3) ' This obviously takes the left-most elements in an array. If ' defined in a standard module, though, I'll have to prefix the ' built-in Left function with "Strings." every time I use it
Filtering of autocomplete members
In any case it can be annoying to write code when every autocomplete list has a thousand possible members, which can be the case when everything is exposed at the global level. It increases the chances of silly mistakes and just takes more wasted mental effort to constantly sift through huge lists of possible members. Using namespaces can really help this by restricting the number of matching items in a given context.
Emulating namespaces in VBA
The solution I use consists of a few simple steps:
- Define members in a class module.
- Set the Instancing property of the class module to “2 – PublicNotCreatable”:
- In a standard module, declare a public variable that is an instance of the class module
I realize this is not very OOP because I’m defining what are intended to be static methods in class modules, where they normally should go in standard modules. But in this case the workaround is useful enough that I’m willing to accept a bit of unorthodoxy. Using this method you can also nest namespaces by declaring inner namespaces as public variables in the outer namespaces’ class definitions.
An extended example of namespaces in VBA
My naming convention is to prefix class modules that are really being used as namespaces with “ns”, but you can do whatever you want. Here I’ll define two top-level namespaces and one nested namespace, and in all three I’ll define a method called Concat.
In class module nsIO_File:
Private fso As New FileSystemObject Public Sub Concat(ByVal TargetFile As String, ParamArray AddFiles()) Dim vFile, tsAppend As TextStream With fso.OpenTextFile(TargetFile, ForAppending) For Each vFile In AddFiles Set tsAppend = fso.OpenTextFile(vFile, ForReading) .Write tsAppend.ReadAll tsAppend.Close Next .Close End With End Sub
In class module nsIO:
Public File As New nsIO_File
In class module nsArrays:
Public Function Concat(ParamArray Arrs()) Dim arrOut, vItem, vInner, cnt As Long arrOut = Array() For Each vItem In Arrs If IsArray(vItem) Then For Each vInner In vItem ReDim Preserve arrOut(0 To cnt) arrOut(cnt) = vInner cnt = cnt + 1 Next Else ReDim Preserve arrOut(0 To cnt) arrOut(cnt) = vItem cnt = cnt + 1 End If Next Concat = arrOut End Function
In class module nsText:
Public Function Concat(ParamArray Values()) As String Concat = Join(Values, "") End Function
Finally, in any standard module:
Public Arrays As New nsArrays Public Text As New nsText Public IO As New nsIO
Now anywhere in my project — or any project that references this project — I can concisely and unambiguously use any of my Concat methods:
' Top-level namespace allTables = Arrays.Concat(sysTables, userTables, "myTable") strSQL = Text.Concat("SELECT * FROM ", allTables(3), " WHERE ID=0") ' Nested namespace: IO.File.Concat "C:\temp\src.txt", "C:\temp\add1.txt", "C:\temp\add2.txt"
Languages that provide namespaces also provide ways to “import” or “use” a namespace in the context of a particular file, so that the members of the selected namespace can be called without having to type the whole namespace stack. In some languages you can even declare a custom alias for the namespace. You can accomplish the same thing in VBA by just declaring a local variable with the type of whatever target namespace class you want to use:
Dim F As New nsIO_File F.Concat "C:\temp\src.txt", "C:\temp\add1.txt", "C:\temp\add2.txt"
Namespaces provide a very helpful mechanism for making code more concise, semantic, and modular. VBA does not support namespaces directly, but they can be emulated using class modules.