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

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 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

Sort by » oldest newest most voted

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

more