Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

return an entire row of a multidimensional array in VBA to a one dimensional array

Tags:

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?

like image 338
surya teja3 Avatar asked Jan 05 '13 16:01

surya teja3


People also ask

What does UBound return in VBA?

The VBA UBound function returns the maximum subscript, or index, of a particular dimension of an array. In most cases, this essentially yields the size of your array.

How do you return an array in VBA?

A function in a normal module (but not a Class module) can return an array by putting () after the data type. Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can't be changed by the calling procedure.

What is a dynamic array in VBA?

Dynamic arrays are arrays that can be dimensioned and re-dimensioned as par the need of the program. You can declare a dynamic array using the ReDim statement. Syntax for ReDim statement − ReDim [Preserve] arrayname(subscripts)


4 Answers

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?

like image 188
rxex Avatar answered Sep 28 '22 07:09

rxex


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

like image 41
KekuSemau Avatar answered Sep 28 '22 09:09

KekuSemau


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.

like image 40
Elias Avatar answered Sep 28 '22 08:09

Elias


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 :)

like image 23
bonCodigo Avatar answered Sep 28 '22 09:09

bonCodigo