Building an Excel Add-in

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

 

The Amsterdam Excel Summit Last-minute discount

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

The Amsterdam Excel Summit 2015

Hi Excel lovers!

Last year we had a terrific Excel event in Amsterdam in May. This year we’re in for a repeat!

I have just opened registration for what is going to be the place to be for anyone Excel-minded. We have two days full of excellent subjects. An impression:

  • Three in-depth Power Query sessions
  • Two sessions on improving your spreadsheet quality
  • Two sessions on charting, making your life easier and enabling you to build charts you didn’t even know you could
  • Two sessions on pivot tables and formulas
  • A session on how to build UDFs

So why don’t you book your flights and hotels and join us on April 13th and 14th for an unsurpassed Excel experience!

Regards,

Jan Karel Pieterse

Recent Update of Office causes problems with ActiveX controls

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

VLOOKUP & INDEX/MATCH Are Both Badly Designed Functions: Here Are Some Better Ones

It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.

So I decided to design and build some better ones.

VLOOKUP INDEX/MATCH problems

Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems

  • Slow exact match (linear search)
  • Approximate sorted match is the wrong default 99.9% of the time and gives the wrong answer without warning
  • Cannot do exact match on sorted data (well they can but only if they ignore sorted!)
  • Numeric VLOOKUP answer column easy to break
  • No built-in error handling for exact match
  • VLOOKUP very inflexible
  • INDEX/MATCH more flexible but still limited
  •  …

MEMLOOKUP/MEMMATCH – easier and faster alternatives to VLOOKUP/MATCH

MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )

The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!

  • Defaults to Exact Match on both unsorted and unsorted data
  • Use either column labels or numbers
  • Fast exact match on both unsorted and sorted data
  • Automatic optimisation of multiple lookups within the same row

So you want more flexibility? Try the AVLOOKUP/AMATCH family of functions

It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.

  • Lookup using any column
  • Lookup using more than one column without slow concatenation
  • Lookup the first, last, Nth or all results on both sorted and unsorted data
  • Lookup both rows and columns (2-dimensional lookup is built-in)
  • Built-in error handling for exact match
  • Return multiple answer columns
  • Case-sensitive lookup option
  • Regex match option

Lookups1

Try them out for yourself

These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.

Download FastExcel V3

If you like them then ask Microsoft to add them to the next version of Excel!

I would be delighted to tell the Excel team how I built these functions and the algorithms they use.

By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.

 

 

Celebrating my MVP award: Discount offer

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

FastExcel V3 Released – At Last!

As some of you may know I have been working on developing Version 3 of my FastExcel product for more years than I care to remember.
First there was the VB6 blind alley, then 64-bit and multi-threaded calculation and that ribbon thing, then I detoured into learning C++ and XLLs, then there was the inevitable scope creep, (and my consulting customers kept asking me to do things, and then there was the sailing) … you get the picture.

Anyway I finally pushed the release button on June 4 2014. Yippee!!!

The initial reaction has been great (no doubt helped by the introductory offer of 50% off).

So what is FastExcel V3?

There are 3 major products in the FastExcel V3 family, targeted at different types of use scenarios.

FastExcelV3

FastExcel V3 Profiler

The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation

  • Profiling Drill-Down Wizard
  • Profile Workbook
  • Profile Worksheet
  • Profile Formulas
  • Map Cross-References

FastExcel V3 Manager

FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks.

  • Name Manager Pro – an invaluable tool for managing Defined Names and Tables
  • Formula Viewer/Editer – a better way of editing and debugging more complex formulas.
  • Sheet Manager – Easily manage and manipulate worksheets.
  • Workbook – Cleaner – Trim down any excess bloat in your workbooks
  • Where-Used Maps – See where your Defined Names, Number Formats and Styles are being used

FastExcel SpeedTools

SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations

  • Calculation timing tools for workbooks, worksheets, and ranges
  • Additional calculations modes to enhance control of calculation so that you only calculate what needs to be calculated.
  • 90 superfast multi-threaded functions
  • Faster and more powerful Lookups and List comparisons
  • Multi-condition filtering and Distinct formulas to eliminate many slow SUMPRODUCT and Array formulas
  • Enhanced functions for Array handling, text, mathematics, sorting, information and logic

If you want to find out more just go to my website

or Download the 15-day full-featured trial of FastExcel V3