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)

?

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" wrote in
message
...
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)

?

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)

?

Gixxer, The answer to this is to use optional arguments for the function
like this:

Public Sub MyFunc(Optional A As Integer, Optional B As Integer)
End Sub

You will need something in the procedure that requires the arguments to
check if they have been provided or not. If they are not, A and B will
return 0 by default.

Hope this helps, Jack




"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)

?

that'll work!

thanks Jack!

"Jack" wrote:

Gixxer, The answer to this is to use optional arguments for the function
like this:

Public Sub MyFunc(Optional A As Integer, Optional B As Integer)
End Sub

You will need something in the procedure that requires the arguments to
check if they have been provided or not. If they are not, A and B will
return 0 by default.

Hope this helps, Jack




"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)

?