473,575 Members | 3,439 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Fix Runtime error 6: Overflow

16 New Member

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.


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)

Dim a, x, y As Integer 'new /new bits to loop through columns
x = Cells(2, Columns.Count). End(xlToLeft).C olumn '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.Coun t, a).End(xlUp).Ro w '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)

'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 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.Wor ksheetFunction. IsNumber(inputd ata.Cells(count er).Value) Then
Data(counter) = inputdata.Cells (counter).Value
counter = counter + 1
addr = inputdata.Cells (counter).Addre ss
End If
i = i + 1

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
For period = 1 To points 'no_N
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.Ave rage(Array1())

S = Application.StD evP(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.Sta tusBar = " 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.Slo pe(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 54240
8,435 Recognized Expert Expert
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
16 New Member

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?



Does an
Oct 9 '07 #3
8,435 Recognized Expert Expert
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
16 New Member

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).C olumn '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")


Oct 10 '07 #5
8,435 Recognized Expert Expert
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
16 New Member
Hi Killer,

Did you get a chance to review further?


Oct 17 '07 #7
8,435 Recognized Expert Expert
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

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 (rstin.EOF) Then rstin.MoveFirst 'loop through all the records rstIN (table tblPhongParseIN) Do While Not rstin.EOF 'set strField1 to tblPhongParseIN Field1
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: Overflow has occurred; evaluation is terminated" . The job return code is 1016 . Thanks With Regards Rakesh
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 following message "Runtime error 6 overflow". I don't see any error in programming version of the software. Why is package? I am using this software on...
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 data into database dim objCart
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 - overflow.
by: .nLL | last post by:
Erorr is --------------------- Microsoft VBScript runtime error '800a0046' Permission denied /a.asp, line 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 user to select a client and
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 CExcel::PrintLine( LPCSTR pchText ) { PrintLine( _bstr_t( pchText ) ); }
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 inside the folder "menu_script". The following is the code in this file. var...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.