Create and assign variables on Workbook_open, pass

Question

So I have a Workbook_open sub that creates Long variables when workbook is opened:

Private Sub Workbook_open()
  MsgBox ("Workbook opened")

  Dim i As Long
  i = 68 
End Sub

How would I pass the i value to a Worksheet_change sub for a particular worksheet?

Answer 1

Dim i inside a procedure scope makes i a local variable; it's only accessible from within the procedure it's declared in.

The idea of passing i to another procedure is very sound: it means you intend to keep variable scopes as tight as possible, and that's a very very good thing.

But in this case it's too tight, because the parameters of an event handler are provided by the event source: you need to "promote" that local variable up one scope level.

And the next tightest scope level is module scope.

You can use the Dim keyword at module level, but for consistency's sake I'd recommend using the keyword Private instead. So in the same module, declare your module-level variable:

Option Explicit
Private i As Long

Private Sub Workbook_open()
  MsgBox "Workbook opened"
  i = 68 
End Sub

If you want to expose that variable's value beyond this module, you can expose an accessor for it:

Option Explicit
Private i As Long

Private Sub Workbook_open()
  MsgBox "Workbook opened"
  i = 68 
End Sub

Public Property Get MyValue() As Long
'invoked when MyValue is on the right-hand side expression of an assignment,
'e.g. foo = ThisWorkbook.MyValue
    MyValue = i
End Property

Now the Sheet1 module's Worksheet_Change handler can read it:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox ThisWorkbook.MyValue
End sub

But it can't write to it, because the property is "get-only". If everyone everywhere needs to be able to read/write to it, then you might as well make it a global variable, or expose a mutator for it:

Option Explicit
Private i As Long

Private Sub Workbook_open()
  MsgBox "Workbook opened"
  i = 68 
End Sub

Public Property Get MyValue() As Long
'invoked when MyValue is on the right-hand side expression of an assignment,
'e.g. foo = ThisWorkbook.MyValue
    MyValue = i
End Property

Public Property Let MyValue(ByVal value As Long)
'invoked when MyValue is on the left-hand side of an assignment,
'e.g. ThisWorkbook.MyValue = 42; the 'value' parameter is the result of the RHS expression
    i = value
End Property

Answer 2

For that, declare i as a Public Variable on a Standard Module like Module1 and then you can access the value of i in Sheet Change Event if it is initialized during Workbook Open Event.

On Standard Module:

Public i As Long

On ThisWorkbook Module:

Private Sub Workbook_open()
  MsgBox ("Workbook opened")
  i = 68
End Sub