formatting output of user defined function

See if this formula helps you. It assumes you have "old" data in A2, and "new" data in B2, so you can calculate their difference and percent change, by entering the following formula in C2:

=("Difference is "&B2-A2)&("; Pct change is "&TEXT((B2-A2)/A2,"#,##0.00%"))

Then you can copy down as needed.

Any help?

Tom Urtis

Hi Jeremy,

I answered a question similar to this recently, and hope that you find the problem similar enough to yours for the code for that solution to be helpful.

First of all, and you are probably aware of this, a function cannot format its own cell. So this must be done with a Sub procedure (macro). Therefore, what I suggest is that you design your function to return the two values as text separated by a separator character. For example:

12.72^45.4

where the 12.72 represents the difference and the 45.4 represents the percentage change. Then write a macro that looks at the text in a cell, removes the carat, and superscripts the text that follows the carat. Probably the easiest way to do this is to design the macro, as I did the one in the example below, to process all the selected cells. If a cell in the selected range does not contain a carat it is ignored.

You should also be aware that in formatting the results of the function, the function itself must be eliminated from the cell. Thus if these specially formatted values on the sheet must be recalculated as input data changes, the approach I have just outlined is not viable. In this case I would implement the calculations entirely in macro form using a worksheet change or calculation event to trigger recalculation AND reformatting of the cells containing the specially formatted values.

Okay, here is the code I mentioned above:

Sub DisplayScientific()

' Displays all cells in the selected range that have numerical values
' in exponential format as 3.5E+05 and changes these to display as
' 3.5 x 10^5 (where the ^5 is a superscript 5).

' Warning: This will wipe out any formulas in the selected cells,
' since formula results cannot be superscripted. Make sure you no
' longer need the values to be computed before using this routine.

Dim InExp As String
Dim ToExp As String
Dim ExpSt As String 'exponent string w/o leading zeroes
Dim nChExp As Integer
Dim Ech As Integer
Dim Cel As Range

Selection.Copy
Selection.PasteSpecial xlValues

For Each Cel In Selection

If IsNumeric(Cel.Value) Then

If Cel.NumberFormat = "General" Then Cel.NumberFormat = "0.00E+00"
InExp = UCase(Format(Cel.Value, Cel.NumberFormat))

Ech = InStr(1, InExp, "E")

If Ech = 0 Then
' no exponent character, so just leave value as is
Else
ExpSt = Right(InExp, Len(InExp) - Ech)
ExpSt = Format(ExpSt, "##0")
ToExp = Left(InExp, Ech - 2) & " x 10" & ExpSt
Cel.Formula = ""
Cel.Value = ToExp
nChExp = Len(CStr(ExpSt))
Cel.Characters(Len(ToExp) - nChExp + 1, nChExp).Font.Superscript = True
End If

End If

Next Cel
Application.CutCopyMode = False

End Sub

I hope this helps. Happy computing.

Damon

Just one more point...

You can't format a cell using a UDF just as you can't format a cell using a built in function. Suppose that whenever you use SUM you would have the result showing with 2 fixed decimals, and there was nothing you could do about that... it's the same for UDF's. The user has the choice of formatting the result in whatever fashion he wants (Or, as Damon points out, with a macro).

Juan Pablo G.

How about the TEXT function?

Juan Pablo G. said that a function cannot set the format of a cell. The TEXT function will change the format, though. For example, the formula

=TEXT(.3,"$0.00") will result in $0.30.

By placing a function to generate some content other than the .3 in the example above, shouldn't you be able to format the output on the fly?

=TEXT(1+1,"$0.00") will result in $2.00

One caveat, you might need to convert back to a value if you wish to use the results in a calculation.

Re: How about the TEXT function?

Because it returns a String... and who decides the format ? the User, not the programmer...

Juan Pablo G.