I’m trying to set the data type for data in a cell (in the case of the function I wrote for the cell calling the function). Below is my code and it does not work but I’ve seen that for example setting NumberFormat via a macro (so a sub, not a function does work although in that case I hardcoded the cell address in the macro). I use a sub to try set the number format exactly because I saw this work via a macro. I would expect to see the double returned by the function display with only two decimals but number format for the caller cell remains unchanged and set to “general”.

Public Function Test() As Double

    Dim row As Integer
    row = Application.Caller.row
    
    Dim column As Integer
    column = Application.Caller.column
    
    SetFormat Cells(row, column)
    
    Test = 1.11111

End Function


Public Sub SetFormat(range As range)

    MsgBox (range.NumberFormat)
    range.NumberFormat = "0.00"
    MsgBox (range.NumberFormat)
    
End Sub