Assign values from one array to another without looping

Question

Is there a way like in MATLAB to assign many values in an array's row?

Let's say you have a matrix M(5,5) and a Vector V = [1, 2, 3, 4, 5]

In Matlab, If you wanted the first row to be filled up with vector V, you would write:

M(1,:) = V

Is there something similar in Excel VBA instead of looping through each column of the first row and assign the value V(j) every time?

No you cannot do that in straight VBA. However Functions For VBA Arrays provides a host of useful functions. It offers GetRow but not SetRow. A little recoding should give the routine you seek. — Tony Dallimore
– Tony Dallimore, Commented Sep 13, 2015 at 15:44
Tony thank you very much for this link, it is really helpful! I am now switching from MATLAB to Excel, and I see some tasks that are really easy in other languages, have to be hard-coded in Excel! — El Savor
– El Savor, Commented Sep 13, 2015 at 16:27
MATLAB is an example of a specialist language. Within its specialty, it provides powerful and easy to use functionality but provides little or no functionality outside that specialty. VBA is a general purpose language. Many programming tasks can be tackled with VBA but it is only really good at providing access to Excel worksheets. I use both VBA and VB.Net. I use VBA for the easy stuff and Excel access. I use VB.Net for the fast processing and enormous library of functions. The Express version of VB.Net is free so why not give it a try. — Tony Dallimore
– Tony Dallimore, Commented Sep 13, 2015 at 17:35

lori_m · Accepted Answer · 2015-09-14 16:34:38Z

0

You could try something along these lines:

Sub InitializeMatrixWithVector()

Dim V(), M()
ReDim M(1 To 5, 1 To 5)

V = Array(1, 2, 3, 4, 5)
M = Application.Choose([1+(row(1:5)=1)], M, V)

Range("B1").Resize(UBound(M), UBound(M, 2)) = M

End Sub

The square brackets are equivalent to the Evaluate VBA function.
Application.Choose is a WorksheetFunction method that can take and return variant arrays.

answered Sep 14, 2015 at 16:34

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign values from one array to another without looping

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related