How to pass vector to a row in an array in Excel vba?

Question

I have an array, and a vector. I want to pass/replace a row in the array with the vector.

2d array, as below
arr = [[1, 2],
       [3, 4],
       [a, b]]       <--- replace with vct here

...where vct = [[5, 6]]

While it can be done unsophisticatedly using the naive looping, I wonder if there is any creative workaround for it. Example as below.

Using naive looping:

For i = 1 to 2
    arr(3, i) = vct(i)
Next i

...or some clever workaround:

arr(3, :) = vct    ...where ":" represents "all columns"

Result expected:

arr = [[1, 2],
       [3, 4],
       [5, 6]]

Tbh I have low expectation for vba since even the simple array indexing isn't actually a thing. I just hope if anybody wanna take a stab on this to provide a solution. Tq

Edit to improve question clarity by adding vba code block. See below

    Dim arr(1 to 3, 1 to 2) as Integer
    Dim vct(1 to 2) as Integer
    Dim i as Integer

    ' filling in arr
    arr(1, 1) = 1
    arr(1, 2) = 2
    arr(2, 1) = 3
    arr(2, 2) = 4
    arr(3, 1) = 10
    arr(3, 2) = 20

    ' filling in vct
    vct(1) = 5
    vct(2) = 6

    ' passing the vector vct into last row of arr using for loop
    For i = 1 to 2
        arr(3, i) = vct(i)
    Next i

    ' as a result,
    ' arr(3, 1) = 5, and
    ' arr(3, 2) = 6
    ' which does its work perfectly
    ' but I am looking if there is any possibility
    ' to have a non-looping approach such as below
    ' arr(3, :) = vct
    '  
    ' this is because I am too accustomed to python syntax
    ' where it can be done as using ":"

I am using vba. I assume you are asking due to the syntax that I used in my code block example. They are not in vba syntax, rather just to represent my points. Do you think I should modify them to be exactly in vba syntax to avoid confusion? — jesse
– jesse, Commented Apr 24, 2019 at 13:53
i think you must post a piece of code so we can understand that you want to do, also you can read this first bettersolutions.com/vba/arrays/index.htm — Dmitrij Holkin
– Dmitrij Holkin, Commented Apr 24, 2019 at 13:54

Dmitrij Holkin · Accepted Answer · 2019-04-24 14:01:16Z

2

It sound like you want something like this

  Sub test()
  Dim vaTest As Variant

    vaTest = Array(Array(1, 2, 3), Array(4, 5, 6))
        Debug.Print vaTest(0)(1) ' Prints 2
    vaTest(0) = Array(7, 8, 9) ' changing first array
        Debug.Print vaTest(0)(1) ' Prints 8
  End Sub

answered Apr 24, 2019 at 14:01

Dmitrij Holkin

2,0755 gold badges41 silver badges90 bronze badges

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

2 Comments

jesse Over a year ago

is vaTest a 2d array, or 1d array of 1d array elements? My requirement is that accessing the vaTest should be like vaTest(0, 1) instead of vaTest(0)(1)

Pᴇʜ Over a year ago

@DmitrijHolkin Actually he must not accept the first answer. While it is a good practice to accept the first correct answer (if there are 2 equal answers) he is free to decide which answer fits better to his question and choose the one which gave the best explanation to the question. This makes people answering not only correct answers but also good anwser with good explanations. • So it is a common practice to accept the answer that you think fits best (even if it came later) and upvote for the other ones that are correct too. This decision is completely up to the OP.

Pᴇʜ · Accepted Answer · 2019-04-24 14:28:58Z

1

I recommend to use array counting 0 based because you can easily use Array(1, 2) to create a vector.

Sub test()     
    Dim Vectors() As Variant

    'fill it with 3 vectors (0 to 2)
    Vectors = Array(Array(1, 2), Array(3, 4), Array("a", "b"))

    'replace third vector with another one
    Vectors(2) = Array(5, 6)
End Sub

Before

After

To directly access one of these vectors use:

Debug.Print Vectors(2)(0) '=5
Debug.Print Vectors(2)(1) '=6

Or to extract eg the second vector use

Dim SecondVector() As Variant
SecondVector = Vectors(1)

Debug.Print SecondVector(0) '=3

For further information about using arrays I recommend reading:
The Complete Guide to Using Arrays in Excel VBA

edited Apr 24, 2019 at 14:28

answered Apr 24, 2019 at 14:14

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

1 Comment

jesse Over a year ago

It works, thanks. Ps/ note that @dmitrij also giving the same answer. Credit to both of you. Upvote this answer for the diagram aid.

Collectives™ on Stack Overflow

How to pass vector to a row in an array in Excel vba?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related