Create shared library of VBA functions in Excel

2

At our company we have a lot of different Excel files to keep track of our projects. We would like to implement some VBA functions in these files, written by us, the IT department. We want it so that we can write the functions, then place them in some sort of central library on the server, so that users can use them in all existing and future Excel files without having any advanced knowledge of Excel.
Ideally they would use these functions just like they now use the existing Excel functions like Sum(), Avg(), Dayofweek(),... So they would just type in a cell =VBAFunction1() and the function will be used.

Is there any way to centralise this or will we have to copy every function into the VBA section of every Excel worksheet?

Nils Tiebos

Posted 2016-01-19T09:10:28.377

Reputation: 165

Answers

1

You can write your library like a COM DLL in C#,VB.NET or older VB. This library need to be registered with regsvr32 and can be used in all VBA / VBScripts like this:

Dim oLib : Set oLib = CreateObject( "myLib.myInterface" )

where myLib is the name of your myLib.dll.

duDE

Posted 2016-01-19T09:10:28.377

Reputation: 14 097

Doing it this way would allow the functions to be available in the suggested functions pop-up? Or will that entirely depend on Public and whathaveyou? – Raystafarian – 2016-01-19T19:25:06.940

1

@Raystafarian: All functions of the DLL are available over the self-defined interfaces: https://msdn.microsoft.com/en-us/library/office/bb687915.aspx

– duDE – 2016-01-19T20:57:03.997

Asked: 2016-01-19T09:10:28.377

Viewed: 1 494 times

Active: 2016-01-19T10:01:10.987