VBE Line continuation for Range definition

[jwise]

Hello,

I'm trying to develop a diagnostic piece of code. In so doing I can't get the VB Editor to accept my multi-line Range definition. I've tried this at least 8 different ways to no avail. Here we go:

Set rng1 = ws1.Range("A3:E3, A6:E6, A9:E9, A12:E12, _
A18:E18, A19:E19, A21:E21, A22:E22, A24:E24, _
A26:E26, A28:E28, A30:E30, A32:E32, A37:E37")


I continuously get compile messages. This particular one says: "Syntax error." I'm sure someone who uses VBE a little more often than I knows how to do this. I'm out of guesses and having a bad Google day.


Thanks

[Kenneth Hobs]

  Set rng1 = ws1.Range("A3:E3, A6:E6, A9:E9, A12:E12," & _
    "A18:E18, A19:E19, A21:E21, A22:E22, A24:E24," & _
    "A26:E26, A28:E28, A30:E30, A32:E32, A37:E37")

[jwise]

Thank you sir. I wouldn't have guessed this in a million years.

To be honest, I can't see why this works. Normally a Range definition works like this:

Set rng1 = ws1.Range("A8")

But if you put that in a loop, it goes like this:

For i = 1 to 5
Set rng1 = ws1.Range("A" & i)
... (other code)
Next i

The i is clearly uncovered by the quotes and a syntax driven scanner has to be able to differentiate between "A"1 and "A1" (where i is 1 in the formula above). It just doesn't seem logical to my admittedly weak brain.

Thanks

[Kenneth Hobs]

The 'i' in your case is coerced into a string. Only strings can be concatenated. That is why the code that I wrote works. Strings can not span lines.

[jwise]

Thanks again for your explanation. It does make sense although the scanners I worked on in the past did not work that way. My first attempt was like this:

set rng1 = ws1.Range("A3:E3", "A4:E4")

which is not valid either. I guess VBA is strictly using the quotes to decide whether the term is a variable or literal since "A8" is a legal variable name as well.

I really appreciate your reply. Getting this straight laid the foundation for verifying my test case which in turn enlightened me on my "big" problem.

[Kenneth Hobs]

That is valid syntax. When using inputs like that, it is a continuous range. Your first example was a discontinuous range.

I am not sure what you mean by scanners. I guess you mean compilers. All languages that I have worked with work that way for the most part. Some vary by the line continue character.