Method 'Range' of object '_Worksheet' Failed Error in VBA

When working with VBA in Excel, encountering errors is not uncommon, especially when dealing with range objects. One such error is the "Method 'Range' of object '_Worksheet' failed". In this post we will explore the causes of this VBA error and how to effectively troubleshoot and resolve it.

Table of contents
Introduction to Method 'Range' of object '_Worksheet' Failed Error
Common Causes of the VBA Error
Troubleshooting the Error

Introduction to Method 'Range' of object '_Worksheet' Failed Error

The "Method 'Range' of object '_Worksheet' failed" error in VBA typically occurs when the code tries to reference a range that does not exist or is incorrectly specified. This error can be frustrating, especially for those new to VBA. Understanding the nature of this error is the first step in resolving it.

Common Causes of the Range Method Error

Incorrect Range Reference
When the specified range in the code does not exist in the worksheet. Here is a simple example.

Sub SelectRange()

   Dim WS As Worksheet

   Set WS = ActiveSheet

   WS.Range("B1400000").Select

End Sub

In this example, macro is trying to select a cell called B1400000. But such a cell is not available in Excel because the row limit of the Excel application is 1,048,576.

Check this post to see more details about Excel row limit.
Number of rows in Excel

So when we run the macro it will show this error message.

Troubleshooting the Range Method Error

To troubleshoot this error, follow these steps.

Check Range References
Ensure that the range referenced in the code exists in the worksheet.

Debugging
Use VBA's debugging tools, like the Immediate Window and Local Window, to track the values and state of variables at runtime. If your code is referencing dynamic ranges then you can detect whether it is referencing nonexistent ranges.

Error Handling
Implement error-handling routines to catch and handle errors.

The error "Method 'Range' of object '_Worksheet' failed" can be a hurdle in VBA programming, but with careful examination of the code, validation of references, and implementation of best practices, it can be effectively managed and resolved. Understanding the root causes and having a structured approach to troubleshooting are key to overcoming this challenge in Excel VBA.

How to Get Selected Item from VBA Listbox

In this lesson, we will explore how to extract the selected value from a multicolumn listbox using VBA in Excel. This is particularly useful when you have a listbox filled with multiple columns of data and you need to retrieve specific values based on user interaction. The process may seem complex, but with the right approach, it becomes a straightforward task.

Table of contents
Using the ListIndex Property
Extracting Value from a Specific Column of the Selected Row - Using List Property
Handling Multiple Selections For MultiSelect Enabled ListBoxes
Extracting Selected Value from a Listbox - Using Column Property
Tips and Tricks for Effective Coding

Before diving into extracting values, it's crucial to understand what a listbox is. Essentially, it's a VBA control that can display items across multiple columns, like a simple table or spreadsheet. This functionality is very useful when you need to show more than one attribute of an item in a VBA control. Because in other VBA controls such as text boxes you can only show one attribute. So the VBA listboxes enhance the user experience by allowing users to view and select from a range of related data points.

Now let’s look at how we can get selected items from a VBA listbox in a few different ways.

Above is a sample dataset consisting of twenty data entries. This dataset includes various fields such as Medicine ID, Medicine Name, Unit, Price, Supplier Name, Address, Phone, Rating and Supply Time. Please note that this data is entirely fictional and created for example purposes only.

Understanding the Listbox Properties
Before diving into the code, it’s important to understand two crucial properties of the listbox: List and ListIndex. The List property represents the array of items in the listbox, while ListIndex refers to the index of the selected item.

The primary challenge in working with multicolumn listboxes is retrieving the user’s selection, particularly when the listbox contains several columns. Let’s consider this example user form.

User has entered a value in the supplier name field and hit the search button. Now all the data related to that supplier name is shown in the listbox. Assume the name of the form is afrmViewDat and name of the listbox is lstSearchResult.

Using the ListIndex Property

The ListIndex property of a listbox is your starting point. The ListIndex property of a listbox returns the index of the selected item. However, it's important to remember that the index starts at 0. Here’s how you can use it.

Sub GetListIndex()

   Dim selectedIndex As Integer

   With frmViewData.lstSearchResult
     selectedIndex = .ListIndex
   End With

   MsgBox selectedIndex

End Sub

So here is the result of the above subroutine.

This method is straightforward but only works well when single selection is enabled. Because if multiselect is on and if the user has selected multiple items, then this subroutine will only return the index of the item you selected lastly.

What will happen if the user doesn’t select any item? Then -1 will be returned as the result. So we can use this value to check whether the user has selected any item before executing the rest of the code.

Extracting Value from a Specific Column of the Selected Row - Using List Property

Once you have the row number, you can extract the value from a specific column using the List property. The List property can take two parameters: row index and column index. For instance, if you want to get the value from the second column of the selected row, you can use this approach.

Sub GetSelectedValue_Method1()

   Dim selectedIndex As Integer
   Dim selectedValue As String

   With frmViewData.lstSearchResult
     selectedIndex = .ListIndex
     If selectedIndex <> -1 Then
       selectedValue = .List(selectedIndex, 1) ' 1 for the second column
     Else
       selectedValue = "No selection"
     End If
   End With

   MsgBox selectedValue

End Sub

Handling Multiple Selections For MultiSelect Enabled ListBoxes

Things get a bit trickier when dealing with listboxes which are set to allow multiple selections. In such cases, you’ll need to iterate through each item in the listbox, checking if it’s selected and then retrieving the required column values. Below subroutine will get values of the second column for all the selected rows.

Sub GetSelectedValues_ForMultiselect()

   Dim i As Integer
   Dim selectedValues As String

   With frmViewData.lstSearchResult
     For i = 0 To .ListCount - 1
       If .Selected(i) Then
         selectedValues = selectedValues & .List(i, 1) & "; "
       End If
     Next i
   End With

   MsgBox selectedValues

End Sub

Extracting Selected Value from a Listbox - Using Column Property

In the above examples, we learnt how to get a selected value using Listindex and List properties. However you can also use the Listindex with Column property to get the selected values from a listbox. Below example subroutine shows how you can get the column 3 value of the selected row.

Sub GetSelectedValue_Method2()

   Dim selectedValue As String

   With frmViewData.lstSearchResult
     If .ListIndex <> -1 Then
       selectedValue = .Column(2, .ListIndex) '2 for the third column
     Else
       selectedValue = "No selection"
     End If
   End With

   MsgBox selectedValue

End Sub

Tips and Tricks for Effective Coding

Remember that VBA is zero-indexed: The first column is column 0, the second is column 1, and so on. Same for the rows.
Always check if an item is selected in the listbox to avoid runtime errors.
Use meaningful variable names for clarity and maintenance purposes.

Also read
Populate Userform Listbox from Range
Add Horizontal Scrollbar to a Listbox
Fill a Listbox From an Array

Add Single Quotes to Excel Cells Using VBA

In our previous post we learnt the uses of adding single quotes to the beginning of cells.

Uses of Adding Single Quotes to Start of Excel Cells

In this lesson you will learn how to add single quotes to Excel cells using VBA. First of all, let’s see how we can do this manually in Excel. Because Excel shows unusual behavior when you add a single quote at the beginning of a cell.

Assume you want to add ‘53 to an Excel cell. Then when you enter '53 to an Excel cell it will show only 53 with a small green triangle at the upper left corner of the cell.

But in the formula bar you can still see the single quote before the number.

Also when you select the cell a small icon will appear and if you take your cursor on top of it, you will see this kind of message.

Then what if we want to see a single quote at the beginning of the cell. Solution is simple. You need to add two single quotes.

Here one single quote is shown in the cell. However you can see both single quotes in the formula bar.

Assume you want to add single quotes at both left and right of the content of the cell.
Example - '53'
How can we do that? To do this you need to add two single quotes at the beginning and only one single quote at the end like this.
''53'

Now let’s see how we can add single quotes to Excel cells using VBA. When we automate this in VBA we need to consider the above behavior too.
Let’s consider this sample Excel sheet.

Sheet has 10 values in column A. Assume we want to add a single quote in front of each value. There are two ways to do this in VBA. We can use either ASCII code or we can use single quotes inside double quotes. First let’s see how we can do this using ASCII code. Assume the name of the worksheet is “My Data”.

Method 1 - Using ASCII code

Sub Add_Single_Quote()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = Chr(39) & WS.Range("A" & i).Value
   Next i

End Sub

In the above code Chr(39) represents the single quote character. Here you can find the full list of ASCII codes.

ASCII Table

Also a For loop is used to iterate through each value in column A.

Method 2 - Using Single quotes directly inside double quotes

Sub Add_Single_Quote_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = "'" & WS.Range("A" & i).Value
   Next i

End Sub

Below is the result we get when we run any of the above subroutines.

Like in the manual scenario, we only get a green triangle at the upper left corner instead of the single quote. To show a single quote we need to add two single quotes using VBA. For that we can modify the above two macro as follows.

Method 1

Sub Add_Single_Quote()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = Chr(39) & Chr(39) & WS.Range("A" & i).Value
   Next i

End Sub

Method 2

Sub Add_Single_Quote_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("My Data")

   For i = 1 To 10
     WS.Range("A" & i).Value = "''" & WS.Range("A" & i).Value
   Next i

End Sub

Now we will see a single quote before each value as expected.

Next let’s see how we can add a single quote at both sides of the cell content. Let’s consider this new sheet. Assume the name of the sheet is “Fruits”.

Below are the two methods to add single quotes at either side of the fruit names.

Method 1

Sub Add_Single_Quotes_At_Both_Sides()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("Fruits")

   For i = 1 To 8
     WS.Range("A" & i).Value = Chr(39) & Chr(39) & WS.Range("A" & i).Value & Chr(39)
   Next i

End Sub

Method 2

Sub Add_Single_Quotes_At_Both_Sides_Method2()

   Dim WS As Worksheet
   Dim i As Integer

   Set WS = Worksheets("Fruits")

   For i = 1 To 8
     WS.Range("A" & i).Value = "''" & WS.Range("A" & i).Value & "'"
   Next i

End Sub

You will get this result if you run any of the above macros.

Uses of Adding Single Quotes to Start of Excel Cells

Adding single quotes to the start of cells in Excel can be necessary in various scenarios to ensure data accuracy and proper interpretation. Here are a few scenarios where this technique might be useful.

Preserving Leading Zeros

When dealing with numeric codes or identifiers that begin with zeros, Excel might automatically remove leading zeros. Adding a single quote before such values prevents Excel from treating them as numbers, preserving the leading zeros.
Example:
00123 might become 123
'00123 remains as is

Preparing Data for SQL Queries

In SQL queries, single quotes are often used to denote string values. If you are creating SQL queries in Excel and have a list of values, adding single quotes ensures that they are recognized as strings.
Example:
Apple might become 'Apple'

Handling Special Characters

If your data contains special characters that might have specific meanings in Excel or other applications, adding single quotes can help avoid misinterpretation.
Example:
=SUM(A1:A10) might be seen as a formula
'=SUM(A1:A10) ensures it's treated as text

Creating CSV Files

When creating Comma-Separated Values (CSV) files, adding single quotes to cells can be beneficial. It helps maintain the intended format, especially when dealing with leading zeros or text that might resemble numerical data.
Example:
01234, 56789 might become 1234, 56789
'01234, 56789 remains as is

Also read
Add Single Quotes to Excel Cells Using VBA
How To Quote All Cells Of A CSV File
How to Put Double Quotes in a String in VBA

Extract Formulas From Cells Using VBA

In Excel, formulas are the backbone of data analysis and calculations. Extracting these formulas using VBA can be a powerful tool for various purposes. In this guide, we'll explore step by step how to use VBA to extract formulas from cells.

Here are a few scenarios where we need to extract formulas from cells using vba

• To help in keeping track of changes made to formulas over time.
• Analyze formulas to identify potential errors or inconsistencies.
• Extracting formulas can help you analyze dependencies and relationships between different cells.
• To partially automate the documentation process, providing insights of the logic behind calculations.

Now let’s see how we can develop a macro to extract a formula from a cell using VBA. First let’s see how we can get the formula from a particular cell we want. Let’s consider this example sheet.

This worksheet has some values from B1 cell to B9 cell. Then I have put the following formula in cell B11.
=SUM(B1:B9)

Assume the name of the worksheet is “Sheet1”. Then the following subroutine will show the formula of cell B11 in a message box.

Sub ExtractOneFormula()

   Dim WS As Worksheet

   Set WS = Worksheets("Sheet1")

   MsgBox WS.Range("B11").Formula

End Sub

Now we learnt how to get a formula from a particular cell using VBA. Next let’s look at how to get a list of all the formulas in a sheet with cell addresses. Let’s consider this example Excel sheet.

In this worksheet, Total sales in column D is calculated using formulas. For an example D2 cell has following formula
=B2*C2

Then Sales Ranking in column E is also calculated using formulas. For an example E2 cell has the following formula.
=RANK.EQ(D2,$D$2:$D$11,0)

Also % of Total Sales in column F is calculated using formulas. F2 cell has following formula
=D2/SUM($D$2:$D$11)

Then 3 fields(Grand Total Sales, Average Quantity Sold, Maximum Unit Price) in cells B13, B14 and B15 are calculated using following formulas.

Grand Total Sales	=SUM(D2:D11)
Average Quantity Sold	=AVERAGE(B2:B11)
Maximum Unit Price	=MAX(C2:C11)

Then here is the macro to print all the formulas and their cell addresses in the immediate window

Sub ExtractFormulas()

   Dim WS As Worksheet
   Dim Rng As Range

   Set WS = Worksheets("Sheet2")

   For Each Rng In WS.UsedRange
     If Rng.HasFormula = True Then
       Debug.Print "Formula in " & Rng.Address & ": " & Rng.Formula
     End If
   Next Rng

End Sub

In the above subroutine, the statement “If Rng.HasFormula = True Then” is used to identify the cells having formulas.

Also Rng.Address statement outputs the cell address of the cell and Rng.Formula statement output formula of that cell.

Also read
How to Add a Formula to a Cell Using VBA
Access Formula Bar Using Keyboard

Extract Only Numbers From a String Using VBA

In this lesson you will learn how to extract only numbers from a string using VBA. Here are some examples where extracting only numbers from a string can be useful.

Data cleaning in Excel
You have an Excel sheet with a column containing alphanumeric data, and you want to extract only the numeric part for analysis or calculation purposes.

Financial Data Processing
You are working with financial data that includes transaction descriptions, and you want to extract only the transaction amounts.

Web Scraping
You are scraping data from a website, and some of the retrieved text includes both numeric and non-numeric characters. You want to filter out only the numeric values.

Above are a few scenarios where you may need to extract only numbers from a string using VBA.

Now let’s see how we can develop a VBA program to do this. Let’s create a function to extract numbers from a given string. Name of the function is ExtractNumbers. It takes one parameter, InputString, which is expected to be a string. The function return will also be a string. Below is the step by step guide on how to develop this function. You can find the completed function and test subroutine at the bottom.

Function ExtractNumbers(InputString As String) As String

End Function

Next we need to declare 3 variables.

Dim i As Integer
Dim Char As String
Dim ResultString As String

i is used as a loop counter. Char represents a single character in the string. ResultString will store the extracted numbers.

A For loop is used to iterate through each character in the inputString. The loop starts from the first character (1) and continues until the length of the string (Len(inputString)).

For i = 1 To Len(InputString)

Next i

Mid Function extracts a single character from the InputString at the position i and assigns it to the variable Char.

Char = Mid(InputString, i, 1)

Check this microsoft documentation to learn more about mid function.

Mid function

If Statement checks if the extracted character is numeric using the IsNumeric function.

If IsNumeric(Char) = True Then

End If

Want to learn more about IsNumeric Function? Then check this post.

IsNumeric Function

If the character is numeric, it is appended to the resultString

ResultString = ResultString & Char

Following line is used to return the extracted numbers as the result of the function.

ExtractNumbers = ResultString

Here is the full code of the ExtractNumbers function.

Function ExtractNumbers(InputString As String) As String

   Dim i As Integer
   Dim Char As String
   Dim ResultString As String

   'Loop through each character in the input string
   For i = 1 To Len(InputString)
     Char = Mid(InputString, i, 1)

     'Check if the character is a number
     If IsNumeric(Char) = True Then
       'Append the number to the result string
       ResultString = ResultString & Char
     End If
   Next i

   'Return the result string containing only numbers
   ExtractNumbers = ResultString

End Function

Now let's create a subroutine to use the ExtractNumbers function on a sample string. This subroutine initializes a string, calls the function, and prints the extracted numbers in the Immediate Window.

Sub TestExtractNumbers()

   Dim originalString As String
   Dim result As String

   'Example string with alphanumeric characters
   originalString = "ProductA123"

   'Call the function to extract numbers
   result = ExtractNumbers(originalString)

   'Display the result in the Immediate Window
   Debug.Print result

End Sub

Run VBA Code Automatically on Workbook Open

In Excel automations, running VBA code upon workbook opening can streamline processes and boost efficiency. From this post you will explore the possibilities of this advanced feature, allowing you to tailor Excel to your unique needs and maximize productivity in your daily workflow. Then this article will guide you through the steps to run a VBA code when opening an Excel file using an example.

First of all let’s explore the possibility of this advanced feature.

• Data Refresh: If your workbook relies on external data sources, running VBA code on open can trigger an automatic refresh, ensuring that your data is always up-to-date.
• Security Measures: You may want to use password prompts when opening the workbook, ensuring that only authorized users can use the file. For this you can use this technique to show a login form when opening the workbook. Check this post to learn how to show a userform automatically when opening an Excel file.
  Show Userform Automatically When Opening Excel File
• User Interface Customization: You can use this technique to VBA to customize the workbook's interface, displaying specific sheets upon opening. For example, your workbook may have several worksheets and you may want to show only a particular worksheet on the workbook open. This is the usage I showed in the example below.
• Initialization Tasks: Running VBA code on open is handy for initializing variables, setting default values, or configuring the environment to default state.
• Version Control: You can use this technique to VBA to log information about when the workbook was last opened, helping with tracking changes.
• Connected Workbooks: In scenarios where multiple workbooks are interlinked, running VBA code on open can establish connections.
• Alerts and Notifications: Displaying alerts or notifications when opening the Excel VBA application. In some Excel VBA applications you might need to inform users about important updates or pending tasks whe he/she opens the application.
• Automated Backups: This technique can be used to create an automatic backup system for your Excel VBA application. For an example you can tell the VBA to create a backup every time when a user opens the application. Or you can limit it to create only one backup per day if the user opens the application multiple times within one day.

So now you explored various possibilities of this feature. Next let’s learn how to write a code to do this. I will explain this using an example.

This example belongs to user interface customization. Assume your workbook has 3 worksheets called “Input”, “Report”, “Settings”. Suppose you want to only show the “Input” worksheet when the user opens the workbook hiding the other sheets. Users might be able to see other sheets using buttons while using the application.

Now first of all we need to develop a simple macro to show only the “Input” tab hiding other sheets.

Sub ShowOnlyInput()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

Note that there are different ways to hide worksheets using VBA. Some methods don't allow users to manually unhide sheets when hidden with VBA. Check this post if you like to learn more about hiding and unhiding worksheets in Excel.

Hide And Unhide Worksheets Using VBA

We can put above simple code inside a module of the VBA project. Don’t know how to insert a module to a VBA project? Then check this post.

How to Insert Modules in Excel VBA Projects

Now we need a way to run this subroutine when the user opens the workbook. To do that we can use the workbook open event. Events are occurrences or triggers that happen within the Excel application, and VBA allows you to write code that responds to these events. So if you write a code inside the Workbook.Open event then it will be executed when the user opens the workbook.

Follow these easy steps to add code to the workbook open event.

Go to the VBA editor and double click on the ThisWorkbook Module.

Now select “Workbook” from the first dropdown.

When you select the “Workbook” from the dropdown, the second dropdown will be automatically changed to “Open” and the following code will be added to the ThisWorkbook module.

Also if you look at the second dropdown, you will notice that there are lots of other events available related to the Workbook object.

Now we can run the previous subroutine we wrote by calling it from inside the Open event of the Workbook.

Private Sub Workbook_Open()

   Call ShowOnlyInput

End Sub

Also instead of putting the code in a separate subroutine, you can put the code directly inside the Workbook_Open event as follows.

Private Sub Workbook_Open()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

But it is always a good practice to divide the code into meaningful sections. Because it enhances readability, maintainability, and overall code quality. When code is divided into sections, each part focuses on a specific task or functionality. This makes it easier for developers (including the original coder or others) to understand the purpose and flow of each section.