Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
On Tuesday, October 2, 2012 5:17:28 PM UTC-4, 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, 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
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 HTH Mick. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a VBA Function return a value in percent format
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Howw can I make a function return a date in date format | Excel Programming | |||
Make VBA wait for return from external function | Excel Programming | |||
How do I make the true return a drop down list in the IF function? | Excel Worksheet Functions | |||
trying to make a formula for percent if greater than a value | Excel Discussion (Misc queries) | |||
Percent Format without Percent Sign | Excel Programming |