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

Do consumers prefer the ease of an EXE installer or the transparency of a ZIP file?

For all the various add-ins available from my website, I have supported two downloadable formats, an EXE and a ZIP file. The EXE is easier to install and includes an uninstall capability. The ZIP hopefully provides the consumer greater transparency and control over what is on their computer.

In addition to the software downloadable from my website, I also develop custom solutions through my consulting work. Most of my projects are sponsored by senior executives in companies or otherwise people with substantial decision making authority. It turns out that even a Managing Director or an Executive Vice President is subject to the automated IT protocols in effect in their organization (e.g., Group Policy).

Small and midsize companies are more tolerant of the kinds of files their employees can download. So, it is my larger clients who prefer — actually, require — a solution acceptable to their respective corporate IT filters. That means no EXE and no MSI, not even inside a ZIP file.

That has been a major stumbling block in my migration to .Net. For all the advantage of the platform, providing an add-in inside a ZIP file is not one of them.

I wondered if that inability (or reluctance) to download an EXE extended to those who download software from my website. Using Google Analytics I decided to check their download preference/requirement. Would the data indicate a strong preference for EXE over ZIP or the other way around? Turns out the result is decidedly mixed! As the table below shows, with the exception of TM Plot and TM TOCCreator, the download choice is about 50-50!

The table below is a PivotTable based on Google Analytics data for my website. For each add-in the table lists the EXE and the ZIP download percentages together with a sparkline for the two formats.

Excel 2013, SDI and modeless userforms

Hi Everyone,

With Excel 2013 we also got new document windowing in Excel; Microsoft decided to make Excel behave the same as Word:

from MDI:

Excel 2010 MDI interface showing two workbooks
The Excel 2010 MDI interface

to SDI:

Excel 2013 SDI interface showing two workbooks
The new SDI interface of Excel 2013

This causes havoc when one shows a modeless userform which should stay on top of all Excel windows:

Excel 2013 SDI can cause a userform to disappear
Excel 2013 SDI can cause a userform to disappear

I’ve devised a way to prevent this problem and written an article about how this was done.

Enjoy!

Regards,

Jan Karel Pieterse

www.jkp-ads.com

 

 

 

Power Utility Pak Add-in for the Mac

Hi all

I like to share the release of this new Mac Excel add-in with you all. After many days of hard work I have upload the first Mac version of the very popular Excel for Windows Add-in “Power Utility Pak” from John Walkenbach on my site.

Power Utility Pak for the Mac, Add-in Tools for Excel 2011

You can also use this link from John’s site to go to the Add-in

The Spreadsheet Page – PUP/Mac Home

As a bonus I also add my mail add-in to the collection. Please give feedback good or bad so I can make this product error-free and accurate.

Use the mail address that you find in the PUP About option in the menu if you want to report bugs or want to give feedback.

Thanks

Excel Services Interactive View

Analyze data with Excel on the web

Microsoft has introduced a new web based capability that extends its Excel Services offering.  This new capability provides a limited interactive view of any table in a web page.

An introduction to this service is below.  For those interested in additional capabilities and more advanced and useful capabilities see:

For the consumer:

Learn more about Interactive View

Analyze a table in any web page with a dynamic interactive view

For the developer:

Implement the Interactive View feature for 1 table

Implement the Interactive View feature for multiple tables

Improve the formatting and layout of the 'Interactive View' buttons

As an example, the image below shows a table, the Interactive View button, and the result.

For a live demo, developer tips, and more, please see http://www.tushar-mehta.com/publish_train/data_visualization/15c%20interactiveview/index.htm

Tushar Mehta

Model business dependencies in an Excel worksheet

The Civilization game dependency tree
How to build a dependency tree when there is no obvious connection between the business model and an Excel model.

A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance brought with it certain additional capabilities and benefits. Of course, there was a requirement before one could acquire an advance, particularly a set of pre-requisites. For example, pre-requisites for the “Navigation” advance were the “Seafaring” and the “Astronomy” advances. In turn, the Seafaring advance required one to already know “Pottery” and “Map Making”.

I created an Excel worksheet that let me use Excel’s Precedent arrows to understand the optimal path to specific advances as in Figure 1. The advances are shown in red and the benefit(s) of each advance are in black, blue and green. The Excel blue arrows show the pre-requisites for the Seafaring advance.

Figure 1

While the dated worksheet may be of limited value even to Civilization enthusiasts, the technique for creating the dependency tree is unique enough to be of value to Excel consumers. What makes it of value is that the game dependencies (in the context of work this would be the dependencies in the business model) had nothing to do with what Excel considers as dependencies! Consequently, to use Excel’s Trace Precedents feature I had to somehow map the model dependency into Excel formula dependencies.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0908%20CIV%20game%20dependency%20tree.shtml

Tushar Mehta

Excel Advanced Filter

Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom filter and Advanced Filter provides that capability. It is a data-driven mechanism that uses Excel formulas to extract specific information from the original data. For those who may have heard of SQL but have never been motivated to learn it, you can now leverage some of the power of SQL without learning a single word of SQL!

The layout of this document is as follows: 1) Introduction to the data set used in the examples, 2) Introduction to the Advanced Filter dialog box, 3) Filter using column headers, 4) Filter using Excel formulas, 5) Extract unique data, 6) Work with dynamic source data, and 7) Create a filter in a different worksheet or workbook.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

Tushar Mehta

Freeze Panes and Split Panes

When working with a worksheet that contains a large number of rows and/or a large number of columns with row and column headers, it is very helpful to always view the headers no matter where one scrolls through the document.

An example is shown below. The table lists the year-by-year carbon dioxide emissions by country (the data set comes from data download page at Gapminder – http://www.gapminder.org/data/). The current worksheet view shows data from the 1950s (columns GQ through GZ) and countries that are alphabetically towards the end of the list of countries (rows 235 through 259). At the same time, the column headers (row 1) and the row headers (column A) are still visible. This lets one quickly establish a context for the numbers. This is the result of using Excel’s Freeze Panes feature.

A complementary feature is called Split Panes. The key differences between freeze pane and split pane are the somewhat different behavior while scrolling and an easier way to adjust the panes, which, of course, comes with extra responsibility to manage the risk of accidental changes to the split panes configuration.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0201%20split%20freeze%20pane.shtml

Tushar Mehta