passing array to function to pick up values

[Birgit]

Hi all,
I am trying to sort out the fijner points of passing arrays between
functions. I simply do not understand how this works. Of course I get it all
to work with a global array, which I can modify anytime, but I'd rather
learn to do it by passing arguments. I have already found to use ByRef while
digging in the archive.

If I run the function UseArray I get as result:
, bla

instead of
<some character> , bla

what's wrong?

code follows
-------
Function MakeArray(ByRef myArray As Variant)
Dim teller As Long

For teller = 1 To 10
'assign some random alphanumeric stuff to array
myArray(teller) = Chr(teller * 10)
Next teller
End Function

Function UseArray()
Dim varArr As Variant
Dim i As Long

'give a large dimension here because can't redim preserve inside other
function
'can be shrunk afterwards if I ever get this to work
'this code is just a tryout for other stuff I'm doing in a project where
I don't know
'how many items I pick up along the way.
ReDim varArr(100)
MakeArray (varArr)
For i = 1 To 10
Debug.Print varArr(i) & " , bla"
Next i
End Function

 

[Dave Lett]

Hi Birgit,

I've used the Join statement to pass a string (a joined array) and then
break it apart again using Split statement in the receiving
routine/function.
You might find the article "How to Create and Use a Global Array in VBA
Procedures" at
http://support.microsoft.com/default.aspx?scid=kb;en-us;170721&Product=wrd
interesting/useful.

good luck and HTH
Dave

 

[Steve Lang]

Hi Birgit,

Here's some code that passes a string array to a function:

Dim strStuff(1 to 5) As String

dim intAnswer As Integer

intAnswer = GetAnswer (strPassed:=StrStuff)

-------------

Public Function GetAnswer (strPassed() As String) As Integer

'blah blah blah

End Function



HTH and have a great day!



Steve

 

[Jay Freedman]

Hi Birgit,

This is a little strange: The array comes back empty because you're calling
a function but not assigning it to anything. If you do this

Dim bRes As Boolean
bRes = MakeArray(varArr)

then it all works. Likewise, if you change the declaration of MakeArray to a
Sub and call it with

MakeArray varArr

then it works. (There's a stupid rule in VBA that you must use parentheses
in a function call but you cannot use them in a subroutine call, as if the
interpreter can't tell the difference.)

 

[Birgit]

Hi Jay,
Thank you so much! I believe I will have to look at all my projects very
carefully because I have this situation a lot (functions being called like
subs). However, I never ran into problems before. But then I was also not
passing around arrays. I'll avoid it in the future in order to make the code
more stable.

I also thank the other contributors to my question. I will keep those tips
for future projects.

Birgit