How to split a constant 2D array initialization on several lines?

Question

I need to create a 2D array which is not intended to be updated, only read. Reading this question, I found this possibility using evaluate:

Dim varData As Variant
varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]

In my case, the number of values requires to split the assignment on several lines, e.g.

varData = [{value1, value2; _
            ...; _
            valueM, valueN}]

However when using _ to split this assignment, VBA complains at the first line with:

Compile Error:
Missing end bracket

I've tried to find the explanation, but all examples seems to use only a single line. What is wrong?

Note: I'm trying to populate an array, not cells in a sheet.

Are you trying to populate a grid of cells with your values? `varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]` would result in a 3 row by 3 column grid. — IvenBach, Jul 07 '17 at 17:56
@IvenBach: That's right, in my case I want two columns and about 20 rows — mins, Jul 07 '17 at 17:57
I think you'll have to use the second formulation in that question if you want to split the lines. — SJR, Jul 07 '17 at 18:00
@SJR: Do you mean assigning cell one by one (`MyArray (0,0) = 1`)? — mins, Jul 07 '17 at 18:04
I've put it in an answer below because I can't format it properly in a comment. — SJR, Jul 07 '17 at 18:06

score 1 · Answer 1 · answered Jul 07 '17 at 18:05

1

I was thinking of this.

Dim A as Variant
A = Array(Array(1, 2), _
          Array(3, 4), _
          Array(5, 6))

answered Jul 07 '17 at 18:05

SJR

Ok, thanks. That would do it, however I was excepting something more simple would be possible. Could you add to your answer the reason it's not possible to split the "declare" version? I believe it would help persons like me, not familiar with VBA specificities. – mins Jul 07 '17 at 18:29

1 Answers1