Placing a SINGLE value into a cell (Excel 2000 SP1)

Topic

New Reply

When placing data into cells using VBA, I get a strange result.

Using the code below (trimmed extensively for this example) I get the following results…
– Place a “Single” value of “68.4” into a cell. The cell displays “68.4” but when I check, it actually contains the value “68.4000015258789”

Can anyone tell me why?

Sub Example()
‘ Convert a string value to a Single and place in a cell

Dim intRow As Integer
Dim strPart As String
Dim sglDistance as Single
intRow = 2
strPart = “68.4”
sglDistance = CSng(strPart)

‘ Single step through and
‘ sglDistance does equal “68.4”

‘ Place the data into a cell
With ActiveSheet
.Range(“E” & intRow).Value = sglDistance
End With

‘ Check the cell value
‘ Displays as 68.4
‘ Contains 68.4000015258789

End Sub

An explanation would be appreciated.

Reply | Quote

Replies

Hi Fred

Sure that is normal Excel behaviour. Excel has a 15-decimal digit precision, and if you count the numbers they will be 15.

Try: Application.WorksheetFunction.Round(sglDistance, 1) or the VBA Round function.

Wassim
PS check Precison as displayed under the Tools|Options|Calculation Tab.

Reply | Quote

The same or similar phenomenon is discussed in a recent thread Here

Andrew C

Reply | Quote

Thanks Andrew (and Wassim)

As usual, a simple post to The Lounge made everything clear.

Thanks especially for the link to the other thread… a quick and effective method of providing me more information… and very applicable too.

Reply | Quote