Subscribe
in a reader
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
- Specification
- Core functionality
- User interface
- Ribbon Callbacks
- Application events
- Creating the Add-in
Comments
Showing last 8 comments of 10 in total (Show All Comments):Comment by: WILLIAM FREUND (10/18/2016 6:15:32 PM)
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)
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)
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)
Very valid remark, I don't really now to be honest!
Comment by: Anthony (1/25/2018 12:01:16 PM)
"'" & 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)
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)
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)
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.