Variables and constants across multiple vba macros in same module & workbook

Question

After a lot of small sub() writing in the same Excel workbook, I realised that I often used same part code, variables and constants. Thus I decided to write funcions() for the code, and declare variables & constant/static as Public outside functions and sub. I am very new to vba declarations and this is not so easy. Let me give you one summary of what i want to achieve. I have writen all funcions and sub in one module under the module directory of the workbook.

Option Explicit
Public ToDate As String  ' variable I use in many sub and functions
Public MyPath As String  ' variable I use in many sub and functions
Public NameOfWorker As Variant  ' constant I use in many sub and functions 
Public Salary As Double ' constant I use in many sub and functions   

NameOfWorker = Cells(14, 19)  ' !!! PB : 14 is highlighed with error : incorrect instruction outside a procedure
Salary = Cells(20, 7).Value  '!!! same as above

How and where shall I declare such constants/statics ? Shall I write a "special" procedure to declare all these variables and constants ? I tried many way to declare them with no success.

Public Static NameOfWorker = Cells(14, 19) As String ' not working
''''''
Public Static nameOfWorker As String
NameOfWorker = Cells(14, 19)  ' not working
''' etc etc

Thank you for help.

EDIT : after more reading, I found one solution this way:

Public Const MY_PATH = "Y:\path\to\directory\"
Public Const WORKERNAME = "14, 19"

Not so bad :-)

ForkandBeard · Accepted Answer · 2013-10-28 13:41:02Z

2

You could create a new module called something like DataHelper which looks like this:

Private NameOfWorker As String
Private AgeOfWorker As Long
Private SetupComplete As Boolean

Public Function GetNameOfWorker()
    If NameOfWorker = "" Then
        NameOfWorker = Sheets("SomeSheet").Cells(14, 19)
    End If

    GetNameOfWorker = NameOfWorker
End Function

Public Function GetAgeOfWorker()
...
End Function

Now in any other code you can retreive the value:

Sub SomeMethod()    
    Cells(1, 1).Value = DataHelper.GetNameOfWorker()    
End Sub

...and you never have to worry if it's been set.

answered Oct 28, 2013 at 13:41

ForkandBeard

88410 silver badges28 bronze badges

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

1 Comment

gabx Over a year ago

TY for your answer, but it seems to me this will let me with more code writing, and it is not what I want. My idea was to extend the scope of variables/constants in a simple way.**EDIT**: I understand I need to write as many helper funcions as constants, right ?

Gary's Student · Accepted Answer · 2013-10-28 13:32:51Z

1

Good question!

I would Dim the globals above all the subs in the module, but initialize the globals at a convenient spot within some sub. For example:

Public NameOfWorker As String
Public AgeOfWorker As Long
Public SetupComplete As Boolean

Sub MAIN()
If SetupComplete Then
Else
    NameOfWorker = Sheets("Sheet1").Range("B9")
    AgeOfWorker = Sheets("Sheet1").Range("B10")
    SetupComplete = True
    MsgBox "Global variable set up complete!"
End If
End Sub

answered Oct 28, 2013 at 13:32

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

3 Comments

gabx Over a year ago

I am not sure to fully understand, but I will give a try. Btw, if elarging the scope of my variables to all procedures inside the same module is painful, I will enjoy Ctrl C, Ctrl V :-). EDIT: got it. Globals oustide all procedures and initialize Inside. TY

ForkandBeard Over a year ago

@gabx if this answer works for you you should consider marking it as the accepted answer...

gabx Over a year ago

Done. Accepeted answer, even if I am lazy and was looking for something more like a "static initialization block" a la Java.

Collectives™ on Stack Overflow

Variables and constants across multiple vba macros in same module & workbook

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related