在Workbook_open上创建并分配变量，然后将其传递给Worksheet_change [英] Create and assign variables on Workbook_open, pass it to Worksheet_change

问题描述

因此，我有一个Workbook_open子项，该子项在打开工作簿时创建Long变量:

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

如何将i值传递给特定工作表的Worksheet_change子项?

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

推荐答案

Dim i使i成为 local 变量；只能从声明它的过程中访问它.

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

将i传递给另一个过程的想法很合理:这意味着您打算尽可能保持可变范围，这是非常非常好的事情.

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.

下一个最严格的作用域级别是模块作用域.

您可以在模块级别使用Dim关键字，但是为了保持一致性，我建议改为使用关键字Private.因此，在同一模块中，声明您的模块级变量:

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

现在Sheet1模块的Worksheet_Change处理程序可以读取了:

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

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

但是它不能写入，因为该属性是仅获取"的.如果各地的每个人都需要能够对其进行读写，那么您最好将其设置为全局变量，或者公开一个 mutator :

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

这篇关于在Workbook_open上创建并分配变量，然后将其传递给Worksheet_change的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！