CODEWITHSUNDEEP
excelvbaargumentsrangereturn-value
sinDizzy
sinDizzy

Reputation: 1364

Excel VBA Function will not work with an argument as a Range

I have written a fairly simple VBA function that I want to use in my worksheet and others that are linked to the macro worksheet. I am allowing the user to input a value OR a range (basically a cell reference) as the argument.

Public Function TSTT(arg As Range) As Single
    Dim a25 As Variant
    a25 = 522.6 - (arg / 155.2867)

    TSTT= a25
End Function

The problem is when I use Range as the type (as above) and the user inputs a value in the formula I get a #VALUE as the return.

If I declare the argument as a single then it works if the user inputs a value or a cell reference.

Finally if I declare the argument as a Variant it also works with both types of input.

To allow both types of inputs should I use the Variant or the expected type?

Upvotes: 1

Views: 3152

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You don't need to declare the argument in the function header, you can test what the user has given you. You can actually test what the user has given you:

Function foo(r) As String
    If TypeOf r Is Range Then
      v = r.Value
    Else
        v = r
    End If
    '
    '   more stuff
    '
End Function

Upvotes: 2

Related Questions