5

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
Improve this question
3
  • 1
    One option would be to put them in a class, and initialize it in Class_Initialize(). If your subs are intended to be used as macros, you can just use them as a "wrapper" for the class functionality. Commented Mar 17, 2017 at 20:24
  • 1
    Workbook open event or auto_open sub will do. Commented Mar 17, 2017 at 20:26
  • 1
    FWIW, you can also just use the CodeNames of the worksheets instead of storing a reference to them. - i.e., just use Sheet1.Foo instead of s1.Foo. The object model gives you those as freebies. Commented Mar 17, 2017 at 20:31

4 Answers 4

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

No worries... :)
3

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.

Improve this answer

5 Comments

Somehow VBA doesn't like the variables s1, s2, and so on. They are not getting transfered to the subs. I even tried just a simple variable with x=5, but in the sub there is no value in x. Do I have to call them somehow in subs from the "workbook_open" function in "ThisWorkbook"?
You can define s1 as 'Public s1 as WorkSheet' in a separate module (similarly do that for all your global variables). Once it's defined like this you should be able to access them from anywhere in your workbook.
Basically you create a new module (say 'GlobalVarDecl') and put all your global variable declarations in there (like: 'Public s1 As Worksheet') but don't bother initializing them there. It's in Workbook_Open where you initialize all your global variables. Also it's a good habit to put 'Option Explicit' at the top of your code. With the latter line excel will create an error if you haven't declared your variables. Trust me, this helps when it comes to debugging.
Hi Amorpheuses, I did what I have added down below (better to see the code instead of here). Is what you suggested the same as what I did?
No, you should create a new module and put the 1st two lines in it (the declarations for s1,s2, x and fd) and remove them from ThisWorkbook. Also you need to use 'Set' for the worksheet variables: Set s1=ThisWorkbook.Worksheets("Form"). After that it should work.
3

You should fill them inside Workbook_Open event. Go to ThisWorkbook module and write:

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

This event is called when you open WorkBook.

Improve this answer

1 Comment

Thanks a lot for all the comments! I was exactly looking for something likte the Workbook_Open function.
0

If you would like to use these values like constants which will have the same value all the time then you still could define them as private members of ThisWorkbook class and implement get property which retuns the value and ensures the value is defined.

The advantage of this solution is that nobody except the ThisWorkbook object can change the values of this variables. This is not the case by public global variables because there someone could accidentally e.g. set the workbook1 to Nothing and because Workbook_Open runs only once at the very beginning an error will occur when workbook1 will be accessed. IMO to use global variables is mostly not good idea. HTH

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 workbook just call the property on ThisWorkbook e.g. like this.

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

Output:

4
Sheet 1
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.