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

Sub Macro1()
'
' Macro1 Macro
'
'dimension arrays
Dim timeinc As Integer
'Locate and open file
'defaultpath = "Untitled:Users:Jim:Documents:Work:Nav_interp:"
 defaultpath = "C:\Yourpathhere\"
  DirPath = InputBox(prompt:="Directory Path to descriptor Files", _
   Default:=defaultpath)
   ChDir DirPath
   Open DirPath & "navoutput.txt" For Output As #1
   default_sheet = "V1.txt"
   nav_file = InputBox(prompt:="Name of navigation file", Default:= _
    default_sheet)
    Workbooks.OpenText Filename:=DirPath & nav_file, Origin:= _
        xlMacintosh, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
        , 1), Array(8, 1), Array(9, 1), Array(10, 1))
'Determine range of data
isdata = Cells(1, 1)
rowuntilcount = 1
Set Vertrange = ActiveCell.CurrentRegion
vertcount = Vertrange.Rows.Count
Do Until IsNumeric(isdata) = True
 isdata = Cells(rowuntilcount, 1)
 Cells(rowuntilcount, 1).Select
 rowuntilcount = 1 + 1
Loop
'Begin loop to interpolate fixes
For rowcount = rowuntilcount To vertcount
Cells(rowcount, 1).Select
'load lat, long, time variables.  This assumes data will always be formatted
'as lat. decimal degrees, long. -decimal degrees, hh, mm, sec., date
 lat1 = Cells(rowcount, 1)
 long1 = Cells(rowcount, 2)
 hour1 = Cells(rowcount, 3)
 min1 = Cells(rowcount, 4)
 sec1 = Cells(rowcount, 5)
 date1 = Cells(rowcount, 6)
 lat2 = Cells(rowcount + 1, 1)
 long2 = Cells(rowcount + 1, 2)
 hour2 = Cells(rowcount + 1, 3)
 min2 = Cells(rowcount + 1, 4)
 sec2 = Cells(rowcount + 1, 5)
 date2 = Cells(rowcount + 1, 6)
  'determine time difference between fixes (decimal hours):
  dechour1 = hour1 + (min1 + (sec1 / 60)) / 60
  dechour2 = hour2 + (min2 + (sec2 / 60)) / 60
  timedif = dechour2 - dechour1
   'if date change, change 24 hour clock to zero
 If date1 <> date2 Then
 timedif = (dechour1 - 23) + dechour2
 End If
  'determine distance between long and lat fixes (decimal degrees):
  latdist = lat2 - lat1
  longdist = long2 - long1
   'how many 1-sec increments are there in timedif?
   timeinc = (timedif * 3600)
    'what is the seed increment relative to timeinc?
     timeseed = timedif / timeinc
     latseed = latdist / timeinc
     longseed = longdist / timeinc
  'begin output with initial fix
   Write #1, lat1, long1, hour1 & ":" & min1 & ":" & sec1, date1
    'begin interp subloop
    If timeinc > 1 Then
    For outputcount = 1 To timeinc - 1
     latinterp = lat1 + latseed * outputcount
     longinterp = long1 + longseed * outputcount
     timeinterp = dechour1 + timeseed * outputcount
     'deconvolve time back to hours, minutes, seconds
      timeinterphour = WorksheetFunction.RoundDown(timeinterp, 0)
      timeinterpmin = WorksheetFunction.RoundDown((60 * _
      (timeinterp - WorksheetFunction.RoundDown(timeinterp, 0))), 0)
      'timeinterpsec = WorksheetFunction.RoundDown(60 * ((60 * (timeinterp - _
      WorksheetFunction.RoundDown(timeinterp, 0))) - (WorksheetFunction.RoundDown(60 * _
      ((WorksheetFunction.RoundDown(100 * (timeinterp - WorksheetFunction.RoundDown(timeinterp, 0)), 0)) / 100), 0))), 0)
      timeinterpsec = CByte(60 * (((timeinterp - timeinterphour) * 60) - timeinterpmin))
      'write incremented/interp output
      Write #1, latinterp, longinterp, timeinterphour & ":" & timeinterpmin & ":" & _
      timeinterpsec, date1
      Next outputcount
      End If
      Next rowcount
      Close #1
End Sub

**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

timeinc = (timedif * 3600)

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.