Is there a way to clear excel variable after using exit sub as the last line?-CodePudding

Is there a way to flush all content of declared variable after Exit Sub code?

I'm experiencing that the variable still contains a value after re-running the Macro. I have a lot variable that needs to be cleared if in case it ended up with exit sub code.

Example:

Public VariableName As String

Sub SampleCode()

    VariableName = VariableName & Chr(10) & ActiveWorkbook.Name

        FinalAnswer = MsgBox("Are you sure you DON'T want to generate these fles?" & vbNewLine & VariableName, vbYesNo   vbQuestion)
        If FinalAnswer = vbYes Then
            Exit Sub
        Else
        End If

End Sub

CodePudding user response：

Put

End

Before your last line of code (so before Exit Sub or End Sub)

It clears the variables

CodePudding user response：

I'm not sure I understand the question, but the first thing to be clear on is that no line after "exit sub" can execute, because the "exit sub" tells the VBA interpreter that you are finished executing code in the procedure. This is why Scott Craner's comment suggests clearing the variable BEFORE the "exit sub".

Chris Maurer's comment is dealing with the scope of variables in VBA. There are three levels of scope for variables in VBA. Global, Module-Level, and Local, and local variables have an interesting variatoin called "static".

In your example "Public VariableName as String" is a global variable because has been declared with the "public" keword at the top of a module. It is accessible to any code in the workbook. Code in any general module, form module, worksheet module, or workbook module can refer to the variable. Alternate syntax is "Global VariableName as String" and would behave the same way. As soon as any code runs the project, memory is allocated for each and they maintain thier values until the code is reset either by executing the "End" statement as some have suggested, or the workbook is closed, or the the code is stopped from within the VBA editor.

A module-level variable is also declared at the top of the module with the following syntax:

Private VariableName as String

Dim VariableName as String

Module-level variables are accessible by any code that resides in the same module where the variable is declared. Module-level variables have the same life as global variables.

Local variables are declared within a sub-procedure or function-procedure using the keyword "Dim" as follows:

Sub my_proc()
    Dim VariableName As String
    VariableName = "fred"
    Debug.Print VariableName
End Sub

Memory is not allocated for local variables until the procedure is called and it is released once the procedure is done running, either by reaching the "end sub" (end function) or executing "exit sub" ("exit function). Accordingly, the following code prints "1" to the immediate window each time it is called:

Sub my_proc()
    Dim x As Integer
    x = x   1
    Debug.Print x
End Sub

However, local variables can also be declared with the "Static" keyword. Static local variables have their memory allocated the first time the procedure is called, but the memory is maintained after the procedure finishes execution. Accordingly, the following code prints "1" the first time it is called but prints "2" the second time, "3" the third time and so on.

Sub my_proc()
    Static x As Integer
    x = x   1
    Debug.Print x
End Sub

The static local variables are accessible like other local variables but have a life similar to module-level or global variables.

As a general rule, you want to declare variables at the lowest level possible to avoid accidental re-use of a variable name, which can very in unexpected behavior that is difficul to debug.