Public variables losing their values

[hershmab]

My VBA application consists of many procedures stored in several modules in one workbook. I have 2 key public variables:

1. a Workbook object that points to any one of several data books.
2. a String variable holding the basic name of the current data book.

They are declared at the top of a normal module that contains the procedure that [re-]initializes them. This procedure is always called when the application is first opened but not (normally) again during an Excel session.


There are no procedures that that do anything to change them.

The whole application works as a series of nested loops, each headed by an Inputbox that allows the user either to change to a different option or to cancel the loop and go back up to the higher level.

Cancelling at the highest level simply returns control to Excel without closing the currently-opened data book or clearing the public variables. While I am developing the application and testing it, I very often cancel at the highest level and then restart at the second level down - which is where the problem occurs:

• 
  [*=1]The public Workbook object is now Nothing
  [*=1]the public String variable is now empty.

even though the previously-used data book is still itself open!
​

What is it that causes Public variables to lose their values? They are declared in a way that allows them to retain their values in all the connected procedures as long as the application is not reset by closing a top-level procedure.

Is this something that could be cured by moving or copying the Public declaration into other modules?

Or must I specifically reset their values when restarting the application at a lower level?

 

[RoryA]

Any state loss will cause public variables to lose their value. It sounds like whatever you are doing (it's not clear to me what that is precisely) is causing state loss, so yes you need to re-run the procedure. Or create functions that either return the current value of the variables or reset them, and call those the same way you use the variables now.

 

[AlphaFrog]

While I am developing the application and testing it, I very often cancel at the highest level and then restart at the second level down - which is where the problem occurs:

You could pause code execution using Ctrl+Break. Then type Exit Sub below the highlighted line in your paused procedure. Then resume code execution. The procedure would then exit and the Public variables would remain static.

Remember to remove the Exit Sub after exiting the procedure.

 

[hershmab]

You could pause code execution using Ctrl+Break. Then type Exit Sub below the highlighted line in your paused procedure. Then resume code execution. The procedure would then exit and the Public variables would remain static.

Remember to remove the Exit Sub after exiting the procedure.

I will try that anyhow, but is that not what effectively happens if the

Cancel

response to the InputBox is handled in the code with

EXIT SUB

?

 

[AlphaFrog]

I missed that you were responding to input boxes. Can you show your input box code and how you handle a cancel response?

 

[hershmab]

I missed that you were responding to input boxes. Can you show your input box code and how you handle a cancel response?

Here is an instance of the code at the top level of the "loop":

Sub StartXDict()
  Dim QUIT As Boolean
  
  Call OpenDict   'choose language database
  
  If MsgBox("Press OK to run X-a-Gram or CANCEL to continue program development", Title:=thisworkbook.Name, _
    Buttons:=vbOKCancel) = vbCancel Then Exit Sub
   Do
    ProcessChoice = "Choose your process:" & vbCrLf & _
      "1. Word-patterns" & vbCrLf & _
      "2. Anagrams - Complete" & vbCrLf & _
      "3. Anagrams - Partial and Full" & vbCrLf & _
      vbTab & vbTab & "Press CANCEL to quit"
      QUIT = False
    ProcessNum = Application.InputBox(ProcessChoice, DictBook.Name, Type:=1, Left:=100, Top:=500)
    Select Case ProcessNum
      Case 1: Call PatSearch
      Case 2: Call AnagSearch
      Case 3: Call AnagSearchPartial
      Case Else: QUIT = True
    End Select
  Loop Until QUIT

End Sub

..and here is the code in a proc called by the above:

Sub OpenDict()
  Dim VWBName As String, Prstring As String
  Dim Voc_Choice As Variant
  
  Prstring = "Choose vocabulary to use - BY Number:" & vbCrLf & "1. English" & vbCrLf & "2. Scrabble"

Choice:
  Voc_Choice = Application.InputBox( _
      prompt:=Prstring, _
      Title:="Cross-a-Gram 7", Type:=1, Left:=20, Top:=20)
      
  If Voc_Choice = False Then Exit Sub

...and finally here is the code from one of the three procs called within the loop:

    Options = InputBox("Enter search-pattern [,maximum word-length] :" & vbCrLf & "Press 'Cancel' to stop.", _
      "XDict7 " & DictionarY, Default:=LastOptions)
      If Len(Trim(Options)) <= 0 Then Exit Sub

 

[tcnt9176]

Any state loss will cause public variables to lose their value. It sounds like whatever you are doing (it's not clear to me what that is precisely) is causing state loss, so yes you need to re-run the procedure. Or create functions that either return the current value of the variables or reset them, and call those the same way you use the variables now.

What does "Any state loss" mean?

 

[hershmab]

I posted this query so long ago that I had completely forgotten it. I guess that some reply or replies must have been helpful (for which much thanks!) since it is no longer a problem and the containing application now works as required.

 

[RoryA]

What does "Any state loss" mean?

Anything that causes your project to reset. There are many possible causes, such as pressing the reset button in the VBE, changing code, adding activex controls to worksheets at runtime, or using the End statement.