Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 16th, 2005, 11:11 AM
Registered User
Join Date: Feb 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Number format in User Defined Functions

I'm writing a basic UDF that will calculate a compound annual growth rate. As part of the function, I want the answer to be expressed as a percentage, and not a decimal.

I believe the code I want to do this is: NumberFormat = "0.00%"
However, I have been unable to apply this code to the UDF.

If it helps, the relevant part of the code where this would fit in is:

Function CAGR(StartValue, EndValue, NumberOfYears)
CAGR = (((EndValue / StartValue) ^ (1 / NumberOfYears)) - 1)
End Function

Any thoughts on how to incorporate in the number formating would be greatly appreciated.


Old February 17th, 2005, 05:23 AM
Authorized User
Join Date: Jan 2005
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts

Please do as follows:
Function CAGR(StartValue, EndValue, NumberOfYears)
CAGR = Format((((EndValue / StartValue) ^ (1 / NumberOfYears)) - 1), "0.00%")
End Function

Similar Threads
Thread Thread Starter Forum Replies Last Post
Indexing tables in user defined functions A17560127 SQL Server 2005 1 July 21st, 2008 03:56 AM
User-defined functions fredh Excel VBA 1 May 7th, 2007 10:28 AM
Portability of User Defined Functions (UDFs) fredh Excel VBA 0 May 3rd, 2007 06:07 PM
User-Defined Functions - How to recalculate? kmoran Excel VBA 1 October 5th, 2004 02:45 AM
user defined functions in vba re_anu Access VBA 2 March 3rd, 2004 01:59 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.