Overloading Subs

[eflannery]

Hello,

I'm having difficulty overloading some methods, and I think I'm doing something wrong setting them up. I have two basic subs I'm using to learn how to do this, but I'm getting an "Ambiguous Name Detected" error. The code is as follows:

Sub tester()
    MsgBox ("in the tester")
End Sub
 
Sub tester(a As Integer)
    MsgBox ("in the tester" & a)
End Sub

and I'm testing it like this

tester
tester 12

I've tried adding "Overloads" in each sub declaration, as follows

Overloads Sub tester()
    MsgBox ("in the tester")
End Sub
Overloads Sub tester(a As Integer)
    MsgBox ("in the tester" & a)
End Sub

But then I get an "Expected: Expression" error popping up

I've also tried using the following code to test it

tester ()
tester (12)

From what I can find online here and on Microsoft's support website, I'm doing the right thing but it still isn't working. (there's a reason I'm an engineer and not a programmer).

Any help would be greatly appreciated.

Thanks!

 

[pboltonchina]

They're both called 'tester'. Change one of the to 'tester1' or something.

 

[mikerickson]

MsgBox is a function. It returns a value depending on which button is pressed.
If you are not interested on the return value and only want it to do its action, you use a different syntax.

' return no Value
MsgBox "in the tester" & a
Msgbox "two button test", vbYesNo

'returns a value
Dim userResponse as Long

userResponse = MsgBox("in the tester" & a, vbOKOnly)

userResponse = MsgBox("twoButtonTest",vbYesNo)

By using parenthesis in the way your posted code does, it confuses these two syntax.

 

[tweedle]

To further your experience here ....


i believe Overloads is a .net thing...never seen it used in VBA b4.

To overload a Sub or Function, one can use Optional keyword and test for them.

Sub Foo(Optional parm1, Optional parm2)
    If Not IsMissing(parm2) And Not IsMissing(parm1) Then
        MsgBox Prompt:=parm1 & vbLf & parm2, Title:="From Foo"
    ElseIf Not IsMissing(parm1) Then
        MsgBox Prompt:=parm1, Title:="From Foo"
    Else
        MsgBox Prompt:="Nope! No parameters here", Title:="From Foo"
    End If
End Sub
 
Sub FooCall()
msg1 = "Engineers are quirky"
msg2 = "...So are programmers"
Foo msg1
Foo msg1, msg2
Foo
End Sub

 

[eflannery]

Thanks for the MsgBox tip, Mike! I am only using it as a debugger in this case, definitely not looking for a user response. I'm used to Java, where I can just System.out.println whatever I want.

But when I tried removing the parenthesis, and leaving

Sub tester()
    MsgBox "in the tester"
End Sub
 
Sub tester(a As Integer)
    MsgBox "in the tester" & a
End Sub

I got the same "Ambiguous Name Detected" error

Any other suggestions?

 

[eflannery]

Tweedle:

Thank you, that's perfect! I didn't know there was a difference between VBA and VB.net. I've never used VB before last monday, so I've got a lot to learn.

Thanks!
Liz

 

[mikerickson]

For debugging, many Excel VBA coders use Debug.Print, which prints to the Imediate Window.

I prefer MsgBox (personal preference). The main difference is that MsgBox will halt excicution.
For debugging loops I sometimes use this us suss out potentialy infite loops.

If MsgBox(someValue, vbYesNo) = vbNo Then Exit Sub

 

[eflannery]

Thanks Mike, I'll give that a try as well!