vba 有没有办法跨模块使用局部变量?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9889202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 12:50:39  来源:igfitidea点击:

Is there a way to use local variables across modules?

vbaexcel-vbaexcel

提问by Cutter

I have a VBA project with a Module and a Userform. I want to use some variables from my module in my Userform.

我有一个带有模块和用户表单的 VBA 项目。我想在我的用户表单中使用我模块中的一些变量。

Is it necessary that I declare these variables globally? I don't like global variables because they stay in memory after the code's execution, and I have to reset these variables one-by-one at the beginning of my macro. I can't use the keyword End to clear the variables from memory because I need to remember one or two of these variables. How do people usually do?

我是否有必要全局声明这些变量?我不喜欢全局变量,因为它们在代码执行后仍留在内存中,而且我必须在宏的开头逐一重置这些变量。我不能使用关键字 End 从内存中清除变量,因为我需要记住这些变量中的一两个。人们通常是怎么做的?

Edit: is there a way to pass a variable from the Userform to the module without creating a global variable?

编辑:有没有办法在不创建全局变量的情况下将变量从用户窗体传递到模块?

采纳答案by Dick Kusleika

Create a Public Property in your userform and pass the variable into the form using the property. Useforms are just class module except that they have a user interface component. Example: In your userform's code module

在您的用户表单中创建一个公共属性,并使用该属性将变量传递到表单中。Useforms 只是类模块,除了它们有一个用户界面组件。示例:在您的用户表单的代码模块中

Option Explicit

Private msLocalVar As String

Public Property Get LocalVar() As String
    LocalVar = msLocalVar
End Property

Public Property Let LocalVar(sLocalVar As String)
    msLocalVar = sLocalVar
End Property

Private Sub CommandButton1_Click()

    Me.LocalVar = Me.LocalVar & " more strings attached"

    Me.Hide

End Sub

And in a standard module

在标准模块中

Sub ShowForm()

    Dim sLocalVar As String
    Dim ufUserForm1 As UserForm1

    sLocalVar = "Some string"

    Set ufUserForm1 = New UserForm1
    ufUserForm1.LocalVar = sLocalVar 'pass in variable
    ufUserForm1.Show

    'This executes after the .Hide in the form
    Debug.Print ufUserForm1.LocalVar

    'Close out the form - previously just hidden
    Unload ufUserForm1

End Sub

回答by Moosli

One way you can do this would be to declare public variables like this:

您可以这样做的一种方法是像这样声明公共变量:

Public strvar as String
Sub Abfrage()
'Your Code
End Sub

But if you don't prefer this way, you could insert a new Excel sheet, set the properties of the new sheet to "2 - xlSheetVeryHidden" and you can write your values in the cells of that sheet and read them in from your UserForm.

但是,如果您不喜欢这种方式,您可以插入一个新的 Excel 工作表,将新工作表的属性设置为“2 - xlSheetVeryHidden”,然后您可以在该工作表的单元格中写入您的值并从您的用户窗体中读取它们.

So you will have no global variables, but all the values are on that sheet. Then, you can take out the values from that sheet if you need to.

因此,您将没有全局变量,但所有值都在该工作表上。然后,您可以根据需要从该工作表中取出值。