Page 88 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 3 MATRICES 65

Option Explicit
Option Base I
Function Arr(ParamArray rng())
'Combines individual I-D or 2-D arrays into a final 2-D array.
'In this version all individual arrays must be "vertical".
'All individual arrays must have same number of rows.
Dim Result()
Dim I As Integer, J As Integer, K As Integer
Dim TempX As Integer, TempY As Integer, XDim As Integer, YDim As Integer
Dim YStart As Integer, YSize As Integer
'First, get sizes of individual arrays, check to make sure all are same size.
For J = 0 To UBound(rng)
'Handles either range, name or array constant arguments
If IsObject(rng(J)) = True Then 'reference is to a range or a name
TempX = rng(J).Rows.Count
TempY = rng(J).Columns.Count
Elself IsArray(rng(J)) Then
TempX = UBound(rng(J), 1)
TempY = UBound(rng(J), 2)
End If
If J = 0 Then XDim = TempX
If XDim <> TempX Then Arr = CVErr(x1ErrRef): Exit Function
YDim = YDim + TempY
Next J

'Now combine each individual array into final array.
'I index is used to select within array of arrays.
'K and J are column & row indices of individual arrays.
ReDim Result(XDim, YDim)
YStart = 0
For I = 0 To UBound(rng)
YSize = rng(l).Columns.Count
For K = 1 To YSize
For J = 1 To XDim
Result(J. YStart -t K) = Application.lndex(rng(l), J, K)
Next J, K
YStart = YStart + YSize
Next I
Arr = Result()
End Function
Figure 3-7. VBA function procedure to combine separate ranges into a single array.
(folder 'Chapter 03 (Matrices) Examples, workbook 'ArrayMaker', module 'Module 1')