5

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.

R3uK
  • 14,417
  • 7
  • 43
  • 77
user2759242
  • 73
  • 2
  • 2
  • 4

2 Answers2

2

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.

Community
  • 1
  • 1
zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • Office 2010 actually. It seems that in the past you could create an add-in and then there was some method to modify that into a com-enabled DLL. If that is no longer the case, then I suppose that is a dead end for us. As I stated above, we are looking to move into .Net, C# or VB, at some point in the future. It's just a matter of convincing the higher ups to buy visual studio. I'll look into the web services method. It may be useful. – user2759242 Sep 08 '13 at 18:09
  • I'm pretty sure that Visual Studio Express is free for companies who have an Office License so long as it's not used to generate revenues. In your case, it seems like it would be ok. – Iggy Dec 05 '17 at 14:13
2

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.

Joe
  • 122,218
  • 32
  • 205
  • 338