The Amsterdam Excel Summit

The Amsterdam Excel Summit

A Unique Opportunity

Dear Excel lovers,

Is Excel the first and last application you use every day?
Do you want to improve your Excel skills and get first-hand knowledge from the absolute best Excel experts in the world?
Then this unique event is for you.

The Amsterdam Excel Summit

Worldclass Excel Experts

An absolute unique group of Excel MVP’s will gather in Amsterdam to share their expert knowledge with you. The Excel MVP’s happen to be in Amsterdam for a meeting and we’ve succeeded in getting some of them to present at our event. There is not much chance on this happening again anytime soon, so make sure you register!

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

Formula Auditing by RefTreeAnalyser: Objects included

Hi all,

I’ve been working on my RefTreeAnalyser again. What I’ve been up to this time is building tools which help with the analysis of dependencies which are mostly hidden from view:

  • Charts (series formula)
  • Pivot table (source data)
  • Data Validation formulas
  • Conditional Formatting formulas
  • Form controls (linked cell, listfillrange)
  • ActiveX controls (linked cell, listfillrange)
  • Picture objects (linked cell)

A new dialog has been added that shows all sources of the objects in your file:

Objects analysed for cell dependencies

Moreover, when you analyse a particular cell for its dependencies, objects are taken into account too (well, to be perfectly honest, only if you purchase a license):

RTAObjectsInRefs

If you haven’t already done so, why don’t you head over to my website and download the tool. The demo is free and (almost!) fully functional.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Improving the Trace Precedents Experience

Hi all,

I’ve been working on my RefTreeAnalyser in the past weeks. One thing I’ve been working on is improving the not-so-intuitive way Excel displays Precedents using arrows, especially if a cell has mutliple off-sheet precedents:

Excel's way to show precedents

Notice that silly goto dialog (which you get when you double-click the off-sheet arrow with the tiny “table” icon next to it)?
Prize question: which worksheets and ranges are the entries in the Goto listbox pointing to?

I redesigned that “experience” to this (I manually added the red call-outs to this screenshot to explain what is what):

My way to show Precedents

Note how off-sheet precedents are represented by a picture of (part of) the range in question. A nice way to enable you to see what the precedent values are.

The boxes contain a hyperlink to that range so a simple click takes you there for further inspection. On hover with your mouse you’ll get the precedent’s source address in a tooltip.

If you like this idea, why not head over to my site and download the demo version so you can try it and shoot some comments at me?

There is one snag: the new feature only works for Excel 2007 and up.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

A new tool: Trusted Document Manager

Hi everyone!

I have just published a new tool today, Trusted Document Manager. This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro’s on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers to possibility to remove files which no longer exist from the list.

This is what the tool looks like:

ScreenshotOfTrustedDocManager

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Office 2013 VBA help files

Hi All,

Microsoft announced today that the Help files for Office 2013 VBA have been made available for download.
The help files are for:

Access
Excel
Office Shared
OneNote
Outlook
PowerPoint
Publisher
Word
Visio

Note that these files do not offer context sensitive help (F1), they are presented as stand-alone help files which you have to open manually.

I invite you all to give your opinions about VBA help. Make sure you include the Office version with your comments.

Queen’s day sale

As some of you may know, I’m Dutch and live in “The Kingdom Of The Netherlands”.
Well, tomorrow is an important day for the Dutch people: Queen’s Day.

More so, tomorrow is an extra special day.

Not because our Queen abdicates to make room for King Willem-Alexander, but because my 24 hour “Queen’s Day sale” is held. :-)

Anyone who places an order for my RefTreeAnalyser between April 30, 2013, 8:00 AM GMT and May 1st, 2013 8:00 AM GMT will receive a 50 % rebate on the list price.

### Update: Well, it’s been a great day yesterday and the Queen’s day sale has ended.###

I’m also going to give away some of the licenses: I’ll randomly select 1 out of every 10 orders placed in that period of time. These people will receive their license for free!

Note that you can head over to my website now and download and install the tool to try it, most of it is fully functional without the license code, you just get a nag screen now and then.

Have a nice Queen’s day (which was held in my home town in 2011) and enjoy a taste of our “orange fever“!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

RefTreeAnalyser goes 64 bit (and full circle)

Hi all,

Some of you will be aware of my “killer” product called RefTreeAnalyser.
It is a tool which makes it easier to navigate the precedents and dependents of a cell.

Let me tell you the tale of how this tool has gone full circle.

The original tool was written in VBA. I decided I wanted more protection of my intellectual property so I ported the code to a VB6 dll. All was fine.

Along came Excel 2010 64 bit and the trouble started. First I thought, hey, lets port this thing to a .NET solution. In between paid work (which -luckily- is rare) I started re-writing the code so VB.NET would stop complaining. Quite some time passed. Think years rather than months. Not very many people have 64 bit Excel you know. But it more or less worked. On my system.

Then Excel 2013 arrived, with a new “App” model built on Javascript. I decided a further investment in learning all the intricacies of deploying .NET Office add-ins was a waste of my time. Apps are the next bee’s knees for Office.

So now what? I know VBA. I decided to port the tool back to VBA. But 64 bit office prevents me from using the treeview control from the common controls library. Step 1 involved building an all-VBA treeview. Thank you Peter Thornton, couldn’t have done it without you :-)

Once that treeview was reliable enough, porting the RefTreeAnalyser back from VB6 to VBA was relatively straightforward. And we’re full circle.

I’m looking for beta tester. Any takers?

If so, send me an email (address at the bottom of each page of my website)

Regards,

Jan Karel Pieterse
www.jkp-ads.com

An MSForms Treeview 2: Ready for beta testing

Hi everyone,

Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart.

This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still waiting for test results.

Peter Thornton (thank you Peter!) jumped in on the project enthusiastically and really made a difference adding all sorts of usefull stuff and optimising the code for performance.

Now we’re ready for beta testing.

Please visit this page of my website for a description of the control and a download file which includes a demo userform implementing the treeview classes we built:

An MSForms (all VBA) treeview

Tell us what you think of it (oh, and please report bugs too!).

Regards,

Jan Karel Pieterse
www.jkp-ads.com