Excel VBA Variable value Scope - assign value to a variable for all subs

Question

I am writing a module with several subs, and I need some variables to have the same value in all the modules. I know about declaring variables as

Public varname as vartype

but how can I assign a global value to such a variable?

Thanks

Put it at the top of any module sheet just below `Option Explicit` and it will be available to all subs in all module and worksheet code pages. This is known as the **Declarations** area. — , Sep 10 '15 at 11:48
@Jeeped I am not talking about declaring the variable, I want to assign a value to it, a value that I want to use all throughout the module; something like a "Public" value; apparently, this is not possible outside a sub — horace_vr, Sep 10 '15 at 11:52
Assign a value as you would normally, in any sub or even a function. If the value to be assigned is static then the var can be declared as a constant; e.g. `public const csURL as string = "http://stackoverflow.com/"` but you will not be able to change it. — , Sep 10 '15 at 11:53
Creating a public variable and assigning a value is definitely possible. I have a feeling you're forgetting to provide some information. — DanL, Sep 10 '15 at 12:02
You need to tell us what **data type** you're trying to use for your "global" variable in order to get a complete answer. — SierraOscar, Sep 10 '15 at 12:28
@MacroMan it is a worksheet type, if that is of any help; I want to set a certain worksheet ("Sheet1") as a variable to be used in all Subs. Not sure why I got the downvote... — horace_vr, Sep 10 '15 at 12:36
@horace_vr I didn't downvote so don't know - basically, you can't declare an object in this way (see here -http://stackoverflow.com/questions/31536519/declare-a-workbook-as-a-global-variable/32251725#32251725 so your only option is to assign a value from an event. — SierraOscar, Sep 10 '15 at 12:40
I am trying to avoid writing codes in events, for now. I ended up this this, which seems to be doing the trick: define it as public, assign the value in a "Settings" sub, then call the Settings sub as the very first line in the Main sub — horace_vr, Sep 10 '15 at 12:44

score 4 · Accepted Answer · edited May 23 '17 at 10:27

For a worksheet object (any object for that matter) you need to do the following:

In a standard code module:

Public varName As Excel.Worksheet

In the Workbook_Open() event:

Private Sub Workbook_Open()
    Set varName = Sheets("mySheet")
End Sub

Then you can refer to varName in any other module for that workbook and it will point to your worksheet object.

From your question/comments, it seems that you actually want some sort of object constant, which can't be done in VBA - see Declare a Workbook as a Global variable for more infromation.

If you're referring to a value data type such as String, Integer or Long then you can use a constant instead of a variable, however a constant's value cannot be changed once it has been declared (kind of the definition of 'constant') i.e.

Public Const someName As String = "Macro Man"
Public Const someNumber As Long = "1234567890"
Public Const someInt As Integer = "1453"

Excel VBA Variable value Scope - assign value to a variable for all subs

1 Answers1