0

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?

PhilNBlanks
  • 117
  • 1
  • 1
  • 8
  • Variables should not be inside the quotes. – BigBen Dec 13 '19 at 19:30
  • 1
    The second attempt looks perfect to me (I just copied and pasted and get no issues). The first attempt could be written as: `Var = ActiveSheet.Evaluate("And(A" & i & ":F" & i & ")")` – JNevill Dec 13 '19 at 19:30
  • 2
    Does this answer your question? [How do I put double quotes in a string in vba?](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba) – alowflyingpig Dec 13 '19 at 19:30
  • @alowflyingpig not really - that one's about escaping `"` double quotes; here it's about concatenating VBA variables into a string literal. – Mathieu Guindon Dec 13 '19 at 19:56

1 Answers1

2

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.

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