VBA has No Block Scope

February 14, 2021

The other day I needed to update a workbook that I developed some years ago for a company. I made my updates, tested my changes, and sent out the new workbook. All seemed well.

Or so I thought.

I was later asked to fix a bug that was happening with the code. Surprised, I asked how to reproduce it and got to work. Sure enough, there was a bug that I coded in there. It was so strange that I thought VBA was broken.

What happened?

Here’s a simplified version of what I ran into. What do you think the debugger will print with the following code?

Public Sub Test()
  Dim i As Long

  For i = 1 To 5
    Dim temp As Long

    If (temp = 0) Then
      Debug.Print "temp is not assigned"
      temp = i
    Else
      Debug.Print "temp is ALREADY assigned - temp=" & temp
    End If

    temp = temp * 2
  Next
End Sub

Easy, it would print temp is not assigned 5 times.

Noooooope.

Here’s what was really outputted:

temp is not assigned
temp is ALREADY assigned - temp=2
temp is ALREADY assigned - temp=4
temp is ALREADY assigned - temp=8
temp is ALREADY assigned - temp=16

…excuse me?

Is VBA broken? No, no it’s not broken, but it feels like it is. If you’re coming from another language that has block-level scope like C#, you’re probably scratching your head wondering why this happens.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Procedure-Level Scoping

The main reason for this is because VBA treats all variables inside a procedure as procedure-level scope. The only exception to this is with static variables inside a procedure, which are still only available to the procedure, but treated like it has modular-level scope.

So If statements and loops like For and While do not have Block Scope.

Having your variables treated with procedure-level scope also means that all variables declared inside a procedure are available everywhere in the procedure after they’re declared. So the temp variable is available even after the loop ends, as shown here:

Public Sub Test()
  Dim i As Long

  For i = 1 To 5
    Dim temp As Long

    If (temp = 0) Then
      Debug.Print "temp is not assigned"
      temp = i
    Else
      Debug.Print "temp is ALREADY assigned - temp=" & temp
    End If

    temp = temp * 2
  Next

  Debug.Print "Loop ended"

  Debug.Print "temp is STILL assigned - temp=" & temp
End Sub

Which has an output of:

temp is not assigned
temp is ALREADY assigned - temp=2
temp is ALREADY assigned - temp=4
temp is ALREADY assigned - temp=8
temp is ALREADY assigned - temp=16
Loop ended
temp is STILL assigned - temp=32

So what does this mean for the code?

It means that we need to be mindful when creating variables inside other blocks like For and While loops. However, we have some options with how to deal with this.

What I would recommend is to take the logic of the loop and place it inside a private procedure like this:

Public Sub Fixed()
  Dim i As Long

  For i = 1 To 5
    Call DoStuff(i)
  Next
End Sub

Private Sub DoStuff(ByVal value As Long)
  Dim temp As Long

  If (temp = 0) Then
    Debug.Print "temp is not assigned"
    temp = value
  Else
    Debug.Print "temp is ALREADY assigned - temp=" & temp
  End If

  temp = temp * 2
End Sub

Now if you call Fixed, you get this output:

temp is not assigned
temp is not assigned
temp is not assigned
temp is not assigned
temp is not assigned

Since the temp variable has a new procedure-level scope (because it lives in its own procedure), we can avoid this issue altogether.

To me, separating out loops into their own sub procedures is a best practice and I would recommend that you do this to keep your code clean.