Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Build Excel Add-in
Deze pagina in het Nederlands

Building an add-in for Excel

Introduction

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.

Download

I prepared a sample workbook for you to download.

Content

 


Comments

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

 


Comment by: WILLIAM FREUND (10/18/2016 6:15:32 PM)

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 (10/19/2016 8:14:13 AM)

Hi Bill,

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

 


Comment by: Anthony (1/24/2018 4:48:04 AM)

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 (1/25/2018 9:47:08 AM)

Hi Anthony,

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

 


Comment by: Anthony (1/25/2018 12:01:16 PM)

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 (1/25/2018 2:06:44 PM)

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 (1/26/2018 12:42:18 AM)

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 (1/26/2018 7:44:56 PM)

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 accoring to our Privacy Policy.