Programming the output format within a UDF

I made a UDF in Excel 2010 which is programmed to give an output in acordance to the value of the transfered parameters, for the best display I need to be able to modify the output format in correspondence with the results, the question is:

Is it possible to include, as a part of the UDF, a code to make a conditional format to display the result in an appropriate format? if so, how can I program it?.

To illustrate the question take this: functionName(A,B,C,D,E)

where A,B,C,D are the parameters for the calculus and E is the format parameter, for example:

if E = 1 filled red, integer
if E = 2 filled green, two decimal
if E = 3 filled blue, text

Format within UDF

Thanks for all your help, I see that it is not possible to set a format within a UDF because MS put reasonable restrictions for the Functions, however, would be an advantage if these constraints could be overcome in case of UDF

Yours

Jorge

RE: Programming the output format within a UDF

Hi,

To be more clear: You cannot do this with user-defined function (UDF). For more information look at the following article:

 Description of limitations of custom functions in Excel

 

Best regards.

Nick's picture

don't do it in code, do it in

don't do it in code, do it in Excel's conditional formatting:

 25. Excel Tips - Conditional formatting

 

 

Vishesh's picture

Here is the code that you can

Here is the code that you can modify as per your requirement...

Dim rng As Range 'the range to be worked upon

Dim E As Integer

Select Case E
Case 1
rng.NumberFormat = "#"
rng.Interior.Color = 9737946
Case 2
rng.NumberFormat = "#.00"
rng.Interior.Color = 10213316
Case 3
rng.NumberFormat = "@"
rng.Interior.Color = 14136213
End Select