Adding Excel user defined functions the easy way

The Problem

I’ve developed a number of Excel User Defined Functions that I find useful in my day-to-day work, and that I’d like to have at my fingertips.

There’s a few ways I could achieve this. For example I could create an Excel Add-in, or I could create a PERSONAL.XLSB file and store the relevant functions in there.

The problem with both these methods is that the code is stored locally on my machine, so if I were to share the workbook I was working on, a colleague wouldn’t have access to the functions I’d embedded, and it wouldn’t work properly at their end. Therefore the code must be embedded in to the active workbook itself.

When adding a number of functions, it can be a little cumbersome.

The solution

I’ve written a short macro that automates the process of importing new modules in to a workbook. I then created a shortcut button that links to this macro and placed it in the Quick Access Toolbar, making importing new modules a breeze:

Download

Click here to download the module containing the relevant code.

Installation Notes

The instructions given in the video above are specific to Excel 2016.

You’ll need to first:

  1. Change your macro security settings as described in the video above.
  2. Display the Developer tab in the Excel ribbon as described here.
  3. Create the PERSONAL.XLSB workbook as described here.
Advertisements

One thought on “Adding Excel user defined functions the easy way

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s