Error 91 in a function but not in a similar sub

Question

I want to convert a string into a range variable that I will use somewhere else. My first idea was to create a text-to-range function and to avoid dealing with the sintaxis ever again. I know it is probably a very basic question but I couldn't figure it.

My first attempt was this. It prints "indirect" were I want to (this was just a test), but running the macro step by step I see that there is an error 91 at the time to "exit" the function.

Dim rng As Range
rng = TXT2RNG(Range("A1").Value)

'This is the function, located in Modulo1
'Function TXT2RNG(text As String) As Range
'Set TXT2RNG = Range(text)
'TXT2RNG.Value = "indirect"
'End Function
End Sub

I have attempted the same but without the function, and it works as I expected.

Dim rng As Range
Set rng = Range(Range("A2").Value)
'Set rng = Range(Range("A1").Value)
rng.Value = "direct"
End Sub

Summary: The second code works as a workaround but I want to know why the first one doesn't so can learn from it and use similar structures in the future. Thank you

Answer 1

Welcome to stack overflow.

From walking through your code, you're not declaring "text" as anything.

If you use the "Watches" feature, you can see it's a blank string

I believe you need to have a function that pulls the range, then a second function to pull the string of that. A Private Sub is much better See this answer https://stackoverflow.com/a/2913690/2463166

Answer 2

Basically, you are simply missing a Set when assigning the result from the function to your variable - you do it correct in your direct example.
Whenever you are dealing with objects (eg worksheet, range), you need to use Set when assigning it to a variable. A good explanation can be found at https://stackoverflow.com/a/18928737/7599798

Omitting the Set will cause an error 91 when assigning it to an object variable. However, if you would declare your rng-variable as Variant, you wouldn't get a runtime error. Instead, VBA would use the so called default property, for a Range this is the Value, so you would end up having the content of the Range in your variable ("indirect" in your example). This is the reason to use the data type Variant only if really needed.

That said, there are at least 2 issues you should take care about:

• when you use the Range-function as you do, it refers to ActiveSheet, which is the sheet that currently has the focus. When coding, that's not always what you want, so think about if you need to approve your function. You should really take the time to read the answers of How to avoid using Select in Excel VBA to get an understanding.
• You should think about what should happen when the text you pass to your function doesn't contain a valid range-address. Currently, you would get a runtime error (1004). Error handling in VBA is done with On Error-statements. You should avoid On Error Resume Next.

You could change your function to:

Function TXT2RNG(ws as Worksheet, text As String) As Range
    On Error Goto InvalidRange
    Set TXT2RNG = ws.Range(text)
    ' TXT2RNG.Value = "indirect"
    Exit Function
InvalidRange:
    ' Think about what to do here, show a message, simply ignore it...
    Set TXT2RNG = Nothing
End Function

And the call to it would be

Dim rng as Range, address as string
address = Range("A1").Value
Set rng = TXT2RNG(activeSheet, address)
if not rng is Nothing then
    (...)