Ask Your Question
0

Macro: how to get a cell value set by a formula [closed]

asked 2016-12-10 23:00:29 +0200

mildlyodd gravatar image

Hi I've written the following function, it just reads in a cell value and puts a wrapper around the detailed steps. It seems to work ok except when the cell of interest has been set by a formula. What I want is for the result of the Macro function to be set to the result of the spreadsheet formula, instead the macro function is set to the spreadsheet formula code. QUESTION - what change should I make to the function?

Function get_cell( sheetname_str, col,row)

'sheetname_str e.g "Sheet1", 'col and row are integers 'Return value can be VALUE or TEXT

Dim localdoc   as object
Dim localsheets as object
Dim my_sheet as object
Dim my_cell as object
Dim cell_value 

localdoc = ThisComponent
localsheets = localdoc.Sheets 
my_sheet = localsheets.getByName(sheetname_str)
my_cell = my_sheet.getCellByPosition(col,row)

Select Case my_cell.Type
    Case com.sun.star.table.CellContentType.VALUE
        cell_value = my_cell.Value
    Case com.sun.star.table.CellContentType.TEXT
        cell_value = my_cell.String
    Case com.sun.star.table.CellContentType.FORMULA
        cell_value = my_cell.FormulaLocal
End Select
get_cell = cell_value

End Function

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by mildlyodd
close date 2016-12-12 17:10:08.278897

1 Answer

Sort by » oldest newest most voted
0

answered 2016-12-10 23:43:01 +0200

Mike Kaganski gravatar image

updated 2016-12-11 00:18:08 +0200

Use my_cell.FormulaResultType instead of my_cell.Type

Function get_cell( sheetname_str, col,row)
'sheetname_str e.g "Sheet1", 'col and row are integers 'Return value can be VALUE or TEXT

Dim my_sheet as object
Dim my_cell as object

my_sheet = ThisComponent.Sheets.getByName(sheetname_str)
my_cell = my_sheet.getCellByPosition(col,row)

If (my_cell.getError <> 0) Then
    get_cell = my_cell.String & " (Error " & my_cell.getError & ")"
Else
    Select Case my_cell.FormulaResultType
        Case com.sun.star.table.CellContentType.VALUE
            get_cell = my_cell.Value
        Case Else
            get_cell = my_cell.String
    End Select
End If
End Function
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2016-12-10 23:00:29 +0200

Seen: 195 times

Last updated: Dec 11 '16