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