Global Common ?

  • Thread starter Thread starter MWE
  • Start date Start date
M

MWE

VBA provides ways to declare variables that are “known”:
within a given procedure
to all procedures in a module
to all procedures in a project

Is there a way to declare variables that are known to all ope
projects?

Thank
 
You could have a function in the workbook that returns the value of that
variable, then call that function using application.Run

Otherwise, you would need to create a reference to the workbook containing
the public variable.
 
Tom, can you give an example? I having trouble getting
this to work. In Macro1, I want to define x = 8. In
Macro2, I want to get the value of x.
 
Tommy,

Use a variable declaration like the following, in a standard code
module, outside of and before any procedure:

Public X As Long

Then, go to the Tools menu, choose VBA Project Properties, and
give a unique name to the project e.g., MyProject.

Then in the workbook that will read or write this variable's
value, go to the Tools menu, choose References, and put a check
next to MyProject. Finally, you can use the variable X in the
other project with code like

MyProject.X = 123


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Unless you have a conflicting variable with the same name, you can just use

X

myvar = X

for example.

(once the reference is created).
 
Assuming you are asking about the none reference method:

In Book2.xls

Public x as long

Sub SetVal()
x = 8
End Sub


Function Macro1()
Macro1 = 8
End Function

In Book1.xls

Sub Macro2()
msgbox Application.Run("Book2.xls!Macro1")
End Sub

Assumes SetVal has already been run in Book2.xls
 
The dialogue on this thread has been most interesting. But I am still
bit confused. Let me clarify what I am trying to do:

I have a number of applications, let's call them A1, A2 and A3, tha
use 30 or so procedures that are specific to these applications. Thes
procedures are stored in a module called A_Proc_Lib. Each applicatio
has its own copy of this module. Each application has its ow
identical declaration of Public variables that are also used by th
A_Proc_Lib procedures.

A1, A2 and A3, and A_Proc_Lib procedures also use some genera
procedures stored in my Personal.xls. Procedures in Personal.xl
communicate with the calling application or prcedure only throug
passed parameters. A1, A2 and A3 all Reference Personal.xls Thing
seem to work fine.

To reduce maintenance of A_Proc_Lib and potentially different versions
I want to put A_Proc_Lib somewhere where any "A" application ca
reference it. I tried putting A_Proc_Lib into Personal.xls but th
Public variables were not "understood" by Personal.xls (compile
errors). I tried creating an Add-In containing A_Proc_Lib (this is
very appealing solution), but had the same problems as wit
Personal.xls (I guess that makes sense). I do not want to eliminat
the inter-procedure communications through public/common.

Any Further thoughts?

Thank
 
I'm intrigued by the statement that you got an error when you put the public
variables in Personal.xls. This is just another workbook, so if the
variables were okay elsewhere, they should be okay in Personal (and it
should be much the same for an addin).

Where and how did you declare these variables, and what error do you get?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob: I did not keep very good notes yesterday when I ran into thes
problems. Things got so screwed up that Excel was puking all the time
I eventually had to reinstall Excel and am now running a slightl
different version of Excel2000 (no recent updates for security stuff).
Strangely, I can not duplicate the type of compiler errors I wa
getting yesterday when I placed the Public statements in Personal.xl
(I copied A_Proc_Lib into Personal.xls and placed a copy of the Publi
statements at the top of that module). I replicated that work a fe
minutes ago. It does not work, i.e., the public common in Personal.xl
is a different public common than the one in the base application, bu
it does NOT gnerate compiler errors now.

Thanks for taking a look

MW
 
Back
Top