469,327 Members | 1,180 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

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

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:


Expand|Select|Wrap|Line Numbers
  1. Sub InsertStartTimeWithDateColumn(QCMSheetname As String)
  2.  
  3.     Dim StartDate As String
  4.     Dim STime As String
  5.     Dim StartTime As Date
  6.     Dim Seconds As Long
  7.     Dim FirstDataRow As Long
  8.     Dim LastDataRow As Long
  9.     Dim secValue As Long
  10.     Dim ii As Long
  11.     Dim TotalSeconds As Variant
  12.     Dim Hours As Long
  13.     Dim Minutes As Long
  14.     Dim TempTime As Variant
  15.  
  16.     Application.ScreenUpdating = False
  17.     Sheets(QCMSheetname).Activate
  18.     Columns("A:A").Select
  19.     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  20.     Range("A10").Select
  21.     ActiveCell.FormulaR1C1 = "Start Time"
  22.     Columns("A:A").ColumnWidth = 19.86
  23.  
  24.     StartDate = Format(Cells(2, 3).Value, "dd/mm/yyyy")
  25.     STime = Format(Cells(3, 3).Value, "hh:mm:ss")
  26.     Hours = Hour(STime)
  27.     Minutes = Minute(STime)
  28.     Seconds = Second(STime)
  29.     FirstDataRow = 11
  30.     LastDataRow = Cells(1000000, 2).End(xlUp).row
  31.  
  32.     For ii = FirstDataRow To LastDataRow
  33.         If (Not (Cells(ii, 2).Value = "")) Then
  34.             secValue = Cells(ii, 2).Value
  35.             TotalSeconds = secValue - Seconds
  36.             TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
  37.             StartTime = StartDate & " " & TempTime
  38.             Cells(ii, 1) = StartTime
  39.             'Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:mm:ss")
  40.             'TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
  41.             If (Format(TempTime, "hh:mm:ss") = "23:59:59") Then
  42.                  StartDate = DateAdd("d", 1, StartDate)
  43.                  Hours = 0
  44.                  Minutes = 0
  45.                  Seconds = secValue + 1
  46.             End If
  47.         End If
  48.     Next ii
  49.  
  50. Application.ScreenUpdating = True
  51. End Sub
Now its giving me an error at following code when "TotalSeconds" 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
Expand|Select|Wrap|Line Numbers
  1. TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
  2.  

Thanks
Prashant
Sep 30 '10 #1
10 7479
Mariostg
332 100+
Try declaring TotalSeconds As Long instead of Variant.
Sep 30 '10 #2
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
Sep 30 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
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?
Sep 30 '10 #4
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
Sep 30 '10 #5
MMcCarthy
14,534 Expert Mod 8TB
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.
Sep 30 '10 #6
Yes,
Any workaround for this.
Because this TimeSeconds value will always be higher
Sep 30 '10 #7
MMcCarthy
14,534 Expert Mod 8TB
OK Add two new variable eMins and eSecs as Integers and replace the block of code indicated below.

Expand|Select|Wrap|Line Numbers
  1. Sub InsertStartTimeWithDateColumn(QCMSheetname As String)
  2.  
  3.     Dim StartDate As String
  4.     Dim STime As String
  5.     Dim StartTime As Date
  6.     Dim Seconds As Long
  7.     Dim FirstDataRow As Long
  8.     Dim LastDataRow As Long
  9.     Dim secValue As Long
  10.     Dim ii As Long
  11.     Dim TotalSeconds As Variant
  12.     Dim Hours As Long
  13.     Dim Minutes As Long
  14.     Dim TempTime As Variant
  15.     Dim eMins As Integer
  16.     Dim eSecs As Integer
  17.  
  18.     Application.ScreenUpdating = False
  19.     Sheets(QCMSheetname).Activate
  20.     Columns("A:A").Select
  21.     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  22.     Range("A10").Select
  23.     ActiveCell.FormulaR1C1 = "Start Time"
  24.     Columns("A:A").ColumnWidth = 19.86
  25.  
  26.     StartDate = Format(Cells(2, 3).Value, "dd/mm/yyyy")
  27.     STime = Format(Cells(3, 3).Value, "hh:mm:ss")
  28.     Hours = Hour(STime)
  29.     Minutes = Minute(STime)
  30.     Seconds = Second(STime)
  31.     FirstDataRow = 11
  32.     LastDataRow = Cells(1000000, 2).End(xlUp).row
  33.  
  34.     For ii = FirstDataRow To LastDataRow
  35.         If (Not (Cells(ii, 2).Value = "")) Then
  36.             secValue = Cells(ii, 2).Value
  37.             TotalSeconds = secValue - Seconds
  38.  
  39.             'New Code
  40.             eSecs = TotalSeconds Mod 60
  41.             TotalSeconds = TotalSeconds - eSecs
  42.             eMins = (TotalSeconds / 60)
  43.             TempTime = TimeSerial(Hours, Minutes+eNins, eSecs)
  44.             'end of new code
  45.  
  46.             StartTime = StartDate & " " & TempTime
  47.             Cells(ii, 1) = StartTime
  48.             'Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:mm:ss")
  49.             'TempTime = TimeSerial(Hours, Minutes, TotalSeconds)
  50.             If (Format(TempTime, "hh:mm:ss") = "23:59:59") Then
  51.                  StartDate = DateAdd("d", 1, StartDate)
  52.                  Hours = 0
  53.                  Minutes = 0
  54.                  Seconds = secValue + 1
  55.             End If
  56.         End If
  57.     Next ii
  58.  
  59. Application.ScreenUpdating = True
  60. End Sub
  61.  
Sep 30 '10 #8
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
Oct 1 '10 #9
MMcCarthy
14,534 Expert Mod 8TB
Try changing Cells(ii, 1) = StartTime to

Expand|Select|Wrap|Line Numbers
  1. Cells(ii, 1) = Format(StartTime, "dd/mm/yyyy hh:nn:ss")
  2.  
Oct 1 '10 #10
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/answer...ating-combobox
Oct 1 '10 #11

Post your reply

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

Similar topics

3 posts views Thread by jer006 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.