Archive for January, 2014

You can only use the Redim Preserve statement to resize the last dimension of a multidimensional array, but there are many times you’ll need to resize the first dimension. The following function uses built-in Excel worksheet function “Transpose” to work around this limitation:

Public Sub ArrayResize1stDim(ArrayToResize As Variant, NewDim As Long)
'Allow the 1st dimension of the passed array to be resized.
' ArrayToResize = The array to be resized (must be passed ByRef)
' NewDim = The total number of elements desired in the
' 1st dimension of the resized array.
Dim lngOrigCols As Long

lngOrigCols = UBound(ArrayToResize, 2)

ArrayToResize = Application.Transpose(ArrayToResize)
ReDim Preserve ArrayToResize(1 To lngOrigCols, 1 To NewDim)
ArrayToResize = Application.Transpose(ArrayToResize)

End Sub

(Note: Due to the usage of Application.Transpose this function will only work with Excel VBA)


Read Full Post »