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]

Sorry I wanted to edit my question but I don't seem to find how to do it but I wanted to highlight the fact that my Excel is a Microsoft 365 version of Excel and it is the 64-bit version. I specify this because I've found posts on the internet of people declaring that somehow the M365 version introduces further limitations compared to other or older versions of Excel.

 

[RoryA]

A UDF called from a cell is restricted in what it can do (that is not a version issue) and pretty much anything other than returning a value to the cell is out. It should be possible to use Evaluate as a hack to do what you want, though I don't really see why you can't just format the cells yourself.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Setting NumberFormat for a cell via a VBA function
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[elisag]

Hello @RoryA,

A UDF called from a cell is restricted in what it can do (that is not a version issue) and pretty much anything other than returning a value to the cell is out. It should be possible to use Evaluate as a hack to do what you want, though I don't really see why you can't just format the cells yourself.

@RoryA as said, setting the format when a percent character is present in the provided format string does achieve the result so somehow things are a bit more complex than the fact that you are not allowed to set anything other than the value of the calling cell. As for using the evaluate function as a hack, as said it is not working for me.

Here is the code to prove it. The following code sets the value and tries to set the NumberFormat of the cell on the right of the caller. The value is correctly set and the msgbox is displayed to confirm that execution reaches the end of the sub with no errors.

Function SetFormatFunction()

    Dim str As String
    str = "SetFormatSub(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & 1.11111111 & ")"

    Evaluate str
    
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;"
    cell.Value2 = val
    MsgBox ("got here")
    
End Sub

 

[elisag]

One last thing, an excel macro is able to successfully set the NumberFormat property, is there any difference in the permission set for functions/subs vs macros to explain these different behaviors?

 

[RoryA]

Macros are subroutines.

Functions called from cells have strict limitations, as already mentioned. Using Evaluate in this way is a basically an undocumented hack and does appear to have limitations too (I can verify that it doesn't set the number format).

So, why do you want to do this?

 

[elisag]

Well for the most obvious reason, because in addition to pushing data into a sheet I'd like to format it appropriately for the data being written saving the end user from having to do it manually but even more, saving the end user from doing it over and over again. It seems to me complete nonsense to allow one thing (setting data) and not allow something else that is closely related to the first (formatting it appropriately) even only talking about the caller cell which could really be enough.

 

[RoryA]

It seems to me complete nonsense to allow one thing (setting data)

Technically, it doesn't allow that either. All a UDF should do is return a value. Excel then assigns that value to the cell, assuming it is the final result of the cell formula; the UDF doesn't perform the assignment.

 

[Joe4]

UDF stand for User Defined Function.
It allows you to create your own functions, which behave like native Excel functions.
The native Excel functions you put in cells only return values, they do not affect formatting.
So you should expect UDFs to behave similarly, as that is what they were designed to do.

If you want to affect cell formatting, you would use a Sub Procedure.