CODEWITHSUNDEEP
excelvba
PhilNBlanks
PhilNBlanks

Reputation: 125

How are the double-quotes managed when I put this code into a loop?

On SO, I was just given two answers that both work when called a single time. Now I want to put them in a loop and loop over several rows of data. However, I'm having a heck of time getting the code correct. I'm suspect it has to how I'm handling the double quotes.

The stand alone code lines are as follows.

  1. Var = ActiveSheet.Evaluate("And(A1:F1)") and
  2. Var = Application.WorksheetFunction.And(Range("A1:F1"))

for the first example I tried:

for i = 2 to 20
     Var = ActiveSheet.Evaluate("And(A & i & :F & i)")
Next i

This produces "Error 2015"

for the second:

for i = 2 to 20
     Var = Application.WorksheetFunction.And(Range("A" & i & ":F" & i))
Next i

This produces a line of red code

What am I doing wrong?

Upvotes: 0

Views: 112

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71197

The Visual Basic Editor is making this harder than it should be, because its default syntax highlighting is making string literals the same color as identifiers:

default syntax highlighting makes both identifiers and string literals black

You can change that under Tools/Options, and make Identifier Text a different color - here teal:

VBE syntax highlighting options

Now string literals are still black, but now identifiers look visually distinctive:

string literal is now obviously not an identifier

What you want to make sure, is that your variables are syntax-highlighted like identifiers - so they're teal, not black - like in your second example:

variable i is clearly not a string literal

Contrast with your first attempt, where i doesn't get syntax-highlighted as the identifier it should be:

the entire string is all black

And since you know that i is a VBA variable and you want VBA to concatenate its value into this string, then i being syntax-highlighted as any other string literal (and not as an identifier) is your visual cue that something's off!

Compare to @JNevill's fixed version:

i is now treated as an identifier

With Identifier Text having a different syntax highlighting than string literals in the editor, it becomes much easier to quickly locate a variable that's accidentally inside a string literal.

That first snippet isn't working, because ActiveSheet.Evaluate takes its parameter and gives it to Excel's expression evaluation engine, ...which has no idea what to do with this i. Variable i only exists in the execution context of the VBA code: only VBA code can evaluate its value.

Upvotes: 2

Related Questions