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

mwolfe02 · Accepted Answer · 2011-03-07 22:34:31Z

4

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

answered Mar 7, 2011 at 22:34

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

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

2 Comments

Jesse Smothermon Over a year ago

Thank you for the answer, it worked perfectly. As it turns out I do have a lot of variables that I need to use in many different functions as well as the main Sub. I feel like Private might fix that problem so it's only module level. Do you have any advice for what kind of assignment I should use? Right now I have ten variables that need to be global with a high potential that more will be added. Thanks again

Thomas Over a year ago

@Jesse Smothermon - A class would be a logical choice. In addition, you can encapsulate the initialization of the variables into the class itself.

RichardTheKiwi · Accepted Answer · 2011-03-07 22:32:16Z

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

answered Mar 7, 2011 at 22:32

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Comments

Thomas · Accepted Answer · 2011-03-07 22:47:11Z

2

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

Yes, although you need to declare the scope as stated by mwolf02. However, what you cannot do in VBA is populate them outside of a function or procedure call like you can with VBScript.

In addition, if you really need global variables, you should derive a naming scheme that differentiates global variables from locally declared variables (e.g. prefix them with g_). You can get this confusing scenario:

Public testVariable as String

Public Sub Main()
    Call Foo()
    Call Bar()
End Sub

Public Sub Foo()
    testVariable = "Foo"
End Sub

Public Sub Bar()
    Dim testVariable As String
    testVariable = "Bar"
End Sub

The value of the testVariable will be "Foo" even though Bar was called later. In fact, there is no way to reference the global variable testVariable from within Bar since it has declared a local variable with the same name.

answered Mar 7, 2011 at 22:47

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

2 Comments

Jesse Smothermon Over a year ago

Awesome point with the prefix idea.... didn't occur to me that you wouldn't be able to change the variable like that later. Thank you

RolandTumble Over a year ago

This points out another Bad Practice that the VB family of languages let you trip yourself up with--locally declared variables do not have to have unique names vis a vis variables iwht broader scope. The narrower-scoped variable just "stands in front of" the broader one.

Tiago Cardoso · Accepted Answer · 2011-03-08 04:15:43Z

1

Alternatively, you can use global constants rather than variables.

Public Const TESTVARIABLE = "A"

I believe it fits better your question rather than setting the value within a specific function.

Obs1.: Global constants are (usually) stored in capital letters.

Obs2.: You cannot set a global constant within a worksheet code. Use a specific module (global.bas, for instance) to do it.

Rgds

answered Mar 8, 2011 at 4:15

Tiago Cardoso

2,1061 gold badge16 silver badges34 bronze badges

Comments

MPękalski · Accepted Answer · 2011-03-07 22:46:23Z

0

Very brief example:

Dim testVariable As String
Dim newVariable As String

Sub setVariable(ByVal x as string)
    testVariable = x
End Sub

Function testFunction() As String
    testFunction = testVariable
End Function

Sub test()
    Call setVariable("A")
    MsgBox testFunction()
    Call setVariable("B")
    MsgBox testFunction()
End Sub

As mentioned in other posts: globals are a bad practice, and a value has to be assigned inside sub/functions. They should also be defined at the beginning of the module.

answered Mar 7, 2011 at 22:46

MPękalski

7,1045 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

VBA Functions and variables

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related