Global variables in VBA with values

Question

I am working on an Excel file with several subscribers that are independent of each other. But they still use the same variables with the same values ​​all the time. Therefore, I would like to define a variable globally, but what I found quite easily was to declare them globally. What do I need to do so that I can also populate the variable with values ​​from the very beginning? Is it possible that the variables are defined from the very beginning, without having to write sub, something like the example below? (I believe this is not possible, or am I wrong?)

I would be grateful for your help!

Thomas

Public s1, s2, s3 As Worksheet Public array1, array2 As Variant s1 = ThisWorkbook.Worksheets("Sheet 1") s2 = ThisWorkbook.Worksheets("Sheet 2") s3 = ThisWorkbook.Worksheets("Sheet 3") array1 = Array(3, 5, 6, 7, 5) array2 = Array(8, 9, 10, 11, 12) Sub code1() ... End Sub Sub code2() ... End Sub 

Answer 1

You can define them in the module as public variables, and then set them in Workbook_Open in the ThisWorkBook module (where the sheets are listed in the MS Visual Basic editor):

 Public Sub Workbook_Open() Set s1 = ThisWorkbook.Worksheets("Sheet 1") Set s2 = ThisWorkbook.Worksheets("Sheet 2") Set s3 = ThisWorkbook.Worksheets("Sheet 3") array1 = Array(3, 5, 6, 7, 5) array2 = Array(8, 9, 10, 11, 12) End Sub 

This method is executed whenever a spreadsheet is first opened.

Answer 2

Is it possible that variables are defined from the very beginning without having to write sub?

No, you can get / set the value of a variable inside an auxiliary procedure or function.

---

For one thing, your current code is:

 Public s1, s2, s3 As Worksheet Public array1, array2 As Variant 

declares s1 and s2 as Variant and s3 as Worksheet - this is a common misconception in VBA that you can declare multiple variables of the same type in this way.

The correct way to do this on a single line in VBA:

 Public s1 As Worksheet, s2 As Worksheet, s3 As Worksheet 

I would say the same for the second line, but you still declared it as an option.

Answer 3

You must populate them inside the Workbook_Open event. Go to the ThisWorkbook module and write:

 Private Sub Workbook_Open() [fill variables here] End Sub 

This event is fired when you open WorkBook.

Answer 4

If you want to use these values, such as constants , that will have the same value all the time, you can still define them as private members of the ThisWorkbook class and implement the get property, which reconfigures the value and ensures that the value is defined.

The advantage of this solution is that no one except the ThisWorkbook object can change the values ​​of these variables. This does not apply to publicly-accessible global variables, because someone might accidentally, for example. set workbook1 to Nothing , and since Workbook_Open only starts once from the very beginning, an error will occur when accessing workbook1 . IMO for using global variables is basically not a good idea. NTN

ThisWorkbook class module:

 Option Explicit Private m_worksheet1 As Worksheet Private m_array1 As Variant Public Property Get Worksheet1() As Worksheet If m_worksheet1 Is Nothing Then _ Set m_worksheet1 = ThisWorkbook.Worksheets("Sheet 1") Set Worksheet1 = m_worksheet1 End Property Public Property Get Array1() As Variant If Not IsArray(m_array1) Then _ m_array1 = Array(3, 5, 6, 7, 5) Array1 = m_array1 End Property 

Then everywhere in the book, just call the property on ThisWorkbook , for example. like this.

 Debug.Print UBound(ThisWorkbook.Array1) Debug.Print ThisWorkbook.Worksheet1.Name 

Output:

 4 Sheet 1