writing functions - runtime error 6 overflow

[ndeans]

Hi all,

I have recently started cleaning up some of my bigger databases by refining the rediculous numbers of wueries ans writing them as functions. I have written a number of functions that have all been successful so far, though this one is a bit more advanced than the others.

This function is a linear regression based on results of a physical performance test called the chester step test.

The following code works great when i apply it on a form dealing with a single data record, but when added to a query with multiple records the calculation works fine but i eventually get the runtime error 6 overflow message. I'm aware that it may be related to declaring the wrong variable type, but i'm not sure which one.

Would appreciate if anyone has any clues as to how this code can be cleaned up or what variable types i may have incorrect.

Function VO2(StepHeight As Double, HR1 As Double, HR2 As Double, HR3 As Double, HR4 As Double, HR5 As Double, TestDate As Date, ClientID As Double) As Double
    Dim L1 As Integer
    Dim L2 As Integer
    Dim L3 As Integer
    Dim L4 As Integer
    Dim L5 As Integer
    Dim Pairs As Integer
    Dim SumL As Integer
    Dim MeanL As Double
    Dim X1 As Double
    Dim X2 As Double
    Dim X3 As Double
    Dim X4 As Double
    Dim X5 As Double
    Dim Y1 As Double
    Dim Y2 As Double
    Dim Y3 As Double
    Dim Y4 As Double
    Dim Y5 As Double
    Dim SumXsq As Double
    Dim SumYsq As Double
    Dim SumP As Double
    Dim M As Double
    Dim B As Double
    Dim DOB As Date
    Dim Age As Integer
    Dim MaxHR As Integer
    
'Calculating Age at date of assessment based on DOB of the client
    DOB = DLookup("DOB", "tblClientDetails", "ClientID = " & ClientID)
    Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd"))
    MaxHR = 220 - Age
    
'Establishing X-Axis marker points based on the different step heights
    If StepHeight = 15 Then
        L1 = 11
        L2 = 14
        L3 = 18
        L4 = 21
        L5 = 25
        Else
        If StepHeight = 20 Then
            L1 = 12
            L2 = 17
            L3 = 21
            L4 = 25
            L5 = 27
            Else
            If StepHeight = 25 Then
                L1 = 14
                L2 = 19
                L3 = 24
                L4 = 28
                L5 = 33
                Else
                If StepHeight = 30 Then
                    L1 = 16
                    L2 = 21
                    L3 = 27
                    L4 = 32
                    L5 = 37
                End If
            End If
        End If
    End If

    If HR4 = 0 And HR5 = 0 Then                     ''If both = 0 then only stage 3 was acheived, therefore regression only uses 3 points
        Pairs = 3                                   ''Minimum 3 pairs required for regression
        SumL = L1 + L2 + L3                         ''Regression calculations
        MeanL = SumL / Pairs
        SumHR = HR1 + HR2 + HR3
        MeanHR = SumHR / Pairs
        X1 = L1 - MeanL
        X2 = L2 - MeanL
        X3 = L3 - MeanL
        Y1 = HR1 - MeanHR
        Y2 = HR2 - MeanHR
        Y3 = HR3 - MeanHR
        SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2)
        SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2)
        SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3)
        M = SumP / SumXsq
        B = MeanHR - (M * MeanL)
        VO2 = (MaxHR - B) / M                       ''Using straight line formula y=mx+b rearranged as x=(y-b)/m
        Else
        If HR5 = 0 Then                             ''4 pairs used for regression
            Pairs = 4
            SumL = L1 + L2 + L3 + L4
            MeanL = SumL / Pairs
            SumHR = HR1 + HR2 + HR3 + HR4
            MeanHR = SumHR / Pairs
            X1 = L1 - MeanL
            X2 = L2 - MeanL
            X3 = L3 - MeanL
            X4 = L4 - MeanL
            Y1 = HR1 - MeanHR
            Y2 = HR2 - MeanHR
            Y3 = HR3 - MeanHR
            Y4 = HR4 - MeanHR
            SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2) + (X4 ^ 2)
            SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2) + (Y4 ^ 2)
            SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3) + (X4 * Y4)
            M = SumP / SumXsq
            B = MeanHR - (M * MeanL)
            VO2 = (MaxHR - B) / M
            Else
                Pairs = 5                           ''All 5 pairs used for regression
                SumL = L1 + L2 + L3 + L4 + L5
                MeanL = SumL / Pairs
                SumHR = HR1 + HR2 + HR3 + HR4 + HR5
                MeanHR = SumHR / Pairs
                X1 = L1 - MeanL
                X2 = L2 - MeanL
                X3 = L3 - MeanL
                X4 = L4 - MeanL
                X5 = L5 - MeanL
                Y1 = HR1 - MeanHR
                Y2 = HR2 - MeanHR
                Y3 = HR3 - MeanHR
                Y4 = HR4 - MeanHR
                Y5 = HR5 - MeanHR
                SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2) + (X4 ^ 2) + (X5 ^ 2)
                SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2) + (Y4 ^ 2) + (Y5 ^ 2)
                SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3) + (X4 * Y4) + (X5 * Y5)
                M = SumP / SumXsq
                B = MeanHR - (M * MeanL)
                VO2 = (MaxHR - B) / M
        End If
    End If
End Function

Many thanks in advance for any assistance.

 

[DCrake]

Overflow usually means that the number generated is greater than the highest number of an Integer value, you could re-define them as long and see if that is the cause. However you have not stated which line it falls over on.

David

 

[gemma-the-husky]

overflow is generally divide by zero.

if you are dividing by zero your prog will crash. you need to either test before the calc, and not do it - or have a error handler that traps it.

the only things i can see you dividing by is pairs, SumXsq and M


maybe this but - if L1,L2,L3 are the same then x1,x2 and x3 are all 0 , and i think 0 then propagates into sumxsq.

SumL = L1 + L2 + L3 ''Regression calculations
MeanL = SumL / Pairs
SumHR = HR1 + HR2 + HR3
MeanHR = SumHR / Pairs
X1 = L1 - MeanL
X2 = L2 - MeanL
X3 = L3 - MeanL

 

[ndeans]

Solved......

Excellent, cheers folks, The divide by 0 gave me something to look at. The error was occuring with the M = SumP / SumXsq line of code which gemma-the-husky correctly identified the SumXsq as being a 0.

Turns out that 1 record had a step height of 33 when the only possibles should have been 15, 20, 25, or 30. Which meant the L1 to L5 were nulls hence the divide by 0.

I have placed an error handler on the data entry point to limit the possible entries.

Thanks all for your help.