Need Help
 

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.

Need Help
 

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

Need Help
 

You can declare it as a public property.

Private vOptionLookup as Variant

Public Property Let OptionLookup (v As Variant)
vOptionLookup = v
End Property

Public Property Get OptionLookup () As Variant
OptionLookup = vOptionLookup
End Property



" 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.