Excel VBA INT 轉換錯誤問題

些許日子，在幫老婆做VBA的整數轉換時
使用INT() 函式將 double轉成int時，原本是4，轉出後變成3

查了國外討論，既然是微軟的問題
解決方法就是在轉換前，先將要轉換變數先轉成Decimal，再轉成INT即可。


As expected, this correctly evaluates to 1001:

? 100.05 * 10 + .5
1001

However, using the same expression inside the Int( ) function gives the wrong result:

? Int(100.05 * 10 + .5)
1000 

 Here’s the correct way to perform rounding:

Function FMS_Round(dblNumber As Double, intDecimals As Integer) As Double
  Dim dblFactor As Double
  Dim dblTemp As Double
  
  dblFactor = 10 ^ intDecimals
  dblTemp = dblNumber * dblFactor + .5
  
  FMS_Round = Int(CDec(dblTemp)) / dblFactor

End Function

With the simple addition of the CDec conversion, FMS_Round( ) returns the correct result:

? FMS_Round(100.05, 1) ' Correctly returns 100.1 

Reference：

When Microsoft Access Math Doesn't Add Up http://www.fmsinc.com/tpapers/math/index.html