I think you misread that help file entry. You can declare public variables as
Variant.
But you used "option explicit"
That means any variable in that module has to be declared. And you didn't
declare rItem and rItemsinfo in your code (at least the code you shared).
This worked ok for me:
Option Explicit
Public vOptionLookup As Variant
Sub SetVariables()
Dim rItem As Variant
Dim rItemsInfo As Range
rItem = "abc"
Set rItemsInfo = ActiveSheet.Range("a:az")
vOptionLookup = Application.VLookup(rItem, rItemsInfo, 37, False)
End Sub
Sub OptionLookup()
'Application.Run "SetVariables"
'just call it:
Call SetVariables
Range("A1").Value = CStr(vOptionLookup)
End Sub
There doesn't look like there's any reason to use Application.run in your sample
code. And cStr() brings back something nicer to range("a1").
wrote:
I am trying to set a variant to be public. My code would look
something like this.
Option Explicit
Public vOptionLookup as Variant
________________________________________
Sub SetVariables ()
vOptionLookup = Application.VLookup(rItem, rItemsInfo, 37, False)
End Sub
_________________________________________
Sub OptionLookup()
Application.Run "SetVariables"
Range("A1").Value = vOptionLookup
End Sub
The problem is that in the help file it says Variants are not
supported to be public, which I found to be true. Is there a work
around or something I can do to make this Variant public so I can use
it in several different places?
Any input would be awsome. Thanks.
--
Dave Peterson