overloading a function

Hi Gixxer,

Not really. You can use optional arguments to simulate overloading:

Public Sub myfunc(Optional a As Variant, Optional b As Variant)
If IsMissing(a) Then
Debug.Print "a not passed"
Else
Debug.Print "a = " & CStr(a)
End If

If IsMissing(b) Then
Debug.Print "b not passed"
Else
Debug.Print "b = " & CStr(b)
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Gixxer_J_97 wrote:
> is there a way to overload a function in vba?
> ie
> public sub myfunc(a, as integer, b as integer)
> end sub
>
> and
>
> private sub myfunc()
> end sub
>
> where the private sub is used for the 'local' userform - and the
> public sub is called by an outside module (where a and b would be the
> arguments that would normally be accessed by the userform, but in
> this case need to be passed)
>
> ?

Remember also that IsMissing only works if the optional argument is a Variant.
If the argument is a string you can check the length of the string to
determine if
it has been pased like this:

If Len(StringArgument) = 0 then

end if


"Jake Marx" wrote:

> Hi Gixxer,
>
> Not really. You can use optional arguments to simulate overloading:
>
> Public Sub myfunc(Optional a As Variant, Optional b As Variant)
> If IsMissing(a) Then
> Debug.Print "a not passed"
> Else
> Debug.Print "a = " & CStr(a)
> End If
>
> If IsMissing(b) Then
> Debug.Print "b not passed"
> Else
> Debug.Print "b = " & CStr(b)
> End If
> End Sub
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> Gixxer_J_97 wrote:
> > is there a way to overload a function in vba?
> > ie
> > public sub myfunc(a, as integer, b as integer)
> > end sub
> >
> > and
> >
> > private sub myfunc()
> > end sub
> >
> > where the private sub is used for the 'local' userform - and the
> > public sub is called by an outside module (where a and b would be the
> > arguments that would normally be accessed by the userform, but in
> > this case need to be passed)
> >
> > ?
>

Note, however, that your code does not distinguish between the
cases of no string being passed and a zero length (empty) string
being passed. Both will return a Len of 0. To determine whether
a string was actually passed, use StrPtr. E.g.,

Function F(Optional S As String) As Integer
If StrPtr(S) = 0 Then ' no string passed
F = -1
ElseIf Len(S) = 0 Then ' zero length string
F = 0
Else ' some text passed
F = Len(S)
End If
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"OfficeHacker" <OfficeHacker@discussions.microsoft.com> wrote in
message
news:E3E0247F-BA11-4CAF-B436-BC8D4371D820@microsoft.com...
> Remember also that IsMissing only works if the optional
> argument is a Variant.
> If the argument is a string you can check the length of the
> string to
> determine if
> it has been pased like this:
>
> If Len(StringArgument) = 0 then
>
> end if
>
>
> "Jake Marx" wrote:
>
>> Hi Gixxer,
>>
>> Not really. You can use optional arguments to simulate
>> overloading:
>>
>> Public Sub myfunc(Optional a As Variant, Optional b As
>> Variant)
>> If IsMissing(a) Then
>> Debug.Print "a not passed"
>> Else
>> Debug.Print "a = " & CStr(a)
>> End If
>>
>> If IsMissing(b) Then
>> Debug.Print "b not passed"
>> Else
>> Debug.Print "b = " & CStr(b)
>> End If
>> End Sub
>>
>> --
>> Regards,
>>
>> Jake Marx
>> MS MVP - Excel
>> www.longhead.com
>>
>> [please keep replies in the newsgroup - email address
>> unmonitored]
>>
>>
>> Gixxer_J_97 wrote:
>> > is there a way to overload a function in vba?
>> > ie
>> > public sub myfunc(a, as integer, b as integer)
>> > end sub
>> >
>> > and
>> >
>> > private sub myfunc()
>> > end sub
>> >
>> > where the private sub is used for the 'local' userform - and
>> > the
>> > public sub is called by an outside module (where a and b
>> > would be the
>> > arguments that would normally be accessed by the userform,
>> > but in
>> > this case need to be passed)
>> >
>> > ?
>>

No, Function Over Loading Is not possible in VBA Macros, VBA does not accept the same function names with different arguments or parameters in brackets.

Remember also that IsMissing only works if the optional argument is a Variant.
If the optional argument is a string you can test the length of the passed
string like this:

If len(StringArgument) = 0 then
' No Argument passed
End if

If the optional argument is numeric you can use a value unlikely to occur as
the default and then test for that:

Function MyFunc(Optional lngVal As Long = -9999)
If lngVal = -9999 Then
' No value passed
End If
End Function

Note also that if you have any intention of migrating the code to .Net in
the future, IsMissing is no longer supported.

All the best.



"Jake Marx" wrote:

> Hi Gixxer,
>
> Not really. You can use optional arguments to simulate overloading:
>
> Public Sub myfunc(Optional a As Variant, Optional b As Variant)
> If IsMissing(a) Then
> Debug.Print "a not passed"
> Else
> Debug.Print "a = " & CStr(a)
> End If
>
> If IsMissing(b) Then
> Debug.Print "b not passed"
> Else
> Debug.Print "b = " & CStr(b)
> End If
> End Sub
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> Gixxer_J_97 wrote:
> > is there a way to overload a function in vba?
> > ie
> > public sub myfunc(a, as integer, b as integer)
> > end sub
> >
> > and
> >
> > private sub myfunc()
> > end sub
> >
> > where the private sub is used for the 'local' userform - and the
> > public sub is called by an outside module (where a and b would be the
> > arguments that would normally be accessed by the userform, but in
> > this case need to be passed)
> >
> > ?
>