In my VBA project I have a number of cells which I refer to within many modules and subs. For ease of reading sake I have declared and initialised a variable to the cell reference in the following way:
LowerBound = ThisWorkbook.Sheets("Rules").Range("B22").Value
I would like to initialize this variable just once in my project, is it correct to use a global variable in this instance? I have found lots of material suggesting these should be avoided (as per this answer Initialising global variables in VBA). If this is incorrect what is best practice when referring to a cell throughout a project?
One thing good practice is initializing the globals in a main procedure, and then passing them to any other procedures that require them. This helps prevent the problem of inadvertently changing a value in another procedure.
If you are unable to do that it might mean you need to rethink the organization of your code.
EDIT: Whether to initialize global variables in WorkBook_Open:
Although you might want to initialize them in the Workbook_Open event, I wouldn't do that unless that's when they are needed.
If, for example, your workbook could be left open for more than a day and you initialize a variable based on the day of the week, doing so when the workbook opens would be a problem. Without specific details, it's hard to say, but my general advice is to initialize them at the start of whatever process relies on them. So, for example, if there needed every time somebody presses a button, initialize them in the routine that responds to the button press. If, on the other hand, they rely on the state of things that the workbook is opened, then you'd want to initialize them at that moment. I hope that helps