VB.net compared to Excel macros

[JimReid]

I find it interesting and maybe you do too. I had a macro updating an Excel spreadsheet that updated about 1063 rows of individual golf holes (18 per round) for about 59 rounds. In Excel macro, it took about 10 seconds to update fully. I converted the macro code to VB.net and it took 10 minutes!! Naturally, I had to change some of the code because all macro code doesn't fit VB but it was the same code. About 1000 lines of code to check each hold as to the number of strokes and whether it was a par, bogie, or whatever, and keep track of the totals. The big difference in time surprised me.

[nkvb]

Even I am interested in knowing.
It seems anyone who has experienced and has gone similar situations like you can throw the light maybe MVP of VBA and VB.NET also can tell us why such a big difference of time. Even different versions of MS-Excel have been upgraded from time to time.
VBA Coded in MS-Excel 2013 will work in MS-Excel MS office Home and Student 2021 but some syntaxes had to be removed for smooth working as experienced.

Will not be able to reply to you on MS Office 365 because I am not using . I don't now which other factors are involved in getting the result for very big time delay

Thanks
nkvb

[jdelano]

I'd post the Excel file and maybe someone else can look and try some alternative methods.

[JimReid]

I'd post the Excel file and maybe someone else can look and try some alternative methods.

The problem with that is the macro is 1000+ lines of code as is the VB.net version.

[nkvb]

May be with the syntaxes of MS-Interop-Excel which opens the .XLSX or .XLSM file in the background. Need to check here first for the time consumption. I suppose the syntaxes of MS-Interop-Excel in vb.net needs to be correctly placed inorder
In VBA , the file is opened to execute the task and therefore could be faster.

I've not yet checked or experienced opening the File with macros written in it thru. vb.net. if this the case, I assume that, maybe Macro or VBA code its running its task.(Double Operation with coding thru vb.net). Just create your .xlsx file ie without the code(or.XLSM file) and run through vb.net.

or let us wait for more profeesional and technical reply.

nkvb

[jdelano]

Was just thinking that seeing the Excel file with its layout and all would allow and data (formulas) - you can strip the VBA from it of course. Either way.

[JimReid]

Was just thinking that seeing the Excel file with its layout and all would allow and data (formulas) - you can strip the VBA from it of course. Either way.

I did remove all the vba code and macros. I then saved the spreadsheet as a xlsx instead of a xlsm with no macros or source code, just the spreadsheets.

No change in the time to run. Takes about 10 minutes in VB.net to process 1063 lines on a single spreadsheet. The macro took about 1 minute.