How to Fix Runtime error 6: Overflow

[LucasLondon]

Hi,

I have the code below that I adapted to loop through each column of data (Columns A to GR) to perform an a calculation based on the data in rows 2 to 31 of each column and place the calculated statistic in row 35 for each column of data.

The problem is the code runs fine for the first six columns of data (i.e row 35 for columns A:F get populated with a correctly calculated statistic but code stops running at column G, and generates a Overflow, Runtime error 6). Debugging takes you to the following line in the code: RS = R / S

Does anyone know what could be causing this. I read that dimming variables as long as opposed to integer can fix this problem but I tried this but I still get the same error.

Could this be a memory issue for Excel?

Note I am not a VBA programming.

I hope someone can help.

Thanks,

Lucas
-----------------------------------------------------
Option Base 1
Sub Newcode() 'code works within worksheet
Dim Data() As Double
Dim Array1() As Double
Dim Array2() As Double
Dim Mean As Double
Dim Result1() As Double
Dim Resultn() As Double
Dim Resultr() As Double
Dim Resultn1() As Double
Dim Resultr1() As Double
Dim maxa() As Integer
Dim points As Integer
Dim pointno As Integer
Dim no_N As Integer
Dim period As Integer
Dim N, pe As Integer
Dim i, j, counter As Integer
Dim m, sc, c, ss, cc As Integer
Dim logten
Dim R, Maxi, Mini, h As Double
Dim S, sum_R, sum_S, Summ As Double
Dim RS, wid, wid1, Sumx, Sumy, Sumxx, Sumxy As Double
Dim nam, nama1, addr, mvar, Msg, nama, os As Variant
logten = Log(10)

'START OF NEW CODE
Dim a, x, y As Integer 'new /new bits to loop through columns
x = Cells(2, Columns.Count).End(xlToLeft).Column 'new/start at row 2, determines no of columns to calculate
For a = 1 To x 'new
y = 31 'specify number of rows else it doesn't work if you let it self select, ensure there is no data below
'y = Cells(Rows.Count, a).End(xlUp).Row 'new
b = Chr(a + 64) 'new
c = b & "2:" & b & y 'starts at row 2

'Get and output total number of data points
Set inputdata = Range(c)
'END OF NEW CODE

'Get total number of data points
points = inputdata.Cells.Rows.Count
pe = 5

If pe < 3 Then
MsgBox "Cannot have less than three periods"
End
End If

ReDim Data(points) As Double

'Get data, ignoring any spaces
i = 1
counter = 1
Do While counter <= points 'no of data points
If Application.WorksheetFunction.IsNumber(inputdata.C ells(counter).Value) Then
Data(counter) = inputdata.Cells(counter).Value
counter = counter + 1
Else
addr = inputdata.Cells(counter).Address
End If
i = i + 1
Loop

ReDim Result1(points) As Double
ReDim Resultn(points) As Double
ReDim Resultr(points) As Double
ReDim Resultn1(points - (pe - 1)) As Double
ReDim Resultr1(points - (pe - 1)) As Double

'Begin main loop
N = pe '3
Do
For period = 1 To points 'no_N
DoEvents
ReDim Array1(N) As Double
ReDim Array2(N) As Double

For i = 1 To N
Array1(i) = Data(i)
Array2(i) = 0
Next i

Mean = Application.Average(Array1())


'STDEVP
S = Application.StDevP(Array1())
For i = 1 To N
Array1(i) = Array1(i) - Mean
Next i

Array2(1) = Array1(1)
For i = 2 To N

Array2(i) = Array2(i - 1) + Array1(i)
Next i

Maxi = Application.Max(Array2())
Mini = Application.Min(Array2())
R = Maxi - Mini
RS = R / S

Resultr(period) = Application.Ln(RS)
Resultn(period) = Application.Ln(N)
Result1(period) = RS / Sqr(N)
N = N + 1

Application.StatusBar = " Running period " & N - 1
wid = ((N / points) * 100) * 2.22
wid1 = (N / points) * 100

If N > points Then Exit For

Next period
Loop Until N > points ' + 2

'Calculate statistic
For i = 1 To points - (pe - 1) '2
Resultr1(i) = Resultr(i)
Resultn1(i) = Resultn(i)
Next i

h = Application.Slope(Resultr1(), Resultn1())

Range(b & "35").Value = h 'NEW - change to reflect where result should be shown

Next a 'NEW
End Sub

[Killer42]

Just go to debugging mode when the error occurs, and display the values of R and S. Perhaps S is zero, which would make the result infinite, which will overflow anything.

[LucasLondon]

Hi,

Indeed the value of s appears to be zero. I changed the data in that range and now the macro is runninig for more columns. But not for all columns, it appears the macro stops working at column z and then generates the following run time error (1004): method range of object of global failed.

Debugging takes you to the following line in the code "Set inputdata = Range(c)"

It seems to be a different from the overflow error I was experiencing as this time it's realting to the range of columns to loop. But I can't see anything in the code that would stop the macro working beyond column z but then again I did not write all the code!

Does anyone know what I would need to change the get the macro to work beyond column Z?

Thanks,

Lucas




Does an

[Killer42]

Let's see...

Ah, got it!

It's actually a fairly simple problem, though you may have to jump through some hoops to fix it. See this code...

b = Chr(a + 64) 'new
c = b & "2:" & b & y 'starts at row 2

Let us assume for the purposes of discussion that y = 10. The following values of a will return the following values in c...

a    c
1    A2:A10
2    B2:B10
26    Z2:Z10
27    [2:[10
28    \2:\10

What's wrong with this picture? :)

[LucasLondon]

Hi,

OK I see that after column 27, there is no proper referencing for the following columns and so the macro fails. Presumably, the column referencing has got something to do with the following line of code:

b = Chr(a + 64)

But I still don't understand how it generates the letters to represent each column?????

Anyway, in terms of fixing, could I not just utilise the following line of existing code in someway?

x = Cells(2, Columns.Count).End(xlToLeft).Column 'new/start at row 2, determines no of columns to calculate

Basicaly as I understand it it is counting the number of populated columns, can't I just use the answer from here to define the loop range somehow?

e.g if the value = 100, then range would become something like

columns 1 to 100
rows 2:31

Or even better could not do something simpler like:

For each column in range ("A2:GF31")

Thanks,

Lucas

[Killer42]

OK I see that after column 27, there is no proper referencing for the following columns and so the macro fails. Presumably, the column referencing has got something to do with the following line of code:

b = Chr(a + 64)

But I still don't understand how it generates the letters to represent each column?????

To understand that, you need to have some familiarity with the ASCII character set. The upper case letters are represented by the codes 65-90. Thus, when a is 1, the above statement generates character number 65, which is "A". And so on. But once you go past 90 (the code for "Z") you move on into other parts of the character set. In this case, "[" then "\" then who knows what.

Anyway, in terms of fixing, could I not just utilise the following line of existing code in someway?

Sorry, I have to dash off now. Will try and have a look at this in the morning.

If you can find an alternative way of achieving your aims though, that's great! Quite often, it's not worth "fixing" your code, if you can find another (preferably simpler) way to do what you want. Never feel that you have to go with the first method you thought of. :)

As I said, I'll try to get back to you in the morning (it's Friday evening here).

[LucasLondon]

Hi Killer,

Did you get a chance to review further?

Thanks,

Lucas

[Killer42]

Did you get a chance to review further?

No I haven't, sorry. Been quite busy.

But as far as getting the column right, you could just set up a string array with the column names in it. In other words, it would contain things like...
ColumnName(1) = "A"
...
ColumnName(26) = "Z"
ColumnName(27) = "AA"
ColumnName(28) = "AB"

And so on. Then just use that in setting up your reference. Probably not the most efficient way to go about it, but perhaps one of the simplest.

I do still plan to go through this in more detail, but as I said, I'm very busy.