Solved: return formatted number from function?

[sandbagger]

I just want to format TEST as a percentage in the following simple example. I cannot seem to get "NumberFormat" property to work. I'm new to excel so any help is appreciated.

Function TEST(x As Range) As Double

Dim cnt As Integer
Dim tmp As Double
Dim i As Integer

cnt = x.Cells.Count
tmp = 0

For i = 1 To cnt
tmp = tmp + x.Cells(i)
Next i

TEST = tmp


End Function

[mumphis]

How about format(variable, "0.00")?

[sandbagger]

Ok, I added the following as the last line in the function.

TEST = Format(TEST, "0.00")

and it did not force the result into percent format.

[mikerickson]

Do you want the function to return a number or a string.?

Percentages are numbers. There is no difference between a number and a percentage, other than the format in which it is shown. The format of the destination cell can be set to show the number as in "0.00%" format. But functions can't set cell formats. (Not when called from a worksheet.)

If you want your function to return a string, changing the function type declaration and using

TEST=Format(tmp,"0.00%")

should do it.

[Bob Phillips]

Why not just

=SUM(range)

in the Excel sheet and format it as required?

[sandbagger]

[vba]Function TEST(x As Range) As String

Dim cnt As Integer
Dim tmp As Double
Dim i As Integer

cnt = x.Cells.Count
tmp = 0

For i = 1 To cnt
tmp = tmp + x.Cells(i)
Next i


TEST = Format(tmp, "0.00%")

End Function[/vba]
yes, the above worked. Thanks guys.

I'm not quite sure the consequences to storing the result as string however. When further manipulating the result, Excel seems to automatically cast the string to a number.

[Bob Phillips]

It will.

But can you tell me why you want such a dumb function (dumb in that it does nothing smart), when you can do it in Excel? I just don't get this approach.

[sandbagger]

the function i am writing is much more complex than this. i just wanted to provide a simple example of what i was after.

[XLGibbs]

It still makes no sense when you can format the cell itself for whatever display you require.

Excel stores all numerical values as numbers. The cell isn't "recasting" the string as a number, but if it is a number being placed in the cell...it will read that as a number. The only way to set a cell's value and be sure it is a string is precede the result with a single carot character.. ' otherwise, a number will be treated as a number.

[sandbagger]

sounds like you guys are saying this is not best practice. if excel is always going to treat the result as a number (not a string) anyway then there seems to be absolutely no downside to doing this. why would i want to always have to click format cell and choose percentage each time i get a result from this function? if function's purpose is to return a percentage why not force it do so?

[XLGibbs]

you don't always have to click the cell to format it...you can format the cell's display in code, or preset the entire column's display format.

further, the result of the function is a number, not a percentage. 10/100 = .10, not 10%. 10% is merely a presentation of the actual number. As stated, you can format the presentation anyway you wish...

[sandbagger]

well how do i format a cell's display in code? of course the formatting needs to be dynamic, not something like Cell C3 is always displayed as a percentage. all my searches turned me to the numberformat property which i stated in the first post did not work.

[XLGibbs]

numberformat property should have worked if you formatted as "0.00%".

If you record a macro, and format a number using the menu items and FOrmat>Cells>percentage...you will see exactly (although excessively so) the code which assigns the number format.


I mean, the numberformat property is the property that stores the number format. It's not like excel is tricking you or anything.

Range("A3").NumberFormat = "0.00%" would do it. It does for me, every time.

It worked for you in post #6 of this thread. The prior examples omitted the %, so it would have been just a 2 decimal number.

[Bob Phillips]

Numberformat will not work from a function callled from a worksheet, you can't change a worksheet or a cell from within a UDF.