Due to a recent post here a thought arose in my mind about how we can overload function calls in VBA.
It’s not exactly the same sort of thing as overloading in ‘VBA’ as it is in ‘C’, but near enough to use…with caution.
A little background…
In ‘C’ we must specify a function prototype, meaning that we must tell the compiler the exact function we are calling. That prototype comprises the function name and a list of the arguments, and the data type of those arguments, used when calling the function. The above is also true for internal functions in ‘C’ because we must also add ‘Header Files’ that specify the internal ‘C’ function prototypes.
In ‘VBA’ the inclusion of ‘Header Files’ is, by and large, not required for internal VBA functions. However, it does leave the ‘door open’ for overloading internal VBA functions.
As an example, the VBA MsgBox function…
CODE
Option Explicit
Option Compare Text
Sub TestIt()
If MsgBox("Kilroy", vbOKCancel + vbInformation, "Kilroy is Foo.") = vbOK Then
VBA.MsgBox "You pressed the 'OK' button."
Else
VBA.MsgBox "You pressed the 'Cancel' button."
End If
End Sub
Public Function MsgBox(ByVal strPrompt As String, _
Optional lngButtons As Long = 0, _
Optional strTitle As String = "", _
Optional strHelpFile As String = "", _
Optional lngContext As Long = 0) As Long
Dim lngReturn As Long
strPrompt = strPrompt & " was here."
If Len(strTitle) = 0 Then
On Error Resume Next
strTitle = CurrentDb.Properties("AppTitle")
If (Err.Number) Then
strTitle = "No title available."
Err.Clear
End If
End If
If Len(strHelpFile) And (lngContext) Then
lngReturn = VBA.MsgBox(strPrompt, lngButtons, strTitle, strHelpFile, lngContext)
Else
lngReturn = VBA.MsgBox(strPrompt, lngButtons, strTitle)
End If
MsgBox = lngReturn
End Function
Option Compare Text
Sub TestIt()
If MsgBox("Kilroy", vbOKCancel + vbInformation, "Kilroy is Foo.") = vbOK Then
VBA.MsgBox "You pressed the 'OK' button."
Else
VBA.MsgBox "You pressed the 'Cancel' button."
End If
End Sub
Public Function MsgBox(ByVal strPrompt As String, _
Optional lngButtons As Long = 0, _
Optional strTitle As String = "", _
Optional strHelpFile As String = "", _
Optional lngContext As Long = 0) As Long
Dim lngReturn As Long
strPrompt = strPrompt & " was here."
If Len(strTitle) = 0 Then
On Error Resume Next
strTitle = CurrentDb.Properties("AppTitle")
If (Err.Number) Then
strTitle = "No title available."
Err.Clear
End If
End If
If Len(strHelpFile) And (lngContext) Then
lngReturn = VBA.MsgBox(strPrompt, lngButtons, strTitle, strHelpFile, lngContext)
Else
lngReturn = VBA.MsgBox(strPrompt, lngButtons, strTitle)
End If
MsgBox = lngReturn
End Function
In the above example the internal VBA MsgBox function is overloaded with a user written MsgBox function. When a direct call to the MsgBox function is made then the user written MsgBox function is called. However, when the function is disambiguated with VBA.MsgBox then the internal VBA MsgBox function is called.
Too much to go into here but we may think about functions as having a ‘scope of reference’ that exceeds both Public and Private. They also have a ‘scope of reference’ due to the collection to which they belong, VBA or otherwise.
That is the basic of the ‘How’, of overloading, but the ‘Why and When’ is up to your imagination.
Regards,
Chris.