Optional or Empty or Missing Parameter in UDF

[Peter]

Dears,

I want to create a UDF that accept user to enter optional parameter, but
these parameters are not really optional, they can skip this parameter or I
can define a default value for it, but they DO need to enter a comma. I don't
know how to express but something like:

=MyFunc(Param1, Optional Param2 As Variant = "", Optional Param3 As Variant
= "") As String

I have to force user to "enter" UDF like: =MyFunc("test1", , ), and display
MsgBox if they enter =MyFunc("test") or =MyFunc("test", )

I'm now stuck in the using of Optional keyword, and Variant VS Object
datatype, and IsMissing, IsEmpty or Is Nothing checking...

Please help!

Thanks,
Peter

 

[Chip Pearson]

I don't think you can accomplish what you want. You can't force
the user to enter commas for optional arguments, unless they are
including a subsequent argument, to the right of the commas.

I'm curious why you would want to force the user to enter
trailing commas.


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

 

[Peter]

Hi Chip,

I'm curious why you would want to force the user to enter
trailing commas.

My UDF work depends on the parameter, but user have to enter "" (2 x double
quote for an empty string) if they don't want to specific 1 or more
parameter.

So for usability, I'd like to help the user and let them enter nothing
instead of "", so my UDF will check and parse the user input:

=UDF(a, b, c)

So if a = "" -> first parameter is empty string; elseif a is nothing (i.e.
user input =UDF(, b, c) -> first parameter is an empty string (Optional
ParamA As String = "")

However, VBA will treat an optional parameter be "nothing", that mean
totally "skip" this parameter =UDF(b, c)

I hope you can understand what I mean.

Thanks!

 

[Tony Jollans]

If I understand what you are asking, the way to do it is with a Paramarray
....

Function MyUDF(ParamArray Args())

If UBound(Args) <> 2 Then
' Wrong number of Arguments
MyUDF = CVErr(xlErrRef)
Exit Function
End If

If IsMissing(Args(0)) Then Args(0) = "Default A"
If IsMissing(Args(1)) Then Args(1) = "Default B"
If IsMissing(Args(2)) Then Args(2) = "Default C"

MyUDF = "All OK"

End Function

 

[Peter]

Thanks Tony,

Your suggested solution make perfect sense!

Peter