How to increase processing speed


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: How to increase processing speed

  1. #1
    Join Date
    Apr 2010
    Posts
    6

    How to increase processing speed

    Hello,

    I'm using the VB6 to read and write data in excel sheets. Works fine but takes a long time processing. The speed is very low. For example, the same program running in VB6 and VBA, in VB6 takes about 15 minutes however in VBA takes about 25 seconds. This is normal? It is a big time difference.. How can I solve this problem? if is possible..

    Thanks very much

  2. #2
    Join Date
    Jun 2009
    Posts
    135
    Without seeing the code it is hard to tell what is going on but I can say the com interface you are using to automate excel will add some time, but not that much time. Do you have/use the Left, Mid, or Right functions? If so, change them to their string equivalent counter parts (Left$, Mid$, Right$) as Left, Mid, and Right return variant strings because the $ explicitly states to return a string. You could also convert any Integers you pass to longs to help speed things up as VB6.0 integers are 16 bit as vb internally converts passed integers to longs.

    Then it is time to check your code over. Make sure you only pull data from a cell once and put it there only once...



    Good Luck

  3. #3
    Join Date
    Feb 2006
    Posts
    133
    I agree with vb5prgrmr. Show us the code/method you are using.

    How many records do you proces? Processing a lot of data in excel using vb6.0 or vb.net is rather slow, if I have to write a lot of data-records, I first write a csv-file, open it with excel and copy it.
    Last edited by Benjamin; 06-14-2010 at 04:01 AM.
    Benjamin

  4. #4
    Join Date
    Apr 2010
    Posts
    6
    thanks for reply.

    It's a very simple code as you will see. I'm only reading data and writing on a sheet.

    Code:
    ----
    Dim excel_a As EXCEL.Application
    Dim excel_w As EXCEL.Workbook
    Dim excel_s As EXCEL.Worksheet
    Dim vel, Pmax, Pmin As Double
    Dim a, b, c, d, e As EXCEL.Range
    Set a = Range("D2")
    Set b = Range("M2")
    Set c = Range("O2")
    Set d = Range("T2")
    Set e = Range("B2")
    Dim i, x, y, j, cont, cont2, cont3, AP As Double
    Dim min, max, soma, media As Double
    Dim flag, erro As Integer
    
    Set excel_a = CreateObject("Excel.Application")
    excel_a.Visible = False
        
    Set excel_w = excel_a.Workbooks.Open(FileName:=abrir.Text)
    Set excel_s = excel_w.ActiveSheet
    
    AP = 500
    max = -1000
    min = 10000
    flag = 0
    y = 0
    
    
    Do
    erro = y
        Do Until a(i + 1, 1) = ""
            
            Do
            i = i + 1
            
            vel = a(i, 1)
            
            
                If b(i, 1) <> "" And b(i, 1) > 0 Then
                    Call standard_pot
                    
                    If b(i, 1) <= Pmax And b(i, 1) >= Pmin Then
                    
                        If b(i, 1) > max Then
                            max = b(i, 1)
                        End If
                        If b(i, 1) < min Then
                            min = b(i, 1)
                        End If
                        
                    cont = cont + 1
                    soma = soma + b(i, 1)
                    End If
                    
                End If
                
                If a(i + 1, 1) = "" Then
                    cont2 = 3
                Else
            
                    If a(i, 1) <> a(i + 1, 1) Then
                        cont2 = cont2 + 1
                    End If
                End If
        
            Loop Until cont2 = 3
        
        If cont = 0 Then
            cont = 1
            max = ""
            min = ""
        End If
        media = soma / cont
        
            Do
            j = j + 1
            
            If b(j, 1) <> "" Then
            
                If b(j, 1) <= 0 Then
                    y = y + 1
                    d(y, 1) = e(j, 1)
                    d(y, 2) = a(j, 1)
                    d(y, 3) = b(j, 1)
                    b(j, 1) = ""
                Else
                   
                        If b(j, 1) < (media - AP) Or b(j, 1) > (media + AP + 50) Then
                                y = y + 1
                                d(y, 1) = e(j, 1)
                                d(y, 2) = a(j, 1)
                                d(y, 3) = b(j, 1)
                                b(j, 1) = ""
                        End If
    
                End If
                
            End If
                
            If a(j + 1, 1) = "" Then
                    cont3 = 3
            Else
        
                If a(j, 1) <> a(j + 1, 1) Then
                    cont3 = cont3 + 1
                End If
                
            End If
        
            Loop Until cont3 = 3        
        
        x = x + 1
        
        c(x, 1) = a(i, 1)
        c(x, 2) = max
        c(x, 3) = min
        c(x, 4) = media       
        
        max = -1000
        min = 10000
        soma = 0
        media = 0
        cont = 0
        cont2 = 0
        cont3 = 0
        
        Loop
        
    flag = flag + 1
    AP = AP - 10
    i = 0
    x = 0
    j = 0
    cont = 0
    cont2 = 0
    cont3 = 0
    min = 10000
    max = -1000
    soma = 0
    media = 0
    Loop Until erro = y And AP = 250
    
    end sub
    
    Sub standard_pot()
    
    Select Case vel
    
    Case 0 To 3: Pmax = 80: Pmin = 0
    Case 3 To 3.5: Pmax = 100: Pmin = 0
    Case 3.5 To 4.5: Pmax = 200: Pmin = 0
    Case 4.5 To 5.5: Pmax = 300: Pmin = 0
    Case 5.5 To 6: Pmax = 400: Pmin = 0
    Case 6 To 6.5: Pmax = 450: Pmin = 0
    Case 6.5 To 7: Pmax = 500: Pmin = 40
    Case 7 To 7.5: Pmax = 650: Pmin = 50
    Case 7.5 To 8: Pmax = 800: Pmin = 100
    Case 8 To 8.5: Pmax = 900: Pmin = 200
    Case 8.5 To 9: Pmax = 1200: Pmin = 250
    Case 9 To 9.5: Pmax = 1300: Pmin = 400
    Case 9.5 To 10: Pmax = 1400: Pmin = 450
    Case 10 To 10.5: Pmax = 1500: Pmin = 550
    Case 10.5 To 11: Pmax = 1700: Pmin = 750
    Case 11 To 11.5: Pmax = 1900: Pmin = 850
    Case 11.5 To 12: Pmax = 2000: Pmin = 850
    Case 12 To 12.5: Pmax = 2200: Pmin = 1000
    Case 12.5 To 13: Pmax = 2200: Pmin = 1250
    Case 13 To 13.5: Pmax = 2200: Pmin = 1500
    Case 13.5 To 20: Pmax = 2200: Pmin = 1600
    End Select
    
    End Sub
    Last edited by Hack; 06-23-2010 at 08:44 AM. Reason: Added Code Tags

  5. #5
    Join Date
    Apr 2010
    Posts
    6

    example

    To see the time diference, I've done a simple little code just to see how faster the VBA is against VB6. You can try this in your computer.

    VBA CODE:
    Code:
    Sub test()
        Dim a As Range
        Set a = Range("A1")
        
        For i = 1 To 1000
            For j = 1 To 1000
            
                If a(i, 1) = a(j, 2) Then
                    x = x + 1
                End If
            
            Next
        Next
    End Sub
    VB6 CODE:
    Code:
    Private Sub Command1_Click()
    
    Dim excel_a As Excel.Application
    Dim excel_w As Excel.Workbook
    Dim excel_s As Excel.Worksheet
    Dim a As Excel.Range
    
    Set excel_a = CreateObject("Excel.Application")
    excel_a.Visible = True
    Set excel_w = excel_a.Workbooks.Add
    Set excel_s = excel_w.ActiveSheet
    Set a = Range("A1")
    
    For i = 1 To 1000
        For j = 1 To 1000
            
            If a(i, 1) = a(j, 2) Then
                x = x + 1
            End If
            
        Next
    Next
    
    End Sub
    Results:
    -------
    VBA - 17 seconds
    VB6 - 25 minutes (discounting the the time for open the excel application)

    What is wrong? or this is normal?

    thanks
    Last edited by Hack; 06-23-2010 at 08:45 AM. Reason: Added Code Tags

  6. #6
    Join Date
    Feb 2006
    Posts
    133
    Hi,

    first remark:
    you use variables to declare your ranges. This is not necessary.
    You can call your range using this for example:

    Code:
    excel_a.activeworkbook.activesheet.range("A1").value
    As I already mentioned, processing large amounts of data using excel is rather slow using vb. Perhaps you should try to read your textfile instead of opening it with excel and then write your results in excel, this should give you some faster results.

    In your last example(to test) you do 1000 * 1000 cells to check, this is verry slow of course. In your original code you use a lot of loops and if-functions, this makes your code slower, the main cause still is processing a lot of cells in excel according to me(my experience with it).

    Another tip: if your code has to execute a lot of if functions let's say 10000 times for example you better use select case statements to increase speed.
    Last edited by Benjamin; 06-15-2010 at 03:14 AM.
    Benjamin

  7. #7
    Join Date
    Apr 2010
    Posts
    6
    Quote Originally Posted by Benjamin View Post

    In your last example(to test) you do 1000 * 1000 cells to check, this is verry slow of course.
    it's true that is a lot of cells to check but, as you can see in last example, in VBA it only takes 17 seconds to finish against the 25 minutes in VB6. It's a big difference... is this that I don't understand

    But thanks anyway for the tips

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    VB6 is not Excel, therefore, it has to make a connection with Excel in order to do anything.

    VBA is running directly from Excel without the need to perform any external tasks.

    That, in itself, will account for some speed differences.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Replies: 9
    Last Post: 02-24-2007, 02:11 AM
  2. Replies: 1
    Last Post: 10-29-2002, 07:57 AM
  3. Replies: 0
    Last Post: 06-17-2002, 02:26 PM
  4. WinSock OCX vs API - SPEED!?
    By rhwiebe in forum VB Classic
    Replies: 2
    Last Post: 05-30-2001, 02:54 AM
  5. WinSock OCX vs API - SPEED!?
    By rhwiebe in forum VB Classic
    Replies: 0
    Last Post: 05-29-2001, 07:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center