Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024
Difficulty: Easy

Using Macro Errors in VBA

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

This article will take you through the fundamentals, techniques, functions and utilities for performing error debugging and macros in Excel VBA.

Place a 'command button' in the worksheet then add the following lines below. To execute the lines, click the command button on the sheet.

Debugging

The various strategies for debugging programs and identifying errors can be as follows:

  1. Single Step - Using the F8 key, one can single-step through their program/snippet in VBA. This will execute each line independently as one goes through the effects of each line as they go through the program.

To start debugging the program, place a new command button in your sheet and try debugging the following program. 

Code

Dim i As Integer, j As Integer

For i = 1 To 2
    For j = 1 To 5
        Cells(i, j).Value = WorksheetFunction.RandBetween(20, 100)
    Next j
Next i

Result

This would be the result of running the above program.

Now to see the output and working of each line of the above program, one can

  1. Empty the range of cells from A1 to E2 and resize the VB editor side by side with the sheet to see the debugging output of each statement in the program.
  2. In the VB editor, place a cursor at the start of the function and press F8. This should change the colour of the first line to yellow.

3. Press the F8 key four times. For i = 1 and j = 1, in excel VBA enters a new random number between 20 and 100 in the cell at the intersection of row one and column one. By holding the cursor steady on a variable, one should be able to see the value of the variable.

4. Now Press F8 two more times. For i = 1 and j = 2, in excel VBA should enter another random number between 20 and 100 in the cell at the intersection of row 1 and column 2.

5. Now single-step through the rest of the program to see how Excel VBA enters the other numbers. This is an excellent way if one wants to learn how a loop works. If you wish to stop the program, click the Reset (Stop) button.

2. Breakpoint - A breakpoint can be used and set to halt/pause the execution of the running program at a specific line.

  1. For the same program used above, first, empty the range of cells from A1 to E2.
  2. Set a breakpoint by clicking on the left margin (in grey) where you might want to place your breakpoint. You should be able to see a red dot appearing there.

3. Keep clicking on the green arrow to execute the macro until the breakpoint is reached.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Error Handling

To handle errors in excel, VBA can either keep executing the program when it encounters errors using "On Error Resume Next" statement, or they could either throw relevant messages using which one could identify the error.

We start by declaring two range objects and initialising the range object with a selected range.

Code

Dim rng As Range, cell As Range
Set rng = Selection

Now since we want to calculate the square root of each cell from a randomly selected range, we use a “For Each Next loop” for this:

 Code

For Each cell In rng
Next cell

Now add the following line to the loop.

Code

On Error Resume Next

Next, we calculate the square root of a value. In Excel VBA, we can use the “Sqr” function for the same. Add the following line to the loop:

Code

cell.Value = Sqr(cell.Value)

Now exit the VBA editor and test the program out.

Result

Explanation

Here, Excel VBA has ignored all cells containing invalid values such as negative numbers and text, and without using the 'On Error Resume Next' statement in the same program should throw two errors. Be careful to only use the 'On Error Resume Next' statement when you are sure ignoring errors is OK.

Err Object

In Excel VBA, when errors are thrown, an error object has all the details related to that error which is filled in its properties.

Here we will try to use the same program we previously used to generate square roots of numbers.

Code

Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
    On Error GoTo InvalidValue:
    cell.Value = Sqr(cell.Value)
Next cell
Exit Sub
InvalidValue:
MsgBox Err.Number & " " & Err.Description & " at cell " & cell.Address
Resume Next

One should get the following result when one selects a Range("B2:B6") and clicks the command button on the sheet: 

Result

Explanation

when an error occurs, the number attribute/property of the 'Err' object is filled with a unique error number of the current error. The Description attribute/property is filled with the error description of that error.

Subscript Out of Range

This is a frequently occurring error in excel VBA, which happens when one refers to a non-existent value in the sheet or a non-existent array element. 

Ex. The initial step here would be to declare two range objects. We call the Range objects rng and cell.

Code

Workbooks("sales.xlsm").Close

Result when there's no open workbook with this name:

Ex. A Worksheets collection in Excel VBA contains all the Worksheet objects present in the workbook. Since we only have 3 worksheets, the line below that tries to place the word Hello into cell A1 on the 4th worksheet causes error.

Code

Worksheets(4).Range("A1").Value = "Hello"

Result when there's no 4th workbook:

Explanation

Here, a 'subscript out of range' error pops up as there is no 4th worksheet, so to fix this error, one can change the 4 to a 1, 2 or 3 (or insert a new worksheet by clicking the plus sign).

FAQs

1. How can a macro be interrupted in Excel VBA?

     One can interrupt a macro in Excel by pressing Esc or Ctrl + Break.

2. How can macro comments be used in Excel VBA?

    To let Excel VBA know that you want to insert a comment, precede the text with an apostrophe.

Key Takeaways

Congratulations on finishing the blog!! After reading this blog, you will grasp the concept of handling and debugging errors using different strategies, techniques and utility functions in Excel VBA.

If you are preparing yourself for the top tech companies, don't worry. Coding Ninjas has your back. Visit this link for a well-defined and structured material that will help you provide access to knowledge in every domain.

Topics covered
1.
Introduction
2.
Debugging
3.
Error Handling
4.
Err Object
5.
Subscript Out of Range
6.
FAQs
7.
Key Takeaways