CODEWITHSUNDEEP
vbaexcel
Jack
Jack

Reputation: 733

VBA truncate value

I'm trying to truncate some values because round functions don't fit my issue since I want to compare the values I'm dealing with with some others values but I don't if they were rounded up or down so I get errors.

Anyway, I've tried many things but still don't get what I want... The must would be something like =TRUNC but it is not available in VBA...

My last try which almost gave what I wanted is :

.Cells(iLine, iCol) = Int(.Cells(iLine, iCol).Value * 1000) / 1000

But the problem is that sometimes when I have a "-1" it gives "-1.001" so that messes things up... I don't really know why it does that since I put a msgbox after the * 1000 (so before the / 1000) and the result is -1000. So the problem comes from the division.

--> So if anyone knows a better solution to truncate or if you know why 1000/1000 gives me -1.001 thank you in advance !

Another solution was : 

Range(.Cells(iLine, iCol), .Cells(iLine, iCol)).Select 
Selection.NumberFormat = "0.000"
Range(.Cells(iLine, iCol), .Cells(iLine, iCol)) = Range(.Cells(iLine, iCol), .Cells(iLine, iCol)).Text * 1

But this is not what I'm looking because of two things :

  1. It forces my cell format to 0.000 which isn't confortable/aesthetic to read when you have -1.000
  2. It is not possible to do that with simple values only with ranges (I'm not about this one though)

EDIT : after some several test on my line .Cells(iLine, iCol) = Int(.Cells(iLine, iCol) * 1000) / 1000 it appears that the problem comes from the Int() function. Because -1*1000 = -1000 but int(-1000) = -1001. Anyone knows why this happens ?

Even when I increase the precision of this cell by let's say -1.000000 I get the same problem, it's only when I put Int() on it that the extra 1 apprears

Upvotes: 2

Views: 5571

Answers (4)

vapcguy
vapcguy

Reputation: 7547

Not sure why you would multiply by 1000, turn it to Int, then divide by 1000... you would just go back to what you had, except you converted it to an Integer in between, which could round it just on its own without the multiplying and dividing, but that's obviously not what you want...

Much simpler, just put your value into a Double:

Dim ourNumber As Double
ourNumber = 3.14159265457279

Then make a string and manipulate it that way. Assign your number to a string variable you create by converting it to string, and then you can manipulate it as a string:

Dim myDoubleStr As String
myDoubleStr = CStr(ourNumber)
myDoubleStr = Left(myDoubleStr, 4) ' equals 3.14

Then you can put it back to double for calculations or whatever you want:

ourNumber = CDbl(myDoubleStr)

So, to relate this back to your Excel scenario:

' Assuming we are passing in iLine and iCol...
Dim ourNumber As Double
ourNumber = worksheet.Range(iLine & ":" & iCol).Value

Dim myDoubleStr As String
myDoubleStr = CStr(ourNumber)
myDoubleStr = Left(myDoubleStr, 4)

worksheet.Range(iLine & ":" & "iCol").Value = CDbl(myDoubleStr)

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

you could use Trunc() function:

.Cells(iLine, iCol).Formula="=Trunc(" & .Cells(iLine, iCol).Value & ",3)"
.Cells(iLine, iCol).Value = .Cells(iLine, iCol).Value

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

One way to truncate a decimal (ie., floating-point) number to be an integer (eg. no decimal places) is to use the INT function.

MsgBox Int(1.234)

or

MsgBox Int(1.789)

...both return 1.

Note that this function works both in VBA and in worksheet formulas.

Floating Point Rounding Errors

You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

Note: Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.

More on this from the source and here.

Edit: Rounding issues when referring to worksheet directly

I can't reproduce your issue here, but it could depend on other factors such as cell format and Excel's settings.

Likely the easiest/best way to ensure you don't have this problem is to put the number into an integer variable before doing any calculations.

So instead of:

Range("A2") = Int( Range("A1") / 1000 ) * 1000

use:

Dim myInt as Integer, myInt2 as Integer  'don't 'Dim' variables within a loop

myInt = Range("A1")
myInt2 = Int( myInt / 1000 ) * 1000
Range("A2") = myInt2

More Information:

Another Edit:

I'm glad you think you found a solution but my concern is that what you're describing is not normal behaviour. Proof:

img

...and so, while your workaround may give you the right answer in this case, there may be other calculations being affected by this that are not so obvious.

There are a number of settings that could affect this. I'd suggest trying again with a new, blank workbook, ideally after rebooting.

Upvotes: 1

Jack
Jack

Reputation: 733

So I wanted to truncate my value and got a wrong number. To test if it was some rounding problem I tried to round to 10-6 before truncating to 10-3 and that worked :

.Cells(iLine, iCol) = Round(.Cells(iLine, iCol), 6)
.Cells(iLine, iCol) = Int(.Cells(iLine, iCol).Value * 1000) / 1000

I don't feel like this tells us why int(-1000.000000) gives -1001.000000 but hey, that works !

Thank you everyone

EDIT : I've just realized that Int() actually rounds because microsoft's doc says that INT(-4.5) = -5 So I guess my -1 was actually -1.0000[...]00001...

Upvotes: 0

Related Questions