Excel global variable reseted to empty when navigating through workbooks

Question

I encounter a weird problem that I believe is related to Excel behavior, rather than to my code.

I have a global variable named "numEtape", which is an integer. My code consists in several steps where the user has to type data on a sheet, then press a button which saves the data in an array and increments the "numEtape", before going to the next step.

The code (simplified) looks like this :

Dim numEtape As Integer

Sub AjoutEssai()
    numEtape = 2
    UFPreAjoutInfos.Show 'Unrelated Userform that asks user for more informations, but doesn't modify "numEtape" or call any other macro
    
    Call InterfaceFiller
    
End Sub

Sub InterfaceFiller()
Dim rangedubtn As Range
Dim btnPrecedent As Button

Select Case numEtape
    Case 2
         'Change value of some cells
    Case 3
         'Change value of some cells
    Case 4
         'Change value of some cells
    Case Is >= 5
         'Change value of some cells
    Case Else
         Debug.Print "Error"
End Select

Set rangedubtn = Sheets("Interface").Range("M3")
    Set btnPrecedent = Sheets("Interface").Buttons.Add(rangedubtn.Left, rangedubtn.Top,rangedubtn.Width, rangedubtn.Height) 
    With btnPrecedent
      .OnAction = "mSuivant"
      .Caption = "Suivant"
      .Name = "btnSuivant"
    End With
End Sub

Sub mSuivant()
    numEtape = numEtape + 1
    Call InterfaceFiller
End Sub

I don't think the code itself is important, what I can expect from it, since I first call AjoutEssai(), is for numEtape to always be greater than 2. However, when during the steps the user opens and close other excel/office files (that don't have any vba code/macros in it), excel seems to empty numEtape, which makes the Select Case go to the Case Else.

When does excel remove global variables from memory, and is there a way to prevent this behavior from happening?

Vityata · Accepted Answer · 2020-07-20T08:17:51.910

Public numEtape As Long

A viable option is to use the word public like public numEtape As Long. Then the variable will be saving its value for as long as the Excel workbook is opened. In older versions of VBA the word was Global (What is the difference between Dim, Global, Public, and Private as Modular Field Access Modifiers?)

Dim numEtape As Long

For using Dim outside of Sub or Function, the variable will be emptied, after the code is over. Take this snippet only:

Dim numEtape As Long

Sub MainTest()        
    numEtape = 23        
End Sub

Once you run it and you hit End Sub the variable would be emptied as well. Check after running the MainTest():

Sub PrintingVariable()
    Debug.Print numEtape
End Sub

If you want to save the value, there are 2 basic ways that work:

Write the value in an excel cell
Write the value in a database

Excel global variable reseted to empty when navigating through workbooks

1 Answers1