在VBA Excel 2007中通过变异的ByRef [英] Passing Variant ByRef in VBA Excel 2007

问题描述

我在一个名为MainForm的窗体中声明的公共Variant变量

I have a public Variant variables declared in a UserForm called MainForm

Public increaseArray As Variant
Public countryArray As Variant

然后在点击按钮的MainForm的子：

Then in sub on button click for the MainForm:

Sub testButton_Click()

    Dim country As Variant

    Set countryArray = Module1.callSomeFunctionThatReturnsVariant(1)
    Set increaseArray = Module1.callSomeFunctionThatReturnsVariant(2)
    For Each country In countryArray
        Call Module1.createPage(country)
    Next country
End Sub

在模块1，我有：

Function callSomeFunctionThatReturnsVariant(ByVal testInt As Integer) As Variant
   .... do something when testInt = 1
   .... do something when testInt = 2
   callSomeFunctionThatReturnsVariant = someVariant
End Function

Public Sub createPage(ByVal country As String)

     Dim testInt As Integer

     ... do something
     testInt=insertSection(country, MainForm.increaseArray)
End Sub

Function insertSection(ByVal country As String, arr as Variant) As Integer
     Dim arrCountry As Variant

     For Each arrCountry In arr
         If country = "France" Then
             ...do something
             insertSection = 1
             Exit Function
         End If
     Next arrCountry

     insertSection = 2

End Function

路过时 MainForm.increaseArray 到 insertSection（）功能我得到ByRef参数类型不匹配错误。我已经使用功能insertSection（国家BYVAL作为字符串，BYVAL ARR为Variant）作为整数尝试，但我得到同样的错误。

I get ByRef argument type mismatch error when passing MainForm.increaseArray to insertSection() function. I've tried using Function insertSection(ByVal country As String, ByVal arr as Variant) As Integer but I get same error.

如果我尝试定义createPage子暗淡testArray为Variant Variant变量，并从其吸气功能得到increaseArray 设置testArray =的MainForm。 getterForIncreaseArray 我收到类型不匹配错误...
如果我直接传递getter函数来insertSection功能调用程序，我得到ByRef参数类型不匹配...

If I try to define a Variant variable in createPage sub Dim testArray As Variant and get the increaseArray from its getter function Set testArray = MainForm.getterForIncreaseArray I get type mismatch error... If I pass getter function directly to caller of insertSection function I get ByRef argument type mismatch...

请帮忙：）

推荐答案

这个简单的code正常工作。

this simple code works fine.

在声明公共用户窗体阵列不允许的（因此使用变种伪装是好主意）。

declaring public array in userform not allowed (so using variant as disguise was good idea).

不过，功能不希望接受把它当作参数作为一个合法的数组，所以我用一个临时的'合法'的数组。

But then, Functions dont want to accept passing it as argument as a legit array, so i used a temporary 'legit' array.

UserForm1上：

on UserForm1 :

Option Explicit

Public a As Variant 'i would usually declare it like this : Public a() as variant, but public arrays not allowed in userforms (throws error)
'Private a() as variant ,  would not throw error (inside userform)

Private Sub UserForm_Initialize()
Dim i&
ReDim a(1 To 2) 'absolutely needed, it shows a is actually an array type
a(1) = 1
a(2) = 2
End Sub

Private Sub UserForm_Terminate()
Erase a
End Sub

模块中：
    显式的选项

in a module : Option Explicit

Sub test()
Load UserForm1
Dim b&
Call get_value(1, UserForm1.a, b)
Unload UserForm1
MsgBox b
End Sub

Sub get_value(ByVal i&, ByRef arr As Variant, ByRef answer As Long) ' function won't let it through, i used a sub with aditionnal variable as Byref.
answer = arr(i)
End Sub

通过调用试射了。

Launch it by calling TEST.

请注意：我并没有传递参数的功能成功，所以通过添加名为回答一个参数做了它在SUB，至极为BYREF

Note : i didn't succeed in passing argument in a Function, so did it in a SUB by adding an argument called Answer, wich is Byref.

注2：我回头看了看我的大code，而且看起来，你可以在一个函数传递一个按地址阵列（伪装成变量），但也许是因为这个人是宣布不与（）或什么的，它不想通过一个函数来工作。

Note2 : i looked back at my older code, and it would seem that you can pass a byref Array (disguised as variant) in a function , but maybe because this one is declared not with () or whatever, it don't want to work through a function.

注3：后thurther挖掘它，我发现了一个解决方案，使用功能，因为我认为，在数组声明是麻烦制造者：

Note 3 : after thurther digging into it, i found a solution using function, and as i thought, the array-declaring was the troublemaker :

'in a module (use the same userform as before)
Sub test()
Load UserForm1
Dim b&
Dim i& 'counter
Dim Temp_Array() As Long 'as variant works too, but i filled it with numbers so as long is ok too
ReDim Temp_Array(LBound(UserForm1.a) To UBound(UserForm1.a))
'Temp_Array = UserForm1.a 'damn, i first thought this would work, in the same way you can fill a listbox in one simple line (wich would be a 3rd solution passing an array from the userform to a module)
For i = LBound(UserForm1.a) To UBound(UserForm1.a)
    Temp_Array(i) = UserForm1.a(i)
Next i
b = get_value(1, Temp_Array)
Erase Temp_Array
Unload UserForm1
MsgBox b
End Sub

Function get_value(ByVal i&, ByRef arr As Variant) As Long
get_value = arr(i)
End Function

这篇关于在VBA Excel 2007中通过变异的ByRef的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！