Unknown number of arrays as argument to VBA function

[Huugin]

Hi

I have written the below function for which I would like to make the number of array arguments optional. In the current case it takes 5 arrays of equal length as argument, and combines them into the StockMatrix array.

Please Login or Register  to view this content.

How can I make the number of required arrays arbitrary, so that the function may also have worked if I called the function with 7 arrays: SMatrix_C(arr1, arr2, arr3, arr4, arr5, arr6, arr7). I don't know if it makes a difference, but the actual arrays I supply as arguments are worksheet ranges, so that an example call with five array arguments will look like =SMatrix_C(B2:B523;C2:C523;D2:D523;E2:E523;F2:F523)

The full function code is supplied below, however with a few calls to other functions; the below code is irrelevant, and only included for completeness.

Please Login or Register  to view this content.

[MrShorty]

When you call the function, will the vectors always be consecutive (B,C,D,E...) or can they be required to be consecutive? If so, then you could reduce all of your one dimensional array arguments into a single 2 dimensional array:

Please Login or Register  to view this content.

called as =smatrix_c(b2:f523)

[Huugin]

Unfortunately no. The argument needs to be able to take none consecutive columns of data :S

[MrShorty]

Next idea is to try declaring a final argument (or maybe the only argument) as a paramarray (see VBA help for the function statement):

Please Login or Register  to view this content.

I have never used paramarrays like this, so I don't understand the details of how they work. It does seem like the closest thing to what you are asking for.

[aelgadi]

I think it would be easier if you temporarily store your data in a data file (external file) i.e random access file.