Often I’m using a PivotTable to aggregate some data to use elsewhere. I’ll take a PivotTable that looks like this
and make it look like a grid so that I can copy and paste it somewhere else. To do that, I first go to the PivotTable Options – Display tab and change it to Classic PivotTable layout.
Then I’ll go to each PivotItem that’s a row and remove the subtotal
and check the Repeat item labels checkbox.
And I get a PivotTable that’s ready for copying and pasting.
After about 50 times of doing that, I got sick of it. Now I just run this code.
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo 0
If Not pt Is Nothing Then
.InGridDropZones = True
For Each pf In pt.PivotFields
If pf.Orientation = xlRowField Then
pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
pf.RepeatLabels = True
I created four workbook-level defined names in a blank workbook. Then I saved that workbook as Book.xlt in C:\Program Files (x86)\Microsoft Office 2010\Office14\XLSTART\. Now every new workbook will have those four named constants for use in formulas.
I put the chances that I will remember that they’re there/use them at about 20%.
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
becomes, in 2013 (and in 2010),
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),
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.
Dim aName As Name
For Each aName In ActiveWorkbook.Names
If Left(.Name, 1) <> "_" Then _
.Name = "_" & .Name
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.
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:
The target value can be a number or a reference to a cell that contains a number,
The add-in retains values previously entered in the dialog box, and
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.
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!
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.