PDA

View Full Version : Solved: Basic VBA problem with Format



mdwagner2
07-06-2011, 09:42 AM
Hello all,

I'm new here, really just getting some experience with VBA with excel, although I have some experience with other similar languages I've used, I'm having a little trouble with this one.

I've searched the net and haven't been able to find quite what I'm looking for.

I am trying to build a simple function that can output the percent change and format as a percente in excel.



Function PercentChange(CurrentValue, PreviousValue)
PercentChange = ((CurrentValue - PreviousValue) / PreviousValue)
FormatPercent (PercentChange,1)
End Function


However, there is an error on the format command when I am running it, user error obviously, except I do not understand what I am doing wrong.

Sorry this is a super basic question, but I'm just getting my feet wet!

-Mark

CatDaddy
07-06-2011, 09:51 AM
you didnt declare any of your variables or assign values to them? this might be the problem

mdwagner2
07-06-2011, 10:02 AM
Hmm, I suppose that could be a problem, although if I omit the FormatPercent line from the code, the function works in excel, it just doesn't automatically format it as a percent.

CatDaddy
07-06-2011, 11:08 AM
Function PercentChange(CurrentValue, PreviousValue)
PercentChange = FormatPercent((CurrentValue - PreviousValue) / PreviousValue)
End Function

mdwagner2
07-06-2011, 11:27 AM
Well initially I was getting an error but now that seems to be working. Thanks.

CatDaddy
07-06-2011, 11:34 AM
no problemo, FormatPercent automatically does two decimal places i dont know if having 1 place was particularly important to you but i think you would have to NumberFormat first:


PercentChange = FormatPercent(Format((CurrentValue - PreviousValue) / PreviousValue), "#,##0.0")

or something to that effect

Paul_Hossler
07-06-2011, 11:51 AM
Q: does FormatPercent return a string or a double. I'd guess string.

Would that be a problem trying to calculate with it?

Paul

CatDaddy
07-06-2011, 04:14 PM
Seems to me it does return a string...might be a deal breaker?

Bob Phillips
07-06-2011, 11:59 PM
no problemo, FormatPercent automatically does two decimal places i dont know if having 1 place was particularly important to you but i think you would have to NumberFormat first:


PercentChange = FormatPercent(Format((CurrentValue - PreviousValue) / PreviousValue), "#,##0.0")

or something to that effect

FormatPercent has a NumDigitsAfterDecimal argument.