I have a worksheet called "Parameters" in my workbook that holds values that will be used in various subroutines. I need to gather the initial values when the workbook opens and keep them for later use. I can gather the values when the workbook opens by placing code in the ThisWorkbook code window. A snippet is shown below.
Private Sub Workbook_Open()
'THIS SUBROUTINE LOADS THE INITIAL PARAMETERS WHEN THE WORKBOOK OPENS
'TODO: Need to set up error trapping to warn &/or escape if fails.
Dim varWorkbooksAddress As String
Dim varWorkbooksRow As String
Dim varWorkbooksCol As String
'varWorkbooksAddress = Cell location of first workbook to track
(What:="Worksheet 1", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
varWorkbooksAddress = ActiveCell.Address
varWorkbooksRow = ActiveCell.Row
varWorkbooksCol = ActiveCell.Column
Cells(1, 1).Select ' PARKING Cell
What is the best method for holding these values for later use?
I can send them to another subroutine for immediate use with something like the following.
But I'm thinking it would be better to retrieve the values when they are needed. Plus I don't always need the values immediately. I could copy the initial values to a worksheet and then retrieve them as needed, but that seems mighty inefficent.
I'd appreciate any suggestions on how this could best be accomplished.
Thanks in advance,