0

I wrote this simple sub procedure for Excel VBA. I intentionally used a colon (:) to separate statements. Why doesn't it work?

Sub ConditionInsideLoop()

    cb = 0: cd = 1: For ca = 1 To 5: If cd = 1 Then cb = cb + 1: Next

End Sub

The question is mostly theoretical. But it might be practical. For example, when we are using the Immediate window and there is a need to use a simple condition inside a simple loop.

Marcucciboy2
  • 3,156
  • 3
  • 20
  • 38
gosh
  • 3
  • 2
  • what doesn't work? And where are your variable declarations? Use Option Explicit as well. After formatting properly (for legibility) and adding in declarations works fine for me. – QHarr Jan 12 '19 at 20:48
  • If you put this line of code into the Immediate Window in VBA it won't work. And it won't work if you run it as a sub procedure. I didn't declare variable explicitly, but I don't think it is the reason why this line of code causes a compile error. Again, we cannot declare variables explicitly in Immediate window. According to this https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/invalid-in-immediate-window this code should work, but it doesn't – gosh Jan 12 '19 at 20:59
  • You are getting a Next without For due to the use of : – QHarr Jan 12 '19 at 21:00
  • Also see https://stackoverflow.com/a/41983139/6241235 – QHarr Jan 12 '19 at 21:09

1 Answers1

1

It's a syntax thing. You can re-write to remove the confounding If statement.

Public Sub ConditionInsideLoop()
    Dim cb As Long, cd As Long, ca As Long
    cb = 0: cd = 1: For ca = 1 To 5: cb = IIf(cd = 1, cb + 1, cb): Next
    Debug.Print cb
End Sub
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • 1
    QHarr, IIf function is exactly what I was looking for. Thanks a lot! – gosh Jan 12 '19 at 21:31
  • 1
    Why does it hurt to read? I am new in VBA, but learned some other languages, and I am very glad there is something similar to a ternary operator (a?b:c) in VBA – gosh Jan 13 '19 at 09:39
  • 2
    The use of : in this instance makes the whole thing hard to read. Using indented multi-line makes the logic and control flow much easier to read. It is also easier to debug on multi-lines. IMO there are few times that using : to combine lines is to be favoured over the enhanced legibility of multi-line equivalents. – QHarr Jan 13 '19 at 10:42