Solved: Basic VBA problem with Format

[mdwagner2]

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.



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


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]

you didnt declare any of your variables or assign values to them? this might be the problem

[mdwagner2]

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]

[VBA]
Function PercentChange(CurrentValue, PreviousValue)
PercentChange = FormatPercent((CurrentValue - PreviousValue) / PreviousValue)
End Function

[/VBA]

[mdwagner2]

Well initially I was getting an error but now that seems to be working. Thanks.

[CatDaddy]

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:

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

or something to that effect

[Paul_Hossler]

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]

Seems to me it does return a string...might be a deal breaker?

[Bob Phillips]

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:

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

or something to that effect

FormatPercent has a NumDigitsAfterDecimal argument.