My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
Overloading Function calls in VBA.
 Sep 9 2006, 11:05 PM
Post
#1
|
|
|
Utterly Banned Posts: 3,905 From: Brisbane, Australia  |
G’day all.
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 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. |
 |
 
|
|
Sep 16 2006, 12:26 PM
Post
#2
|
|
|
UA Admin Posts: 16,130 From: Newcastle, WA |
Excellent demo, Chris. Thanks. One comment came to mind when I first read the title of your post. This is NOT a comment on the demo itself, just an observation about the vagaries of language, even among those of us who happen to speak the "same" language in different ways.
To me, "overloading a function call" first appears to mean something like putting so much stress on it that it fails, as in putting too much of a load on a truck might cause the tires to fail. I thought to myself, "how is he going to overload a function? Call it 10 billion times in a row or something?" Only when I read the post itself did it become clear what you were doing. I don't have a good alternative term to suggest, either. It's just that the meaning of "overload" evokes a different meaning to some of us. Thanks again. Excellent code, as usual. George |
|
|
|
|
|
Go to Top · Lo-Fi Version | Time is now: 4th February 2012 - 11:05 PM |
Tag cloud: