Variables and constants across multiple vba macros in same module & workbook
Solution 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
Solution 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.
Related videos on Youtube
gabx
I have been an active trader on Financial markets for 25 years. Since the 2008 crisis, finding a suitable position in the bank or hedge fund industry is thought, thus my professional turnaround towards IT and coding. My box since two years is a beloved Arch Linux distro. I am working with Java, Excel-vba, R-project, Latex, HTML, CSS, PHP. All these are fairly new to me and I am lost. But I learn ! I am very excited about open-source principles since a long time ago and want to apply this model to my current project : web paid servicesfor wealth managers.
Updated on January 30, 2020Comments
-
gabx about 4 years
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 :-)
-
gabx over 10 yearsTY 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 ?
-
gabx over 10 yearsI 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
-
Gary's Student over 10 yearsThanks for the feedback!
-
ForkandBeard over 10 years@gabx if this answer works for you you should consider marking it as the accepted answer...
-
gabx over 10 yearsDone. Accepeted answer, even if I am lazy and was looking for something more like a "static initialization block" a la Java.