Create global variables

Question

Bit of a newbie to VBA, sorry

I need to create some variables that are available throughout my workbook, but I can't seem to figure it out. I've read in previous questions where some people have suggested create a separate dim for this?

When the workbook opens I need to set some variables equal to certain cells in a worksheet, these variables need to be called from dims in others worksheets.

So far I have tried to use

Workbook_Open()

In the 'ThisWorkbook' code area but to no avail.

Any tips?

Reagards

EDIT ----

I have tried with the following:

In 'ThisWorkbook'

Public wsDrawings As String
Public Sub Workbook_Open()
     wsDrawings = "Hello"
End Sub

And in Sheet1

Private Sub CommandButton1_Click()
    MsgBox wsDrawings
End Sub

I do not get an error, but the message box is empty.

Answer 1

Just declare the variables you need wherever they are first used (ThisWorkbook is a fine place to do it) and replace the typical Dim with Public. It will then be accessable in all your code

Answer 2

You can create global variable with code like this

Public testVar As String

you need to place it outside function or sub and then this variable has value till you close workbook. But i think it have scope only in current module.

So you can have something like this

Public testVar As String

Private Sub Workbook_Open()

testVar = "test"
End Sub

Sub testEcho()

MsgBox testVar
End Sub

for shared variable between multiple modules look here

edit:

So now i found, that you can use public variable from ThisWorkbook using this

Sub testSub()

    MsgBox ThisWorkbook.wsDrawings
End Sub

Answer 3

you can use module for creating global variable.