none
Excel custom property name has invalid return type? RRS feed

  • Question

  • I have added to Excel worksheets in a VSTO (VB.Net) solution customproperties to keep track of things like codenames and sheet types. Each of those properties has a name and a value. 

    Here's a line of code from my solution:

    Dim oPropName as Object = TargetSheet.CustomProperties(1).Value

    Result? "Invalid Cast Exception. Return argument has an invalid type."

    The value is "12345678#123" and even IntelliSense displays it correctly. But it says now that it is impossible to make that an object.

    How do I fix this?

    Monday, July 28, 2014 2:21 PM

Answers

  • I actually found a solution. It is not pretty, it is definitely how it should be, but it works. The problem is that the code is too fast, so this helps:

    Public Shared Function GetNumberOfCustomProperties(ByRef shtTarget As Excel.WorksheetAs Integer

            Dim iMaxTries As Integer = 100000

            For iTry = 1 To 100000

                Try

    If shtTarget.CustomProperties is nothing then return 0

                    Dim oNumberOfCustomProperties As Object = TryCast(shtTarget.CustomProperties.Count, Object)

                    Dim iNumberOfCustomProperties As Integer = CInt(oNumberOfCustomProperties)

                    Return iNumberOfCustomProperties

                Catch

                    Debug.Print("Failed to get the count, have tried " & iTry & " times")

                End Try

            Next iTry

            Debug.Print("Failed to get the count, have tried " & iMaxTries & " times. Out of tries")

            Return Nothing

        End Function

     

    Feel free to improve it.

    Tuesday, July 29, 2014 1:12 PM

All replies

  • Hello,

    I'd recommend starting from releasing underlying COM objects.  Use System.Runtime.InteropServices.Marshal.ReleaseComObject to release an Outlook object when you have finished using it. Then set a variable to Nothing in Visual Basic (null in C#) to release the reference to the object.

    What property or method call exactly throws the exception?

    I'd recommend declaring each property or method call on a separate line. Thus, you will be able to identify the problematic call.

    Monday, July 28, 2014 3:16 PM
  • Hi,

    I can't reproduce this issue.

    Here is what I have tried in :

    Private Sub CheckCustomProperties()
            Dim wksSheet1 As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
    
            ' Add metadata to worksheet. 
            wksSheet1.CustomProperties.Add(Name:="Market", Value:="Nasdaq")
    
            Dim oPropName As Object = wksSheet1.CustomProperties(1).Value
        End Sub

    Is there anything I missed?

    Suppose the issue is more related to "wksSheet1.CustomProperties(1).Value", if you debug the code, what does this value look like?

    In addition, I would suggest you using TryCast Operator (Visual Basic) before converting an object to another.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 29, 2014 8:53 AM
    Moderator
  •                 

    Here's the way I use to set the codename. It has been so far always successful.

     Public Shared Sub SetCodeNameOfSheet(ByRef shtTarget As Excel.Worksheet, ByVal strCodeName As String)
            'If already exists, change
            For Each prop As Excel.CustomProperty In shtTarget.CustomProperties
                If prop.Name = SHEET_CODE_NAME_FIELD Then
                    prop.Value = strCodeName
                    Return
                End If
            Next prop

            'If doesn't exist, add
            shtTarget.CustomProperties.Add(SHEET_CODE_NAME_FIELD, strCodeName)
        End Sub

    This is the way I use to receive the number of Custom Properties of a worksheet. That Dim oNum... crashes occasionally.

        Public Shared Function GetNumberOfCustomProperties(ByRef shtTarget As Excel.Worksheet) As Integer
            Dim oNumberOfCustomProperties As Object = TryCast(shtTarget.CustomProperties.Count, Object)
            Dim iNumberOfCustomProperties As Integer = CInt(oNumberOfCustomProperties)
            Return iNumberOfCustomProperties
        End Function

    This is the way I use to get the Custom Property name. The Dim oProp... crashes occasionally

        Public Shared Function GetCustomPropertyName(ByRef shtTarget As Excel.Worksheet, ByVal iNumberOfCustomProperty As Integer) As String
            Dim oPropName As Object = TryCast(shtTarget.CustomProperties(iNumberOfCustomProperty).Name, Object)
            Dim strPropName As String = oPropName.ToString
            Return strPropName
        End Function

    This is the way I use to get the Custom Property value. The Dim oProp... crashes occasionally

        Public Shared Function GetCustomPropertyValue(ByRef shtTarget As Excel.Worksheet, ByVal iNumberOfCustomProperty As Integer) As String
            Dim oPropValue As Object = TryCast(shtTarget.CustomProperties(iNumberOfCustomProperty).Value, Object)
            Dim strPropValue As String = oPropValue.ToString
            Return strPropValue
        End Function

    This is the way I use to get the code name of the worksheet. No direct calls to CustomProperties.

        Public Shared Function GetCodeNameOfSheet(ByRef shtTarget As Excel.Worksheet) As String
            Dim iNumberOfCustomProperties As Integer = GetNumberOfCustomProperties(shtTarget)
            For iCustomPropertyIndex As Integer = 1 To iNumberOfCustomProperties
                Dim strPropName As String = GetCustomPropertyName(shtTarget, iCustomPropertyIndex)
                If strPropName.Equals(SHEET_CODE_NAME_FIELD) Then
                    Dim strPropValue As String = GetCustomPropertyValue(shtTarget, iCustomPropertyIndex)
                    Return strPropValue
                End If
            Next iCustomPropertyIndex

            'Nothing found
            Return String.Empty
        End Function

    Above are the what I use now. The weird thing still is that IntelliSense has absolutely no problems with anything here. For example, when the GetNumberOfCustomProperties crashes on that line         
    Dim oNumberOfCustomProperties As Object = TryCast(shtTarget.CustomProperties.Count, Object)
    the IntelliSense shows _instantly_ the correct number. Same with that GetCustomPropertyValue. Still Visual Studio says:

    System.InvalidCastException occurred
      Message=Return argument has an invalid type.
      Source=mscorlib
      StackTrace:
           at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)
           at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)
           at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
           at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
           at Microsoft.Office.Interop.Excel.CustomProperties.get__Default(Object Index)

    In order to give more info about the error, I'd like to know what would help from the "View Detail..."

    I added the set nothing that Eugene Astafiev proposed, but I got a bit lost when the talk turned to Outlook. This has nothing to do with Outlook. 

    Oh and sorry that those are not in the code blocks. I have absolutely no idea how to 1) Preserve the lining on code block after a copy-paste and 2) How to delete that empty code box from the beginning of this post.

    edit. Also immediate window is completely fine with this, shows correct answer instantly.
    • Edited by KilpAr Tuesday, July 29, 2014 12:56 PM
    Tuesday, July 29, 2014 12:02 PM
  • I actually found a solution. It is not pretty, it is definitely how it should be, but it works. The problem is that the code is too fast, so this helps:

    Public Shared Function GetNumberOfCustomProperties(ByRef shtTarget As Excel.WorksheetAs Integer

            Dim iMaxTries As Integer = 100000

            For iTry = 1 To 100000

                Try

    If shtTarget.CustomProperties is nothing then return 0

                    Dim oNumberOfCustomProperties As Object = TryCast(shtTarget.CustomProperties.Count, Object)

                    Dim iNumberOfCustomProperties As Integer = CInt(oNumberOfCustomProperties)

                    Return iNumberOfCustomProperties

                Catch

                    Debug.Print("Failed to get the count, have tried " & iTry & " times")

                End Try

            Next iTry

            Debug.Print("Failed to get the count, have tried " & iMaxTries & " times. Out of tries")

            Return Nothing

        End Function

     

    Feel free to improve it.

    Tuesday, July 29, 2014 1:12 PM