Create Public Variable that can be used in multipl

Question

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?