Home Newsletter

Deze pagina in het NederlandsHome > Article index >

Building an add-in for Excel


Excel is a very powerful application with thousands of options and functions to help you build your models and reports and analyses. Oftentimes your daily job could do with some additional functionality. Something Excel does not offer "out of the box".

With VBA, augmenting Excel with new functionality is possible. This article demonstrates how I created a small utility. It outlines all steps I used to create the tool. The principles I used in these steps may help you when set out to build your own add-in.


I prepared a sample workbook for you to download.


  1. Specification
  2. Core functionality
  3. User interface
  4. Ribbon Callbacks
  5. Application events
  6. Creating the Add-in



Showing last 8 comments of 10 in total (Show All Comments):


Comment by: WILLIAM FREUND (18-10-2016 18:15:32) deeplink to this comment

The link on the "Building an Add-in For Excel" page that reads "I prepared a sample workbook for you to download" takes me to your "Downloads" page, however I am unable to locate an item in the list of Downloads that "sounds like" it refers to .... whoops, never mind, I just took another look at the download list and noticed the item called "SheetTools" with the notation that reads "this is the file that accompanies my article on 'How to build an Excel Add-in'.

Incidentally, I have been advised (by women) that my inability to locate things that they (the women) have subsequently been able to locate for me is a common condition known as "man looking". :-)

Keep up the good work lads and ladies,
Bill Freund

Comment by: Jan Karel Pieterse (19-10-2016 08:14:13) deeplink to this comment

Hi Bill,

LOL glad you were able to locate the file. I'll add a direct download link though.

Comment by: Anthony (24-1-2018 04:48:04) deeplink to this comment

Nice code to hold the onLoad pointer. But is the following safe in 64bit?

ThisWorkbook.Worksheets("Sheet1").Range("RibbonPointer").Value = ObjPtr(moRibbon)

What if ObjPtr is > than 2^48 (floating point). Would a CSTR help?

Also, what about storing it in an Application named ranges using XL4 -- then it is truly transient.

(The fact that any of this is necessary shows the contempt that Microsoft has for VBA.)

Comment by: Jan Karel Pieterse (25-1-2018 09:47:08) deeplink to this comment

Hi Anthony,

Very valid remark, I don't really now to be honest!

Comment by: Anthony (25-1-2018 12:01:16) deeplink to this comment

I changed the code to set the value to
"'" & Cstr(ObjPtr(moRibbon))

And then Clnglng the value back if Win64.

Seems to work Excel 64. (The issue is how memory is arranged, and whether in practice only the lower 48 bits is used.)

I am also thinking of using the XL4 approach to store it as an application Name,

SET.NAME("Ribbon Handle", ...)

(The ribbon API is appalling. Rather than just set a property, you catch an event to get a handle to fire an initialize event which can be caught to return the value!! Nothing learnt from the existing elegant APIs. At the very least they could provide Application.RibbonHandle. But they won't, because they do not care. Wot's hot and wot's not. VBA, the soul of Excel, is not.)

Comment by: Jan Karel Pieterse (25-1-2018 14:06:44) deeplink to this comment

Hi Richard,

Are you absolutely sure the conversion to Excel works properly if the address is larger than a 32 Long? I'm not sure whether CStr does that properly?

Agree with your comments about the Ribbon.
Seems internally they rigged the callbacks into VBA by using Application.OnTime given what happens if you are debugging code and the ribbon starts firing callbacks.

Comment by: Anthony (26-1-2018 00:42:18) deeplink to this comment

Yes, tested along lines of

dim x as longptr
x = 1
x = x * 256 * 256 * 256 * 256 * 256 * 256 * 256
print cstr(x)

Comment by: Jan Karel Pieterse (26-1-2018 19:44:56) deeplink to this comment

Hi Anthony,

Thanks. WOuld you be willing to show the full working code?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.