Excel VBA: adding a row to a variant array in one line

Question

I´d like to add a row to a variant array:

Dim arrMod As Variant
arrMod(numberOfRow) = Array(myValue1, myValue2, myvalue3)

The execution of this code results into an exception: Error 13: type mismatch How can I do it without iterating each column?

Thanks,

Regards

Steven · Accepted Answer · 2015-05-28 13:38:07Z

2

Your variable arrMod is not an array. You need to define it in VBA as an array using parenthesis:

Dim arrMod(0) As Variant

Obviously replace 0 with the maximum number of rows you have, or resize dynamically using redim preserve.

edited May 28, 2015 at 13:38

answered Apr 20, 2015 at 10:20

Steven

7911 gold badge7 silver badges23 bronze badges

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

2 Comments

Luis Andrés García Over a year ago

This is a good solution, but I don´t know the array size (it would depend of some values read in each iteration). How can I prevent redim in each iteration?

Steven Over a year ago

If you are unable to correctly size the array at instantiation then you don't really have much choice but to redim on each iteration as the VBA array is mutable. You could look at immutable collection types?

genespos · Accepted Answer · 2015-04-20 11:59:37Z

1

Do you need something like this:

Dim arrMod()
For i = 1 To 5
    ReDim Preserve arrMod(i)
    arrMod(i) = i
    MsgBox Join(arrMod, vbCrLf)
Next i

answered Apr 20, 2015 at 11:59

genespos

3,3216 gold badges44 silver badges78 bronze badges

Collectives™ on Stack Overflow

Excel VBA: adding a row to a variant array in one line

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related