Solved: Check if cell reference

[jmenche]

Howdy,

I am writing a custom function and just want to do a little error trapping. I want to include an If statement that makes sure if an argument is a cell reference. If not, I will display a message box.

Can someone help me out with just the cell reference check part?

Thanks

[Emily]

Misunderstood the question, sorry !!!

[Bob Phillips]

If you declare the argument as type Range you will not be able to pass anything else but a range, it will error at that point.

[mdmackillop]

[VBA]Dim k As Range, a As String
a = "BBB1"
On Error Resume Next
Set k = Range(a)
If Err.Number <> 0 Then MsgBox "Not a range"
[/VBA]

[mvidas]

If you don't want to set the variable type like xld suggests, you could always use the TypeName function to determine it:[vba]Function MyFunction(MyVariant As Variant) As String
If TypeName(MyVariant) = "Range" Then
MyFunction = "you passed a range!"
Else
MyFunction = "you didnt pass a range"
End If
End Function[/vba]An example:[vba]Sub MySub()
MsgBox MyFunction("hi")
MsgBox MyFunction(Range("A1"))
End Sub[/vba]Matt

[jmenche]

xld,

That's exactly what I did. Is there any way to trap that error and give the user some info? It just gives an error when I do it.

[jmenche]

mvidas,

I just saw your post and I think that could be the winner the I am looking for.

Thanks!