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”.
<code>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
</code>
<code>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
</code>
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