MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formatting output of user defined function

Posted by Jeremy on December 10, 2001 10:34 PM

I hope someone knows the answer to this.

The problem:
I have two numbers. I want to calculate the difference between these two numbers, and the percentage change between these two numbers.

I then want to display these two results in one cell as a main number and a superscript number.

The main number represents the difference between the two numbers and the superscript represents the percentage change.

Any ideas??

I tried converting my results to STRINGS and using .character to format the text, but without success.

Thanks in advance

Posted by Tom Urtis on December 10, 2001 11:33 PM

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

Posted by Damon Ostrander on December 10, 2001 11:50 PM

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:


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.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
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.


Posted by Juan Pablo G. on December 11, 2001 6:04 AM

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.

Posted by Bill Walker on December 11, 2001 11:59 AM

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.

Posted by Juan Pablo G. on December 11, 2001 12:04 PM

Re: How about the TEXT function?

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

Juan Pablo G.