Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|