Newbie question - regarding function parameters

[=?ISO-8859-1?Q?Thomas_S=F6hne?=]

Hello Folks,

i want to write a function, to call from my Excelsheets.
As param i want to pass either a String or a Cellreference on a Cell
containing the string.

Do i have to declare the param in functionsheader as variant?
If so, how can i check if it is a Cellreference or a String?

I would be glad to get any hint on, where to look and read in OH or
other material.

Greetings,
Thomas

 

[Rob van Gelder]

Public Function myfunction(var As Variant) As String
Dim str As String

If TypeOf var Is Range Then
str = "Is Range"
ElseIf VarType(var) = vbString Then
str = "Is String"
Else
str = "is something else"
End If

myfunction = str
End Function

 

[Bob Phillips]

There is nothing wrong with what Rob writes, but if you just what to use the
parameter value, you don't have to test it

Function Test(var As Variant)
Test = var
End Function

Of course, if you want to do more than that, you might want to. For
instance, you might want to ensure that the range, if it is a range, only
includes one cell

Public Function myfunction(var As Variant) As String
Dim str As String

If TypeOf var Is Range Then
If var.Count > 1 Then
myfunction = CVErr(xlErrRef)
Else
str = "Is Range"
End If
ElseIf VarType(var) = vbString Then
str = "Is String"
Else
str = "is something else"
End If

myfunction = str
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[=?ISO-8859-1?Q?Thomas_S=F6hne?=]

Hello Rob and Bob,

thank you for your hints.

It is exactly what i was searching, i will be able to do my task with
your help.

so long,
regards
Thomas