J-Walk & Associates, Inc.

Excel And AV Software

I got an email last week from Joel Klein, a rather frustrated Excel user. He was having some annoying performance problems with Excel. At first he blamed Vista:

There is very little information out there (that I can find) that discusses the operating system environment in which Excel is run. For example:

• In Windows XP, if you select a large (200,000 cell) range and either Delete or Clear All, Excel responds quickly and fluidly.
• In Windows Vista, if you try this, Excel hangs.
• If you do the same thing using VBA commands, XP is again very fast, but with Vista there is a noticeable pause (less than a second).

This is on multiple Vista-based machines with all of the Aero nonsense turned off, 8 GB RAM and a sturdy graphics card.

I've never noticed a problem like that. Thinking that it might be something unique to his file, I asked Joel to send me a copy. I couldn't reproduce his problems (using Vista and Excel 2007). All of those actions occurred instantly on my system.

Then, on a hunch, I mentioned that he should try disabling his anti-virus software. He wrote back:

Fascinating. First, I disabled McAfee. No change at all. So then I was playing around with a few other things, all to no avail, and then I figured I'd just uninstall it altogether. Problem solved.

I still think it is an interesting resource allocation problem for discussion among people who know more than me. Virus protection software scans files. The total CPU burden seemed low (based on the Task Manager).

It is not sucking up a lot of RAM (why would it?). So what has virus protection software got to do with the data in a spreadsheet? Especially if it's totally (allegedly) disabled? And why would it be different between deleting worksheets versus large single-sheet ranges? Is there a way to split the difference -- to have some virus protection but not hobble a key application? Finally, I had the same virus protection on my XP and it never did this!

I've never used anti-virus software, so I have no answers. Joel and I would both be interested to hear about the anti-virus software experiences of others.

Excel 2007 Help Updated

If you use Excel 2007 -- and haven't yet figured out that it's more efficient to use Google rather than the official Help system -- this is for you: Updates for Microsoft Office Excel 2007 Help.

This update installs the latest assistance content for the Microsoft Office Excel 2007 Help file and the Office Excel 2007 Developer Help file.

That's all they tell you about it.

Microsoft Has A Download Store

Always the trendsetter... MS Windows, Office available to download.

Although Microsoft launched it quietly in the UK last June, its online store offering Windows and more for download is finally opening in the US.

That the Microsoft Store is only now going live in the company's home market may come as a surprise in an age when even corner shops have an online presence.

Still, it's not exactly straightforward - downloads are available and can be accessed multiple times, of course, but aren't exactly permanent.

Unlike physical disks that can be used until they wear out, MS grants access to the product "until mainstream support ends", which is "typically five years", according to program manager Trevin Chow.

Here it is: Microsoft Store.

This store is obviously intended for people who must have the software immediately, and don't mind paying the full list price. For example, you'll pay $680 for Office Ultimate 2007. Or, buy it for $400 at Amazon.com.

Free PUP Source Code

From now through Monday Wednesday.... Purchase a PUP license and get the VBA source code for no additional cost. This applies to:

• PUP v6 - For Excel 2000, 2002, and 2003
• PUP v7 - For Excel 2007

How it works:

Order PUP online, and I will be notified of the transaction. I will follow-up by email and send you information on how to access the VBA code for all of the PUP components.

With access to the PUP VBA code, You'll learn how it works, and you'll pick up dozens of new programming techniques along the way. The PUP source code is an excellent learning tool for VBA programmers of all levels. Even advanced programmers will probably pick up lots of new techniques.

This offer applies to all online orders placed between November 13 through 17 19.

* * *

Update: I'm going to extend this offer through Wednesday, November 19.

Small Basic

It has nothing to do with Excel, but it's from Microsoft. It's a way for kids to learn about programming: Small Basic.

Small Basic is a project that's aimed at bringing "fun" back to programming. By providing a small and easy to learn programming language in a friendly and inviting development environment, Small Basic makes programming a breeze. Ideal for kids and adults alike, Small Basic helps beginners take the first step into the wonderful world of programming.

It's a small download, but it requires the .NET Framework 3.5. If you don't have it, then it's a BIG download and a very lengthy install.

I played around with a bit and tried some of the examples from the Small Basic Forum. The Intellisense is pretty cool. My main complaint is the very long delay between pressing F5 (Execute) and seeing the result. Maybe it's just a problem with my installation, but sometimes it took as long as 10 second before anything happened. That kind of takes the fun out of it.

Excel In A Browser

It's official: Microsoft to offer Office online.

Microsoft announced at the Professional Developers Conference Tuesday that it is finally putting Office apps Word, Excel, and PowerPoint online, but not killing the traditional versions. It's about time Microsoft got with the program here. Online apps offer several advantages over software apps, which Google has been leveraging in its Google Docs suite. Primarily, documents that are created in an online app can be opened up for sharing and collaboration very simply.

Microsoft isn't going to give it away, of course.

As far as pricing, Microsoft will be competing with Google's free Google Docs as well as Zoho's suite. Microsoft cannot afford to give away its core productivity app completely. The company has not revealed its entire pricing strategy, although representatives note that the current Office Live has both free, ad-supported options as well as subscription services.

You can find some screen shots running in Internet Explorer here. As you can see, it implements a quasi-Ribbon interface.

I guess I'm too far removed from the business world. I just don't see what all the excitement is about.

AC/DC Video In Excel

Here's a first: AC/DC Music Video Distributed as Excel Spreadsheet.

Phil Clandillon and Steve Milbourne, who work at a digital design boutique division of Sony/BMG in London, have put together what they call "the world's first music video in Excel format," for AC/DC.

They decided on this unusual format because they wanted the video to penetrate even the most Draconian corporate firewalls. After all, who can't receive an Excel spreadsheet?

Download the XLS file here. Does not work in Excel for Mac.

The VBA project is not protected, so you can take a look at the macros. The workbook has an embedded WAV file, but the "video" is not real video. Disable the Worksheet_SelectionChange procedure in the Sheet1 module and select the B2:J20 merged range while it's playing. Unhide column Q and change the background color to see where the "frames" are coming from.

(Thanks Patrick Crosley)

29 Years Of Spreadsheets

Next year marks the 30th anniversary of the electronic spreadsheet. The 29th anniversary came and went last week, and I forgot all about it.

Peter Jennings writes about VisicCalc 1979:

The first copy of VisiCalc for the Apple ][ (Version 1.37) went out the door on October 17, 1979. By the end of the month, we had shipped 1293 copies. As the sales figures grew, VisiCalc became a household word. It even appears in the Oxford English Dictionary...

VisiCalc was the software which brought the power of the personal computer to the common man. Before its introduction, computers, even personal computers, could only be programmed by people who had made the effort to learn a programming language. Users without programming skills were locked into using the existing applications. What they could not do was to solve problems. VisiCalc unlocked the power of the computer as a problem solving device to those who did not wish to learn programming skills and in such a way that it did not seem like programming at all. Even today, most users of spreadsheet applications do not think of themselves as programmers. But that is exactly what they are doing every time they type a formula.

At Dan Bricklin's site, The Idea:

The idea for the electronic spreadsheet came to me while I was a student at the Harvard Business School, working on my MBA degree, in the spring of 1978. Sitting in Aldrich Hall, room 108, I would daydream. "Imagine if my calculator had a ball in its back, like a mouse..." (I had seen a mouse previously, I think in a demonstration at a conference by Doug Engelbart, and maybe the Alto). And "..imagine if I had a heads-up display, like in a fighter plane, where I could see the virtual image hanging in the air in front of me. I could just move my mouse/keyboard calculator around, punch in a few numbers, circle them to get a sum, do some calculations, and answer '10% will be fine!'" (10% was always the answer in those days when we couldn't do very complicated calculations...)

The summer of 1978, between first and second year of the MBA program, while riding a bike along a path on Martha's Vineyard, I decided that I wanted to pursue this idea and create a real product to sell after I graduated.

If you're too young to have actually used VisiCalc on the job, you can download a copy and take it for a spin: VisiCalc Executable for the IBM PC. The complete app is 27K. Installation is not necessary.

You can find some basic instructions here: VisiCalc Instructions. You'll need them; the user interface is rather cryptic. If you prefer to just dig around, here's a tip: Press the Slash key for the menu.

I still have a copy of VisiCalc, and I dug it out of the garage and took a photo of the manual and diskette. It has a copyright date of 1981. But, of course, I no longer have a 5.25" floppy drive.

Fortunately, the Web has lots of good information about VisiCalc, so this old program isn't going to be forgotten any time soon.

Does anyone have any old *.VC files they can send me?

Hyperlink Misinformation

This is pretty odd. In the past month, I've received emails from three people who read this tip: Removing Or Avoiding Automatic Hyperlinks. An excerpt:

Surprisingly, Excel doesn't provide a direct way to remove all hyperlinks on a worksheet. In fact, the only way to accomplish this is one cell at a time: Activate the cell that contains the hyperlink, right-click, and then select Hyperlink, Remove Hyperlink. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro.

These three people all claimed that I was wrong, and they explained to me how to remove hyperlinks from multiple cells:

1. Select the cells that have a hyperlink
2. Choose Edit - Clear - Format (or Home - Clear - Clear Formats in Excel 2007)

This command simply removes the formatting from the cells. Cells that contain a hyperlink normally appear with blue underlined text. Removing this formatting does not remove the hyperlinks.

I wonder where this advice is coming from?

Using The Enter Key In The Enhanced Data Form

My Enhanced Data Form is an add-in that provides a data entry dialog box for any worksheet database. Pressing the Tab key while this dialog box is active activates the next control in the dialog box.

Several users have asked me if there's a way to use the Enter key to move among the fields. There is, but it requires some additional programming. I updated the Enhanced Data Form Customizing page with this code: Using the Enter key to move among the fields.

Office Offline

This has been around for about a year, but I hadn't seen it. It's a comic blog by Microsoft employee David Salaguinto: Office Offline.

Here's a recent comic that features Excel:

And here's a panel from a comic about merging cells:

Read more about David here: Conversation with David Salguinto.

Excel-Related Problem At Barclays

Excel made the news: Excel error leaves Barclays with more Lehman assets than it bargained for.

A reformatting error in an Excel spreadsheet has cropped up in the largest bankruptcy case in U.S. history, prompting a legal motion by Barclays Capital Inc. to amend its deal to buy some of the assets of Lehman Brothers Holdings Inc.

The law firm representing Barclays filed the motion (download PDF) on Friday in U.S. Bankruptcy Court for the Southern District of New York, seeking to exclude 179 Lehman contracts that it said were mistakenly included in the asset purchase agreement. The firm - Cleary Gottlieb Steen & Hamilton LLP - said in the motion that one of its first-year law associates had unknowingly added the contracts when reformatting a spreadsheet in Excel.

A few details:

According to the motion, Barclays sent the spreadsheet containing the list of contracts to Cleary Gottlieb at 7:48 p.m. EDT on Sept. 18. The spreadsheet - which contained almost 1,000 rows of data with a total of more than 24,000 individual cells - needed to be reformatted and converted into a PDF file so it could be posted on the bankruptcy court's Web site before midnight. At 11:37 p.m., Cleary Gottlieb sent the converted file to the court, the motion said.

However, contracts that had been marked as "hidden" in the spreadsheet when it was received by the law firm were added to the purchase offer during the reformatting process, according to the motion. Those contracts weren't supposed to be part of the deal; they also were marked with an "N" for "No" in the original version of the spreadsheet, Cleary Gottlieb said in the motion.

It would be interesting to know exactly what happened to this workbook. And it's unfortunate that the headline referred to it as an "Excel error."

Windows 7 Name Announced

Everyone has been wondering what the official name for Windows 7 will be. Today, we found out: Introducing Windows 7.

And, as you probably know, since we began development of the next version of the Windows client operating system we have been referring to it by a codename, "Windows 7." But now is a good time to announce that we've decided to officially call the next version of Windows, "Windows 7."

That's it. Move along. Nothing to see here.

Progress Bars: The Movie

Everyone loves a progress bar.

Speaking of progress bars, here are a few links to help you make a progress bar for your VBA macros:

• Displaying A Progress Indicator (Spreadsheet Page)
• Progress Bar (Daily Dose of Excel)
• Progress Meters (AJP Excel Information)
• Status Bar as a Progress Indicator (VBA Express)

(video via Miss Cellania)

Excel 2007 Security Oddity Posted

I've received several emails from people asking about the security warnings in Excel 2007. Sometimes they see the warning above the formula bar (shown here); other times the warning comes in the form of a dialog box.

It seemed odd to me too, but I eventually figured it out and posted the answer to the puzzle. See: Two Types of Security Warnings in Excel 2007.

* * *

And speaking of annoyances. If you open a file and get the security warning shown above, the natural inclination is to press Alt+F11 and check out the code. But if you do that you lose the opportunity to enable the macros. So if the macros check out OK, you need to re-open the file. That scenario has happened to me at least 30 times.