How to make a VBA Function return a value in percent format

  • Thread starter Thread starter John Black
  • Start date Start date
J

John Black

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
 
John,

This should work, but it will return a string.

Function GP(Val1 As Double, Val2 As Double)
GP = Format((Val2 - Val1) / Val1, "%0.00")
End Function
 
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 thathappen. Right now it returns a number like 0.45632 and I have to click thecell and format it to percentage. Is this possible? Thanks. John Black
Click to expand...

Hi,

I'm pretty sure that what you are asking for is impossible in that VBA functions
when used as worksheet functions have no side-effects and changing cell format
would be a side effect. One idea that I tested (to some extent) and which seems to work is to add an event-handler which does the side effect when youwant automatically whenever the formula is entered:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If UCase(Target.Formula) Like "=*GP(*" Then Target.NumberFormat = "0.00%"
End Sub

If you haven't done things like this before - this should be put
in the code for the Workbook object rather than in a general code module.
The On Error Resume Next was needed since if you e.g. highlight a range of cells
and hit delete then it triggers the Change event but with no well-defined
target.formula

This approach has a certain overhead of course. You can always remove the event-handler at a later time once you are happy with your workbook.

hth

-John
 
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
Click to expand...
Hi

Try

With GP
..NumberFormat = "0.00%"
End With

HTH
Mick.
 
Hi

Try

With GP
.NumberFormat = "0.00%"
End With
Click to expand...

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
 
John,

This should work, but it will return a string.

Function GP(Val1 As Double, Val2 As Double)
GP = Format((Val2 - Val1) / Val1, "%0.00")
End Function
Click to expand...

Having it return a string may be a problem in some cases. But for grins, I tried this and it
output this:

%0.10

:-)

John Black
 
I would like the result to automatically be displayed in "%0.00" format but I cannot figure

John,

Based on the line copied above, I assumed that for whatever reason you wanted the "%" sign in front. If you change the "%0.00" in the function to "0.00%", it will return:

0.10%

As an added bonus, when I ran this on a test sheet Excel recognized the result as a decimal.

Ben
 
John,

Based on the line copied above, I assumed that for whatever reason you wanted the "%" sign in front. If you change the "%0.00" in the function to "0.00%", it will return:

0.10%

As an added bonus, when I ran this on a test sheet Excel recognized the result as a decimal.
Click to expand...

Thanks Ben!!

Yes, you actually just copied *my* error. I meant to say "0.00%" in the original post.

Anyway, as you say, your version works exactly like I wanted. If I do =GP(10,15), I get
50.00%. And I can refer to that cell in other equations and it will treat it like a number
0.5, and not give an error because its really technically a string.

Given that, does anyone know of any downsides of returning a string instead of a number here?

John Black
 
percentage. Is this possible? Thanks. John Black
Hi,

I'm pretty sure that what you are asking for is impossible in that VBA functions
when used as worksheet functions have no side-effects and changing cell format
would be a side effect. One idea that I tested (to some extent) and which seems to work is to add an event-handler which does the side effect when you want automatically whenever the formula is entered:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If UCase(Target.Formula) Like "=*GP(*" Then Target.NumberFormat = "0.00%"
End Sub

If you haven't done things like this before - this should be put
in the code for the Workbook object rather than in a general code module.
The On Error Resume Next was needed since if you e.g. highlight a range of cells
and hit delete then it triggers the Change event but with no well-defined
target.formula

This approach has a certain overhead of course. You can always remove the event-handler at a later time once you are happy with your workbook.

hth

-John
Click to expand...

John, Thanks for the reply. I will keep this idea in my back pocket but its probably
overkill for this application. Ben gave me a nice simple solution.

John Black
 
Back
Top