2

In the following Subroutine, defining a two dimensional array doesn't seem to work with line continuation. TestArray1 initializes as expected, but when I add line continuation I get the message,

"Compile Error - Closing bracket missing".

(Actually I'm not sure of the exact wording in English, doing this in German. In German the error message is,

"Fehler beim Komilieren: Fehlende schliesende Klammer".

I'm sure the English is not far off.)
Why would this not work?

Sub TestArrays()


Dim TestArray1 As Variant, TestArray2 As Variant

TestArray1 = [{"1String1", "1String2", "1String3"; "2String1", "2String2", "2String3"; "3String1", "3String2", "3String3"}]

TestArray2 = [{"1String1", "1String2", "1String3"; _
"2String1", "2String2", "2String3"; _
"3String1", "3String2", "3String3"]}

End Sub
Lyspon
  • 31
  • 3
  • Maybe this --> https://stackoverflow.com/questions/22854386/how-to-continue-the-code-on-the-next-line-in-vba? – Ryan Wildry Dec 12 '19 at 17:16
  • Does this answer your question? [vba line continuation with bracket expression](https://stackoverflow.com/questions/43175304/vba-line-continuation-with-bracket-expression) – BigBen Dec 12 '19 at 17:16
  • 4
    Though note that you've got the order mixed up at the end `}]`, not `]}`. – BigBen Dec 12 '19 at 17:17
  • Thanks, these two posts do address the question, however I'm left with the same problem. So I get that whenever you use square brackets [], Excel takes this as Excel, and not as VBA, so doesn't recognize continuation. Stringing short strings together in a longer string and then using Evaluate does work, but only for strings less than 255 characters long in totoal. I was trying to avoid filling a two-dimensional array by using a whole lot of lines of: myArray (1,1) = "Str1", myArray(1,2) = "Str1 and stuff", etc., or using a loop. Any other ideas before I get down to the grind? ;) – Lyspon Dec 13 '19 at 09:39
  • to BigBen - yes thanks, and it also got the same error when I corrected it. Must have gotten switched in submission. – Lyspon Dec 13 '19 at 09:43

1 Answers1

7

Don't use square brackets.

Square brackets in VBA DO NOT stand for "this is an array", even though it looks like it (if you're any familiar with JSON anyway), and even though it might work.

Square brackets in VBA stand for "this is an expression that the host application will be evaluating at run-time".

In other words, it's giving work to Excel's expression evaluation engine: it's not VBA, it's Excel. The syntax of whatever is inside a square-bracketed expression must be legal in Excel's formula bar1.

Use the Array standard VBA function to create an array in VBA:

TestArray1 = Array("1String1", "1String2", "1String3", "2String1", "2String2", "2String3", "3String1", "3String2", "3String3")

Split it up with a line continuation at any point between two strings:

TestArray1 = Array( _
    "1String1", "1String2", _
    "1String3", "2String1", _
    "2String2", "2String3", _
    "3String1", "3String2", _
    "3String3")

Note that the inconsistent ; vs , separators are probably part of the problem: Excel formulas use your system's list separator character: that's the character you'll want to use in square-bracket expressions - but you don't need to do that, because you don't need any square-bracket expressions.

There is no syntax for inline-initializing a 2D array in VBA. Declare & size your array explicitly instead:

Dim my2D(1 To 10, 1 To 10)
my2D(1, 1) = "1string1"
'...

If you have that much hard-coded strings in your code, then you are coding data. Data belongs in storage, not in code. Read the data from a worksheet, you'll get a 2D variant array for free, with a one-liner, without abusing language syntax, and if the data needs to change, then the code doesn't:

Dim my2D As Variant
my2D = sourceRange.Value

1 unless it's a VBA foreign identifier, in which case Excel doesn't get to evaluate it. Just don't use square bracket expressions, they're confusing.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235