PDA

View Full Version : Solved: Check if cell reference



jmenche
09-13-2006, 07:45 AM
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:beerchug:

Emily
09-13-2006, 08:13 AM
Misunderstood the question, sorry !!!

Bob Phillips
09-13-2006, 08:20 AM
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
09-13-2006, 08:27 AM
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"

mvidas
09-13-2006, 08:48 AM
If you don't want to set the variable type like xld suggests, you could always use the TypeName function to determine it: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 FunctionAn example:Sub MySub()
MsgBox MyFunction("hi")
MsgBox MyFunction(Range("A1"))
End SubMatt

jmenche
09-13-2006, 10:27 AM
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
09-13-2006, 10:30 AM
mvidas,

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

Thanks!