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

An MSForms Treeview

If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.

There are a couple of problems with this Treeview control:

  1. Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
  2. The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.

Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)

The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:

Not bad, right?

Both Treeviews allow for checkboxes:

And both allow icons (windows Treeview not shown here):

You can also edit a node:

And expand and collapse nodes and navigate the tree using your arrow keys.

We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.

We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.

Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!

Regards,

Jan Karel Pieterse