Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Check If Named Range Exists with Excel VBA

Fortunately, many of us use Excel in our business organisations. In any business organisation, we use Excel to organize data as per need. Moreover, one thing is that you can check if a named range exists with VBA for better operation. However, I have used Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure of how to check if a named range exists with VBA in Excel. Hence, read through the article to learn more and save time.

Here, I have used a VBA code to check whether if you input a named range on the VBA, it exists on the active worksheet or not.

overview of VBA to check if named range exists in excel


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


What Is Named Range in Excel?

In excel, the Named range means using a name that refers to cell references, formula results as well as values. Usually, it reduces appearing large values in the formula every single time and makes it easier to use. At the same time, It reduces using cell reference (such as cell B4:B15) every time. However, one can easily use the assigned name in Excel VBA and get the desired result. Here, you will need a code in order to check the named range.


Step-by-Step Procedure to Check If Named Range Exists with Excel VBA

Often, we need to check if a named range exists with VBA for certain business analytics, and the process becomes more interesting with Excel. However, the task is very easy and simple. But you will need an arrangement and a particular VBA code in order to perform the operation properly. Hence, read through the following steps if you want to check the named range using Excel VBA properly and easily.


📌 Step 1: Select Dataset

To provide a demonstration, let’s investigate a sample dataset. Here, it contains monthly fruit sales of a super shop.  For instance, the following dataset has four columns titled Items and their sales for the month of January, February, and March. In addition, I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.

Sample dataset of different items and sales

Here, this dataset contains 6 named ranges.

  • Initially, go to the Formulas tab.
  • Now, select the Name Manager.

Open name manager in excel

  • Then, the Name Manager dialog box will appear.
  • However, you can see the named ranges available in the workbook.

Name manager dialog box

  • Afterwards, press close.

Read More: How to Use Named Range in Excel VBA (2 Ways)


📌 Step 2: Insert VBA Code to Check Named Ranges If They Exist

Afterward, a VBA code will help you to check if a named range exists in Excel. A VBA code is necessary for this method. Additionally, the process is quite simple and straight to operate. However, you can just copy the code and run it in your Excel worksheet. Hence, go through the following steps in order to complete the task properly.

  • Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
  • Secondly, click the Insert button and select Module from the menu to create a module.

insert module in excel VBA

  • Thirdly, a new window will open and write the following VBA macro in the Module.
  • However, you have to insert all the named ranges which you want to check.
  • Here, I have provided 6 range names to check with the given dataset.
Sub VBA_Check_Named_Range()
    Dim pckRng As Range
    Dim kRangeName(6) As String
    Dim bdOt As Long
    Dim g As Integer
    kRangeName(0) = "Apples"
    kRangeName(1) = "Lemons"
    kRangeName(2) = "Cherry"
    kRangeName(3) = "Bananas"
    kRangeName(4) = "Guava"
    kRangeName(5) = "Litchi"
    kRangeName(6) = "Grapes"
    Application.ScreenUpdating = False
    For g = 0 To 6
        On Error Resume Next
        bdOt = Len(ThisWorkbook.Names(kRangeName(g)).Name)
        On Error GoTo 0
        If bdOt <> 0 Then
            Set pckRng = Range(kRangeName(g))
            MsgBox "Provided Named Range: '" & kRangeName(g) & "' Found!", vbInformation, "ExcelDemy Publications"
            bdOt = 0
        Else
            MsgBox "Provided Named Range: '" & kRangeName(g) & "' Can Not be Found!", vbInformation, "ExcelDemy Publications"
        End If
    Next g
    Application.ScreenUpdating = True
End Sub

type VBA code in Module box to check if named range exists

VBA Code Breakdown

  • Firstly, I created a new procedure Sub in the worksheet using the below statement.
Sub VBA_Check_Named_Range()
  • Secondly, I declared variables as below.
    Dim pckRng As Range
    Dim kRangeName(6) As String
    Dim bdOt As Long
    Dim g As Integer
  • Thirdly, I inserted the named range which I want to search for.
    kRangeName(0) = "Apples"
    kRangeName(1) = "Lemons"
    kRangeName(2) = "Cherry"
    kRangeName(3) = "Bananas"
    kRangeName(4) = "Guava"
    kRangeName(5) = "Litchi"
    kRangeName(6) = "Grapes"
    Application.ScreenUpdating = False
  • Now, I applied the For loop and then the IF loop to start checking the named range and activate the current workbook and range name.
    For g = 0 To 6
        On Error Resume Next
        bdOt = Len(ThisWorkbook.Names(kRangeName(g)).Name)
        On Error GoTo 0
        If bdOt <> 0 Then
  • After that, I set values for a variable and created a message box to get the output.
Set pckRng = Range(kRangeName(g))
            MsgBox "Provided Named Range: '" & kRangeName(g) & "' Found!", vbInformation, "ExcelDemy Publications"
            bdOt = 0
        Else
            MsgBox "Provided Named Range: '" & kRangeName(g) & "' Can Not be Found!", vbInformation, "ExcelDemy Publications"
  • Now, I ended the IF loop and returned to the next value of g.
  • Lastly, I ended the Sub of the VBA macro.
End If
    Next g
    Application.ScreenUpdating = True
End Sub
  • Fourthly, press the F5 key to run the code.

the VBA macro code returns message box showing named ranges

  • Finally, press the OK button.

Read More: How to Name a Range in Excel (5 Easy Tricks)


Final Output

Last but not least, you will get your desired output. However, the output will show you whether a named range exists or not. Just you have to press the OK button every time the dialog box appears.

final output of message box showing availability of Excel named ranges


💬 Things to Remember

  • First of all, you can choose any name in order to check whether it exists or not.
  • Then, you have to provide the Named Range according to your datasheet. Here, insert the range that you want to check for.
  • Next, you can change the VBA code according to your preference.
  • Afterwards, it will also show if the name range does not exist, you inserted.
  • At last, you can get the output in a MsgBox. For this purpose, you have to modify the code according to your desire and you can change the appearance also.

Conclusion

These are all the steps you can follow to check if a named range exists with VBA in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo