vba 如何在vba模块中的函数调用之间保留变量值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19470272/
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
How to preserve variable value between function calls in vba module?
提问by Ga?per Sladi?
I've created module in vba. I have two functions "AddDropDowns" and "RemoveDropDowns". In first function I would like to store a value in a variable that would be accessible in second function at a later time. I declared a variable called "DropDownsCounter" in the same module as these two functions are in, but the variable does not preserve it's value between function calls. My question is why and how to achieve this? Code of this module is below.
我在 vba 中创建了模块。我有两个函数“AddDropDowns”和“RemoveDropDowns”。在第一个函数中，我想将一个值存储在稍后可在第二个函数中访问的变量中。我在这两个函数所在的同一模块中声明了一个名为“DropDownsCounter”的变量，但该变量不会在函数调用之间保留其值。我的问题是为什么以及如何实现这一目标？该模块的代码如下。
Option Explicit
Private DropDownsCounter As Integer
Public Const QueryAttributes = "Query1:Query2:Query3:Query4:Query5"
Private Const DropDownsWidth = 70
Private Const DropDownsHeight = 16.5
Function AddDropDowns()
DropDownsCounter = DropDownsCounter + 1
Dim QueryAttributesArray() As String
Dim NumberOfDropDowns As Integer
QueryAttributesArray() = Split(QueryAttributes, ":")
Application.ScreenUpdating = False
Dim x As Integer
For x = 0 To UBound(QueryAttributesArray)
Dim Name As String
Name = "DropDown_" & (NumberOfDropDowns + x)
Dim CmbBox As OLEObject
Set CmbBox = Worksheets("Poizvedba").OLEObjects.Add("Forms.ComboBox.1")
With CmbBox
.Left = GetLastDropDownLeftPos(DN)
.Top = Range(DNStartCell).Top + x * DropDownsHeight
.Width = DropDownsWidth
.Height = DropDownsHeight
.Name = Name
End With
Next x
Application.ScreenUpdating = True
End Function
Function RemoveDropDowns()
Dim QueryAttributesArray() As String
Dim LastDropDown As Integer
DropDownsCounter = DropDownsCounter - 9
QueryAttributesArray() = Split(QueryAttributes, ":")
Dim OleObj As OLEObject
For Each OleObj In Worksheets("Poizvedba").OLEObjects
Dim SplittedObjectName() As String
SplittedObjectName() = Split(OleObj.Name, "_")
If SplittedObjectName(0) = "DropDown" Then
LastDropDown = SplittedObjectName(1)
End If
Next OleObj
Dim StartIndexToRemove As Integer
Dim EndIndexToRemove As Integer
StartIndexToRemove = LastDropDown - UBound(QueryAttributesArray)
EndIndexToRemove = LastDropDown
Dim Sh As OLEObject
For Each Sh In Worksheets("Poizvedba").OLEObjects
Dim x As Integer
For x = StartIndexToRemove To EndIndexToRemove
If Sh.Name = "DropDown_" & x Then
Sh.Delete
Exit For
End If
Next x
Next Sh
End Function
Private Function GetLastDropDownLeftPos(ByVal DropDownCategory As String) As Integer
Dim pos As Integer
pos = Range("A4").Width + Range("B4").Width + DropDownsWidth * DropDownsCounter
GetLastDropDownLeftPos = pos
End Function
New code that still loses variable value
仍然丢失变量值的新代码
Worksheet code:
工作表代码：
Public QueryDropDownsCollection As New Collection
Public Sub CommandButton1_Click()
Dim NewQuery As QueryDropDown
Set NewQuery = New QueryDropDown
QueryDropDownsCollection.Add NewQuery
Call NewQuery.Initialize(1, 20, 20, 70, 17, 9)
NewQuery.AddDropDowns
End Sub
Public Sub CommandButton2_Click()
QueryDropDownsCollection(QueryDropDownsCollection.Count - 1).RemoveDropDowns
End Sub
Class Code:
班级代码：
Private pID As Integer
Private pDropDownsWidth As Integer
Private pDropDownsHeight As Integer
Private pLeftPos As Integer
Private pTopPos As Integer
Private pNumberOfDropDowns As Integer
Private pDropDownNames() As String
Property Get ID() As Integer
ID = pID
End Property
Private Const DropDownsWidth = 70
Private Const DropDownsHeight = 16.5
Public Sub Initialize(ByVal ID As Integer, ByVal LeftPos As Integer, ByVal TopPos As Integer, ByVal DropDownsWidth As Integer, ByVal DropDownsHeight As Integer, ByVal NumberOfDropDowns As Integer)
pID = ID
pLeftPos = LeftPos
pTopPos = TopPos
pDropDownsWidth = DropDownsWidth
pDropDownsHeight = DropDownsHeight
pNumberOfDropDowns = NumberOfDropDowns
pSheet = Sheet
End Sub
Function AddDropDowns()
For x = 0 To (pNumberOfDropDowns - 1)
Dim Name As String
Name = "DropDown_" & pID & "_" & x
ReDim Preserve pDropDownNames(0 To x)
pDropDownNames(x) = Name
With ActiveSheet.OLEObjects.Add("Forms.ComboBox.1")
.Left = LeftPos
.Top = pTopPos + x * pDropDownsHeight
.Width = pDropDownsWidth
.Height = pDropDownsHeight
.Name = Name
With .Object
.AddItem "Krneki1"
End With
End With
Next x
End Function
Function RemoveDropDowns()
Dim Sh As OLEObject
For Each Sh In ActiveSheet.OLEObjects
Dim x As Integer
For x = 0 To pNumberOfDropDowns
If Sh.Name = pDropDownNames(x) Then
Sh.Delete
Exit For
End If
Next x
Next Sh
End Function
采纳答案by Ga?per Sladi?
Well the problem with preserving global variables between function calls lies in the dynamically adding OLEObjects to worksheet. When OLEObject is added from the VBA code to Worksheet, the project needs to recompile, because the OLEObject itself becomes a property of the project. It the process of recompiling, it losses all the variable values. Some references to this problem that I also found:
那么在函数调用之间保留全局变量的问题在于动态添加 OLEObjects 到工作表。当 OLEObject 从 VBA 代码添加到 Worksheet 时，项目需要重新编译，因为 OLEObject 本身成为项目的一个属性。在重新编译的过程中，它会丢失所有变量值。我还发现了一些对这个问题的引用：
回答by jacouh
The variable DropDownsCounter should be incremented by each call of AddDropDowns(), it is only used in GetLastDropDownLeftPos(), but not in RemoveDropDowns().
变量 DropDownsCounter 应该在每次调用 AddDropDowns() 时递增，它仅用于 GetLastDropDownLeftPos()，而不用于 RemoveDropDowns()。
In AddDropDowns() the variable NumberOfDropDowns is a local variable, In RemoveDropDowns() the variable NumberOfDropDowns is implicitly global variable.
在 AddDropDowns() 中，变量 NumberOfDropDowns 是局部变量，在 RemoveDropDowns() 中，变量 NumberOfDropDowns 是隐式全局变量。
Have you confused NumberOfDropDowns with DropDownsCounter variable ?
您是否将 NumberOfDropDowns 与 DropDownsCounter 变量混淆了？
In all your VBA code, you should declare explicitly your variables, by adding on the header of a module:
在您的所有 VBA 代码中，您应该通过在模块的标题上添加来明确声明您的变量：
Option Explicit
This will give compiling error of your code for debugging.
这将导致您的代码编译错误以进行调试。
回答by Cool Blue
I would suggest creating a class module to manage the drop downs. You can then instance it with a Public, Module level declaration in an ordinary module. The properties and internal variables will retain their values between calls until the project is reset by an End statement or by VBE reset.
我建议创建一个类模块来管理下拉菜单。然后，您可以在普通模块中使用公共、模块级别的声明来实例化它。属性和内部变量将在调用之间保留其值，直到项目被 End 语句或 VBE 重置为止。
