Variable problem

[TC]

Can someone please tell me what I'm doing wrong?

If I use a variable vRngName and set it to NamedRng1 (the name of a
named range), this procedure runs fine.

Sub test()
Dim vRngName As String
vRngName = "NamedRng1"
Range(vRngName).Select
End Sub


Now if I split this into 2 procedures and put Public vRngName As
String at the top of the module and run the following, I get an error
on line 1 of the second procedure "Method 'Range' of object' _global'
failed"

Sub test()
Dim vRngName As String
vRngName = "NamedRng1"
Call test2
End Sub

Sub test2()
Range(vRngName).Select 'error is here
End Sub

Why does using 2 procedures cause this to fail?
Thanks for the help

 

[JE McGimpsey]

The problem is that you're using the macro-level variable in test2, but
a local variable in test().

Delete the Dim statement in test().

 

[GJones]

Hi TC;

You have to pass the parameter to the second call.
Something like this;


Sub test()
Dim vRngName As String
vRngName = "NamedRng1"
Call test2 vRngName
End Sub

Sub test2(vRngName)
Range(vRngName).Select 'error is here
End Sub




Thanks,

Greg

 

[TC]

Thanks Greg and JE.
Both of these solutions work. As a relative Excel newbie I assumed
posting a variable as Public at the top of a module simply allowed the
variable declared in a macro with the Dim statement to be used
globally.. I didn't realize it created 2 separate variables.
Occasionally I think I'm becoming an Excel expert but then the drugs
wear off and I'm back here for help.
Thanks again.