How to refer to an Excel NamedRange in Visual Studio using Microsoft.Office.Interop

Question

I'm transitioning from VBA to VB.NET for an Excel Add-in using ExcelDNA. Within my code, I'm trying to refer to a NamedRange by it's name (like I could in VBA) to set that Name's 'ReferTo' property. However, I get an error that it cannot convert the name I supplied to an Integer. Error: Conversion from string "MyNamedRangeName" to type 'Integer' is not valid.

In the code below, you can see where the error occurs and why.

Imports Microsoft.Office.Interop
Imports ExcelDna.Integration
Public Class Class1
    Sub SetReferToProperty()
        Dim ap As Excel.Application = ExcelDnaUtil.Application
        Dim wb as Excel.Workbook = ap.ActiveWorkbook

        'This is where the error occurs. Apparently, I can't (?) refer to    
        'the NamedRange by it's name. I need to use it's index.
        wb.Names("MyNamedRangeName").RefersTo = 0

        'If I use the Index instead (assume it's 1) it will work, but I
        'want to use the name instead - not the index.
        wb.Names(1).RefersTo = 0
    End Sub
End Class        

Answer 1

Sometimes (I'm not sure when exactly) VB.NET requires you to specify the collection property (in this case, Items) explicitly, and the default indexer doesn't work. So you need to say:

 wb.Names.Item("MyNamedRangeName").RefersTo = 0

Note that if you want to add a new name, you'd say:

wb.Names.Add("MyNamedRangeName", 0)