Defining variables across Subs (w/in module) in VBA

user_12252560 0
Viewed 183

Would like to define reference variables (calls a value from a cell in the sheet using ActiveSheet.Cells[row, col]) in one location in a module, for use across multiple subs in an MS Excel file. The file is an action tracker, the subs automate some of the emailing (each subs opens emails under given conditions). All reference variables are the same for each sub - defining in one place will make maintaining the spreadsheet much simpler.

Tried to define variables above first sub, error message appears on first value (as detailed below). I've searched (a) Global Variables and (b) how to define above the subs. However (a) variables all in the same module (b) error message as detailed below. I haven't located a guide on defining variables using ActiveSheet.Cells() references.

Option Explicit
'Defines variables for all macros:

'Defining Reference Variables
Today = ActiveSheet.Cells(2, 4)
ActionLogTitle = ActiveSheet.Cells(3, 3)
IPT_Leader = ActiveSheet.Cells(7, 7)

(On Today = ActiveSheet.Cells(2,4) error highlights on "2")

Compile error: Invalid outside procedure

vba excel variables
2 Answers

As the compiler is hinting, you cannot write assignments outside of a Sub/Function.

You can declare a function for each variable:

Function MyValue()
    MyValue = ActiveSheet.Cells(2, 4).Value
End Function

Ideally you don't use ActiveSheet unless that's really what you want though.

user_4788840

There are a lot of ways to define today, but using a word, which is used by Excel English formula =TODAY(), is probably a discussable idea (although it will work!). In general, consider declaring the variable like this somewhere in the modules:

Public myToday as Date

Then, you may reset it everytime the worksheet is openned:

Private Sub Workbook_Open()
    myToday = Date
    'or 
    myToday = Worksheets("Name").Range("D2").Value
End Sub

Anyway, working with Public variables is in general discouraged in any programming language, thus it is probably a better idea to come up with a dedicated function or class for it.

user_54486260
Related