COM Exception Error When Counting Comments (XlCellType.xlCellTypeComments) in Range

[Cj]

HI Govert...

The following function compiles and works GREAT when COUNTING comments correctly in a range providing there EXIST comments in that range but if there are NO comments in the specified range then
up pops the ComException "No cells were found." error...

I have tried every preCheck I can think of to test for NOTHING exists before running the count code... and 'nothing' works... (isnothing, isDBnull, etc, etc, etc.)

I have even handled it (trap) in the Catch and it does Catch it and return the correct value of 0 to the calling Sub... but i have read it is not good code to pass return values in the catch area... and it feels wrong ?

There must be more elegant code to pre-vent the com ex ?


   Public Shared Function CountCommentsInRange(rangeName As Range) As Integer

        Dim CellCount As Integer = 0

        Try
            If IsNothing(rangeName.SpecialCells(XlCellType.xlCellTypeComments).Count) = False Then        'COM EXCEPTION occurs on this line if there are NO comments in the range... ditto next line if no pre IF test              
                CellCount = rangeName.SpecialCells(XlCellType.xlCellTypeComments).Count                                    
                CountCommentsInRange = CellCount
            End If

        Catch ex As System.Runtime.InteropServices.COMException
            If ex.Message = "No cells were found." Then
                CountCommentsInRange = 0
            End If

        End Try

  End Function

[Cj]

SOLVED... Getting An Error When Testing for Special Cell Value Formats if the range you are testing does not have any of them in it... .

There is no workaround to this... If within a range if your code checks for a Special Cell value such as XlCellType.xlCellTypeComments and the range does NOT have any in it you will get a System.Runtime.

InteropServices.COMException that you must handle your self...

Add In Express Forum Help Had Same Answer...  Catch The Exception...   https://www.add-in-express.com/forum/read.php?FID=1&TID=3560

StackOverflow Had Same Answer...  Catch The Exception... Even Custom Write Your Own Exception Example There...  http://stackoverflow.com/questions/3022157/handling-no-cells-were-found-error-in-excel/18278592#18278592

After a number of hours trying to find a solution for this and then finally finding the AddIn Express help and the StackOverflow help i came up with this... which works smoothly...

Public Shared Function CountCommentsInRange(rangeName As Range) As Integer
        Dim CellCount As Integer = 0
        Try

            CellCount = rangeName.SpecialCells(XlCellType.xlCellTypeComments).Count
            CountCommentsInRange = CellCount

            Exit Function

        Catch ex As System.Runtime.InteropServices.COMException
            If ex.Message = "No cells were found." Then

                Err.Clear()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function

this handles the error exception on nothing counts and properly returns the correct integer amounts to the calling code...

its a feature not a bug... :o)

CJ..

*******************************************************************************************************