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