Check if Named Range is equal to another Named Range-CodePudding

I’m trying to resize a Named Range in Excel if the Named Range “InventoryList” is equal to the Named Range passed into the Sub. I can’t seem to get the correct syntax for this. The "IF" statement is where I'm getting the error. Hopefully someone can help me out.

Thanks in advance.

    Sub Resize_Inventory_Range(InventoryList As Excel.Range)

        Dim xlApp As Excel.Application
        Dim WB As Excel.Workbook
        Dim WS As Excel.Worksheet
        Dim FullInventory As Excel.Range

        xlApp = GetObject(, Constants.ExcelApp)
        WB = xlApp.Workbooks("Product")
        WS = WB.Sheets("Inventory")
        FullInventory = WS.Range("Full_Inventory")

        If InventoryList.Name.Name = "Hardware_Inventory" Then
            FullInventory.Resize(FullInventory.Rows.Count   1, 4).Name = FullInventory.Name.Name
        End If

    End Sub

CodePudding user response：

You can check if the two ranges are the same by comparing their addresses. For example:

MsgBox InventoryList.Address = FullInventory.Address

CodePudding user response：

What name.name returns depends on wether the name is defined on workbook- or on worksheet-level. In case of worksheet level name will return e.g. "Table1!Hardware_Inventory".

So your check should be:

If InventoryList.Name.Name like "*Hardware_Inventory" Then

(In case there could be a name "Old_Hardware_Inventory" you would have to built a more complex check ...)

And to be on the safe side - in case the supplied range has no name - you should use this function instead. Checking for the name always returns an error when there is no name.

Public Function tryGetName(c As Range) As String
'if range has no name this will catch the error
'an empty string is returned
On Error Resume Next
tryGetName = c.Name.Name
On Error GoTo 0
End Function

This is one of the rare occasions where I think on error resume next is ok.

Your check would then look like:

If tryGetName(InventoryList) like "*Hardware_Inventory" Then