VBA Functions and variables

Question

I have a question about variables and functions in VBA. I'm not going to include exactly what the project is in hopes that the initial question won't get to obscure. I can explain the project this will be used for if requested.

Is it possible to create universal variables in VBA so that all functions can use them?

Ex:

Dim testVariable As String
Dim newVariable As String

testVariable = "A"

Function testFunction() As String
    ....
    newVariable = testVariable
End Function

As of now, testVariable (when it is in the function) is "Empty"

Thank you,

Jesse Smothermon

Answer 1

Yes, but the value assignments must occur within procedures:

Public testVariable As String
Public newVariable As String

Sub SetTestVariable()
    testVariable = "A"
End Sub

Function testFunction() As String
    ....
    newVariable = testVariable
End Function

Keep in mind that it is generally bad programming practice to use a lot of global variables. You want to limit the scope of your variables to where they will be used. For more information about scope in VBA see this Microsoft KnowledgeBase article: Scope of variables in Visual Basic for Applications

Answer 2

Leave the variables out of any sub/function, so they become Globals

Then, call an init function before you run any code that contains the default settings, such as the code

Sub InitGlobalVars
    testVariable = "A"
End Sub

Sub MyProjectMain
    InitGlobalVars
    ....
    ... rest of code
End Sub