Static Arrays as Return Values.
There's a common trick when returning an array from a function: to assign the array to the function name in the very last statement before leaving the function (i.e. just before "Exit Function" or "End Function"). This makes VB switch pointers rather than copy the local array to the return value. The local copy is left empty but that doesn't matter because it's guaranteed not to be used (or even exist) afterwards since we immediately exit the function after the statement. Thus this function
Public Function MyArrayFunc() As Long()
Dim lstRet() As Long
lstRet = InitArray
DoSomething
MyArrayFunc = lstRet ' Last statement: DOESN'T COPY local array.
End Function
will be faster than
Public Function MyArrayFunc() As Long()
Dim lstRet() As Long
lstRet = InitArray
MyArrayFunc = lstRet ' Not last statement: DOES COPY local array.
DoSomething
End Function
So that's a good thing, then. It's not exactly intuitive but you get a speed increase by the simple measure of assigning the return array in the last statement, which in most cases makes perfect sense anyway.
Now for a not so good thing: VB TREATS STATIC ARRAYS THE SAME WAY.
This function will work the first time around but return an empty array from all subsequent calls:
Public Function MyStaticArrayFunc() As Long()
Static lstRet() As Long
Static bInit As Byte
If bInit = 0 Then
lstRet = InitArray
bInit = 1
End If
MyStaticArrayFunc = lstRet ' Last statement: DOESN'T COPY local array EVEN THOUGH IT'S STATIC!
End Function
The "static" array is initialized and then emptied in the first call.
To avoid this we need to ensure that there's at least one statement, any statement, between "MyArrayFunc = lstRet" and the function exit. In the above example there are no other calls to be made so instead "bInit = 1" could be placed after the return value assignment:
Public Function MyStaticArrayFunc() As Long()
Static lstRet() As Long
Static bInit As Byte
If bInit = 0 Then
lstRet = InitArray
End If
MyStaticArrayFunc = lstRet ' Not last statement: DOESN'T COPY local array.
bInit = 1 ' Looks unnecessary, should be unnecessary, but isn't.
End Function
This is another annoying VB hack (like we didn't have enough of those). Assigning 1 to bInit should of course not be necessary except in the first call but that superfluous assignment is a small price to pay for keeping the static array truly static.
| 
