Is there any way to return the value of an entire row of a multidimensional array to a one dimensional array In VBA?
Something like, arr_1dim = arr_2dim(3,:) is a matlab expression for assigning the row 3 of arr_2dim array to arr_1dim in one single stretch.
Is there any similar less expensive method in Excel VBA?
There is a simple way to get a column or a row of a two-dimensional array. Assign a zero to column to get the row, or assign a zero to row to get the column, thus:
Application.WorksheetFunction.Index(array, 0, columnyouwant) /* or */
Application.WorksheetFunction.Index(array, rowyouwant, 0)
See here: How do I slice an array in Excel VBA?
No there is no VBA-function to get a row or column. You can only write it yourself, or take a look here:
http://www.cpearson.com/excel/vbaarrays.htm
This is what I do to easily print out one dimension of a multidimensional array.
Basically, I define a new, 1D array and stuff it with the values from the bigger array.
Example (3D to 1D to Printout):
Sub PrintItOut()
ReDim big_array(10,5,n) as Variant, small_array(n) as Variant
'use multidimensional array
'place multi-dimensional values into the 1D array
For i = 0 to n
small_array(i) = big_array(0, 0, i)
Next
Range(Cells(1, 1), Cells(1, n + 1)) = small_array
End Sub
That's how I do it. I hope that makes sense to whoever may be reading it. I think it's a very simple way to do it.
Matlab is such an awesome application to work when it comes to matrices, arrays, vectors... ;) But Excel is not that bad, it too is matrix based.
So Assuming you do not want to loop through. You may simply ouput your multi-D array into a worksheet using Transpose function.
Then pull a Row to your desired range size into an array using Transpose.
Dim vArr as Variant
'--output multi-D array into worksheet
Sheets(2).Range("E2").Resize(UBound(multiDArray) + 1, _
UBound(Application.Transpose(multiDArray))) = multiDArray
'--pull back the row you need: we double transpose here to get 1D. Coz single transpose
'-- results in 2D array..
vArr = WorksheetFunctions.Transpose( _
WorksheetFunctions.Transpose(Sheets(1).Range("A2:G2").Value))
To be absolutely dynamic, you may resize your range A2:G2 with a dynamic row count using the multi-D array row upperbound :)
Since I had this question by myself recently, I want to share my code. I wrote a ready-to-use function where you can choose if you want a column or a row to be extracted:
'*** Modul 1, define function ***
Function getOneLine(array2D As Variant, lineIndex As Integer, choice As String) As Variant
' returning one column or row of a 2D array
' array2D: 2 dimensional Array
' lineIndex: the index of column or row, starting at 0
' choice: "c" for column or "r" for row
Dim i, n As Integer
Dim oneLine As Variant
If choice = "c" Then
n = UBound(array2D, 2)
ReDim oneLine(n)
For i = 0 To n
oneLine(i) = array2D(lineIndex, i)
Next
getOneLine = oneLine
End If
If choice = "r" Then
n = UBound(array2D, 1)
ReDim oneLine(n)
For i = 0 To n
oneLine(i) = array2D(i, lineIndex)
Next
getOneLine = oneLine
End If
End Function
'*** Modul 2, call function ***
Sub SomeProcess()
' Creating a 3x2 Matrix
' (In VBA-arrays the column is indexed before the rows
' starting at 0. So 3x2 looks like 1x2)
Dim SomeArray(1, 2) As Variant
SomeArray(0, 0) = 1
SomeArray(0, 1) = 2
SomeArray(0, 2) = 3
SomeArray(1, 0) = 4
SomeArray(1, 1) = 5
SomeArray(1, 2) = 6
Dim oneLine As Variant
oneLine = getOneLine(SomeArray, 1, "c")
Debug.Print oneLine(2)
' prints 6
End Sub
A way to do something close:
ReDim TwoDim(10) As Variant
TwoDim(2) = Array("a", "b")
Debug.Print TwoDim(2)(1) 'Shows "a"
Debug.Print Join(TwoDim(2), "") 'Shows "ab"
