Ask Your Question
0

comment from a cell as a value into another cell [closed]

asked 2014-11-24 19:43:31 +0100

Yan Do gravatar image

Hi,

there is a Calc spreadsheet with comments to cells. We need to sort the sheet based on the data in comments. For this we have to put comments into the nearby cells as values.

Found the example of Excel function:

Function getComment(incell) As String
On Error Resume Next  
getComment = incell.Comment.Text 
End Function

But this one returns error (no object defined)

I've started to write something on myself, but havn't found method for a cell how to get out its comment.

Solution should be very simple, any ideas? fnx )

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-06 17:39:52.850945

1 Answer

Sort by » oldest newest most voted
2

answered 2014-11-25 19:03:24 +0100

JohnSUN gravatar image

Not Comment, but Annotation. Not Text, but String. So it will be

getComment = incell.getAnnotation().getString()

It will be so if incell really is a object cell. If it's just a text string with cell address, you'll need to write more and more... Something like as

Function getComment(incell As String) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oCell As Variant
Dim oAnnotation As Variant
    On Error Resume Next 
    getComment = ""
    oSheets = ThisComponent.getSheets()
    oCellRangesByName = oSheets.getCellRangesByName(incell)
    oCell = oCellRangesByName(0).getCellByPosition(0,0)
    oAnnotation = oCell.getAnnotation()
    getComment = oAnnotation.getString()
End Function

Call it as

=GETCOMMENT("Sheet2.C4")
edit flag offensive delete link more

Comments

fnx!

I've added 2 strings in code like

sTmp = oAnnotation.getString()
getComment = IIf (sTmp = "" , "" , sTmp)

and I call it as

=GETCOMMENT(CELL("ADDRESS";A1))

than it's easy copypaste/strech

Yan Do gravatar imageYan Do ( 2014-11-26 20:59:56 +0100 )edit

Dear friend, excuse me, what do you mean in this line IIf (sTmp = "" , "" , sTmp)? If sMtp already equal "empty string" then... what?.. Set getComment "empty string"?.. Why?!!

JohnSUN gravatar imageJohnSUN ( 2014-11-27 07:24:22 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-11-24 19:43:31 +0100

Seen: 1,029 times

Last updated: Nov 25 '14