Q: Using Scripting.Dictionary iterator in VBA returns incorrect answer I'm working on some code that needs to compare measurements taken on different days, where the location measured tends to drift between runs.  I'm aligning it by looking at one of the specific variables that are measured, offsetting it between the range of -100 metres to +100 metres, and looking at the rate of change between subsequent readings (they're half a metre apart), squaring the difference and looking at what value offset minimises the sum of the squares.
So I've got three ways of doing it.  I can dump the values in Excel, and use offset formulas.  For the example with code below, the minimised error is when the offset is 259 readings (29.5 metres positive, given 200 is the amount to bring -100 metres to no offset, and each reading is 0.5 metres, so 59*0.5 is 29.5 metres).  Value of the sum of errors is 2618.24690000001 - that doesn't mean much, it's just a reference at this stage.
Option 2 is to pass the recordset result to an array via the .GetRows method, and calculate the difference without using Excel worksheet and range objects.  I get the same answer - offset of 259 readings, for exactly the same sum of errors, down to the 15th sig figure.
Option 3 is to put the two recordset results in a scripting.dictionary instance, and calculate the errors using those.  There I get an offset of 59 readings, but exactly the same sum of error figure, again to 15 sig figures.  I've tested a few different locations, and it seems to be always out by 200 readings.
I have stepped through the code, I have searched this site, I've even gone so far as to look at any google results from non StackOverflow sites (yuck!), and I'm at complete loss.  I'm sure it's something really simple (and most likely incredibly stupid) that I've done, and I'll be embarrassed as all hell when it's point out, but I cannot for the life of me work it out.
Array code:
Private Sub array_manipulation(track_section As c_track_section)

Dim ss_variance As Double, offset_variance As Double
Dim ss_offset As Integer, i As Integer
Dim record_count As Long
Dim ref_array As Variant, run_array As Variant

record_count = Application.WorksheetFunction.Min(track_section.rs.RecordCount, track_section.align_rs.RecordCount - 400) - 1
ref_array = track_section.rs.GetRows(-1, 0)
run_array = track_section.align_rs.GetRows(-1, 0)
ss_variance = return_squared_errors(ref_array, run_array, 0, record_count)
ss_offset = 0
For i = 1 To 400
    offset_variance = return_squared_errors(ref_array, run_array, i, record_count)
    If offset_variance < ss_variance Then
        ss_variance = offset_variance
        ss_offset = i
    End If
Next i

MsgBox "ss_error is " & ss_variance & " at an offset of " & ss_offset & " intervals", vbOKOnly, "Did it work?"

End Sub

And the return_squared_errors function:
Private Function return_squared_errors(ref_array As Variant, run_array As Variant, offset As Integer, record_count As Long)

Dim i As Long
Dim ss_variance As Double


ss_variance = 0

For i = 1 To record_count
    ss_variance = ss_variance + ((ref_array(1, i) - ref_array(1, i - 1)) - _
                                (run_array(1, i + offset) - run_array(1, i + offset - 1))) ^ 2
Next i

return_squared_errors = ss_variance

End Function

The dictionary code:
Private Sub dict_manipulation(track_section As c_track_section)

Dim ss_variance As Double, offset_variance As Double
Dim ref_dict As Object, run_dict As Object
Dim ss_offset As Integer, i As Integer

Set ref_dict = CreateObject("Scripting.Dictionary")
Set run_dict = CreateObject("Scripting.Dictionary")
Call populate_dictionary(track_section.rs, ref_dict)
Call populate_dictionary(track_section.align_rs, run_dict)

ss_variance = return_dict_ss(ref_dict, run_dict, 0)
ss_offset = 0
For i = 1 To 400
    offset_variance = return_dict_ss(ref_dict, run_dict, i)
    If offset_variance < ss_variance Then
        ss_variance = offset_variance
        ss_offset = i
    End If
Next i

MsgBox "ss_error is " & ss_variance & " at an offset of " & ss_offset & " intervals", vbOKOnly, "Did it work?"

End Sub

And the return_dict_ss function:
Private Function return_dict_ss(ref_dict As Object, run_dict As Object, offset As Integer) As Double

Dim key As Variant
Dim ss_variance As Double, reading_interval As Double, offset_distance As Double
Dim current_key As String

reading_interval = 0.0005
offset_distance = offset * reading_interval
ss_variance = 0
For Each key In ref_dict.keys
    If ref_dict.exists(CStr(CDbl(key) - reading_interval)) And _
        run_dict.exists(CStr(CDbl(key) + offset_distance)) And _
        run_dict.exists(CStr(CDbl(key) - reading_interval + offset_distance)) Then
            ss_variance = ss_variance + ((ref_dict(key) - ref_dict(CStr(CDbl(key) - reading_interval))) - _
                (run_dict(CStr(CDbl(key) + offset_distance)) - run_dict(CStr(CDbl(key) - reading_interval + offset_distance)))) ^ 2
    End If
Next key

return_dict_ss = ss_variance

End Function

Edit:  I probably should note that the track_section of c_track_section object is a user defined class.  It has no functions or methods, it just holds a lot of setter/getter pairs  to avoid having to pass down a multitude of arguments when calling subs or functions.
