
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

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

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 datarecords, I first write a csvfile, open it with excel and copy it.
Last edited by Benjamin; 06142010 at 04:01 AM.
Benjamin

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; 06232010 at 08:44 AM.
Reason: Added Code Tags

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; 06232010 at 08:45 AM.
Reason: Added Code Tags

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 iffunctions, 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; 06152010 at 03:14 AM.
Benjamin

Originally Posted by Benjamin
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

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.
Similar Threads

Replies: 9
Last Post: 02242007, 02:11 AM

By Paulo Fernandes in forum Database
Replies: 1
Last Post: 10292002, 07:57 AM

By Scott in forum ASP.NET
Replies: 0
Last Post: 06172002, 02:26 PM

By rhwiebe in forum VB Classic
Replies: 2
Last Post: 05302001, 02:54 AM

By rhwiebe in forum VB Classic
Replies: 0
Last Post: 05292001, 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

Forum Rules

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