CODEWITHSUNDEEP
vbaexceltruncate
Loplac92
Loplac92

Reputation: 63

How to Truncate a Double in VBA

I have a variable in VBA that I need to truncate to 4 significant figures. I can't seem to find anything that won't round the number up or down. But I just want to remove the numbers after the 4th significant figure. I've tried, 

compressibility = round(compress, -3 - (Int(Log(Abs(compress)))))

It removes the numbers after the 4th digit but it still rounds the number up.

Compress is a number around 0.000245848385 as an example, and I need the compressibility number to be 0.0002458.

Any suggestions would be great! Thanks.

Upvotes: 3

Views: 2279

Answers (4)

Scott Craner
Scott Craner

Reputation: 152585

Sub test()
Dim compress As Double
compress = 0.000245858

Dim compressibility As Double
compressibility = Int(compress * 10 ^ -(Int(Log(Abs(compress))) - 3)) / 10 ^ -(Int(Log(Abs(compress))) - 3)

Debug.Print compressibility
End Sub

Upvotes: 0

Dmitry Morozov
Dmitry Morozov

Reputation: 11

It seems to me that you want to avoid rounding UP, but not rounding down, since rounding down should produce the exact result you want. So, instead of using VBA Round function, you could use Excel WorksheetFunction.RoundDown method to achieve the result you need.

ROUNDDOWN(0.00024586548385;7)=0.000245800000 ROUNDDOWN(0.00024583548385;7)=0.000245800000

Upvotes: 0

Olly
Olly

Reputation: 7891

Try this function:

Function RoundSignificant(ByVal dValue As Double, iFigures As Integer)
    Dim dSig As Double
    dSig = Abs(dValue)
    dSig = Application.Log10(dSig)
    dSig = 1 + Int(dSig)
    dSig = iFigures - dSig
    RoundSignificant = Round(dValue, dSig)
End Function


Sub test()
    Debug.Print RoundSignificant(0.000245848385, 4)
End Sub

Upvotes: 4

xidgel
xidgel

Reputation: 3145

Using worksheet functions:

=VALUE(TEXT(compress,"0.000E+00"))

For VBA

CDbl(Format(compress,"0.000E+00"))

Hope that helps.

Upvotes: 0

Related Questions