CODEWITHSUNDEEP
excelroundingvariantvba
farmerandy
farmerandy

Reputation: 139

Rounding a Variant

Why does a number rounded to 1 digit still write to Excel with many digits.

For instance:

dim myvar as variant
dim mysingle as single
dim myrange as range

mysingle = 4567.23494376
myvar = round(mysingle,1)

myrange = "A1"
myrange.value = myvar

Even though I'm writing myvar which has been rounded to one digit to cell A1, although the cell displays 4567.2, clicking on the cell shows the entire value with all the digits to the right of the mantissa in the formula bar.

Should I expect this behavior?

Upvotes: 1

Views: 760

Answers (2)

Charles Williams
Charles Williams

Reputation: 23540

Using singles is nearly always a bad idea - Excel uses Doubles - converting singles to doubles leads to trouble - this code works as you expect for the double but gives your unexpected answer with a single without involving Round

    Sub roundit()
Dim myvar As Variant
Dim myDouble As Double
Dim mySingle As Single
myDouble = 4567.23494376
mySingle = 4567.2
myvar = Round(myDouble, 1)

Range("a1").Value = myvar
Range("a2").Value = mySingle
End Sub

Upvotes: 2

Mertinc
Mertinc

Reputation: 781

Try myvar = WorksheetFunction.Round(mysingle, 1)

instead of myvar = round(mysingle,1)

Please also see the information below provided by @YowE3K

WorksheetFunction.Round always returns a value of type Variant/Double, while Round returns a value of the same type as the input parameter (Single in this case).

Upvotes: 4

Related Questions