Author Topic: Passing arrays from VSTO Addin function/sub to VBA  (Read 262 times)

0 Members and 1 Guest are viewing this topic.

Visisthebest

  • Full Member
  • ***
  • Posts: 128
Passing arrays from VSTO Addin function/sub to VBA
« on: May 27, 2020, 09:54:39 AM »
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

Nikolay

  • Hero Member
  • *****
  • Posts: 931
    • UnmanagedVisio
Re: Passing arrays from VSTO Addin function/sub to VBA
« Reply #1 on: May 27, 2020, 10:05:53 AM »
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

  • Full Member
  • ***
  • Posts: 128
Re: Passing arrays from VSTO Addin function/sub to VBA
« Reply #2 on: May 27, 2020, 10:13:38 AM »
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!

Visisthebest

  • Full Member
  • ***
  • Posts: 128
Re: Passing arrays from VSTO Addin function/sub to VBA
« Reply #3 on: June 24, 2020, 06:42:11 AM »
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/