roundup not behaving in VBA

Ok been a long time since I worked with VBA.

Formula in spreadsheet that works correctly for Canadian, Ontario, PST

ROUNDUP($M11*PSTRate,2))

where $M11 = 6.55 and PSTRate = .08

correct answer $0.53 which does print correctly in the sheet no problems. Now in a userform try to do the exact same thing with VBA code!

' // with variables all defined as currency

DIM memPSTAmount as Currency

Dim memAmount as Currency

Dim memPSTRate as currency

memAmount = 6.55

memPSTRate = 0.08

' Calculate the PST Amount
memPSTAmount = memAmount * memPSTRate
lblpst.Caption = Application.RoundUp(memPSTAmount, 2)

gives wrong answer as $0.52

WHY?

Declare your variables as Double.
The Currency data type limits numeric values to 4 fixed decimal places.
That characteristic is apparently screwing up your result.

As a general rule, use either the  Long or Double data type for numeric values in VBA.

Thanks Jim:

Double did the trick although unsure why Currency won't have worked.

Also remembered the difference between Long and Double. Long is long integer and therefore won't hold the decimal value where Double is for long decimal numbers.

Currency should have worked as well in this case with 4 decimals as the wrong answer one only needed to hold .524 and should have roundup to .53

Strange error but will not use Currency from now on.

Currency does have more decimal places than your result, but who knows what goes on under the covers?

You can get the correct results just by declaring the result variable as Double, but in reality, why bother with Currency data type? It seems to offer no specific use that I can see.

Create a form "userform1". Add two text boxes "textbox1" and "textbox2" to the form. Copy the following code to the declaration section of the form. Start the form with F5 and then click on the form. The answer in textbox1 will be incorrect. When the roundup function is called using the worksheetfunction property, it will give the correct answer, as shown in textbox2.

Tested with Excel97 & Windows XP on a PIII machine.
 
Code for demonstration:
 
Option Explicit

Dim memPSTAmount As Currency

Dim memAmount As Currency

Dim memPSTRate As Currency

Private Sub UserForm_Click()

    memPSTAmount = memAmount * memPSTRate

    TextBox1.Value = Application.RoundUp(memPSTAmount, 2)

    TextBox2.Value = Application.WorksheetFunction.RoundUp(memPSTAmount, 2)

End Sub

Private Sub UserForm_Initialize()

    memAmount = 6.55

    memPSTRate = 0.08

End Sub

See the above posts - the OP has the exact same issue as you, and the solution is provided.

The replies explain that the currency datatype only stores 2 decimal places.  So when you calculate your memPSTAmount, it is already being internally rounded to 2dp.  Following the suggestions from above to change your datatypes to double will ensure that your calculations are not truncated before you do it intentionally with the roundup function.

Without testing it myself, I assume than if you make these changes it'll solve your problem. Let us know if you find different.