473,288 Members | 1,745 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

How to Fix Runtime error 6: Overflow

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
Oct 8 '07 #1
7 54198
Killer42
8,435 Expert 8TB
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.
Oct 8 '07 #2
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
Oct 9 '07 #3
Killer42
8,435 Expert 8TB
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...

Expand|Select|Wrap|Line Numbers
  1. b = Chr(a + 64) 'new
  2. 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...
Expand|Select|Wrap|Line Numbers
  1. a    c
  2. 1    A2:A10
  3. 2    B2:B10
  4. 26    Z2:Z10
  5. 27    [2:[10
  6. 28    \2:\10
What's wrong with this picture? :)
Oct 9 '07 #4
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
Oct 10 '07 #5
Killer42
8,435 Expert 8TB
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).
Oct 12 '07 #6
Hi Killer,

Did you get a chance to review further?

Thanks,

Lucas
Oct 17 '07 #7
Killer42
8,435 Expert 8TB
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.
Oct 17 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: phong.lee | last post by:
Is there a reason why i keep getting this run time error, i never gotten it before. Can someone help me out with this? here is my code: With rstout 'first check if not on EOF If Not...
0
by: krakesh11 | last post by:
Hi , In my sas program , I am facing a problem of overflow error in one of my SAS programs . Kindly help me in gettingresolve this issue . The exact error message which I got is : "ERROR:...
5
by: creative1 | last post by:
Hi all. I got a problem in my .exe version of the software. I created package wizard. For one command button, it displays messge "Cancel transaction". When I click OK, program crashes and displays...
3
by: grace01 | last post by:
My asp webpage was working well in the past 5 years. Recently it shows the message: Microsoft VBScript runtime error '800a0006' Overflow: 'addOrder' The source code is as follow: 'insert...
1
by: amolbehl | last post by:
Dim tempLeft As Long Dim tempWidth as integer tempLeft = Round(((tempWidth - i) * 100) / 18.39, 1) Hi, When I try to run this code and when tempWidth = 343, I get Runtime error 6...
0
by: .nLL | last post by:
Erorr is --------------------- Microsoft VBScript runtime error '800a0046' Permission denied /a.asp, line 3 -----------------------
3
by: Jim Armstrong | last post by:
Hello all - This is driving me crazy. I have a table called tblClients - very simple, has the following fields: taxID (PK) ClientName SalesName The main form of my application allows a...
2
by: Iamrashmin | last post by:
I have converted VC6 project to .Net 2005 VC++. It gives me warning : warning C4717: 'CExcel::PrintLine' : recursive on all control paths, function will cause runtime stack overflow void...
3
by: suganya | last post by:
Hi Some professionals already has developed the project using menu. In my company, they have given me task to clear the error in that. It is a script file named as "menubarAPI4.js" which is kept...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.