none
Excel PIA Names("name") call throwing exception instead of returning a Name object RRS feed

  • Question

  • In VB, I'm trying to get a Name object back from Names using a literal name String. E.g, from a simplified testcase:

    ...
    wks.Names("Sheet1!rngWksRng")
    ...

    But I'm getting a cast exception System.InvalidCastException: 'Conversion from string "Sheet1!rngWksRng" to type 'Integer' is not valid.' "FormatException: Input string was not in a correct format."

    If I test ? wks.Names(0).Name in the immediate window at the point of the exception I get the expected "Sheet1!rngWksRng".

    I'm probably doing something obviously stupid, but, three hours later, I'm still not seeing it. I tested it in VBA and it worked exactly as expected.

    Thanks in advance if you can spot my error.


    • Edited by Dick Watson Monday, November 26, 2018 3:45 AM
    Monday, November 26, 2018 3:44 AM

All replies

  • Hi Watson,

    Here's an example of the usage of Excel PIA Names("name"):

    Set nms = ActiveWorkbook.Names 
    Set wks = Worksheets(1) 
    For r = 1 To nms.Count 
        wks.Cells(r, 2).Value = nms(r).Name 
        wks.Cells(r, 3).Value = nms(r).RefersToRange.Address 
    Next

    For more information, please review the following link:

    Names object (Excel)

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, November 27, 2018 3:20 AM
    Moderator
  • Yup. True as far as it goes. But not responsive to the issue I'm having.

    The Excel docs say [my emphasis added] "Use Names(index), where index is the name index number or defined name, to return a single Name object." The Names(definedName) form works in VBA just fine to return a Name object. It work doesn't work for me from VB via the PIA. It's this that I'm asking about, not some general question about how to use the Names() interface.

    Should I put it a feedback that the docs are wrong? I don't think they are supposed to be wrong, since the Names(definedName) form works fine in VBA.

    Tuesday, November 27, 2018 3:29 AM
  • Hi Watson,

    >> But I'm getting a cast exception System.InvalidCastException: 'Conversion from string "Sheet1!rngWksRng" to type 'Integer' is not valid.' "FormatException: Input string was not in a correct format."

    According to your description, I think it is a Format error. Does your string "Sheet1!rngWksRng" contain a currency symbol? If so, you can try to convert it to an integer like this:

            Dim MyString As String = "Sheet1!rngWksRng"
            Dim MyInteger As Integer = Integer.Parse(MyString, Globalization.NumberStyles.AllowCurrencySymbol)

    And the Excel docs also said we can use Item(Object, Object, Object) to returns a single Name object from a Names collection. So can we try in this way?

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    Tuesday, November 27, 2018 6:32 AM
    Moderator
  • The string in the line I pasted in above is real. It's a real literal string "Sheet1!rngWksRng". This is what Excel Range Names look like. No currency symbols, no localization. And, as noted, calling Names("Sheet1!rngWksRng") works in VBA. And is consistent with what the docs say will work via the PIA. In my testcase/Unit Test that named Name is Names(0).

    Barring a solution to the failure to operate like the docs suggest, I'm using this in my Unit Test:

            ' this looping find is because Names("stringname") refuses
            ' to work
            Dim nm As Name = Nothing
            For Each nm In wks2.Names
                If nm.Name = "Sheet2!rngWksNew" Then Exit For
            Next
            ' .Names("Sheet2!rngWksNew") causes exception
            Assert.IsTrue(IsNameRefValid(nm))
    

    It works.

    Tuesday, November 27, 2018 6:41 AM
  • Hi Watson,

    You can mark your solution as answer and please help us close the thread.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, November 27, 2018 8:11 AM
    Moderator
  • You seem to be willfully missing the point. My workaround--that doesn't use the .Names() interface at all--isn't an answer to the question of why can't I make the .Names("definedName") interface work as documented and as it works in VBA? My workaround also has bad performance implications if there are lots of Names.



    • Edited by Dick Watson Tuesday, November 27, 2018 2:48 PM
    Tuesday, November 27, 2018 2:45 PM
  • I only have VBA in which for you all works fine, so a couple of comments in case they lead to anything. First I assume:

    rngWksRnd is defined as a worksheet level name (in the Names box with Scope: Sheet1).
    wks refers to Sheet1, and specifically to Sheet1 in the workbook with the name. "Sheet1" is literally the name of the sheet in your actual code (and not a name that might require embracing with apostrophes).

    I'm surprised wks.Names(0) returns anything, it wouldn't in VBA as the index of the first name is 1

    What do these return:
    ?wks.Names.Count
    ?wks.Names(wks.Names.Count).Name
    I'd expect the latter to error if the index of the first name is zero

    If the name is in the sheet's names collection (which I assume it is) you should be able to return it without the sheet-name qualifier like this
    ?wks.Names("rngWksRg")

    If not already try testing similar in a new workbook with only this workbook loaded.

    Wednesday, November 28, 2018 12:12 PM
    Moderator
  • This code lifted from a VB Unit Test project, the test method was purpose built and executed just now to prove the points:

    Imports Microsoft.Office.Interop.Excel
    
    <TestClass()> Public Class HelpersXlTest
    
        Private app As New Application
        Private wkbk As Workbook
    
        <TestMethod()> Public Sub PiaNames()
            Dim wks As Worksheet
            wkbk = app.Workbooks.Add
            wks = wkbk.Worksheets("Sheet1")
            wkbk.Names.Add(Name:="rngWkbk", RefersTo:=wks.Range("a1"))
            Assert.AreEqual(wkbk.Names.Count, 1)
            Assert.AreEqual(wkbk.Names(0).Name, "rngWkbk")
            ' this line throws exception: "System.InvalidCastException: 'Conversion from string "rngWkbk" to type 'Integer' is not valid.' FormatException: Input string was not in a correct format."
            'Assert.AreEqual(wkbk.Names("rngWkbk"), "rngWkbk")
            wks.Names.Add(Name:="rngWks", RefersTo:=wks.Range("b2"))
            Assert.AreEqual(wks.Names.Count, 1)
            Assert.AreEqual(wks.Names(0).Name, "Sheet1!rngWks")
            ' these two lines throw exception like: "System.InvalidCastException: 'Conversion from string "Sheet1!rngWks" to type 'Integer' is not valid.' FormatException: Input string was not in a correct format."
            'Assert.AreEqual(wks.Names("Sheet1!rngWks"), "Sheet1!rngWks")
            'Assert.AreEqual(wks.Names("rngWks"), "rngWks")
            ' this line doesn't work:
            'Assert.AreEqual(wks.Names(wks.Names.Count).Name, "Sheet1!rngWks")
            ' this line does:
            Assert.AreEqual(wks.Names(wks.Names.Count - 1).Name, "Sheet1!rngWks")
        End Sub
    
    End Class

    All the un-commented assertions prove true (i.e., the test method passes). The four assertions that are commented out fail. The first three for reasons still unknown, but it sure looks to me like Names("definedName") in the Excel PIA is broken vs. the docs and vs. VBA behavior. The fourth commented out case fails because in the PIA, Names() is 0-based, not 1-based, as demonstrated in the line that does work. Go Figure.

    Such Joy.




    • Edited by Dick Watson Wednesday, November 28, 2018 4:04 PM clarify that the exception text is not identical in second and third exception case
    Wednesday, November 28, 2018 3:58 PM
  • I should add: the Names(1) (VBA COM) vs. Names(0) (PIA) thing tripped me up for a while.
    Wednesday, November 28, 2018 4:06 PM
  • Hi Dick,

    I don't have a much experience with Visual studio so setting up a test project takes too much time for me.

    However there are some things to note.

    For a range name rngWks local to a worksheet Sheet1, the correct syntax to get that name object is:

    Worksheets("Sheet1").Names("rngWks")

    or:

    ActiveWorkbook.Names("Sheet1!rngWks")

    The default property of a name is its refersto string, not its name. So this:

    MsgBox(Worksheets("Sheet1").Names("rngWks"))

    returns a message box with the string: "=Sheet1!$B$2"

    If 

    Worksheets("Sheet1").Names("rngWks")

    fails, perhaps this does work:

    Worksheets("Sheet1").Names(, "rngWks")

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Thursday, November 29, 2018 10:18 AM
  • There's nothing magical about a Unit Test Project. I'd never used VS before last week. and got Unit Test working in just a few minutes with some Googling.

    If you think VBA, you can read

    Assert.AreEqual(wks.Names(wks.Names.Count - 1).Name, "Sheet1!rngWks")

    as

    Debug.Assert wks.Names(wks.Names.Count - 1).Name = "Sheet1!rngWks"

    As to your other comments, Both Worksheets("Sheet1") and ActiveSheet return a Worksheet object, so

    Worksheets("Sheet1").Names("rngWks")
    ActiveSheet.Names("rngWks")
    wks.Names("rngWks")

    are all equivalent lines if ActiveSheet is "Sheet1" and wks has been assigned to Worksheets("Sheet1"). This is as true in VBA as in VS (if .Names("definedName") worked in VS).

    Your comment about the default property correctly points out another problem with my failing lines. If the .Names("definedName") calls returned the Name object instead of causing an exception, I would still have failed those three tests. I did correct the code in the test; it still fails before it gets to testing the Assert.AreEqual part.

    The comma before the name literal won't even compile in VB because Names() wants one argument and the initial comma compiles as an omitted first argument and a second argument; I suspect it won't compile in VBA, for the same reason, but didn't test.

    For completeness/correctness, here's the revised testcase:

    Imports Microsoft.Office.Interop.Excel
    
    <TestClass()> Public Class ExcelPIAnames
    
        Private app As New Application
        Private wkbk As Workbook
    
        <TestMethod()> Public Sub PiaNames()
            Dim wks As Worksheet
            wkbk = app.Workbooks.Add
            wks = wkbk.Worksheets("Sheet1")
            wkbk.Names.Add(Name:="rngWkbk", RefersTo:=wks.Range("a1"))
            Assert.AreEqual(wkbk.Names.Count, 1)
            Assert.AreEqual(wkbk.Names(0).Name, "rngWkbk")
    
            ' VB docs at https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.names?view=excel-pia say:
            ' "... Use Names(index), where index is the name index number or defined name, to return a single Name object. ..."
            ' Names("definedName") works in VBA.
            ' in VB, using the PIA, it does not work:
    
            ' this line throws exception: "System.InvalidCastException: 'Conversion from string "rngWkbk" to type 'Integer' is not valid.' FormatException: Input string was not in a correct format."
            'Assert.AreEqual(wkbk.Names("rngWkbk").Name, "rngWkbk")
    
            wks.Names.Add(Name:="rngWks", RefersTo:=wks.Range("b2"))
            Assert.AreEqual(wks.Names.Count, 1)
            Assert.AreEqual(wks.Names(0).Name, "Sheet1!rngWks")
    
            ' these two lines throw exceptions like: "System.InvalidCastException: 'Conversion from string "Sheet1!rngWks" to type 'Integer' is not valid.' FormatException: Input string was not in a correct format."
            'Assert.AreEqual(wks.Names("Sheet1!rngWks").Name, "Sheet1!rngWks")
            'Assert.AreEqual(wks.Names("rngWks").Name, "rngWks")
    
        End Sub
    
        <TestCleanup> Public Sub Cleanup()
            wkbk.Close(False)
            app.Quit()
        End Sub
    
    End Class



    • Edited by Dick Watson Thursday, November 29, 2018 5:03 PM
    Thursday, November 29, 2018 3:47 PM
  • Names(,"TheName") does compile in VBA, The second argument is the NameLocal property so you can address built-in names by their name as shown in the UI, rather than using the US syntax of the name. This is for Names like Sheet1!Print_Area which -in Dutch- is shown as Sheet1!Afdrukbereik but is still known as Sheet1!Print_Area inside the names collection.

    Names you define yourself have identicel Name and NameLocal property and hence can be addressed using both Names("TheName") and Names(, "TheName"). In VBA that is.

    Anyways, try as I might, I can't get VB to understand Wkbk.Names("TheName") in any variation, including Wkbk.Names.Item("TheName"), so I think you're stuck with traversing them.

    There is one lucky thing that might help to speed up your name finding: Names are indexed in an alphabetically sorted list. SO code like this should speed up the finding of a name by its name (this is VBA of course):

    Function FindName(Wb As Workbook, sName As String) As Name
        Dim lNm As Long
        Dim lFirst As Long
        Dim lLast As Long
        Dim Nm As Name
        sName = LCase(sName)
        lNm = 1
        lFirst = 1
        lLast = Wb.Names.Count
        Do
            lNm = lFirst + (lLast - lFirst) \ 2
            Set Nm = Wb.Names(lNm)
            If Nm.Name < sName Then
                lFirst = lNm
            Else
                lLast = lNm
            End If
        Loop Until LCase(Nm.Name) = sName Or lFirst = lLast
        If LCase(Nm.Name) = sName Then Set FindName = Nm
    End Function

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Thursday, November 29, 2018 5:03 PM
  • Live and learn. That second argument to Names() is not defined in the VBA object model docs that I can find. But it makes sense they might have to deal with this as a localization issue. Localization makes so many things more complicated...

    Thanks for the tip on seeking in the Names() faster. I'm not sure that many of the cases I have have enough names that it matters, but about the first thing I learned about VB + Excel is that a call to Excel is VERY EXPENSIVE in performance terms compared to the same call in VBA. So your tip may yet come in handy.

    Thanks for also testing Names("definedName") in VB. I was pretty sure it wasn't just me... I put in a FeedbackHub item against Excel. I may also create an item against the PIA Names interface page.


    • Edited by Dick Watson Thursday, November 29, 2018 5:17 PM
    Thursday, November 29, 2018 5:17 PM