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]

At this point what I do not understand then is in what context have the code samples I see around proposed for similar needs been used considering that nothing that runs in VBA code can achieve this result (apart macros but only executed directly from the user, not any executed programmatically)? The NumberFormat function used in a macro seems to me pretty useless, why even provide it at this point?

 

[RoryA]

It's only UDFs called from a cell that have that restriction. If you use NumberFormat in other code, such as macros or event routines, it will work just fine.

 

[Joe4]

It is all about using the correct tools with the correct options. Functions and Sub Procedures have different purposes and different rules.
Just like you can use cars and boats as means of transportation, but wouldn't use a car to cross a lake.

 

[elisag]

@Joe4 I'm not aware of many other options other than ribbons (vsto and vba) or addins (xll). @RoryA when you say routines what do you mean?

 

[Joe4]

See here for an explanation on the differences between Sub Procedures and User Defined Functions.
They are both VBA, but have different properties, rules, and uses.

VBA: Sub vs Function

In this article, we will discuss the key differences between a sub vs function. Both are sets of commands that are used to perform specific tasks in VBA.

corporatefinanceinstitute.com

 

[elisag]

@Joe4 I believe I know the difference between a function and a sub and when in the context of excel someone says routine I think of subs. Macros are parameter-less subs but when calling subs (with or without parameters) from a function they inherit the context of the calling function. I tried .NET UDFs and calling VBA subs from such a UDF and the behavior is the same of calling them from a VBA UDFs. I remember that VSTO ribbons could write anywhere (any cell, any sheet) and set backgrounds, formatting, conditional formatting, ecc.....

 

[RoryA]

It's only code called from a cell that has these restrictions. If you use a macro or something like a worksheet_change or worksheet_calculate event, there are no such restrictions.

 

[elisag]

I understand, but code that populates a cell is, I believe, the ideal place to also format data in that same cell, isn't it?

 

[RoryA]

It may be but, as I said, a UDF doesn’t actually populate a cell. It just returns a value

 

[Joe4]

I understand, but code that populates a cell is, I believe, the ideal place to also format data in that same cell, isn't it?

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?