0

I'm trying to create a variable that can be used in multiple workbooks. I thought If I created public variable that I would be able to use across work books. for example in Workbook 1, I created the following.

Public number_test As Integer



Public Sub number()

number_test = 5

End Sub

Then in Workbook 2, I created the following code.

Sub testone()

MsgBox (number_test)

End Sub

However, instead of showing 5 in the message box, the message box is blank. Workbook 1 opens up first and runs the code to create number_test and then workbook 2 is opened (while Workbook 1 is still open), and that code is ran.

How do I make a variable that can be used across workbooks?

AlmostThere
  • 557
  • 1
  • 11
  • 26
  • 2
    You cannot have such variable. The workbook that wants to use a variable declared in another workbook must either reference that workbook through Tools - References or be given an instance of the `Workbook` object to query that variable from. – GSerg Oct 21 '18 at 15:37
  • 1
    And to expand on the previous comment, why would you? If variable scope didn't depend on direct referencing you would end up polluting the global "namespace" with random variables simply based on the workbooks that happened to be open at the time. Do you ***really*** want to deal with programming in an environment where somebody has a random `Public i As Integer` tossed in there somewhere? I suspect this is an [X-Y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) - what exactly are you trying to accomplish? – Comintern Oct 21 '18 at 15:44
  • I have multiple reports which have some common and uncommon parameters. Instead of opening each report and entering the variables and refreshing them, I wanted to create a separate workbook in which you could enter all of the variables for all of the reports. Then that separate workbook would have code to open each of the reports and enter the appropriate variables and execute the reports and then save and close the reports. I guess I can set the variables to the cell values in the separate workbook but was hoping to able to assign as variables in VBA that could be passed between workbooks. – AlmostThere Oct 21 '18 at 15:50
  • for user level settings you can check `SaveSetting` https://stackoverflow.com/questions/32970489/store-settings-on-user-pc. To get value from a closed workbook https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook – Slai Oct 21 '18 at 15:59
  • I was able to make `workbook 1` as a reference in `workbook 2` by going through the `Tools` and `Reference` menus in VBA editor which allowed me to pass the variable. However, now when I open `workbook 2`, `workbook 1` also opens. I'm guessing this is because it is referencing it. Is there a way to have `workbook 2` open without opening `workbook 1`? – AlmostThere Oct 21 '18 at 16:25
  • 1
    In each of the report workbooks, have a `Public Sub` with parameters. Call that sub with correct parameters from the main workbook to execute the report. – GSerg Oct 21 '18 at 18:25

0 Answers0