Passing Variant Array ByRef

[Steve D]

Hi, All. I'd like to pass an empty Variant Array to a function by reference.
The function opens and reads a text file, and returns the populated Variant
Array. See code below. I find I have to define two variant arrays as shown
in Sub Test(), pass one to the function, then assign it to the second
Variant Array.

Question: Why can't I just call ImportAll(vWords) as shown below and have
vWords get populated ByRef?

TIA, Steve

Sub Test()
Dim vWords As Variant
Dim nWords As Variant

nWords = ImportAll(vWords) <-- This Works
'ImportAll (vWords) <--Doesn't Work
End Sub


Function ImportAll(ByRef vWords As Variant) As Variant
On Error GoTo E_Handle
Dim strImport As String
Dim lngChars As Long
Dim intFile As Integer
Dim i As Integer

intFile = FreeFile
Open "C:\Documents and Settings\username\Desktop\Capitalization
Exceptions List.txt" _
For Input As intFile
lngChars = LOF(intFile)
strImport = Input(lngChars, intFile)

vWords = Split(strImport, vbCrLf)
ImportAll = vWords
End Function

 

[Jezebel]

You're confusing two issues. FRirst, if you're going to return a variant
array as the function value, you don't need to pass the array TO the
function in the first place --

nWords = ImportAll

would work just as well if you change the last lines of the function to >
ImportAll = Split(strImport, vbCrLf)
and remove the argument entirely.

Second, if you use this sytnax --

ImportAll (vWords)

the brackets around the argument force VBA to pass the argument ByVal not
ByRef. Remove the brackets and it will work.

 

[Steve D]

Thanks, Jezebel. Worked perfectly by calling "importAll vWords" without the
brackets. I'd already figured out how to assign the split function result
directly to the passed array by " vWords = Split(strImport, vbCrLf)" rather
than making it a return value of the function.

I always have to struggle to remember ByRev vs. ByVal, how to pass arrays,
etc. I usually finally figure out, but this time I was stumped.