• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 17
  • Last Modified:

Maintain Public Variable Values Across ALL Open Excel Workbooks

Everything I read says that Excel Public VBA variables should retain their values across all open workbooks.  I created two new, simple workbooks (Test1.xlsm & Test2.xlsm) with identical code:

Public Test As Boolean

Sub SetVarTrue()
  Test = True
End Sub

Sub SetVarFalse()
  Test = False
End Sub

If I activate Test1.xlsm & run "SetVarTrue", it changes the value of Test ONLY in Test1.xlsm .  If I switch to Test2.xlsm, the value of Test remains "False".  Works the same if I change the value in Test2.

How can I make Test TRULY PUBLIC across both workbooks?

Thank you.
0
Wayne Kaniewski
Asked:
Wayne Kaniewski
  • 2
  • 2
1 Solution
 
AlanConsultantCommented:
Hi Wayne,

Maybe I am wrong, but I have always considered public variables / constants to be workbook based, so your test is exactly what I would have expected.

Alan.
1
 
Martin LissOlder than dirtCommented:
You can't do that.
0
 
AlanConsultantCommented:
Hi Wayne,

If you need to maintain cross-workbook variables, you could create a single workbook to hold those, and do nothing much else, then explicitly pull them from there wherever needed?

Alan.
0
 
Martin LissOlder than dirtCommented:
The only thing that I can think of to do it would be to create a file that contains the yes/no value and have the workbooks read it when they open and update it when they close.
1
 
Wayne KaniewskiAuthor Commented:
Thank you Alan!  I feared that, but at least I have an answer & can quit banging my head against the wall.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now