VBA in Excel assigns a value to a cell, but that is not the value shown in the cell after code is run

Question

I wrote VBA code that reads through a simple text file containing comma separated values and assigns these values to an array. Each array value then gets assigned to a cell on an Excel worksheet. Relevant lines of code are as follows:

Dim arySingle(826) As Single

Dim sht As Worksheet

Set sht = ActiveWorkbook.Sheets("Input Form")

... various lines of code...

sht.Range("STLI").Value = arySingle(654)

... code continues on...

Note: "Input Form" is the only worksheet in the workbook.

The value read in from the text file is .06. I have verified this both by looking at the text file contents and by breaking the code on the VBA line above and tested the value of arySingle(654) in the Immediate window. No other line of code affects the named range "STLI".

The named range (single cell) "STLI" is formatted as a two decimal percentage cell. After the VBA code runs, the cell shows 6.00%, but when selected, it shows a value of 5.99999986588954% in the formula bar. I do have other cells similarly formatted, and when a value is passed to them, they also show a value other than the value passed by the VBA code.

What would be causing this? What is the solution?

Thank you for your help.

Did you try rounding your value? Can we see the rest of the code and see how things are being handled? — Mark S.
You're up against a well known numeric precision discussion with rounding errors. This article in Wiki gives a good explanation. — PeterT

Aiyuni Aiyuni · Accepted Answer · 2019-07-30T17:45:22

Excel, and to an extension VBA, does not have the precision that programming languages such as Java or C# have. Excel has a precision limit of 15 significant figures. You are using the Single data type, which is considered a floating point value.

This is a good read about floating point precisions in Excel: https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

VBA in Excel assigns a value to a cell, but that is not the value shown in the cell after code is run

1 Answers