Hi everyone!
If you write a lot of VBA code you probably sometimes run into performance issues. A customer rings you and complains that the Excel file you built for them does not perform well. I’ve written a small article on my site, inspired by a post here: Error Handling via an Error Class. I needed a quick way to log and report performance of subroutines and I also wanted to see how many times a routine was called.
Enjoy!
Jan Karel Pieterse
www.jkp-ads.com
Hi Everyone,
I’ve just added a new page to my site on how to synchronise slicers which point to different pivotcaches:
Synchronising Slicers
Enjoy the read!
Jan Karel Pieterse
www.jkp-ads.com
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:
A new dialog has been added that shows all sources of the objects in your file:
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):
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
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:
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):
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
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:
Enjoy!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
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.
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
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