Setting NumberFormat for a cell via a VBA function

[elisag]

I'd like to be able to set the display format of data for a cell (for example the caller cell) via a VBA function and I've found posts around the internet that seem to suggest it to be feasible and indeed it is feasible via a macro on a hardcoded cell (for example cell A1) but all my attempts to do it via a function (or a sub that the function calls) have failed. In my code I'm not getting any error, but apparently my setting of NumberFormat seems to be ignored. The fact that setting NumberFormat is ignored is not completely true because when in the string format I provide is present the percent character such as in "0.00%" the new NumberFormat is indeed applied but this is the only case where the thing works. I have also tried using the trick/workaround of invoking the evaluate function to call a sub that sets the NumberFormat for the desidered cell but it does not allow achieving the intended result.

Below is an example code I used in one of the many attempts I made.

Function SetFormatFunction()

    SetFormatSub Application.Caller, 1.1111
    
End Function

Private Sub SetFormatSub(cell As Range, val As Double)
    
    cell.NumberFormat = "0.0;[Red]-0.0;0;na;"
    cell.Style.NumberFormat = "0.0;[Red]-0.0;0;na;"
    
End Sub

 

[elisag]

Can you name any native Excel function that does that?
That formats the cell that you put it in?

I think we both know that the answer is "No".
So why would you expect your own custom Function to do that?
To have some property that the native Excel functions do not?

@Joe4, I don't have a holistic knowledge of Excel to be able to provide an answer to your question, but @Joe4 and @RoryA thank you both for challenging me and providing inputs to move my attempts further on. I was able to find a way to make the thing work.

 

[Joe4]

@Joe4, I don't have a holistic knowledge of Excel to be able to provide an answer to your question, but @Joe4 and @RoryA thank you both for challenging me and providing inputs to move my attempts further on. I was able to find a way to make the thing work.

You are welcome.
Yes, I got the impression that much of it might have just been an unfamiliarity with the different forms of Excel VBA code and what they are used for.