About Us

John Black

In article ,
says...

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

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Howw can I make a function return a date in date format	Jan Kronsell	Excel Programming	12	January 21st 10 04:24 PM
Make VBA wait for return from external function	Gib Bogle	Excel Programming	8	January 3rd 09 03:42 AM
How do I make the true return a drop down list in the IF function?	Brian	Excel Worksheet Functions	5	April 13th 06 09:40 PM
trying to make a formula for percent if greater than a value	cervenyc	Excel Discussion (Misc queries)	10	January 12th 06 05:45 PM
Percent Format without Percent Sign	jstrater	Excel Programming	1	April 29th 04 07:28 AM

Menu

About Us