Why do my Private and Public variables in Excel VBA still contain information when my code is finished running?

I am struggling to understand a behaviour in MS Excel VBA.

I have a Subroutine that opens a file dialog box. I select the file, it opens it, reads some data, does a few bits with file structure and closes it. I discovered the quirk when I run the code again immediately, but then clicked cancel - the code continued to run with the file I had previously selected, even though I had, in my mind, exited.

I played around with a bit of print debugging, and it turns out that after the code had run, the variables that I had defined at module level were maintaining their value, even though code was not running. To be clear, the Locals window was empty and status .

Private UserName As String

I expected all variables to be reset after the code had run, so I'd be grateful if someone could help me understand what is going on. If I press reset, the variables lose their values, but can still be accessed by testing code.

Sub TestUserName()
MsgBox UserName
End Sub

As I want to be able to run multiple times, with potential cancelling, I can think of a few workarounds:

1. Clear the variables values at the start of the Subroutine (e.g. set to "").
2. Clear the variables values at the end of the Subroutine.

If someone could explain the behaviour to me, and what the best solution would be, I'd be very grateful.

That's by design. Variables defined on Module level keep their values (even if no code runs) until

• The code execution is stopped by pressing the Reset button in the VBE (little blue square)
• A runtime error occurs and you press [End]
• The VBE needed to recompile the code. This happens for example if you define a new module variable or a new Sub.

The keywords Public or Private don't change this behavior, they define only if code in other modules can access the variable. Using Dim on module level is the same as Private, and Global on module level is (nearly) the same as Public.

Local variables (variables declared on Procedure/Function) level always loose their value once the routine is left, except if they are declared as Static.

As a consequence:

• Avoid module level (global) variables if not really needed. In your case, it sounds as if you don't need a global variable at all.
• If multiple routines share values, pass them as arguments. If you have a routine that does something that you need in another routine (eg getting a filename), consider to create a function.
• If a global variable is really needed, take care that you initialize it always when you don't want to have a "previous" value, eg setting the filename to "" before calling the file dialog.