VBA在用户窗体代码中使用全局变量 [英] VBA use global variable in userform code

问题描述

我正在使用Excel 2010 VBA.有没有一种方法可以访问用户窗体内部的代码中在用户窗体外部声明的全局变量中的值?我的用户表单中的代码将全局变量返回为null-无法找出原因！

I'm working in Excel 2010 VBA. Is there a way of accessing values in global variables declared outside a userform, in code inside the userform? Code inside my userform returns the global variable as null - can't work out why!

该变量在ThisWorkbook模块中声明为:

The variable is declared in the ThisWorkbook module as:

Public TargetCell As Range
Public TargetCellWorksheet as Worksheet
Public CurrentValue As Long

在用户表单中，我在更新"按钮上具有以下代码:

Inside the userform, I have this code on the "Update" button:

Private Sub Update_Click()
    MsgBox ("Start of Update sub. TargetCellWorksheet =" & TargetCellWorksheet)
End Sub

msgbox为变量返回".

The msgbox returns "" for the variable.

希望有人可以帮助我理解这一点，以及如何访问用户表单中的变量?预先谢谢你

Hoping someone may be able to help me understand this and how to access the variable inside the userform? Thank you in advance

推荐答案

对于问题本身，您声明

Public TargetCellWorksheet as Worksheet

，然后尝试将其显示到MsgBox中:

and then try to show it into a MsgBox:

MsgBox ("Start of Update sub. TargetCellWorksheet =" & TargetCellWorksheet)

您可能是指 TargetCellWorksheet.Name 或 TargetCellWorksheet.Range("A1").Value ，因为MsgBox希望接收字符串吗?

Did you maybe mean TargetCellWorksheet.Name, or TargetCellWorksheet.Range("A1").Value, since the MsgBox expects to receive a string?

但是，如果您确定自己的代码，则可能取决于变量未正确声明为Public且仅在模块级别进行的事实.如果变量是表单本身的一部分，则可能需要向表单中添加属性(我假设您打算使用 CurrentValue ，但是您可以简单地从更改属性的类型>长到工作表并使用它):

However, if you're sure about your code, it might depend on the fact that the variable is not properly declared as Public and it goes at module level only. You might want to add a property to your form, if the variable is part of the form itself (I assume that you meant to use CurrentValue, but you can simply change the type of the property from Long to Worksheet and use it instead):

这在表单代码中

Dim pCurrentValue As Long
Public Property Get CurrentValue() As Long
    CurrentValue = pCurrentValue 
End Property
Public Property Let CurrentValue (value As Long)
    pCurrentValue = value
End Property

因此，将变量从模块传递到如下形式:

Hence, passing the variable from the module to the form like this:

在输入表单代码之前，这已进入您的模块

Dim myForm As New yourForm
myForm.CurrentValue  = whateverYourVariableIs

，然后像这样在表单中使用变量:

and so using the variable inside your form like this:

因此，您可以通过从表单的属性中调用变量来使用变量

myVariableInTheForm = Me.CurrentValue 

我必须说，但是，一个公共变量没有到达表格的堆栈很奇怪.您确定不仅在声明变量之前没有分配任何值吗?

I must say that, however, it is strange that a public variable is not reaching the stack of the form. Are you sure you're not only declaring the variable without assigning any value before?

这篇关于VBA在用户窗体代码中使用全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！