[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to optimize speed of IsDate, Cdate, and Splitting strings into arrays -- in a small VB6 project.

Posted on 2009-05-16
4
Medium Priority
?
222 Views
Last Modified: 2013-12-26
Okay, I have a program that runs through textfiles or csv to find the date and output weither the date is found or not. It uses the VB6 DatePicker control, which is located at the MS common controls 6.0-2.

Right now I have it so that it finds 'specific text' an textfile, and only gives THOSE lines with the text in them. However, there is also a date range I am now adding, which will find only specific dates too.

Using the code below works find to get the date from the line -- 'str1' does well...

Basicly it grabs the line, checks for valid dates, bookmarks the number it if it finds it in a specific place in the array so it can be processed faster 'next line' until the end of the file, and then makes checks to see if the date in the file is = to the date range I have specified.

However, the program goes from about 6 seconds without the date range filter to almost 30+ with a 300k lined file. Thus I do not think my coding is quite optimized.

Is there a better solution for this? I use CDate because the date that comes in can be 11/12/09, 11 12 09, Dec 11 09, etc.. Cdate basicly solves all this for me in a nice package.. Unless there is something 'better' then cdate, or a function 'made' manually..

Basicly I am wondering how to improve performance. I do know it will not be the 'same' speed without the date range filter, but I am wondering if I can use something other then arrays, or cdate/isdate.. Or by looking at the coding below, a way to increase performance minor/major.

Thanks!
Line Input #intFileHandle2, str1
               validline = 0
                                
                If Check1.Value = 1 Then
                    ReDim marray(0)
                    validon = Replace(str1, Chr(34), "")
                    validon = Replace(validon, "'", "")
                    marray = Split(str1, ",")
                    
                    If validfound = -1 Then
                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                            End If
                        Next
                    End If
                    
                    If validfound <> -1 Then
                        If IsDate(marray(validfound)) Then
                            currentdate = DTPicker1.Value
                            finddate = Format(CDate(marray(validfound)), "short date")
                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                End If
                                currentdate = currentdate + 1
                            Loop
                        End If
                    End If
                End If
            'Do rest of stuff here with validline being equal to 1...

Open in new window

0
Comment
Question by:Valleriani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 24402128
You may want to exit out of the loops once your conditions are met.  Thus,

                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                            End If
                        Next

becomes:

                        For i = 0 To UBound(marray)
                            If IsDate(marray(i)) Then
                                validfound = i
                                Exit For
                            End If
                        Next

and:

                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                End If
                                currentdate = currentdate + 1
                            Loop

becomes:

                            Do While currentdate <= DTPicker2.Value
                                If finddate = currentdate Then
                                    validline = 1
                                    Exit Do
                                End If
                                currentdate = currentdate + 1
                            Loop
0
 
LVL 7

Author Comment

by:Valleriani
ID: 24402222
Thank you for the reply!

Ah right I did miss though, however it unfort doesn't change much because most 'searched' aren't there, for example in a 300k file, maybe 10k of those are of 3-4 dates.

But still, every little bit helps! (I will credit you a bit when I get more solutions too :) )
0
 
LVL 7

Author Comment

by:Valleriani
ID: 24402229
To be honest, I believe a good portion may be the split and the array of it. Is there a better way to do this?
0
 
LVL 22

Accepted Solution

by:
danaseaman earned 1600 total points
ID: 24402579
1. Read entire file and Split with VbCrLf
2. Move Check1 to outer loop
3. Remove redundant IsDate ouside Do Loop
4. Use Date var for finddate
5. validon is not being used in posted code


Option Explicit
 
Private Sub Process()
   Dim sArray()      As String
   Dim mArray()      As String
   Dim str1          As String
   Dim validon       As String
   Dim i             As Long
   Dim lLine         As Long
   Dim validline     As Long
   Dim validfound    As Long
   Dim currentdate   As Date
   Dim finddate      As Date
 
   sArray = FileToArray(App.Path & "\MyFile.csv")
   If Check1.Value = 1 Then
      For lLine = 0 To UBound(sArray)
         validline = 0
         str1 = sArray(lLine)
         validon = Replace(str1, Chr(34), "")
         validon = Replace(validon, "'", "")
         mArray = Split(str1, ",")
 
         If validfound = -1 Then
            For i = 0 To UBound(mArray)
               If IsDate(mArray(i)) Then
                  validfound = i
                  Exit For
               End If
            Next
         End If
 
         If validfound <> -1 Then
            'If IsDate(mArray(validfound)) Then
            currentdate = DTPicker2.Value
            finddate = CDate(mArray(validfound)) 'was Format(CDate(mArray(validfound)), "short date")
            Do While currentdate <= DTPicker2.Value
               If finddate = currentdate Then
                  validline = 1
                  Exit Do
               End If
               currentdate = currentdate + 1
            Loop
            'End If
         End If
 
         'Do rest of stuff here with validline being equal to 1...
      Next
   End If
End Sub
 
Private Function FileToArray(strPath As String)
   On Error GoTo ErrHandler
   Dim FF As Integer
 
   If Dir(strPath) = "" Then Exit Function
   FF = FreeFile
   Open strPath For Input As #FF
 
   FileToArray = Split(Input(LOF(FF), #FF), vbCrLf)
ErrHandler:
   Close #FF
End Function

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question