# Thread: Function Returning an Array? (Excel 2003)

1. ## Function Returning an Array? (Excel 2003)

Hello all,

I have this code which is nearly optimised (well - optimised relative to my beginner's framework...). The last task before reaching (relative) esthetic perfection is to "functionalise" (if such a word exists) the following piece of code which I run repeatedly throughout:

ReDim Array(n) As Single
For i = 0 To UBound(Array)
Array(i) = Range("Array_InputRange").Offset(0, i).Value
Next i

Ideally I would like to define a function called, say, Pop_Array, which takes as its argument the array's input range, and populates the array directly, so that the above line of code would become simply:

ReDim Array(n) as Single
Array = Pop_Array("Array_InputRange")

(You can assume n has been previously calculated to correspond to the number of cells in "Array_InputRange".)

Does anybody know how to do this? I know there is an Excel-defined function called Array, but I don't seem to be able to use that for my ends.

Alexander

2. ## Re: Function Returning an Array? (Excel 2003)

Welcome to Woody's Lounge!

You don't need a function. Simply declare your variable as a variant:

Dim varArray As Variant
varArray = Range("Array_InputRange")

Note: the result will ALWAYS be a two-dimensional array, even if the range consists of only one column or one row. To retrieve an element of the array, you must provide a row index and a column index:

varArray(2, 1)

3. ## Re: Function Returning an Array? (Excel 2003)

Thanks a lot Hans that looks great.

One more question (probably very naive): doesn't a Variant array consume more memory than a Single array? Is there no way to avoid using a Variant?

Alexander

4. ## Re: Function Returning an Array? (Excel 2003)

Yes, storing a variant involves some overhead, because information about the actual data type has to be stored alongside the data themselves. This also means that processing a variant is theoretically slower than processing a typed variable with the same contents, because the VBA interpreter has to check the data type. But with the amount of memory and the processor speed in today's PCs, you probably won't notice the difference unless you are working with very large arrays.

If you want to avoid variants, you can do something like this:

Sub PopArray(sngArray() As Single, oRange As Range)
ReDim sngArray(1 To oRange.Rows.Count, 1 To oRange.Columns.Count)
Dim i As Long
Dim j As Long
For i = 1 To oRange.Rows.Count
For j = 1 To oRange.Columns.Count
sngArray(i, j) = oRange.Cells(i, j)
Next j
Next i
End Sub

Note that PopArray is not a function, but a procedure that modifies the sngArray argument passed to it. Example:

Sub TestIt()
Dim sngTest() As Single
PopArray sngTest, Range("A1:A4")
Debug.Print sngTest(2, 1)
Erase sngTest
End Sub

Since sngtTst and sngArray are declared as Single arrays, you are repsonsible for supplying a range that contains only numeric values (or blanks). Text values will cause a Type Mimatch error.

5. ## Re: Function Returning an Array? (Excel 2003)

Hans - thanks!

One last question: does modifying sngArray in PopArray mean this will be recognised in the main sub?

6. ## Re: Function Returning an Array? (Excel 2003)

Yes. Arguments in procedures and functions in VBA are by reference unless you explicitly specify them as by value.

By reference means that the procedure or function works with the "original" of the variable. By value means that the procedure or function uses a local copy, so that the original will not be modified.

To specify the way a argument is passed, use ByRef or ByVal. If omitted, ByRef is assumed.

7. ## Re: Function Returning an Array? (Excel 2003)

Thanks Hans, I have implemented this and my code is now as streamlined as I can see it being.

Regards,

Alexander

8. ## Re: Function Returning an Array? (Excel 2003)

Why is it array arguments can only be ByRef?

9. ## Re: Function Returning an Array? (Excel 2003)

Why? I don't know. If you click Help in the error message, you'll get some information, but not a reason.

Variants to the rescue again: you can specify a ByVal Variant argument, and pass an array into it:

Sub Caller()
Dim arr(1 To 4) As Integer
arr(3) = 5
Debug.Print arr(3)
Process arr
Debug.Print arr(3)
End Sub

Sub Process(ByVal arr As Variant)
arr(3) = 9
Debug.Print arr(3)
End Sub

Running Caller will result in
5
9
5

10. ## Re: Function Returning an Array? (Excel 2003)

There is no explanation from MS as Hans said. However, as an old programmer, I will take a guess. When parameters are passed by value, then the value itself must be pushed onto the processor stack. Since arrays can be of different sizes, and can contain very large numbers of values, this would create all kinds of programming problems and potential for error conditions like stack overflow.

11. ## Re: Function Returning an Array? (Excel 2003)

Hans, Legare -

Thanks a lot for all your help. I have lots of offshoot questions at this point, none of them practical, so I guess I should just educate myself!

Regards,

Alexander

12. ## Re: Function Returning an Array? (Excel 2003)

You're welcome to ask if and when you want. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

13. ## Re: Function Returning an Array? (Excel 2003)

As a (getting) old programmer, I'd tend to agree with your explanation Legare. Back in the 60s or whenever it happened, the great software disaster initiated a major rethink about programming and programming languages. One of the problems identified was programmers passing copies of arrays (often unnecessarily) to functions, creating huge drains on memory resources. If you start thinking multi-dimensional arrays being copied by routines that are called recursively, it's not hard to envisage the results. The solution was to rewrite programming languages to enable the passing of array variables only by reference, and this appears to have "stuck", with good reason.

Other things identified as contributing to the "disaster" were the use of global variables, undeclared variables (like Hans always says, set "Option Explicit" as the default), untyped variables (purists would frown on the Variant type allowed in VB, and certainly at typeless script language implementations) and the indiscriminant use of "Go To" statements, often as "Get out of Jail cards".

<img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
Alan

14. ## Re: Function Returning an Array? (Excel 2003)

I know little/ nothing of the VB .NET language, but I seem to recall the mention of it implementing RTTI (runtime type identification) like C++ has provided for aeons. It certainly represents a better programming practice than the way Variants are dealt with in VB. Do you (or anybody else) know any references to this feature?

Alan

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•