Passing arrays from VSTO Addin function/sub to VBA

Started by Visisthebest, May 27, 2020, 02:54:39 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Visisthebest

I hope everyone is healthy and doing well during these extraordinary times!

With all that extra time at home I am looking at a way to speed up some processor-intensive VBA functions & subs.

For example a sub that does data manipulations/calculations then passes a set of arrays 'back' with the results (including UDT Arrays, these arrays are passed ByRef as arguments as that is the only way to pass multiple arrays back and forth, not best practice but works well within the limitations of VBA).

This code is easy to convert to VB.NET (Structs instead of UDTs etc), if I do so and move it in to an add in, will it be faster as compiled VB.NET and can I pass these arrays back and forth between a VSTO add in and VBA?

Say a single call to the Sub takes 10 seconds to execute now, is it reasonable to expect it to execute in 1 second as compiled VB.NET? :)

To be sure, this sub does not use the Visio object model I could move it in to Excel it would work as is. I understand VSTO Addins that do a lot with the Visio object model can be a LOT slower than VBA.

Your guidance and advice is very much appreciated! If there is a good chance of a significant speed boost this would be something to consider!

https://bettersolutions.com/vba/data-types/user-defined-types.htm
Visio 2021 Professional

Nikolay

I would say this sounds quite reasonable. I would try to do it, but without COM records (user-defined types), IMHO they are too messy.
If it is reasonable, you could just pack your data in array (or one big string) and then pass to VB.NET code, then unpack. And vice-versa.

VB.NET code is significantly faster that VBA by itself. So if you have some complex calculations, you may gain some benefits.
Although usually major performance improvements come from changing the algorithm that processes the data.

Visisthebest

Thank you Nikolay so I understand using regular Arrays is better, this is possible I could strip the sub to the bare bones data manupulation/calculations and just pass that back and forth.

Will start looking in to VSTO Addins exciting!
Visio 2021 Professional

Visisthebest

I didn't know about Parallel.For and Parallel.ForEach in .NET this can be used for part of the algorithm (no dependencies in these calculations) so even more of a potential speed boost.

https://algconsultings.wordpress.com/2010/09/13/visual-basic-for-application-vs-visual-studio-part-1-performance-review/
Visio 2021 Professional

Visisthebest

Quote from: Nikolay on May 27, 2020, 03:05:53 PM
I would say this sounds quite reasonable. I would try to do it, but without COM records (user-defined types), IMHO they are too messy.
If it is reasonable, you could just pack your data in array (or one big string) and then pass to VB.NET code, then unpack. And vice-versa.

VB.NET code is significantly faster that VBA by itself. So if you have some complex calculations, you may gain some benefits.
Although usually major performance improvements come from changing the algorithm that processes the data.

Nikolay I understand that in an add-in it is important to make sure that COM objects created are best disposed of as soon as you're finished with them (https://docs.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.marshal.releasecomobject?view=netframework-4.8). Any tips about this?
Visio 2021 Professional

Nikolay

#5
I believe it's going to work fine even if you don't do anything about it, the dotnet is smart enough not to break that easily :)
I mean, don't call ReleaseComObject unless you know what you are doing. You should be able to use Visio objects the same way you would do with VBA.

Basically, the thing is, COM (that is used by Visio) was created long before .NET; and it uses reference counting.
When there are no more references to an object, it's deleted. That's how VBA and macros work basically.

.NET does not use reference counting (it uses garbage collection approach); but it has a robust built-in mechanism that allows using COM objects (such as Visio API) from a .NET code.
It does not release COM objects immediately though, like COM does, since it does not count the references. But eventually, it does.
This means, some of your COM object may live until the time Visio is closed. But most cases, you shouldn't care.

The recommendation from my side could be, don't use Visio objects in secondary threads (like Prallel.For) in your add-in,
and make sure all secondary threads (background operations) are finished before Visio is closed. Basically, that should be good enough.

Visisthebest

Ok yes thank you Nikolay, I was worried that COM objects might keep the Visio EXE from closing properly if they haven't been 'cleaned up' by setting the reference counter to 0, and Visio.exe might hang in a background process after the user closes the application.
Visio 2021 Professional

Nikolay

If everything else fails you could try this "cleanup" (the repetition is important).
After executing this (for example, in OnUnload), all disposed COM objects that survived that far but whose deletion is pending should be released:

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

May it be a light to you in dark places, when all other lights go out  :D

Visisthebest

Thank you very much Nikolay I am scared to do anything with garbage collection (a black box to me) this is very useful if I see Visio.exe hanging to see if there might be COM objects still 'hanging around in memory' blocking the proper closing of the Visio process.
Visio 2021 Professional

Visisthebest

Did you see this article Nikolay:
https://www.add-in-express.com/creating-addins-blog/2020/07/20/releasing-com-objects-garbage-collector-marshal-relseasecomobject/

I have a hunch this is about dealing with the unpredictable behavior of older versions of office and COM and .NET garbage collection, but it did scare me a bit on the potential for weird side effects and hanging Visio.exe processes.
Visio 2021 Professional

Nikolay

I kind of prefer to deal with problems when I see them :D
Do you have a real issue (Visio crashing, not closing, hogging memory), or just assuming something bad may happen in the future?  :D

Visisthebest

Reading up on potential issues, so not bumped in to anything yet but want to make sure I don't start with the wrong design then spend tons of time debugging. Thank you Nikolay!
Visio 2021 Professional

Nikolay

From my side, I can say that even without doing ReleaseComObject or GC specifically, it looks pretty solid. At least with the add-ins I've built so far.