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

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Thomas Söhne" wrote in message
...
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

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)


"Rob van Gelder" wrote in message
...
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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Thomas Söhne" wrote in message
...
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

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