Function fails. What have I done wrong ?

[AlexanderBB]

Hi,
I reverse engineered a function that sets the text color in the first line of a comment.
I'm now trying to read that color back and failing.

Function GetCommentLineOneColor(ksheet, kAddr) As Variant
Dim r As Range, l As Integer
Set r = Worksheets(ksheet).Range(kAddr)
With r
    If Not .Comment Is Nothing Then
       l = InStr(.Comment.text, vbLf)
        With .Comment
           With .Shape
               Debug.Print .Characters(1, l).Font.Color
           End With
       End With
    End If
End With
End Function

I call this from the immediate window as
? GetCommentLineOneColor("Sheet1", "J116") where there's a comment in J116.

I'm getting Run-time error '438':
Object doesn't support this property or method
on the Debug.Print line.
Any help sorting this appreciated. Thanks

 

[shg]

If you are tryng to modify a comment via a function called from a cell, it won't work. With a few arcane exceptions, a UDF can only return a value to the cell in which it appears.

 

[RoryA]

The Shape object doesn't have a Characters property. You need:

With .Shape.TextFrame

 

[AlexanderBB]

Shg, I'm trying to read it from a module. And Rory I got there just before your msg !!! then I struck a further problem
with it returning a color of Null. Managed to fix that with 'Debug.Print .Characters(1, l - 1).Font.Color"
I guess including the vblf casued that.

Many thanks, ABB

 

[RoryA]

You should also check that the Instr call doesn't return 0 before you try and use it.

 

[AlexanderBB]

Will do Rory, thank you