CODEWITHSUNDEEP
vbadll
user2759242
user2759242

Reputation: 73

Create a DLL using VBA editor

I have a position where I essentialy write Visual Basic Macros full time to support a number of divisions in the company. I've done quite well, learned quite a bit, and enjoy my position quite a bit.

We're laying out plans for the future, and while I'm quite good at searching for information I need for these projects (and stack overflow has been a great resource in that), I've come up dry on something I've been trying to do for a bit.

Here is the lay of the land. We distribute macros attached to various other programs via a network drive that everyone in the company has access to. These macros also interface with and automate a proprietary system our company uses. As such, when slight alterations to that system are made, we have to make alterations to a large number of macros in order to stay up to date.

As such, what we would like to try to do is to make a DLL of methods for interacting with our proprietary system. We would like to place that DLL on the network drive, and set the reference for all our macros there. Unfortunately, we do not, at least for the moment, have access to visual studio. All we have access to as far as development environments go is Visual Basic 6.5 as it comes packaged with office. We're working on getting more, but any of you that work at a large company know how that goes.

So, I'd like to know if it is possible to create a DLL using the visual basic editor, and if I should expect any issues having dozens of macros potentially accessing it around the same time.

Thanks in advance for any help or advice you may be able to offer.

Upvotes: 5

Views: 14103

Answers (2)

to StackOverflow
to StackOverflow

Reputation: 124746

Although you can't build a DLL with VBA, you can build an Excel Add-In (.xla) with shared macros and store it on your network drive.

Upvotes: 2

zedfoxus
zedfoxus

Reputation: 37099

I assume you are using Office 2007 products. VBA is a scripting language, using which you build your macros. DLLs are compiled assemblies. VBA editor will not allow you to create a DLL.

You could use Visual Basic (not Visual Basic for Applications) to create a DLL and then reference them in your macros.

You can also use .NET to create an unmanaged export library as mentioned in this post and then reference it in your macros. Although not necessary, I'd recommend using Visual Studio to build a DLL.

I'll state the obvious - why not invest in building a .NET application (or Java or something more enterprisy) than maintaining macros? You may be able to build a flexible configuration so that when your proprietary system changes, the application intelligently changes its parameters also. This may take time but it will reduce maintenance through macros.

It is possible that you don't have a choice. In that case, I'd recommend splitting out your macros in a way that only one or two have to change instead of many. Reduce dependencies appropriately and all the change-prone macros could be brought into a single location. Again, that may not be feasible. In that case, take all the macros and combine them into web services (you could use open source PHP/Python etc. if Visual Studio/.NET isn't available) and then change your office products to call web services. A post about calling web services in excel is here.

Upvotes: 2

Related Questions