Increase VBA performance using compiled DLL files

[bradyboyy88]

Has anyone ever given this a try to see if this truly does speed up your code vs running functions natively:

https://analystcave.com/vba-compiler-add-in-to-vb-net/

Curious of everyones thoughts. Seems like an interesting concept if this is true that DLLs would run quicker.

 

[RoryA]

DLLs (and XLLs) do run quicker than bog standard VBA. The older versions of Office used to have a Developer edition that allowed you to create dlls and they certainly performed better, so I'd assume .Net versions have the same benefit.

 

[Kyle123]

With the added caveat of "it depends what you are doing", they're a lot faster assuming you aren't interacting with the Excel object model

 

[bradyboyy88]

We have created a userform that has everything built into it. It literally looks like a stand alone program and excel never even shows its face from open to close and its in its own instance of excel 100 percent of the time. A lot of times I am creating objects on the fly like labels and things of that nature and connecting to an access database via ado to store the data from this program. Multiple users use it simultaneously so making it quicker is key for us. So could DLLs of creating excel objects run quicker than creating the object in excel? Also, the slowest part of ado is opening the connection so I wonder if a DLL of opening the connection could be much quicker as well.

Basically this DLL concept has be very intrigued ha. So if you have done this its quite impressive and I want to pick your brain for these few things noted above

 

[Kyle123]

What does this mean?
So could DLLs of creating excel objects run quicker than creating the object in excel?

Honestly, if it’s a standalone application, you’d be better just writhing the whole thing in a proper language as a stand-alone application

 

[bradyboyy88]

What does this mean?
So could DLLs of creating excel objects run quicker than creating the object in excel?

Honestly, if it’s a standalone application, you’d be better just writhing the whole thing in a proper language as a stand-alone application

I couldnt agree more but we dont have visual studio so creating forms is just a pain with just code lol. I was referring to the fact that vba is quite slow at creating objects on the fly so i did not know if loading these functions in DLLs which create the object would up the speed at which they are created. Does that make sense? Sorry if I am explaining this poorly.

 

[Kyle123]

No, you couldn’t do that with a dll. Visual studio is free under certain licensing conditions if that’s what you’re concerned about

 

[bradyboyy88]

No, you couldn’t do that with a dll. Visual studio is free under certain licensing conditions if that’s what you’re concerned about

I work for the govt so its a bit more difficult to get software installed. All sorts of compliance issues as im sure you are aware. So why could that not be loaded into a DLL and how do you know when certain functions could be compiled to a DLL vs not such as you mentioned above? Thanks for the info.

 

[Kyle123]

You’d want the whole form done that way and then you load it as an addin. You’d use a dll typically where you want to d onothing that interacts with the excel model, so doesn’t access sheets, forms etc or you wanted to provide functionality that’s difficult to do with VBA. Complex financial or statistical functions that have simply inputs and outputs that don’t touch the worksheet would be good candidates. You can do other types of work too, like database interaction etc, but honestly it’s much harder than doing it in VBA unless you’ve got something like visual studio

 

[bradyboyy88]

That link I sent says it does the export of the DLL file so I figure it would be easy to implement but it sounds like from what your saying is that these functions I export would need to be more independent from the excel object model or MSForms object model since its loaded in VBA. Do you call these DLL files similar to that done for the windows api calls for certain functions?