[Excel/VBA]: How to resolve Overflow Error (ErrorId: 6) in Timeserial Function?

[prashantdixit]

Hi,

I have imported a huge file(35 MB log file). This file contains a column called "Seconds" which contain data for each seconds starting from 1.

Initial Time and date is also given in the report.

Now what i am trying to do is to insert another column in this sheet called "Start Time". Then for each value corresponding to "Seconds" column, i am adding it to Initial Time given in the report. This value along with date is then displayed "Start Time" column. The subroutine for this is:

Sub InsertStartTimeWithDateColumn(QCMSheetname As String)

    Dim StartDate As String
    Dim STime As String
    Dim StartTime As Date
    Dim Seconds As Long
    Dim FirstDataRow As Long
    Dim LastDataRow As Long
    Dim secValue As Long
    Dim ii As Long
    Dim TotalSeconds As Variant
    Dim Hours As Long
    Dim Minutes As Long
    Dim TempTime As Variant
    
    Application.ScreenUpdating = False
    Sheets(QCMSheetname).Activate
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "Start Time"
    Columns("A:A").ColumnWidth = 19.86
    
    StartDate = Format(Cells(2, 3).Value, "dd/mm/yyyy")
    STime = Format(Cells(3, 3).Value, "hh:mm:ss")
    Hours = Hour(STime)
    Minutes = Minute(STime)
    Seconds = Second(STime)
    FirstDataRow = 11
    LastDataRow = Cells(1000000, 2).End(xlUp).row
    
    For ii = FirstDataRow To LastDataRow
        If (Not (Cells(ii, 2).Value = "")) Then
            secValue = Cells(ii, 2).Value
            TotalSeconds = secValue - Seconds
            TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
            StartTime = StartDate & " " & TempTime
            Cells(ii, 1) = StartTime
            'Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:mm:ss")
            'TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
            If (Format(TempTime, "hh:mm:ss") = "23:59:59") Then
                 StartDate = DateAdd("d", 1, StartDate)
                 Hours = 0
                 Minutes = 0
                 Seconds = secValue + 1
            End If
        End If
    Next ii
    
Application.ScreenUpdating = True
End Sub

Now its giving me an error at following code when "TotalSecon ds" value reaches to 32768. As you can see, TotalSeconds is defined as Long. Still its giving Overflow error.

Can someone help me to resolve this issue.

at

TempTime = TimeSerial(Hours, Minutes, TotalSeconds)

Thanks
Prashant

[Mariostg]

Try declaring TotalSeconds As Long instead of Variant.

[Prashant Dixit]

I have tried it that as well. But didn't work.
Argument of TimeSerial function is as follow:
TimeSerial(Hour As Integer, Minute As Integer, Seconds As Integer )

As you can see, "Seconds" data type is Integer. I guess because of this, i am getting overflow at TimeSeconds = 32687

Anywork around for this

[MMcCarthy]

OK I'm having a few problems following what you are doing.

So secValue is a very large number representing the number of seconds elaspsed?
From that you subtract the number of seconds from the start time. How does this result in a number less that 60 which is what you would need for the TotalSeconds value in the TimeSerial function?

[prashantdixit]

No it doesn't result in number less than 60.
For instance TimeSeconds value is coming as 1000. Then this value is getting automatically converted to hh:mm:ss format by TimeSerial.
Error is coming when TimeSeconds value reaches 32768

[MMcCarthy]

Sorry I've never used TimeSerial like this so I didn't realise. Your problem is that this function only accepts integers. So once your value goes higher than that the function falls over.

[prashantdixit]

Yes,
Any workaround for this.
Because this TimeSeconds value will always be higher

[MMcCarthy]

OK Add two new variable eMins and eSecs as Integers and replace the block of code indicated below.

Sub InsertStartTimeWithDateColumn(QCMSheetname As String)
 
    Dim StartDate As String
    Dim STime As String
    Dim StartTime As Date
    Dim Seconds As Long
    Dim FirstDataRow As Long
    Dim LastDataRow As Long
    Dim secValue As Long
    Dim ii As Long
    Dim TotalSeconds As Variant
    Dim Hours As Long
    Dim Minutes As Long
    Dim TempTime As Variant
    Dim eMins As Integer
    Dim eSecs As Integer
  
    Application.ScreenUpdating = False
    Sheets(QCMSheetname).Activate
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "Start Time"
    Columns("A:A").ColumnWidth = 19.86
 
    StartDate = Format(Cells(2, 3).Value, "dd/mm/yyyy")
    STime = Format(Cells(3, 3).Value, "hh:mm:ss")
    Hours = Hour(STime)
    Minutes = Minute(STime)
    Seconds = Second(STime)
    FirstDataRow = 11
    LastDataRow = Cells(1000000, 2).End(xlUp).row
 
    For ii = FirstDataRow To LastDataRow
        If (Not (Cells(ii, 2).Value = "")) Then
            secValue = Cells(ii, 2).Value
            TotalSeconds = secValue - Seconds

            'New Code
            eSecs = TotalSeconds Mod 60
            TotalSeconds = TotalSeconds - eSecs
            eMins = (TotalSeconds / 60)
            TempTime = TimeSerial(Hours, Minutes+eNins, eSecs)
            'end of new code

            StartTime = StartDate & " " & TempTime
            Cells(ii, 1) = StartTime
            'Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:mm:ss")
            'TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
            If (Format(TempTime, "hh:mm:ss") = "23:59:59") Then
                 StartDate = DateAdd("d", 1, StartDate)
                 Hours = 0
                 Minutes = 0
                 Seconds = secValue + 1
            End If
        End If
    Next ii
 
Application.ScreenUpdating = True
End Sub

[prashantdixit]

Hi,
Thanks
It works. Can you please solve one more isssue that i am having after running this code.

Start Time value enters perfectly in "Start Time" column. However, there is issue that when start time value changes from end of month to start of new month then its value in the cell automatically changes from DD/MM/YYYY to MM/DD/YYYY format.

For instance, let say starttime value is 31/08/2010 23:59:59 Then its displaying the same value in cell of the sheet. But for next iteration, value in that cell displays as 09/01/2010 00:00:00 instead of 01/09/2010 00:00:00

thanks
Prashant

[MMcCarthy]

Try changing Cells(ii, 1) = StartTime to

Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:nn:ss")

[prashantdixit]

No actually it didn't work. But i used CDate() function to work it.

Sorry to ask one more question in same forum. but can you look it this thread. I am getting an error while populating combo box.

http://bytes.com/topic/access/answers/897183-excel-vba-how-resolve-2147467259-error-while-populating-combobox