Test for comment in cell

[Ken Warthen]

I have a routine that search for a given number in a range on worksheet. If
the given number if found I want to test for a comment associated with the
found cell. My code is returning true for any given number so something is
wrong, but I can't seem to figure it out. If anyone has experience with
testing for a comment, I'd be very appreciative. My code follows.

TIA,
Ken

Public Function fCellHasComment(lngStoreNumber As Long) As Boolean
On Error GoTo PROC_ERROR
Dim Cell As Range

With ThisWorkbook.Worksheets("Price Groups")
For Each Cell In Range("PriceGroupsStoreNumbers")
If Cell.Value = lngStoreNumber Then
If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then
fCellHasComment = False
Else
fCellHasComment = True
End If
End If
Next Cell
End With


PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modUtilities", "fCellHasComment", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
End Function

 

[Peter T]

fCellHasComment = Not Cell.Comment Is Nothing
or
Dim cm as Comment
Set cm = cell.comment
fCellHasComment = Not cm Is Nothing

but looking at your code what happens if more than one cell in the range has
the value of lngStoreNumber, and some of these cells have comments and some
don't...

Regards,
Peter T

 

[Mike H]

Ken,

Try this. I couldn't duplicate your ShowError sub because I don't know what
is does


Public Function fCellHasComment(lngStoreNumber As Long) As Boolean
On Error GoTo PROC_ERROR
Dim Cell As Range

With ThisWorkbook.Worksheets("Price Groups")
For Each Cell In Range("PriceGroupsStoreNumbers")
If Cell.Value = lngStoreNumber Then
Set mycomment = Cell.Comment
If mycomment Is Nothing Then
fCellHasComment = False
Else
fCellHasComment = True
End If

End If
Next Cell
End With
PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modUtilities", "fCellHasComment", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ryan H]

I noticed a few things that are wrong about your code.

1.) I would highly recommend not using On Error GoTo statements unless
absolutely necessary, this only causes problems when you are trying to debug
code.

2.) In my code, I test if the cell has a comment, even if the comment is a
zero length string. If it doesn't have a comment the MyComment variable is
Nothing. If it does have a comment, I then test if the comment is a non-zero
length string. If so, the function returns True.

3.) You don't have to set a function to False. The default value for a
function that returns a Boolean data type will always be False, unless set to
True.

4.) I also added a "." in front of Cells(Cell.Row, Cell.Column).Comment
which will get the correct cell from the Price Groups worksheet. Without the
"." you will be getting cells from the activesheet. Maybe in your case the
activesheet is Price Groups, but it's worth noting.

5.) Hope this helps! If so, let me know, click "YES" below.

Public Function fCellHasComment(lngStoreNumber As Long) As Boolean

Dim Cell As Range
Dim MyComment As Comment

With ThisWorkbook.Sheets("Price Groups")
For Each Cell In Range("PriceGroupsStoreNumbers")
If Cell.Value = lngStoreNumber Then
Set MyComment = .Cells(Cell.Row, Cell.Column).Comment
If Not MyComment Is Nothing Then
If MyComment.Text <> "" Then fCellHasComment = True
End If
End If
Next Cell
End With

End Function

 

[Peter T]

Couple of 'comments' !

1.) I would highly recommend not using On Error GoTo statements unless
absolutely necessary, this only causes problems when you are trying to
debug
code.

I'd recommend the exact opposite in production code (unless 110% no error
can occur). Can temporarily set a global, eg gbDebug, so that any error is
handled differently while debugging.

Set MyComment = .Cells(Cell.Row, Cell.Column).Comment

why not simply
Set MyComment = Cell.Comment

If MyComment.Text <> "" Then fCellHasComment = True

Even an empty comment still means the cell has a comment.

However, and I didn't notice before -

With ThisWorkbook.Sheets("Price Groups")
For Each Cell In Range("PriceGroupsStoreNumbers")

The "With" line is only required if the range "PriceGroupsStoreNumbers" is a
worksheet level range, in which case "Range" should be prefixed with a dot.
However, if ThisWorkbook is not the active workbook would also need the With
and a dot before Range (but not the sheet qualification unless a worksheet
level name)

Regards,
Peter T

 

[Ryan H]

I guess it's personal preference on the On Error statement. I prefer to do a
little extra coding to protect against errors rather than use it. Not to say
it never should be used, but I feel it can lead to problems.

I noticed I should have used Cell.Comment after I had posted.

I used this line: If MyComment.Text <> "" Then fCellHasComment = True,
because in Ken's orginal code he was testing if the comment had a non zero
lenght string. I just added the If...Then statement that tested if a comment
exists even if it is a zero length string.

 

[Ken Warthen]

Thanks for the help. Your suggestion worked fine.

Ken