Does anyone know why an Excel Macro would be faster than VB.net code?
I wrote a macro to process about 1000 rows of an Excel spreadsheet. After losing the macro (found it later) I decided to rewrite the code in VB.net. When I found the macro I used the same code, modifying some features that don't work in VB.net, and found that it ran much much slower than the macro. The macro runs the entire 1000 rows in about 20-30 seconds. The VB.net code could only get through about 40 rows in the same amount of time. Is VB.net that much slower normally of is it something within Excel or is it my code? Just curious if anyone knows.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
No, it isn't that much slower normally, so something is going on. Without seeing the code, we can only guess, but with a slowdown as great as what you are talking about, the first place I would look would be exception handling, because there should be no slowdown at all, and a slowdown as excessive as that makes me think that some exceptions are being thrown and handled poorly. Still, there are MANY other possibilities, so the only thing that can be said for certain is that this is not how it SHOULD be.
One thing to check is that you are closing Excel properly. After a test pull up task manager and make sure you don't have multiple versions of Excel running.
I did check the Excel running after a test and you were right multiple versions were running and then I cleared them and it was still very slow. I posted the code to ChriE both the macro and vb code, not sure why the code isn't listed here.. Not sure what exceptions I'm not handling because I can see every single row being processed and it is slow on each row.
hi Jim
use the # "Code Tags around selected Text" and then copy Paste your code in there
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
jdelano, I'm not using anything but basic Excel with Vb.net from Visual Studio 2022 so no not NuGet package. Didn't even try a CSV file. Thought I needed most of the Excel options because of the calculations within the spreadsheet after I altered cells with VB.
Yes, if you have dependent formulas you'll want to use the interop, you can turn off auto calculation as you fill in the cells and then let it loose afterwards. As others have suggested, seeing your code will allow someone to give advice to speed things up (if possible)
Something to consider, when you run the VBA macro, Excel is already running. When you click the button on a winform, the code has to load Excel first, run the same macro and then close Excel. It isn't quite apples to apples conparrison because of that.
jdelano,
I understand what you are saying about VB.net having to load Excel, etc., but I was looking at the speed at which each version was going through the rows, not the overall speed. The Excel macro was so fast that it only took about 30-60 seconds to go through all 1000+ rows whereas the VB.net version I could actually watch the Excel step through the row and it took 30-60 seconds to get through about 20-30 rows.
There is something basically wrong with the way I am calling or starting Excel. I have another test program and I'm trying to see what is wrong. I look at Cell(1,1) and put it in a text field on the form before going to the next row. It never displays the next cell(1,1) on row 2. I can post that code also since it is very small and uses the same Excel spreadsheet.
Code:
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim app As New Excel.Application
Dim wkb As Excel.Workbook = app.Workbooks.Open("D:\GolfScores.xlsm")
Dim wks As Excel.Worksheet = CType(app.Worksheets(3), Worksheet)
Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
Dim icnt As Integer
app.Visible = True
For icnt = 2 To 1032
If wks.Cells(icnt, 9).value = 1 Then
Debug.Print(icnt)
'Debug.Print(wks.Cells(icnt, 9).text)
txtbox1.Text = wks.Cells(icnt, 9).value
txtbox1.Refresh()
End If
Next icnt
End Sub
The print icnt works but the second print doesn't. The first time the contents of cell(1,1) is moved to the text box but nothing after. Every 28 rows is a number (actually the round number) going from 1-59.
Last edited by Shaggy Hiker; Dec 31st, 2024 at 10:59 AM.
Reason: Added CODE tags.
I added [CODE][/CODE] tags to format the code a bit better. That's the # button.
This part doesn't make sense, as written:
Code:
For icnt = 2 To 1032
If wks.Cells(icnt, 9).value = 1 Then
Debug.Print(icnt)
'Debug.Print(wks.Cells(icnt, 9).text)
txtbox1.Text = wks.Cells(icnt, 9).value
txtbox1.Refresh()
End If
Next icnt
It might be that this is largely an example, which might explain why it doesn't make sense, but as written...it doesn't. What's the point of putting the contents of the cell anywhere? The If statement will only be true if the contents of the cell is 1, so the only thing that will ever be shown is 1. You could just print 1 some number of times and you'd get the same result.
However, there's a different issue, as well. That loop should be FAST!! If that's all you are doing, it should be so fast that even calling Refresh may not be good enough. That SHOULD force the textbox to redraw the display every time Refresh is called, and it might, but I've seen cases where the redraw would give up in a sufficiently fast loop. You may run into that. That may be especially true because all you are ever writing into that textbox is 1, so you won't see it change anyways.
I'd expect an issue there given the use of .text and not .value.
Also, you have an If statement around where the textbox is updated, this statement is checking cell(row, "I") not cell(1,"A") as you are eluding to here
It never displays the next cell(1,1) on row 2
which cell should it be looking at?
Are you sure the value in the cell is an integer or just rounded in the display formatting in Excel. Perhaps convert the value to Int as seen below.
Code:
If CInt(wks.cells(icnt, 9).value) = 1 Then
Debug.Print(icnt)
'Debug.Print(wks. Cells(icnt, 9).text) <<< should be .value
txtbox1.Text = wks. Cells(icnt, 9).value
txtbox1.Refresh()
End If
Here is a sub routine close to what you're trying to do (only reading comparing and writing to textbox1), I am using a listbox to display the relevant information as it executes.
Replace your file name of course. I just used random numbers to show that it is getting the value correctly.
Code:
Private Sub btnDoExcel_Click(sender As Object, e As EventArgs) Handles btnDoExcel.Click
ListBox1.Items.Add("Opening Excel") : Application.DoEvents()
Dim xlApp As New Excel.Application
ListBox1.Items.Add("Loading Excel File") : Application.DoEvents()
Dim xlWkb As Excel.Workbook = xlApp.Workbooks.Open("F:\Temp\VBForums_JimReid.xlsx")
ListBox1.Items.Add("selecting sheet1") : Application.DoEvents()
Dim xlWks As Excel.Worksheet = xlApp.Worksheets(1)
Dim icnt As Integer
xlApp.Calculation = -4135 'Manual ' this stops excel from calculating until the code is done
ListBox1.Items.Add("looping rows") : Application.DoEvents()
For icnt = 1 To 24
ListBox1.Items.Add($"found value in column I: {xlWks.Cells(icnt, 9).value}") : Application.DoEvents()
If xlWks.Cells(icnt, 9).value = 1 Then
Debug.Print(xlWks.Cells(icnt, 9).value)
TextBox1.Text = icnt
ListBox1.Items.Add("found '1' placed the row number in the textbox") : Application.DoEvents()
End If
Next icnt
xlApp.Calculation = -4105 ' Automatic
ListBox1.Items.Add("close the workbook") : Application.DoEvents()
xlWkb.Close(False) ' *************** you'll want to save the data change to true
ListBox1.Items.Add("quit Excel") : Application.DoEvents()
xlApp.Quit()
End Sub
I couldn't attach a 2nd screenshot as vbforums says it makes me go over quota, here is a link https://imgur.com/a/EQKhDK9
jdelano,
Both responses miss the point. The spreadsheet is a list of golf rounds. There are 18 rows for each round (1 per hole). Position Icnt, 1 is the round number (1 to 59 for 59 rounds). Rows 2-18 of each round is blank in position Icnt, 1. When I posted the code it didn't include all of it. I have a FOR loop of row 2 to l1032. So it should show 1,2,3,4,etc because those are the round numbers and there are blanks for the balance of 17 rows for each round. I'll try to put the full program in now.
Code:
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim app As New Excel.Application
Dim wkb As Excel.Workbook = app.Workbooks.Open("D:\GolfScores.xlsm")
Dim wks As Excel.Worksheet = CType(app.Worksheets(3), Worksheet)
Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
Dim icnt As Integer
app.Visible = True
For icnt = 2 To 1032
If wks.Cells(icnt, 9).value = 1 Then
Debug.Print(icnt)
'Debug.Print(wks.Cells(icnt, 9).text)
txtbox1.Text = wks.Cells(icnt, 9).value
txtbox1.Refresh()
End If
Next icnt
'txtbox1.Text = wh.Cells(1, 1).text
'txtBox2.Text = wh.Cells(1, 2).text
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
app.Quit() '*** Closes Excel
Me.Close() '*** closes program
End Sub
End Class
So it should show 1,2,3,4,etc because those are the round numbers and there are blanks for the balance of 17 rows for each round.
As SH has pointed out in a previous post, the only thing TextBox1 will display is "1" because that's the value your testing for "If wks.Cells(icnt, 9).value = 1 Then". Nothing else.
Code:
For icnt = 2 To 1032
If wks.Cells(icnt, 9).value = 1 Then
Debug.Print(icnt)
'Debug.Print(wks.Cells(icnt, 9).text)
txtbox1.Text = wks.Cells(icnt, 9).value
txtbox1.Refresh()
End If
Next icnt
Boy do I feel dumb now!! One simple line caused all this confusion. Thanks wes4dbt. SH too, I didn't catch that comment either. thanks everyone. Can mark this as complete.
Shaggy,
You have to understand. I'm an 80-year-old prior cobol programmer who only learned some VB about 15 years ago and in all practical purposes, I'm a newbie. I made one change: to stop all automatic calculations until the end of the process so that it wouldn't make the calls on each row. As for exception handling, I'm not sure how that works. How do you identify an exception and then how do you handle it? So I'm at a loss with that. The manual calculations sped it up but still nowhere near as much as the macro. Thanks for your help. Maybe you can point me in the direction of exception handling. Thanks again. JR
Actually, that wasn't right. In the code shown, you aren't doing any exception handling, which means that if there was an exception, you'd certainly know, since the program would crash. Exception handling costs nothing as long as no exception is ever thrown. Some people use exception handling to handle non-exceptional events. Basically, they let exceptions get thrown, then deal with them. That's terribly costly in time, so it's a really bad idea. However, if you don't have exception handling, then any exception thrown will just crash the program...which isn't nearly as bad as it sounds, because then you'll know you have an issue and can fix it. In this case, you aren't handling exceptions, and the program isn't crashing, so there aren't any exceptions to handle. That means the cause of the slowdown is somewhere else.
I was thinking that it was misguided exception handling because the slowdown you mentioned was so extreme that something like bad exception handling would generally be required for it to be that bad. Since that wasn't the problem, I'm really quite surprised at the slowdown you are reporting. The code doesn't look that bad. That Refresh will slow things down a little, but in my experience, it won't slow it down all THAT much.
Shaggy Hiker,
I think the sample you are looking at isn't the one that is slow. I actually have two issues and the one with the Refresh isn't a problem any longer (my dumb coding). It is the one that is about 1000 lines of code that actually manipulates the slow spreadsheet.
That is where I'm trying to convert the macro to VB.net and that is where the timing is so different. It is interesting about the exception handling because I thought that was what I didn't handle but I don't have any crashes so there must not be any exceptions.
Shaggy Hiker,
I think the sample you are looking at isn't the one that is slow. I actually have two issues and the one with the Refresh isn't a problem any longer (my dumb coding). It is the one that is about 1000 lines of code that actually manipulates the slow spreadsheet.
That is where I'm trying to convert the macro to VB.net and that is where the timing is so different. It is interesting about the exception handling because I thought that was what I didn't handle but I don't have any crashes so there must not be any exceptions.
No one here can give substantive suggestions based on a description of "my attempt to translate Excel Macro code to VB.NET is resulting in slower execution." To answer your question posed in your opening post of "Does anyone know why an Excel Macro would be faster than VB.net code?", the answer is Yes! If the Excel Macro is coded to run efficiently, and the VB.NET code isn't. But that probably doesn't give you much to go on, I suspect.
You need to post the actual code that is slow, and it would really help if you post the VBA macro code that isn't slow. Then we can compare and hopefully figure out where the issue(s) might be.
You DO need to have the Refresh if you are filling the textbox in a loop. The textbox will only update when it has a chance to paint, which won't happen until the loop finishes. With the Refresh, you'd see every change as it happens (though, in my experience, if there are enough changes fast enough, this will stop happening eventually). Without the Refresh, you'd only see the final value, without seeing anything along the way.
You DO need to have the Refresh if you are filling the textbox in a loop. The textbox will only update when it has a chance to paint, which won't happen until the loop finishes. With the Refresh, you'd see every change as it happens (though, in my experience, if there are enough changes fast enough, this will stop happening eventually). Without the Refresh, you'd only see the final value, without seeing anything along the way.
When I run this,
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
For i = 1 To 21
TextBox1.Text = i.ToString
TextBox1.Refresh()
Next
End Sub
All I see in the textbox is "21". I know it's updating the textbox but it's to fast to see. But I'm old, maybe your eyes are better than mine.
Last edited by wes4dbt; Jan 1st, 2025 at 10:03 PM.
With the following code I saw every update to txtBox1:
Code:
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim app As New Excel.Application
Dim wkb As Excel.Workbook = app.Workbooks.Open("D:\GolfScores.xlsm")
Dim wks As Excel.Worksheet = CType(app.Worksheets(3), Worksheet)
Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
Dim icnt As Integer
app.Visible = True
For icnt = 2 To 1032
If wks.Cells(icnt, 9).value = 1 Then
'Debug.Print(icnt)
'Debug.Print(wks.Cells(icnt, 9).value)
txtbox1.Text = wks.Cells(icnt, 1).value
'txtbox1.Refresh()
End If
Next icnt
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
app.Quit() '*** Closes Excel
Me.Close() '*** closes program
End Sub
End Class
Last edited by Shaggy Hiker; Jan 2nd, 2025 at 09:22 AM.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
For i = 1 To 21
TextBox1.Text = i.ToString
TextBox1.Refresh()
Next
End Sub
All I see in the textbox is "21". I know it's updating the textbox but it's to fast to see. But I'm old, maybe your eyes are better than mine.
It won't always update, even with Refresh. I've never spent any time trying to figure out why that would be. Refresh SHOULD cause execution of the loop to pause until the paint has happened, and usually it does....but if the loop is fast enough, sometimes it does not, which is really weird.
It won't always update, even with Refresh. I've never spent any time trying to figure out why that would be. Refresh SHOULD cause execution of the loop to pause until the paint has happened, and usually it does....but if the loop is fast enough, sometimes it does not, which is really weird.
I've seen that in IDE but never in the executable. Unless that is what you meant.
I can't remember whether I saw that only in the IDE or not. It was always an utterly trivial application, though, so it may never have made it to an EXE. That could well account for it.
Reply With Quote
Originally Posted by JimReid
