ExcelBanter - View Single Post - How to make a VBA Function return a value in percent format

John Black

In article om, says...

On 3/10/2012 7:17 AM, John Black wrote:
Hi,

I've been searching google for a while on this question but I can't find anything. I wrote a
very simple function that returns the growth percentage between two numbers:

Function GP(Val1 As Double, Val2 As Double)
GP = (Val2 - Val1) / Val1
End Function

I would like the result to automatically be displayed in "%0.00" format but I cannot figure
out how to make that happen. Right now it returns a number like 0.45632 and I have to click
the cell and format it to percentage. Is this possible? Thanks.

John Black

Hi

Try

With GP
.NumberFormat = "0.00%"
End With

That looks promising, but where do I put that? I tried it in the function like this:

Public Function GP(Val1 As Double, Val2 As Double)
GP = (Val2 - Val1) / Val1

With GP
.NumberFormat = "0.00%"
End With

End Function

but that did not work. I get #VALUE when I try to use the function. And if I'm supposed to
put it somewhere else besides in the function, I don't know where?

John Black