Daily Dose of Excel

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree.

The following in 2003

Figure 1

becomes, in 2013 (and in 2010),

Figure 2

The basic problem is that names that are legitimate names in Excel 2003 may become unacceptable in 2007 (or later). But, a more devastating problem is with a formula using a name with a dot in it. Even though it is completely legitimate, Excel changes the dot to a colon. This causes the formula =SW1.SW2 to become =SW1:SW2. Don’t ask me why. It just does. The result is the formula is all wrong and destroys the integrity of the workbook.

It appears that the cause may be Excel trying to help manage the transition of a XLS workbook into the newer format. In 2007, Microsoft increased the number of columns from 256 to 16,384. Consequently, the reference to last column went from IV to XFD. So, a name such as SW1, completely OK in 2003, became unacceptable in 2007. On converting a XLS file to a XLSX file, Excel will convert such names by adding an underscore at the start of the name. But, it seems to go beyond that, converting formula references to certain names with dots in them to a colon. This happens if both the tokens to the left and to the right of the dot could be legitimate cell references. So, Excel converts the formula =XFD1.XFD2 to =XFD1:XFD2 but it will leave =XFD1.XFE2 alone.

To replicate the problem:

• Start with Excel 2003. Create a workbook and add the names shown in the Figure 1. Save and close the workbook.
• Open the workbook in Excel 2013. Save it as a XLSX file. Acknowledge the warning message (see Figure 3),

  
  Figure 3

• Close and reopen the new XLSX workbook. The formulas will have the errors shown in Figure 2.

The safest way to work around this problem is to add an underscore before every name in the workbook before making the transition to the 2007 format. Obviously, the quickest way to do this would be with a very simple VBA procedure. But, through trial and error I discovered the code will not work in 2003. It runs without any problems but it doesn’t do anything!

So, the correct way to use the code is the following sequence.

• Open the XLS file in 2013 (or 2010).
• Run the macro below.
  Option Explicit
  
  Sub fixNames()
      Dim aName As Name
      For Each aName In ActiveWorkbook.Names
          With aName
          If Left(.Name, 1) <> "_" Then _
              .Name = "_" & .Name
          End With
          Next aName
      End Sub
• Now, save the file in the newer format. If your original workbook had no code in it, save the file as a XLSX file and acknowledge the warning that the VB project will be lost.
• Close and reopen the file. You should see the correct data with all the names now starting with an underscore.

  
  Figure 4

Tushar Mehta

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

TM Goal Seek enhances the existing user interface to Excel’s Goal Seek feature. The built in Goal Seek is a simple optimization tool that suffices for a large number of scenarios. The UI, unfortunately, is extremely unwieldy and unfriendly. TM Goal Seek is a simple add-in that is easier to use than the default dialog box because of three critical benefits:

1. The target value can be a number or a reference to a cell that contains a number,
2. The add-in retains values previously entered in the dialog box, and
3. One can interact with the worksheet even with the dialog box open.

The motivation to develop the add-in came from work I was doing for a client that involved risk analysis on multiple investment scenarios of financial derivative trades. The default Goal Seek interface took way too long.

The current version of the add-in will expire on May 1, 2013.

For more see http://www.tushar-mehta.com/excel/software/goal_seek/index.htm

Tushar Mehta

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

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.

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:

The Excel 2010 MDI interface

to SDI:

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

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

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

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:

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

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 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