VBA or VB6?

[roelmcebu]

I created a VBA that reads a text file(sequential,very large file) then puts the values in excel cells. Out of curiosity and as practice coding, I copied the VBA code into VB6 and tried running it. I noticed that VBA does it faster(90% faster) than VB6.

is VBA really faster than VB6 when it comes to Office manipulation?

 

[RoyVidar]

If the intention is to populate Excel cells, then I wouldn't be surprised Excel/VBA is faster, since there's no need to Automate another application. But - for VB - did you test the difference between a compiled version vs running in the IDE.

Roy-Vidar

 

[roelmcebu]

the compiled version only improved by around 10%. Negligible.
Since most of my work is populating excel cells with variables extracted from large text files,

I also noticed that when VBA is running, I cannot mess with the excel file. But when VB is the one populating cells, clicking on one cell stops the VB...generating a runtime error.

is it advisable then to just stick with VBA?

 

[RoyVidar]

I'm really just a VBA guy, so I wouldn't know the real answer from the VB side - but if your tests show that kind of performance difference, and that's the more important issue for your apps, then I wouldn't have any doubt at all.

When automating Excel from other applications, I will often make Excel invisible through the process, to avoid such errors;-)

Roy-Vidar

 

[roelmcebu]

thanks Roy.

I am trying to use VBA to monitor cellular network/circuit status. what I really wanted was monitor several networks, so I need to shift between several sheets...unfortunately, VBA does not allow me to manually click on other sheets while it is still running.

Or, is there a trick that will allow me to manually click on other sheets while VBA is executing?

 

[kodr]

Sounds like what you may want to do is build a graphical interface for monitoring (VB.) If you need to make a snapshot or record of the network at any point, then save the data you're tracking as a .csv file (or whatever format you choose.)

Making a shape change color to indicate circuit status is just as easy as changing the value of a cell.

But that is just my opinion.. Take it with a shot and a pinch of salt.

 

[roelmcebu]

@kodr, that is exactly what I did. But how will you do it if you are monitoring circuit status of 10 or more networks? The graphical interface is just an added feature, what I am after is the formatted data for post processing.

I created separate sheets for each network, but again my problem is I cannot view the other sheets if the macro is running. That's why I tried porting it to VB6...but it seems very slow compared to VBA.

I'm still in the dark for a workaround.

Thanks.

 

[strongm]

Can we see the competing VB6/VBA code?

 

[roelmcebu]

These codes don't do the monitoring but based on them in action, I can see that VBA is faster than VB6 in terms of populating excel cells...(data extracted from very large text file).

@strongm,
I don't see any difference in the codes. As what I've said, I only copied the VBA code in the VB6 IDE...so basically they are the same in the "populating portion of the code". Anyway here they are:

The VB6 code:
Open filepicked For Input As #1
row = 2
Do While Range("A" & row) <> ""
row = row + 1
Loop
Do While Not EOF(1)
Input #1, sline
marker = Mid(sline, 1, 13) 'SHORT MESSAGE or END of REPORT
NEnamemark = Mid(sline, 11, 3)
If marker = "MSC OBSERVATI" Then
flag = 1
flagg = 0
End If
If NEnamemark = "MSC" Or NEnamemark = "MSS" Then
NEname = Mid(sline, 11, 6)
End If
If flag = 1 Then
marknum = Mid(sline, 1, 21)
marknum2 = Mid(sline, 1, 19)
marknum3 = Mid(sline, 1, 17)
If marknum = "CALLING NUMBER :" Then
Anum = Mid(sline, 26, 12)
End If
If marknum = "CALLED NUMBER :" Then
Bnum = Mid(sline, 26, 12)
End If
If Mid(Anum, 1, 1) = "0" Then
Anum = "'" & Anum 'CALLING NUMBER
End If
If Mid(Bnum, 1, 1) = "0" Then
Bnum = "'" & Bnum 'CALLED NUMBER
End If
If marknum2 = "IMEI :" Then
A_IMEI = Mid(sline, 21, 15)
B_IMEI = Mid(sline, 47, 15)
End If
If marknum2 = "CGR/BSC/PCM-TSL :" Then
A_CGR = Mid(sline, 21, 20)
B_CGR = Mid(sline, 47, 20)
End If
If marknum2 = "LAC/CI/CELL BAND :" Then
A_LAC = Mid(sline, 21, 20)
B_LAC = Mid(sline, 47, 20)
End If
If marknum2 = "SUBSCRIBER :" Then
If Mid(sline, 21, 1) = "A" Then
Sheets("Sheet1").Cells(row, 4).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
If Mid(sline, 21, 1) = "B" Then
Sheets("Sheet1").Cells(row, 8).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
If marknum3 = "TYPE :" Then
SMStype = Mid(sline, 19, 10)
Sheets("Sheet1").Cells(row, 12).Value = SMStype 'OUT/IN Type
End If

markDelvryTime = Mid(sline, 1, 13) 'DELIVERY TIME or INCOMING TIME
If markDelvryTime = "DELIVERY TIME" Or markDelvryTime = "INCOMING TIME" Then
DelvryTime = Mid(sline, 19, 23) 'value of delivery time or incoming time
Successval = Mid(sline, 55, 3) 'value of successful delivery
Sheets("Sheet1").Cells(row, 1).Value = NEname
Sheets("Sheet1").Cells(row, 2).Value = DelvryTime
Sheets("Sheet1").Cells(row, 3).Value = Successval
Sheets("Sheet1").Cells(row, 4).Value = Anum 'CALLING NUMBER
Sheets("Sheet1").Cells(row, 8).Value = Bnum 'CALLED NUMBER
Sheets("Sheet1").Cells(row, 5).Value = A_IMEI 'A IMEI
Sheets("Sheet1").Cells(row, 9).Value = B_IMEI 'B IMEI
Sheets("Sheet1").Cells(row, 6).Value = A_CGR 'A CGR
Sheets("Sheet1").Cells(row, 10).Value = B_CGR 'B CGR
Sheets("Sheet1").Cells(row, 7).Value = A_LAC 'A LAC
Sheets("Sheet1").Cells(row, 11).Value = B_LAC 'B LAC
End If

If flagg = 1 Then
SMSdata2 = Mid(sline, 1, 55)
If Trim(SMSdata2) = "END OF REPORT" Then
SMSdata2 = " "
End If
SMSdata = SMSdata & SMSdata2
Sheets("Sheet1").Cells(row, 13).Select
Sheets("Sheet1").Cells(row, 13).Value = SMSdata
nextrow = 1
End If
If marker = "END OF REPORT" Then
flagg = 0
End If
If marknum3 = "SM DATA :" Then
flagg = 1
SMSdata = Mid(sline, 19, 55)
End If

End If ' flag = 1
If marker = "END OF REPORT" Then
flag = 0
If nextrow = 1 Then
row = row + 1
nextrow = 0
End If
End If

Loop
Close #1
Sheets("Sheet1").Cells(2, 13).Select 'select the cell at row2 col13
End Sub

===========================================================

The VBA code
Open filepicked For Input As #1
For Input As #1
row = 2
Do While Range("A" & row) <> ""
row = row + 1
Loop

Do While Not EOF(1)
Input #1, sline

marker = Mid(sline, 1, 13) 'SHORT MESSAGE or END of REPORT
NEnamemark = Mid(sline, 11, 3)
If marker = "MSC OBSERVATI" Then
flag = 1
flagg = 0
End If
If NEnamemark = "MSC" Or NEnamemark = "MSS" Then
NEname = Mid(sline, 11, 6)
End If

If flag = 1 Then
marknum = Mid(sline, 1, 21)
marknum2 = Mid(sline, 1, 19)
marknum3 = Mid(sline, 1, 17)

If marknum = "CALLING NUMBER :" Then
Anum = Mid(sline, 26, 12)
End If
If marknum = "CALLED NUMBER :" Then
Bnum = Mid(sline, 26, 12)
End If
If Mid(Anum, 1, 1) = "0" Then
Anum = "'" & Anum 'CALLING NUMBER
End If
If Mid(Bnum, 1, 1) = "0" Then
Bnum = "'" & Bnum 'CALLED NUMBER
End If

If marknum2 = "IMEI :" Then
A_IMEI = Mid(sline, 21, 15)
B_IMEI = Mid(sline, 47, 15)
End If
If marknum2 = "CGR/BSC/PCM-TSL :" Then
A_CGR = Mid(sline, 21, 20)
B_CGR = Mid(sline, 47, 20)
End If
If marknum2 = "LAC/CI/CELL BAND :" Then
A_LAC = Mid(sline, 21, 20)
B_LAC = Mid(sline, 47, 20)
End If
If marknum2 = "SUBSCRIBER :" Then
If Mid(sline, 21, 1) = "A" Then
Sheets("Sheet1").Cells(row, 4).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
If Mid(sline, 21, 1) = "B" Then
Sheets("Sheet1").Cells(row, 8).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
If marknum3 = "TYPE :" Then
SMStype = Mid(sline, 19, 10)
Sheets("Sheet1").Cells(row, 12).Value = SMStype 'OUT/IN Type
End If

markDelvryTime = Mid(sline, 1, 13) 'DELIVERY TIME or INCOMING TIME
If markDelvryTime = "DELIVERY TIME" Or markDelvryTime = "INCOMING TIME" Then
DelvryTime = Mid(sline, 19, 23) 'value of delivery time or incoming time
Successval = Mid(sline, 55, 3) 'value of successful delivery
Sheets("Sheet1").Cells(row, 1).Value = NEname
Sheets("Sheet1").Cells(row, 2).Value = DelvryTime
Sheets("Sheet1").Cells(row, 3).Value = Successval
Sheets("Sheet1").Cells(row, 4).Value = Anum 'CALLING NUMBER
Sheets("Sheet1").Cells(row, 8).Value = Bnum 'CALLED NUMBER
Sheets("Sheet1").Cells(row, 5).Value = A_IMEI 'A IMEI
Sheets("Sheet1").Cells(row, 9).Value = B_IMEI 'B IMEI
Sheets("Sheet1").Cells(row, 6).Value = A_CGR 'A CGR
Sheets("Sheet1").Cells(row, 10).Value = B_CGR 'B CGR
Sheets("Sheet1").Cells(row, 7).Value = A_LAC 'A LAC
Sheets("Sheet1").Cells(row, 11).Value = B_LAC 'B LAC
End If

If flagg = 1 Then
SMSdata2 = Mid(sline, 1, 55)
If Trim(SMSdata2) = "END OF REPORT" Then
SMSdata2 = " "
End If
SMSdata = SMSdata & SMSdata2
Sheets("Sheet1").Cells(row, 13).Select
Sheets("Sheet1").Cells(row, 13).Value = SMSdata
nextrow = 1
End If
If marker = "END OF REPORT" Then
flagg = 0
End If
If marknum3 = "SM DATA :" Then
flagg = 1
SMSdata = Mid(sline, 19, 55)
End If

End If ' flag = 1
If marker = "END OF REPORT" Then
flag = 0
If nextrow = 1 Then
row = row + 1
nextrow = 0
End If
End If

Loop
Close #1

Sheets("Sheet1").Cells(2, 13).Select 'select the cell at row2 col13

End Sub