CODEWITHSUNDEEP
vbaexcel
Karhumies
Karhumies

Reputation: 11

How to make sure numbers with decimals contain exactly two decimals in VBA?

This question concerns programming in VBA

Data type I am using is Double, because data type Decimal is not recognized by the compiler. This is a contributing issue.

Private Number1 as Double
Number1 = (read from a data source which contains 2 decimals)
Number1 = 0.15 * Number1 '#client needs to have a 0.15 multiplier for Number1. This multiplication causes number format problems.
Number1 = Round(Number1, 2)
Number1 = formatNumber(Number1, 2)

Does not work. There are still much more than only 2 decimals remaining in Number1 in the output.

Is there an easy pre-built library solution to this problem? Or should I make a custom method to format the number? What would be a recommended way to build that custom method?

Upvotes: 1

Views: 2672

Answers (2)

PaulG
PaulG

Reputation: 1189

If you need to use a decimal type, you can declare as a variant and convert.

Dim d As Variant
d = CDec(3.14)

At a guess though, it looks to me though, your code snippet isn't at fault here....maybe..

It looks to me that after this function is called, you're converting from a double to something else which is where you need to concentrate your efforts - not inside this function.

Upvotes: 0

Vasily
Vasily

Reputation: 5782

In examples below you can see that only 2 decimals remaining in Number1 after round() function, or in another similar functions

Sub test()
    Dim Number1 As Double
    Dim FX As Object: Set FX = WorksheetFunction

    Number1 = 0.15 * 1.236:                                      Debug.Print Number1 'return 0.1854
    Number1 = 0.15 * 1.236: Number1 = Round(Number1, 2):         Debug.Print Number1 'return 0.19
    Number1 = 0.15 * 1.236: Number1 = FX.Round(Number1, 2):      Debug.Print Number1 'return 0.19
    Number1 = 0.15 * 1.236: Number1 = FX.RoundUp(Number1, 2):    Debug.Print Number1 'return 0.19
    Number1 = 0.15 * 1.236: Number1 = FX.RoundDown(Number1, 2):  Debug.Print Number1 'return 0.18
    Number1 = 0.15 * 1.236: Number1 = FX.Ceiling(Number1, 0.01): Debug.Print Number1 'return 0.19
    Number1 = 0.15 * 1.236: Number1 = FX.Floor(Number1, 0.01):   Debug.Print Number1 'return 0.18
End Sub

test

enter image description here

So your code should work's fine, problem must be elsewhere (for instance in cell format).

Upvotes: 1

Related Questions