Macro: how to get a cell value set by a formula

[mildlyodd]

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

[mikekaganski]

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