Daily Dose of Excel

Hey, if you’re within 5,000 miles of Sydney, Australia next month, be sure to sign up for the Excel User Conference.

Australian Excel User Conference

I’ll be presenting some of the sessions and I’m pretty excited about that. I’m less excited about spending 18 hours in coach, but at least I’ll finish that book I started two years ago.

I’m going to finish up the outlines for my lectures this weekend, so I’ll post those when they’re done. See you down under!

From Carlos:

Carlos Quintero (MVP) has just released MZ-Tools 6.0 for VS.NET (http://www.mztools.com/v6/mztools6.aspx). MZ-Tools is a productivity add-in for Visual Studio .NET that supports VB.NET, C#, Visual J# and C++ (partial support) and adds 40+ features to the IDE to locate code faster, to code faster, to design faster, to generate documentation and to enhance your IDE experience. This new version targets VS.NET 2002, 2003, 2005 and 2008 (aka “Orcas”) in a single version.

I use the free 3.0 version for VBA and it’s great. You can get both at mztools.com.

The folks at Viziware have released an update of Navipane, previously discussed on DDoE here. Via email, they said

I would just like to inform you that we have an updated version of Navipane available, which fixes a lot of the problems that you mentioned in your blog post, and more. Additionally features include a much more powerful Sheets Organizer, History Complete, Integrated Search, among others.

Here’s a quote from their website:

45-Day Money Back Guarantee. We don’t want your money if you’re not happy.

I like that attitude. I also note they have a 30-Day Trial period, which is great. If you’re interested in purchasing Navipane, they’ve generously offered a discount to Daily Dose readers. Use the coupon code 40DISCOUNT to get 40% off all of their products.

Note: I don’t work for Viziware. I don’t get anything if you buy any of their products. They gave me a license to Navipane earlier this year so that I could write a review about it.

A while ago, Patrick O’Beirne sent me a copy of his book Spreadsheet Check and Control to review. I read it, but I never reviewed it. It’s a very well written, well organized book and I recommend it. But book reviews are hard and I’m a little lazy. Needless to say, I owe Patrick one. To try to make it up to him, I’m publishing the following press release for the EuSpRIG 2007 Conference.

EuSpRIG 2007 - Spreadsheet experts meet in London

Experts From Europe, United States & Canada Meet to Discuss Enterprise Spreadsheet Management issues at the 8th Annual Spreadsheet Risks Conference.

Bury St Edmunds, UK - 3rd June 2007 - The European Spreadsheet Risks Interest Group (EuSpRIG), in association with Compassoft, is holding its 2007 conference on the theme of “Enterprise Spreadsheet Management: A Necessary Evil?” on the 11th - 13th July 2007 at Greenwich University, London, United Kingdom. Keynote speakers include Professor Ray Panko of the University of Hawaii, who will be speaking on “Spreadsheet Errors - What the Research Says”, Dean Buckner of the Financial Services Authority who will be giving a regulatory update on the use of spreadsheets in the financial markets, and Paul Bach, CEO of Compassoft, who will be outlining the state of technology identifying and managing spreadsheets in enterprise environments.

“Many studies have shown that significant sums of money are lost by organisations of all sizes because they fail to check thoroughly that spreadsheets, critical to their business, are free of material error”, said Patrick O’Beirne, EuSpRIG chairperson. “EuSpRIG was formed in 1999 to address this and related issues. The annual conference is an excellent place for leaders in this field to come together to discuss the issues and agree on best practices” he continued.

“Finance teams and auditors everywhere know that there is a very high likelihood of multiple material errors in key spreadsheets that are used every day in their company. Compassoft is proud to work with EuSpRIG to raise this issue to a top priority among CFOs, Controllers, and Audit Firms”, said Paul Bach, CEO of Compassoft, the main conference sponsor.

EuSpRIG 2007 is a forum for business people, regulators, auditors, academics and other interested parties to share information and ideas about the management of spreadsheet risks, related problems and opportunities.

There are 18 papers and presentations in this year’s conference. Topics include “Enterprise Spreadsheet Management - A Necessary Good”, “Impact of Errors in Operational Spreadsheets” and “Risk Management for Complex Calculations”, Speakers and delegates are expected from all over Europe, North America and Australia including representatives from Lloyds TSB, HMRC UK, Shell, and universities worldwide.

Further information about EuSpRIG, including the conference programme and booking information is available at www.eusprig.org

About EuSpRIG

EuSpRIG is an organisation dedicated to informing organisations about the material commercial and financial risks involved in the uncontrolled use of untested spreadsheets created by end-users who are not experienced in developing information systems. EuSpRIG is a not-for-profit organisation governed by an elected committee operating under the terms of a written constitution. Committee members include senior managers and directors of leading accounting firms and senior academics from European Universities.

EuSpRIG 2007 Main Conference Sponsor - Compassoft, Inc. Compassoft reduces financial risk by discovering, validating, monitoring and controlling critical financial data files residing on departmental servers and desktop computers worldwide. Compassoft Enterprise is the industry’s only solution that automatically creates a comprehensive analysis of all spreadsheets, databases and reports distributed across your entire organization, including those within and outside of document management systems. This easy to use and deploy software creates and documents a comprehensive controls structure for key distributed spreadsheets, data bases and reports found across your company’s divisions and reporting entities.

EuSpRIG 2007 Conference Co-Sponsors
The following organizations are pleased to support the EuSpRIG 2007 Conference: SecureXLS, Baker Tilly, Spreadsheet Engineering Ltd, AuditNet, Systems Modelling, Information Systems Audit and Control Association - Northern Chapter, University of Wales Institute Cardiff, University of Greenwich.

EuSpRIG Contact
Grenville Croll, EuSpRIG Membership Secretary, c/o
Spreadsheet Engineering Ltd, 63a Churchgate Street, Bury St
Edmunds, United Kingdom, +44 (0) 1284 748020,
grenville@spreadsheetrisks.com.

There are a couple of applications out there that simulate the Excel 2003 environment in Excel 2007.

The first is Toolbar Toggle. I didn’t have much luck with this one. Installing it took a half an hour because it had to install the .Net 2 Framework or some such thing. When it was finally installed, it gave me a hideous splash screen that I had to dismiss before I could do anything in Excel. Once I got to Excel, it looked like this:

Granted, this screen isn’t maximized so I could produce a reasonably sized picture, but Toolbar Toggle sure seems to eat up the real estate. I hit a few buttons, but didn’t give it a real workout. I closed Excel and was going to uninstall it so I could test out the next one. Then I got to thinking: I wonder what the ‘toggle’ means. Maybe there’s an easy way to hide that toolbar so I only give up screen real estate when I want to. I fired Excel back up, but it hung on Toolbar Toggle’s progress bar. I let it sit for about seven hours. When I came back, the progress bar was gone, the splash screen was nowhere to be seen, and Excel was as frozen as Nebraska in January. I killed the Excel process, restarted, and got more of the same.

Having had quite enough of that, I uninstalled it (after which Excel started up normally), and installed Classic Menu for Office 2007. It installed very quickly, took no more screen real estate than the ribbon, and didn’t hang when I exited and restarted multiple times. I also tried out some buttons, and bold and italic work just like expected. Here’s what entry #2 looks like:

With Toggle Toolbar, I was able to customize the ribbon very similar to how I would do it in Excel 2003. I could find no such facility in Classic Menu.

Classic Menu uses 2007 controls that don’t exactly simulate the 2003 experience. Hence, there are no tear way toolbars. I didn’t get a chance to test that in Toggle Toolbar.

Toggle Toolbar is reasonably priced at USD20.00 with very generous volume discounts (they start at two copies). Classic Menu is USD30.00, although you can get just Excel for USD16.00. They both have pathetic trial periods for which they should be ashamed (10 and 15 days).

So which would I choose? Neither, of course. If you’re going to use Office 2007, then take 20 minutes and learn to use the ribbon for goodness sake. “Oh, but I’m so productive with the old menus.”, I’m sure you’re saying. I’ll bet in high school you could code the hell out of Pascal too. When was the last time you wrote a Pascal program?

However, if you’re so inclined, I had the best luck with Classic Menu. If anyone else has tried either, please leave a comment.

I’ve been using Navipane from viziware for the last couple of days. It’s a workbook analyzer and navigator. It also works in Word and Powerpoint, but I didn’t try it with those. Here are some thoughts I have about it:

Trials: Here’s some advice to anyone who makes software: Thirty day trials of full functioning software is the minimum. You have to get the user used to using your product. As a user, I’m in evaluation mode for the first few days. Only later am I really using your product. If I use it for ten or 15 days, I can very easily discard because I’m not quite invested enough. However, after a month or 45 days, I’m still using it because it’s useful. Maybe I even have some spreadsheets that depend on it. Or maybe I’ve become so spoiled by that one feature that I don’t want to give it up. Now you’ve got me.

Viziware drops the ball on the trial. It’s only 15 days and it only works on the first two workbooks you have open. If one of those workbooks is Personal.xls, or any other hidden workbook, you’re down to one.

Keyboards: Oh, you knew this one would be near the top. Navipane has pretty good keyboard support. It’s better than most add-ins I’ve seen, but still not perfect. They have a page in the Options dialog that allowed me to enable or disable shortcuts, but not change the actual shortcuts. The shortcuts allow me to move around the pane easily, but there are a few things I couldn’t do. For instance, I could navigate to a Favorite, but I couldn’t open it without my mouse.

There is a keyboard shortcut to show/hide the pane, which is great. There is also a feature called QuickToggle which “minimizes”, but doesn’t hide, the pane. I’m not sure what the difference is, but it’s nice to get it out of the way when you’re not using it.

Unfortunately I had to disable shortcut keys. When I navigate to a cell with data validation, I use Alt+Down to expand the DV list. That’s a shortcut key defined in Navipane, so it doesn’t work for DV. I’m not sure if the right answer is to be able to disable shortcut keys one at a time, remap keys, or simply have pane-navigation key combinations revert to normal when the pane is hidden.

Screen: Screen real estate is pretty darn important to me, and apparently to viziware too. They add a menu item to the menubar, which I don’t mind, and the “pane” that is navipane can be hidden and resized. That’s about all I can ask for out of a program, so they good marks on this point.

Home

First, hidden workbooks should not show up here. I’ve hidden them for a reason. Maybe an option.

In general, this tab held the least intrigue for me. I picked a particularly egregious workbook to illustrate my point. With the volume of stuff that Navipane is tracking, it’s not very usable to me. When I expanded the tree to see the 41,000 formulas, it took some time. I don’t know how much because I went and did some other stuff, but it wasn’t quick. And once expanded, it was of little use to me. If I have 1,000 rows of the same formula, I don’t need to see that formula 1,000 times. In fact, it distracts from what I may be looking for. Similar formulas in contiguous ranges should be grouped together, I think.

Of the 496 shapes, 322 were from autofilters, 168 were comments, and 2 were charts. Since those objects are listed elsewhere, I don’t need them listed here and it prevented me from figuring out what the other four shapes are.

Speaking of autofilters, there are 322 columns to which an autofilter is applied, not 322 separate ranges. Grouping, again, makes this more usable.

Validations shows >0. When I expanded that part of the tree, the fireworks show began (screen flicker). Apparently Navipane is actually inserting a worksheet multiple times into my workbook then deleting it. I’m not sure why, but it takes several seconds. I’m not sure that I’m comfortable with that level of interaction with my workbook, although it didn’t seem to hurt anything and I did it four times.

If someone is a big time spreadsheet auditor, I can see where the information on this tab might be useful. Clicking on any of the stuff in the list navigates to that object, which has some value. It’s easier to see what’s going on with a workbook with less stuff, but then of course you don’t really need Navipane to get around small workbooks.

Sheets

Now we get to the good stuff. The Sheets tab lists all the workbooks and all the sheets in a treeview. It’s purely for navigation, but it does that beautifully in my opinion. I found it easier (or at least more convenient) to unhide Navipane and navigate to a sheet than using Excel’s built-in methods. I usually use Ctrl+Page to move between sheets, but it’s nice to see where you are in the stack.

Favorites

This is another feature that I really liked. I can right click on any open spreadsheet on the Home tab to add it to favorites and I can drag the favorite to a group.

The icons at the top need a little work. The plus sign adds favorites via a File Open dialog. I think it should add the active workbook to the favorites list. The delete button asks “Are you sure you want to delete 0 favorites?” when nothing is selected. I’m a big fan of disabling controls when they are of no use.

As I mentioned earlier I need a way to open a favorite without the mouse. There may be a way, but I couldn’t figure it out.

And finally when I’m keyboarding, the list scrolls to the left unexpectedly. A few small problems with this pane, but over all I like the implementation.

History

Another great tab. Now you know why you haven’t seen many posts here lately. I haven’t been using Excel much lately. I like that I can add one of these to my favorites with the little star icon. I don’t like that I can delete an item though, but I suppose it doesn’t hurt me if I don’t delete it. Both here and in Favorites, the icons need little tooltips when I hover over them, so I know exactly what they do.

I had the same scrolling problem here as I did with Favorites.

Both Favorites and History need a search feature.

Overall I was impressed with the features of this product. I don’t have much use for the Home tab, but the other tabs were great. Would I pay USD 69.00 for it? Nope. But I can’t think of any Office add-in that I would pay that much for. It’s just too expensive for me.

If you’ve tried Navipane, or even if you haven’t, your comments are welcome.