Passing Variant ByRef in VBA Excel 2007

Question

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

Public increaseArray As Variant
Public countryArray As Variant

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

In Module1 I have:

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

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.

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...

Please help :)

I don't think the scope of a userform falls outside of it even if it's public. — findwindow
@findwindow, wrong, you definitly can, just you need to declare as public, and then call it with the userform's name. as for the question, pass the argument as byref (actually write byref , don't be lazy) — Patrick Lepelletier
@PatrickLepelletier you have documentation or code to prove that? I had to hack my own code but would be nice if the scope fall outside. — findwindow

Patrick Lepelletier Patrick Lepelletier · Accepted Answer · 2016-02-05T23:07:56

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.

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.

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.

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.

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

Passing Variant ByRef in VBA Excel 2007

2 Answers