Odd error using Excel VBA to create number sequence

Question

I'm trying to take a series of number ranges (IE: .214-.217) and create excel files listing each number in the range. This is for the purposes of use in an ODK XLSform external CSV reference file.

The problem I am running into is that when certain numbers end a range they are not written into the excel file. The rest of the range is written, and when those numbers occur in the beginning or middle of a range they are written, but not when they end it. The numbers I've found this happens with so far are: .217, .010, .012

The min and max numbers were being stored in single precision floating point variables arrays. When I changed to a double precision FP variable .217 is written, but .010 and .012 are not.

Here is my code:

Sub BuildODKRange()

Dim PNStr As String
Dim MinMax(1 To 7) As Double
Dim CurrentDim(1 To 4) As Double
Dim CountInt As Integer

For Each Cell In Sheets("Data").Range("A2", Range("A65536").End(xlUp))

    Cell.Activate
    PNStr = Cell.Value

    For CountInt = 1 To 7
        MinMax(CountInt) = ActiveCell.Offset(0, CountInt)
    Next CountInt

    CurrentDim(1) = MinMax(1)
    CurrentDim(2) = MinMax(3)
    CurrentDim(3) = MinMax(5)
    CurrentDim(4) = 0

    While CurrentDim(1) <= MinMax(2)
    Sheets("hss_parts_od").Range("A65536").End(xlUp).Offset(1, 0) = PNStr
    Sheets("hss_parts_od").Range("B65536").End(xlUp).Offset(1, 0) = CurrentDim(1)
    CurrentDim(1) = CurrentDim(1) + 0.001
    Wend

    While CurrentDim(2) <= MinMax(4)
    Sheets("hss_parts_id").Range("A65536").End(xlUp).Offset(1, 0) = PNStr
    Sheets("hss_parts_id").Range("B65536").End(xlUp).Offset(1, 0) = CurrentDim(2)
    CurrentDim(2) = CurrentDim(2) + 0.001
    Wend

    While CurrentDim(3) <= MinMax(6)
    Sheets("hss_parts_thk").Range("A65536").End(xlUp).Offset(1, 0) = PNStr
    Sheets("hss_parts_thk").Range("B65536").End(xlUp).Offset(1, 0) = CurrentDim(3)
    CurrentDim(3) = CurrentDim(3) + 0.001
    Wend

    While CurrentDim(4) <= MinMax(7)
    Sheets("hss_parts_conc").Range("A65536").End(xlUp).Offset(1, 0) = PNStr
    Sheets("hss_parts_conc").Range("B65536").End(xlUp).Offset(1, 0) = CurrentDim(4)
    CurrentDim(4) = CurrentDim(4) + 0.001
    Wend

Next Cell

End Sub

This is my first attempt at coding in VBA, so I apologize in advance if I've just made a basic mistake here.

This is the excel file used to generate the dimension lists. The macro has already been run on this copy and the data generated by it is on the hss_part_XXXX tabs. You can see the erroneous behavior on the hss_parts_conc tab with the last numbers for PW002 and PW104: Excel File

trincot trincot · Accepted Answer · 2018-08-27T14:56:23

This happens due to floating point imprecision: 0.001 cannot be stored exactly in floating point representation and so by adding it over and over again the error on the summed up values increases.

You can use the Currency data type instead of Double, which is exact for when you use up to four digits in the fractional part, which seems to be OK for your case.

Odd error using Excel VBA to create number sequence

1 Answers