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.
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)
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:
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.
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!
One of the things he did in his presentation was show the equivalent of an Excel bubble chart. He showed how different countries measured over the years. He also created a trail showing how a country progressed over time.
I decided to do the same with an Excel bubble chart – and implement both capabilities, i.e., the time snapshot and the time trail tracing the path, without any VBA code! The example I used was data from one of a series of seminars I had taught to healthcare executives. They participated, in teams, in a real-time, interactive, web-based simulation. In the simulation each team made decisions about how much of their limited resources to invest in (1) product development and operations and (2) marketing and sales. Their profitability depended both on their own decision and also their competitors. The simulation typically lasted 10 to 12 periods. The scroll bar in each chart controls the period shown or the latest period, as appropriate. The checkboxes control which teams have their performance history traced in the chart.
While I implemented the solution in Excel 2010, it should work in Excel 2007 and Excel 2003, though, in all fairness, I haven’t verified the older versions.
Have you ever experienced the dreaded “Circular reference warning” popping up when you opened an Excel file or entered a formula?
Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll tried to demystify that message in a new article: