Category: User Interface

Building an Excel Add-in

Posted on by jkpieterse

Hi there!

Only recently I read this quote somewhere: “If you want something done, ask a busy person”. I found two entirely different people as the originator of this quote: Benjamin Franklin and Lucille Ball. I wonder which it is…

Well, turns out I’ve been quite busy as of late. So I decided it was time to dust off some old stuff I prepared to add to my site but never came round to finishing (I must have become less busy when I was almost done :-) ).

If you’re about to embark on the journey to create an add-in out of a set of macro’s you have been using for some time now, this article is a nice read as it takes you through most of the steps needed when building an add-in for Excel.

Enjoy!

 

Jan Karel Pieterse

www.jkp-ads.com

 

AET Excel Utilities

Posted on by Andrew Engwirda

Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.

Update
So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!

I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.

But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.

So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).

Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).

Worksheet Tools

Rows And Columns

Formula Tools

Deletion Tools

Object Tools

Export Tools

Text Tools

Number Tools

Time And Date

Chart Tools

Path And Folder

Workbook Tools

Developer Tools

Fun And Games

Other Utilities

Cell Menu

Row Menu

Column Menu

Sheet Menu

Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.

In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.

The Amsterdam Excel Summit Last-minute discount

Posted on by jkpieterse

Hi everyone,

Our event is coming real soon now and we’re very much looking forward to it. We have outstanding speakers and excellent content, so everything is lined up to make this a superb Excel event.

To entice the undecisive Excel lovers to make up their minds and subscribe after all, we decided to make it even more attractive to attend.

As of March 27st, 2015 we offer a € 200 last-minute discount per attendee for both days and € 100 for one day!

Register now at http://topexcelclass.com/index.php/amsterdam-excel-summit/registration/ and meet us on April 13th and 14th in Amsterdam.

Regards,

Jan Karel Pieterse

www.jkp-ads.com

Recent Update of Office causes problems with ActiveX controls

Posted on by jkpieterse

Hi,

Yesterday, I installed a host of updates, including some of Office.
As it happens, I tried to add an ActiveX control to a worksheet and received an error.
After some research I discovered the cause of the error to be two-fold:

1. The controls were updated by the update
2. Excel did not clean up after itself properly and left some temporary files behind.

The solution is to:
– Quit Excel
– Open Explorer
– Select C: drive
– Search for *.exd
– Remove all files found.

Hope this helps other people who might be suffering from the same problem.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

#####UPDATE Dec 22, 2014#####
Microsoft has published a so-called Fixit to make resolving this matter easier:
https://support.microsoft.com/kb/3025036/EN-US?wa=wsignin1.0

Quick Access Toolbar Usage Survey

Posted on by Dick Kusleika

Chris Macro at TheSpreadsheetGuru surveyed some Excel users to see what’s on their QAT. Here’s my contribution:

I Don’t Use That Thing!

To my surprise there were a handful of Excel bloggers I reached out to who don’t use the Quick Access Toolbar at all! This includes the likes of Petros Chatzipantazis (Spreadsheet1.com & RibbonCommander.com), Andy Pope (AndyPope.info), Dick Kusleika (DailyDoseOfExcel.com), and Oscar Cronquist (GetDigitalHelp.com). Jon Peltier (PeltierTech.com) even went as far as to state that he “hate, hate, hates the QAT (it ain’t worth squat!).” I found this extremely intriguing and I hope these guys will share their philosophy on not making use of the QAT in the comments section below.

That’s good enough company for me. I don’t hate the QAT, I’m simply indifferent to it. I was at home when I responded to Chris’ request and when I got to work I noticed that I had added

and

, although I’m sure I’ve never used them. If I have used the speaking thing, I hunted for it on the Ribbon oblivious that I had added it to the QAT.

Incidentally (and uninterestingly) I use it extensively in Outlook. There’s no

so I have to have some way to get at those macros.

Where Are The Macros?

One of the biggest surprises for me was that there were not too many people running macros out of there QAT. I was especially surprised that some people who have dedicated blogs for VBA (cough, cough…Jordan Goldmeier….yeah I’m calling you out!) didn’t have one trace of VBA code hanging out in the QAT. I did get feedback from some stating that most of their macro code used on a regular basis was executed via assigned keyboard shortcuts and that does make sense. About 5 mouths ago I started to shy away from using shortcuts with my macros. Here was my reasoning:

Tell us how you use (or don’t use) the QAT in the comments here or at Chris’ site.

Celebrating my MVP award: Discount offer

Posted on by jkpieterse

Hi everyone!

Every quarter Microsoft announces who are the lucky ones to receive their Most Valuable Professional Award. An MVP award lasts a year, so for a quarter of the MVPs, October 1st is an important day.

I got re-awarded!

And to celebrate that I am offering a 3 day 50 percent discount on my Formula auditing tool: RefTreeAnalyser

From October 8, 2014 to October 10, 2104 you receive 50 % off of the list price when you enter this coupon code: MVP2014

Head over to my website and download the tool, you can try it for free!

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Pimpin’ My Site

Posted on by Andrew Engwirda

When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.

Having a vivid imagination, this is what went through my head.

Yo Dawg!

Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)

Here’s some stuff I’ve been working on recently.

A multi-field Find and Select/Replace tool.

AET Find and Replace

Although a bit old, (like me), some of the code came from this.

AET Cell Watch Form

Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)

An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!

AET Status Bar

And some games. (For the kids, but you can play too)
Grrr...
That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?

Copy Selection Sum to Clipboard

Posted on by Dick Kusleika

Last month I posted some metrics on the keyboard shortcuts I use. One of the pieces of code that I could not link to (because I’ve never posted it) is CopySum. I don’t remember what prompted me to write this little procedure, but it has been surprisingly useful. It sums the selected cells and puts that sum into the clipboard. That’s all it does.

Sub CopySum()

Dim doClip As MSForms.DataObject

On Error Resume Next

gclsAppEvents.AddLog "^+c", "CopySum"

Set doClip = New MSForms.DataObject

If TypeName(Selection) = "Range" Then
doClip.SetText Application.WorksheetFunction.Sum(Selection)
doClip.PutInClipboard
End If

End Sub

If I want to get a one-off sum of something and use it in another program, this comes in handy. I could SUM in a cell, copy that cell, paste it, and delete it. If I paste into Notepad, it’s fine, but if I try to paste into Outlook or even Gmail those programs try to get fancy and make an HTML table. Sometimes I just want the text.

One shortcoming of this procedure is that it doesn’t do well with filtered cells. The Selection includes both visible and hidden cells, but I probably only want visible. I’m changing the code to

doClip.SetText Application.WorksheetFunction.Subtotal(9, Selection)

so it works with filtered data.