Displaying arguements in a UDF

[Sachin]

Thanks to all the help from Chandoo's articles and everyone's advice here, I've completed my first user-defined function. It has 10 arguements, which must be selected in the correct order. When I select the function, I want the arguements to appear like so:


=UDF(arg1, arg2, arg3, ...)


Any advice on how to make it happen?


Off to write the next UDF.


Thanks,


Sachin

 

[Luke M]

The way it appears in your worksheet will be determine by how you defined things in the UDF. E.g., this UDF (my replacement for CONCATENATE)

[pre]

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String

Dim cell As Range, Area As Variant

If IsMissing(Delimiter) Then Delimiter = ""

For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each cell In Area
If Len(cell.Value) Then ConCat = ConCat & Delimiter & cell.Value
Next
Else
ConCat = ConCat & Delimiter & Area
End If
Next

ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

[/pre]
will appear like this in my workbook if I type "=CONCAT("

=CONCAT(Delimiter, CellRanges)

Does that answer your question?

 

[Sachin]

Unfortunately, that's the way I was set up to begin with. Here is partial code:

[pre]

Function Status(Arg1 As Date, Arg2 As Date, Arg3 As Date, Arg4 As Date, Arg5 As Date, Arg6 As Date, Arg7 As Date, Arg8 As Date, Arg9 As Date, Arg10 As Date) As String

Dim RetStatus As String     ' RetStatus = temporary variable to capture status of job req
Dim Report_Date As Date     ' Converts ReportDate Range to Date for manipulation in UDF
Dim ReportDate As Range     ' Refers to ReportDate named range in spreadsheet

' following are the status named ranges from the Lookups worksheet

<Status Ranges from Report here>

Report_Date = Range("ReportDate")

If Arg1 >= Arg2 And Arg2 > "0" Then
RetStatus = Range("Complete")
ElseIf
<more if...then...elseifs here>

End If

Status = RetStatus

End Function

[/pre]

Hope this helps. It's a minor thing, but something I want to make sure is addressed properly.

 

[Luke M]

I guess I'm confused as to what your asking. The function should be displaying in the same manner you described in first post. If you were literally wanting it to be abbreviated, I don't think that's possible, as no other function (native) does that.

 

[Luke M]

Another idea, if you're already selecting multiple cells for all the dates, would be to use the ParamArray like:

[pre]

Function Status(ParamArray Arg() As Date) As String

Dim RetStatus As String     ' RetStatus = temporary variable to capture status of job req
Dim Report_Date As Date     ' Converts ReportDate Range to Date for manipulation in UDF
Dim ReportDate As Range     ' Refers to ReportDate named range in spreadsheet

' following are the status named ranges from the Lookups worksheet

<Status Ranges from Report here>

Report_Date = Range("ReportDate")

If Arg(1) >= Arg(2) And Arg(2) > 0 Then 'Since it's a date variable, can't store a string
RetStatus = Range("Complete")
ElseIf
<more if...then...elseifs here>

End If

Status = RetStatus

End Function

[/pre]
This method would show up as:

=Status([Arg], ...)

 

[Sachin]

I would do the ParamArray, but I need the argement names to appear, as they must be entered in a specific order.


I'm glad I'm on the right track, but I still can't get the arguement names to appear. It's a minor issue, but one I'd like to resolve. If you want, I can send you a heavily redacted file that you can examine.

 

[dan_l]

Function demoformula(FirstNum As Integer, SecondNum As Integer, AsText As Integer)

Dim total As Double


total = FirstNum + SecondNum


If AsText = 1 Then

demoformula = FirstNum & " + " & SecondNum & " = " & total

ElseIf AsText = 0 Then

demoformula = total

End If


End Function


like this?


I have some....lets say 'specialized' UDF's that are actually sort of a shi**y practice: a bunch fairly complex lookup routines. When a formula needs to be trouble shot, I add a switch to the end of the formula to let people specify the output 0 resolves the formula, 1 displays what the inputs are, 2 displays what the outputs are---whatever works.

 

[cyrilz]

Hello Sachin,


Starting with XL2007 you can only have the name of the function displayed.


With earlier version , you would need "Microsoft HTML Help Workshop" in order to create an .CHM file (HTML pages gathered into a unique help file).


Once this is done, you need to associate your UDF with the help file and its corresponding context ID via the Macro menu / contexte, but this won't work with XL2007 and later.


Hope this help.


PS : You should consider dan's solution which is clever to debug UDFs.

 

[Sachin]

Thanks Cyril. I will have to include instructions that no one will read.