Object Oriented VBA: Overloading


Overloading is where the same method name can be called, but the type and number of arguments can differ with each call. Depending on the combination and order of those arguments, different code will be executed. For example:

Public Sub MyMethod(p As Integer)
…some code here…
End Sub

Public Sub MyMethod(p As String)
…some code here…
End Sub

Public Sub MyMethod(p As Integer, q as String)
…some code here…
End Sub

Here, MyMethod is overloaded because, depending on the data type (Integer or String) of the parameter p, and the presence or otherwise of the parameter q, a different set of code will run.

Overloading is a feature of statically-typed languages. Statically-typed languages require both (a) the explicit declaration of a variable, and (b) the declaration to be of a certain, fixed type, eg. integer, float, string, etc. Once a variable has been declared to be of a type, it can only be bound to objects of that type. For example:

Dim MyVar As Integer
MyVar = 6

is perfectly acceptable, but this will fail with a statically-typed language:

Dim MyVar As Integer
MyVar = “6”

Of course, this will work:

Dim MyVar As Integer
MyVar = CInt(“6”)

VBA is not a statically-typed language. It is dynamically-typed. Consider the ‘Variant’ data type in this code snippet:

Option Explicit
Public Sub Test()
Dim var As Variant
var = 6
Debug.Print TypeName(var)
var = “6”
Debug.Print TypeName(var)
End Sub

If you run this, you’ll see the intermediate window produces:

Integer
String

The variable “var” can (and does) change its data type during code execution. You can repeat this example but with ‘Option Explicit’ removed as well as removing the variable declaration (Dim var as Variant). You’ll get the same result. By design, VBA does not require explicit declaration of a variable’s type and, furthermore, you can still do so and yet change that type at run time (by declaring the Variant data type).

Now that we’ve shown VBA to be a dynamically typed language, it should come as no surprise to learn that it does not feature “Overloading”. I’ve used quotation marks here because I want to emphasise that the popular conception of “Overloading” (as demonstrated at the start of this post) is really down to semantics. Overloading really is just a means to call the same function name but pass in different parameter types so that code execution is altered. That is all. In VBA this can be achieved using a combination of (a) Optional parameters and/or ParamArray, and (b) branching (Select Case or If-Then-Else). The advantage to “Overloading” is singular: it keeps code clean. One function does one thing rather than having a single function littered with If statements trying to figure out what to do based on what was passed in. Cleaner code makes for easier maintenance. The challenge for the VBA Developer is to implement the concept (Overloading) in a way that is easy to maintain. Here is a trivial example of overloading in VBA:

Option Explicit

Public Sub Harness()
Call Test
Call Test(6)
Call Test(“7”)
Call Test(6, “7”)
End Sub

Private Sub Test(ParamArray params() As Variant)
Dim numParams As Integer: numParams = UBound(params) + 1
Select Case numParams
Case 0
Debug.Print “No Params”
‘…some code here…
Case 1
Debug.Print “One Parameter: ” & CStr(params(0)) & ” (” & TypeName(params(0)) & “)”
‘…some code here…
Case 2
Dim s As String
Dim i As Integer
For i = 0 To numParams – 1
s = s & CStr(params(i)) & ” (” & TypeName(params(i)) & “), ”
Next i
Debug.Print “Two Parameters: ” & Left(s, Len(s) – 2)
‘…some code here…
End Select
End Sub

You can see I’ve ‘overloaded’ the subprocedure “Test”. Running “Harness” produces:

No Params
One Parameter: 6 (Integer)
One Parameter: 7 (String)
Two Parameters: 6 (Integer), 7 (String)

in the immediate window. The heavy lifting is done inside the Select Case statement of “Test”. While relatively simple, you can see that it’s not as elegant as the (unobtainable) overloading example demonstrated at the start of this post. Such is the reality of being a VBA Developer: the language is dead (ie. MS is not actively developing it) so you have to create your own tools to match features found in actively developed languages.

Categories:VBA

1 comment

  1. For anyone reading this, this is a good way to go about it, but I *highly* recommend strongly typed private methods for each different overload instead of inlining each of their implementations.

Leave a Reply to Christopher J. McClellan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: