VBA help - Overflow (error 6)

[Djordje8]

Hi all,

I am using an MSExcel template (MAKESENS 1.0, it's free and available online) to calculate the Mann-Kandall test and Sen's slope for a trend analysis of a wind speed time series. So, I have 62 years of data and the template (VBA code) works fine if I don't have zero in the time series. However, if zero zero occurs then I get an error message "Run-time error '6': Overflow ". If I click debug, it shows that the red line line is an issue (I attached the code below, please see the red line at the end).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Function tiedSum(n As Integer, x() As Double) As Integer
'Calculates sum related to tied groups(= two or more equal values)
' for the variance of Mann-Kendall statistics S
'n = number of values in the array x including missing values
'Function tiedSum is called by subroutines Sen and MannKendallNorm

Dim m As Integer ' number of tied groups
Dim tval() As Double ' data values of tied groups
ReDim tval
Dim t() As Integer, nt As Integer ' number of data in tied groups
ReDim t
Dim p, i As Integer 'indexes for the loops
Dim newValue As Boolean
Dim tSum As Integer

'Calculation of the number of tied groups m and the number of data
' in tied groups t()
m = 0
For i = 1 To n - 1
If x(i) <> MissingValue Then
newValue = True
If m > 0 Then
For p = 1 To m
If x(i) = tval(p) Then
newValue = False 'this value is alredy managed
Exit For
End If
Next p
End If

If newValue Then
nt = 1 'number of equal values x(i)
For p = i + 1 To n
If x(p) = x(i) Then
nt = nt + 1
End If
Next p

If nt > 1 Then ' new group only if nt>1
m = m + 1
t(m) = nt
tval(m) = x(i)
End If
End If
End If
Next i

'Calculating the sum related to tied groups for variance
tSum = 0
If m > 0 Then
For p = 1 To m
tSum = tSum + t(p) * (t(p) - 1) * (2 * t(p) + 5)
Next p
End If
tiedSum = tSum
End Function 'tiedSum
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Please, can I get some hepl?

Thans,
Djordje

 

[Jerry Sullivan]

Hi Djordje,

Looking at just the code example you provided, the problem is probably due to some of the variables being declared as Integer data types.

The largest value that an Integer data type can store is 32,767 and if you try to exceed that or even multiply Integers whose product will exceed that you will get the Overflow error that you describe.

I don't know if your observation that this happens with you have a zero in the time sequence is a coincidence or if somehow that drives the calculated values higher.
It appears to me that the numbers will get large with higher frequencies of matches- but the value of the matched item (whether it be 0 or 1234) does not affect the resulting tSum.

Perhaps when you have blanks or zeroes in your data the frequency of zeros is much greater than any single non-zero values.

If the data type is the problem, you can fix it by changing these declarations:
Private Function tiedSum(n As Integer, x() As Double) As Long

Dim t() As Long
Dim tSum As Long

Of course if you could have more than 32,000 items in your series, then you'll also need to change the data type of the index variables.

 

[Djordje8]

Hi Jerry,

It works fine after I implemented your suggestions and results are good. Thank you so much!

 

[aradhana]

sir how to solve this overflow (error 6) problem in MKSENS excel sheet?