VBA End

What is End Function in VBA?

The VBA End statement is used to terminate the execution of a macro or procedure prematurely. It effectively stops the code execution at the point where it is encountered. It’s often used for debugging or when you want to exit a procedure based on certain conditions.

Let us look at an example. Imagine you have an Excel spreadsheet filled with data in column A and want to select the last non-empty cell in column A.

To accomplish this, you can utilize a VBA end property. We start by creating a special box called ‘lastRow’ to remember the row number of the last cell with data in column A.

• We start at the very bottom of column A (‘Cells(Rows.Count, “A”)’) and then move upwards (‘.End(xlUp)’).
• When we find the first cell with data, we write its row number in our ‘lastRow’ box.

• With ‘lastRow’ holding the row number of our target cell, we use ‘Cells(lastRow, “A”).Select’ to tell Excel to choose that specific cell in column A.

When you run this VBA macro, you will see that the last non-empty cell is selected automatically in the column A.

End Property in VBA

“VBA End Property” is a statement used within a class module. In the context of class properties, “VBA End Property” is used to specify the end of a property declaration within a class module. It ensures that the property’s value is correctly stored and retrieved when the property is accessed.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use End Function in VBA?

Examples

Example #1 – Use VBA End Property to Move in Worksheet

• Step 1: In the new module, create a new subroutine named ‘MoveCells.’

• Step 2: Here, we declare a variable named ‘currentCell’ as a Range. Initialize ‘currentCell’ to represent the currently selected cell (ActiveCell).

• Step 3: Begin a ‘Do’ loop to execute a set of instructions repeatedly.

• Step 4: Inside the loop, use the ‘InputBox’ function to prompt the user to enter a value and store the entered value in the ‘currentCell.’

• Step 5: Implement an ‘If’ statement to check if the entered value in ‘currentCell’ is equal to “end.”

• Step 6: If the condition in Step 5 is true (i.e., user entered “end”), use ‘Exit Do’ to prematurely exit the VBA End loop.

• Step 7: If “end” is not entered, use ‘Set currentCell = currentCell.Offset(1, 0)’ to move ‘currentCell’ one row down while keeping the column unchanged.

• Step 8: Continue repeating Steps 4 to 7 until the user enters “end” to exit the loop.

• Step 9: Save the macro and click on Run. Once you start executing it, you will see a popup of the user input box asking you to enter any numeric value.

• Step 10: In the user input box, enter any numerical values and click “OK.” As you input the numbers, you can enter multiple values consecutively. However, when you want to stop and calculate the sum, simply type “end” into the input box and click “OK” again.

• Step 11: The above action will record all the entered numerical values into the Excel worksheet until you input “end.” The input box will close once “end” is entered, and the sum of the entered values will be calculated and displayed as the result.

Here is the full code:

Sub MoveCells()
Dim currentCell As Range
Set currentCell = ActiveCell
Do
currentCell.Value = InputBox(“Enter a value (or type ‘end’ to stop):”)
If currentCell.Value = “end” Then
Exit Do ‘ Exit the loop when ‘end’ is entered
End If
Set currentCell = currentCell.Offset(1, 0) ‘ Move to the cell below
Loop
End Sub

Example #2 – Selection Using End Property

• Step 1: Create a new subroutine named ‘SelectRange’ within the VBA Editor in Excel in the new module.

• Step 2: Now, declare a variable userInput as a string to store the user’s input. Also, declare a variable selectedRange as a Range. This variable will store the selected range.

• Step 3: We implement error handling with On Error Resume Next to temporarily ignore errors.

• Step 4: Now create a prompt user to enter a range address using the InputBox function and store the input in the userInput variable. We’ve set Type:=2 to ensure Excel expects a cell reference input.

• Step 5: Reset the error handling with On Error GoTo 0 to enable error checking.

• Step 6: Check if the entered value in userInput is equal to “end” using an If statement.

• Step 7: If the condition in Step 6 is true (i.e., the user entered “end”), exit the subroutine using the Exit Sub statement.

• Step 8: Implement error handling again with On Error Resume Next to temporarily ignore errors.

• Step 9: Attempt to set the selectedRange variable to the range specified by the user input using Range(userInput).

• Step 10: Reset the VBA error handling with On Error GoTo 0 to enable error checking.

• Step 11: Check if the selectedRange variable is Nothing, indicating that the range does not exist or the input is invalid.

• Step 12: If selectedRange is ‘Nothing,’ display a message box with the text “Invalid range address or range does not exist.”

• Step 13: If selectedRange is not ‘Nothing,’ it means a valid range was selected. Use selectedRange.Select to choose the range.

• Step 14: Save the macro and click on Run. Once you execute it, it will display a user input window for you to enter the range.

• Step 15: If a valid range is entered, it selects it in the Excel worksheet. If the input is invalid or the range does not exist, it displays a message box stating, “Invalid range address or range does not exist.”

Here is the full code:

Sub SelectRange()
Dim userInput As String
Dim selectedRange As Range ‘ Declare a variable to store the selected range
On Error Resume Next ‘ Ignore errors
userInput = Application.InputBox(“Enter a range address (e.g., A1:B5):”, Type:=2)
On Error GoTo 0 ‘ Reset error handling
If userInput = “end” Then
Exit Sub ‘ Exit if ‘end’ is entered
End If
On Error Resume Next ‘ Ignore errors
Set selectedRange = Range(userInput) ‘ Try to set the selected range
On Error GoTo 0 ‘ Reset error handling
If selectedRange Is Nothing Then
MsgBox “Invalid range address or range does not exist.”
Else
selectedRange.Select ‘ Select the range if it exists
End If
End Sub

Example #3 – Select Right to Left, Right to Bottom, & Top

• Step 1: Create a new subroutine named ‘SelectCells’ within the VBA Editor in Excel in the new module.

• Step 2: Now create a prompt user for Direction:

Use the ‘InputBox’ function to prompt the user to enter a direction (e.g., “left,” “bottom,” or “top”) and store the input in the ‘direction’ variable.

• Step 3: Utilize a ‘Select Case’ statement to evaluate the ‘direction’ variable and select the appropriate code block based on the input.

• Step 4: Use the following Inside the ‘Select Case‘ block:

For “left,” use ‘ActiveCell.End(xlToLeft).Select’ i.e., VBA End xlleft to select the cell to the left.

For “bottom,” use ‘ActiveCell.End(xlDown).Select,’ i.e., VBA End xldown, to select the cell below.

For “top,” use ‘ActiveCell.End(xlUp).Select’ i.e., VBA End xlup to select the cell above.

• Step 5: If the user enters an invalid direction, display a message box with the text “Invalid direction.”

• Step 6: Save the module and click on run. Once you run the code, you will see a user input window allowing you to enter “left, bottom, top.”

• Step 7: Suppose, that your active cell is C5; in this scenario, here is the resulting output:

If you enter “left,” the code will select the cell to the left of the currently active cell.

If you enter “bottom,” the code will select the cell below the currently active cell.

If you enter “top,” the code will select the cell above the currently active cell in VBA.

If you enter any other input (invalid direction), the code will display a message box with the text “Invalid direction.”

Here is the full code:

Sub SelectCells()
Dim direction As String
direction = InputBox(“Enter direction (left, bottom, top):”)
Select Case direction
Case “left”
ActiveCell.End(xlToLeft).Select ‘ Select the cell to the left
Case “bottom”
ActiveCell.End(xlDown).Select ‘ Select the cell below
Case “top”
ActiveCell.End(xlUp).Select ‘ Select the cell above
Case Else
MsgBox “Invalid direction.”
End Select
End Sub

Important Things to Note

• The use of VBA End statements should be judicious, as prematurely terminating code can lead to unexpected behavior and data loss.
• In most cases, it’s better to use conditional statements like If, Then, Exit Sub or If, Then, Exit For to exit procedures or loops based on specific conditions rather than relying solely on the VBA End statement.
• The VBA End statement should be used for debugging and development purposes and is generally not suitable for production code.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA End, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA End. We learn how to use the End function in Excel VBA, End Property, along with examples & points to remember. You can learn more from the following articles –

• VBA Substring
• VBA Insert Columns
• VBA DIR Function