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
Is there a way to use local variables across modules?
提问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.
因此,您将没有全局变量,但所有值都在该工作表上。然后,您可以根据需要从该工作表中取出值。