Runtime error '6' : Overflow

**elfvis** · 10-19-2007, 12:38 PM

I have read the other posts on here and swapped, Dim timeinc As Interger to Dim timeinc As Long and Dim timeinc As Double but neither worked. They both make the calculation 0 and I can not figure out why. This macro has worked before and is failing only on this new dataset which I have attached as well.

I am solving this problem on a PC and the macro was built for a Mac but I do not think that should change much as when I run it on a Mac it fails the same

Please Login or Register  to view this content.

**Tarball** · 10-19-2007, 01:03 PM

It is very hard to read the post and without seeing all of your declarations I cannot tell for sure. But, this line

Please Login or Register  to view this content.

will cause an overflow if the timedif is greater than 9 (assuming that you did not Dim timedif as a long or double). If you didnt Dim timedif at all or if it is an integer then you get the overflow.

An observation: For each of your sheets, in the VBA, use OPTION EXPLICIT to force type declarations. This will make your code run faster as well as make it easier to read and debug.

**royUK** · 10-19-2007, 01:10 PM

Integer is misspelt, try using Option Explicit to avoid such errors & to force you to declare variables

http://www.excel-it.com/clear_code.htm

You still have code referring to Mac.

**Tarball** · 10-19-2007, 01:18 PM

Integer is misspelt, try using Option Explicit to avoid such errors & to force you to declare variables

misspelt is also misspelled

**elfvis** · 10-19-2007, 02:27 PM

Aside from the code refering to macs in the line:

timeinc = (timedif * 3600) like stated above if timedif is set as an integer it will overflow; however, if I set it to a long (for example) it then fails right below that line:

timeseed = timedif / timeinc

I have changed Dim timeinc As Integer (orginially misspelled but only in my post on the forums) to Dim timeinc As Long which then makes the line
timeinc = (timedif * 3600) become 0. I do not really quite understand why this is occuring.

When add Option Explicit to the header I have to define a lot of things. Like you said this will help me in the long run and I would like to go this route but the situation needs kinda of an immediate solution.

Is there any particular reason that when I change from Integer to Long it is creating a zero? This is the first time this macro has arrived at this error and I am pretty confused by it.

If I Dim timedif As Long below Dim timeinc the same failure occurs. This is the complete code and data sheet generating this error. thanks for your responses and time.

**shg** · 10-19-2007, 02:34 PM

misspelt is also misspelled

Alternate spellings in my (American Heritage) dictionary, with the former preferred in the UK.

Elfvis,

I just noticed this line:

Please Login or Register  to view this content.

That's not your problem, but it is a problem.

**Tarball** · 10-19-2007, 02:48 PM

Use type DOUBLE for floating point math

Please Login or Register  to view this content.

**shg** · 10-19-2007, 04:16 PM

After watching your code import (separating data at tabs and semicolons), it doesn't appear to agree with the way you extract it.

The data starts with four columns (lat, lon, a time column that is mostly blank, and another time column. Your code

Please Login or Register  to view this content.

apperas to be trying to extract a date from the hour value of a time. Can you explain?

**elfvis** · 10-19-2007, 04:26 PM

Tarball mailed me the solution ty

**Tarball** · 10-19-2007, 04:29 PM

I sent you the Workbook back to the email address you provided me in the PM.

The issue is due to dividing by zero which I corrected by doing the following:

Please Login or Register  to view this content.

Also, I formatted your code, and declared everything. One other thing, the code looks like it was generated using the Macro recorder which is fine, but just understand that the code that is generated is generally not very efficient. For example, each cell is selected in the following line

Please Login or Register  to view this content.

. Rather than do this, you can refer to the cell using the Range object. You can also read the sheet rather than hard code the array indices.

But, in the end, if the solution does what you want it to do, and it works, then use it