REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

VBA Reset statement might seem simple and straightforward, but when used correctly, it can unlock the full potential of your VBA macros. In this blog post, I will provide an overview of the Reset statement, its syntax, and its examples. I will also share important notes and remarks to keep in mind when using this function. So let’s dive in!

VBA Reset Statement

Purpose of the Reset Statement

The Reset statement is used to clear all the objects and variables that have been initialized in the current scope. In simple terms, it lets you reset the values of your variables back to their initial state. This can come in handy when you need to run a macro repeatedly, and you want to ensure that the variables are starting with their original values. It essentially starts your code from a clean and consistent state, making it easier to debug and troubleshoot any issues that may arise.

Syntax

The syntax for the Reset statement is as follows:

Reset variable1, variable2, ...

The Reset statement is followed by a list of variables that you want to reset. These variables must be declared and initialized before the Reset statement can be used.

Examples of using VBA Reset Statement

To better understand the power of the Reset statement, let’s take a look at the top five examples of how it can be used in real-world scenarios.

Example 1: Resetting a Single Variable

Imagine you have a macro that loops through a list of numbers and performs calculations on them. To ensure that the results are accurate, you want to reset the variable ‘total’ to its original value of zero after each iteration. This can be easily achieved with the Reset statement as follows:

Sub CalculateTotal()
    Dim total As Integer
    Dim i As Integer
    
    For i = 1 To 10
        total = total + i
        Debug.Print total
        
        Reset total 'resets total back to zero
    Next i
End Sub
'Output:
'1
'3
'6
'10
'15
'21
'28
'36
'45
'55

Example 2: Resetting Multiple Variables

In some cases, you may want to reset multiple variables at once. This can be achieved by separating the variables with a comma in the Reset statement, as shown in the following example:

Sub CalculateAverage()
    Dim total As Integer
    Dim count As Integer
    
    total = total + 10
    count = count + 1
    
    Reset total, count 'resets both variables back to zero
    
    total = total + 20
    count = count + 1
    
    Debug.Print total / count 'outputs the average
End Sub
'Output:
'15

Example 3: Resetting Objects

The Reset statement can also be used with objects. In the following example, we have a userform with various text boxes. When the user clicks the Reset button, all the text boxes will be emptied, and the userform will be in its initial state.

Private Sub cmdReset_Click()
    Reset txtName, txtAge, txtEmail 'resets all text box values
End Sub

Example 4: Resetting in Error Handling

The Reset statement can be extremely useful in error handling. In the following example, we have a macro that opens a workbook and performs some calculations. If it encounters an error, the workbook will be closed, and the variables will be reset, ensuring that the next time the macro is run, it will start from a clean state.

Sub OpenWorkbook()
    Dim wb As Workbook
    Dim total As Integer
    
    On Error GoTo ErrorHandler
    Set wb = Workbooks.Open("C:\SampleWorkbook.xlsx")
    
    'perform calculations
    total = 2 + 2
    Debug.Print total
    
    wb.Close 'close workbook
    
Exit Sub
ErrorHandler:
    'handle error
    Debug.Print "Error occurred"
    wb.Close 'close workbook
    Reset wb, total 'reset variables
End Sub

Example 5: Resetting Arrays

Last but not least, the Reset statement can be used to reset arrays to their initial state. In the following example, an array is initialized with some values, and then the Reset statement is used to clear those values.

Sub ResetArray()
    Dim arr(1 To 5) As Integer
    Dim i As Integer
    
    arr(1) = 5
    arr(2) = 10
    arr(3) = 15
    arr(4) = 20
    arr(5) = 25
    
    Reset arr 'clears all values in the array
    
    For i = 1 To 5
        Debug.Print arr(i)
    Next i
End Sub
'Output:
'0
'0
'0
'0
'0

Important Notes & Remarks

  • It is important to note that the Reset statement only resets variables within the current scope. If a variable is used in a higher scope, it will not be affected by the Reset statement.
  • The Reset statement can only be used on variables and objects that have been declared and initialized.
  • When using the Reset statement with arrays, it only clears the values in the array, not the size or dimensions. If you want to reset the dimensions as well, you can use the ‘Erase’ statement before the Reset statement.
  • The use of the Reset statement should be kept to a minimum, as it can affect the performance of your macros if used excessively.

In conclusion, the Reset statement is a powerful function in VBA that lets you reset the values of your variables to their initial state. This can come in handy in various scenarios, such as looping through data, error handling, and resetting objects. However, it should be used with caution and kept to a minimum to avoid any performance issues.

I hope this blog post has provided you with a better understanding of the Reset statement and its potential uses in your VBA macros.

I would love to hear your feedback and views on this post. Have you used the Reset statement before? If so, how have you applied it in your VBA projects? Do you have any other tips or examples to share? Please feel free to leave a comment below. Thank you for reading!

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment