write help for a UDF

[gavinkelly]

how do i write "help" for a user defined function which can be viewed in the "insert function" tab.

Also why do the arguments not show when i begin to write =UDF(... ) unlike a none user defined fuction i.e. the fuctions already in excel? (UDF is the name i have called my user defined funtion?

 

[Oaktree]

I'm not sure if you can get the tooltips up for a UDF, so this isn't exactly the answer you're looking for... but, if your UDF is:

function my_UDF(var1 as double, var2 as double) as double
my_UDF = var1 * var2
end function

and you type =my_UDF( in a cell and press CTRL+SHIFT+A, you'll see the arguments shown (or, CTRL+A will bring up the dialog box).

 

[schielrn]

See this link in the 4th post by hatman:

http://www.mrexcel.com/forum/showthread.php?t=286745&highlight=udf

It is a link to another site, but I believe it is what you are looking for.

Well nevermind that link is no longer good, I will see if I can find another link for you.

 

[schielrn]

Ok maybe try this:

http://www.eng-tips.com/viewthread.cfm?qid=79365&page=9

There is a lot in here and I didn't look through it too well, but I think what you need should be here.

 

[gavinkelly]

ok but also:
i have declared an optional argument but how do i set a default value if the user does not enter the argument?

 

[schielrn]

Can you post the UDF? I believe you can set the optional like this:

Optional Variable As Long = 1

Hope that helps.

 

[gavinkelly]

Function ContainsText2(Rng As Range, Phrase As String, Optional KeyWordPosition As Integer, Optional AssignedPosition As Integer) As String

ContainsText2 KeyWordPosition:="1" 'this is not working
ContainsText2 AssignedPosition:="2" 'this neither


n = 1
For x = 1 To Rng.Rows.Count
If 0 < InStr(Phrase, Rng(n, KeyWordPosition)) Then
ContainsText2 = Rng(n, AssignedPosition)
Exit For
End If
n = n + 1
Next x

End Function

 

[schielrn]

Can you do something like:

Function ContainsText2(Rng As Range, Phrase As String, Optional KeyWordPosition As Integer=1, Optional AssignedPosition As Integer=1) As String

Or:

Function ContainsText2(Rng As Range, Phrase As String, Optional KeyWordPosition As Integer, Optional AssignedPosition As Integer) As String

 if KeyWordPosition= 0 then KeyWordPosition = 1
 if AssignedPosition =0 then AssignedPosition = 2

n = 1
For x = 1 To Rng.Rows.Count
If 0 < InStr(Phrase, Rng(n, KeyWordPosition)) Then
ContainsText2 = Rng(n, AssignedPosition)
Exit For
End If
n = n + 1
Next x

End Function

Preferrably I'd use the first approach. Hope that helps.

 

[gavinkelly]

Fantastic, much appreciated