Global variables in VBA with values

Question

I am working on an Excel file with several subs, which are independent from each other. But they still use the same variables with the same values all the time. Therefore, I would like to define the variable globally but what I found quite easily was to only declare them globally. What do I have to do that I can also fill the variable with values straight from the beginning on? Is it possible that the variables are defined from the beginning without having to write a sub, something like the example below? (I believe that isn't possible or am I wrong?)

I would appreciate 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

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

No, you can only get/set the value of a variable within a sub procedure or function.

---

On a side note, your current code:

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

declares s1 and s2 as Variant and s3 as Worksheet - it's 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 one line in VBA would be:

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

I'd say the same for the second line but you've declared that as a variant anyway.

Answer 2

You can define them in a module as public variables and then set them in Workbook_Open in the ThisWorkBook module (where 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 the spreadsheet is first opened.