Increase VBA performance using compiled DLL files

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top