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.

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.

Here, this dataset contains 6 named ranges.

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

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

• Afterwards, press close.

Step 2: Insert VBA Code to Check Named Range

Afterwards, 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.

• 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

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

• Fourthly, press the F5 key to run the code.

• Finally, press the OK button.

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.

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.

The post How to Check If Named Range Exists with Excel VBA appeared first on ExcelDemy.