VBA Excel =?windows-1252?Q?=93Set=94_question?=

I am trying to use a variable that was declared as “Public” at a Module
level in one workbook, in a second workbook. I know I need to use a
“Set” statement, but so far I have not come up with the correct
combination to make it work. I am relatively new to VBA and would
appreciate any suggestions.

J. Thomas

You do not need to use Set to load a public variable, just assign it like so

myVar = "Bob"

However, you cannot reference a variable in one workbook from another book.
One way around this is to create a simple public sub in the workbook with
the variable, and access the variable from there. You then use that macro
like so

Application.Run "FirstBook.xls"!TestVariable"

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"John Thomas" <[email protected]> wrote in message
news:UR2Cf.11772$zh2.321@trnddc01...
> I am trying to use a variable that was declared as “Public” at a Module
> level in one workbook, in a second workbook. I know I need to use a
> “Set” statement, but so far I have not come up with the correct
> combination to make it work. I am relatively new to VBA and would
> appreciate any suggestions.
>
> J. Thomas
>

In the 2nd workbook create a reference to the 1st workbook (Tools |
References...). Whether you need a Set or not depends on the type of
variable.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <UR2Cf.11772$zh2.321@trnddc01>, [email protected]
says...
> I am trying to use a variable that was declared as =3FPublic=3F at a Module
> level in one workbook, in a second workbook. I know I need to use a
> =3FSet=3F statement, but so far I have not come up with the correct
> combination to make it work. I am relatively new to VBA and would
> appreciate any suggestions.
>
> J. Thomas
>
>

You can't use a public variable in one workbook in another workbook. Public
means public to the project and the project is the workbook. There are no
application level variables. A workaround would be to put the information
in a defined name (manually it would be Insert=>Name=>Define but it can be
done in code), then get the information that way.

Now that that is said, there are two workarounds. In the second workbook,
you can go into the VBE and in Tools=>References you can create a reference
to the first workbook. Once created, you can use public variables and code
in the first workbook as if it were in the second workbook. If you do
create such a reference and save the workbook so it is preserved, then
whenever you open the second workbook, the first workbook will be opened
automatically.

Another approach would be to put a function or functions in the first
workbook which can work with the public variable. then you can call these
function using Application.Run

First workbook in a general module.

Public myvar as Variant

Public Function SetMyVar(arg1 as variant)
On Error goto ErrHandler
myvar = arg1
SetMyVar = -1
Exit Function
ErrHandler:
SetMyVar = 0
End Function

Public Function ReturnMyVar()
ReturnMyVar = myVar
End Function

then in the second workbook

Dim res as Long
res = Application.Run("FirstBook.xls!SetMyVar",21)
if res = 0 then
msgbox "Problems"
exit sub
End if
msgbox "MyVar = " & Application.Run("FirstBook.xls!ReturnMyVar")

--
Regards,
Tom Ogilvy


"John Thomas" <[email protected]> wrote in message
news:UR2Cf.11772$zh2.321@trnddc01...
> I am trying to use a variable that was declared as “Public” at a Module
> level in one workbook, in a second workbook. I know I need to use a
> “Set” statement, but so far I have not come up with the correct
> combination to make it work. I am relatively new to VBA and would
> appreciate any suggestions.
>
> J. Thomas
>

Tom Ogilvy wrote:
> You can't use a public variable in one workbook in another workbook.

You can.
Open first workbook with your variable, open second workbook, go to VBE
open Tools/References and add your first workbook to a list of
references of the second workbook.
Of course it is problematic when your try to move such combination to
another computer, but it works.

Another solution is to have functions which set and return value of such
variable and call then using Application.Run

To call function you don't have to reference it.

If your going to correct me, at least have the courtesy of reading my
answer before you post.

--
Regards,
Tom Ogilvy

"Kris" <[email protected]> wrote in message
news:[email protected]...
> Tom Ogilvy wrote:
> > You can't use a public variable in one workbook in another workbook.
>
> You can.
> Open first workbook with your variable, open second workbook, go to VBE
> open Tools/References and add your first workbook to a list of
> references of the second workbook.
> Of course it is problematic when your try to move such combination to
> another computer, but it works.
>
> Another solution is to have functions which set and return value of such
> variable and call then using Application.Run
>
> To call function you don't have to reference it.