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.]]> 2008-11-18T16:45:07-07:00 http://spreadsheetpage.com/index.php/blog/excel_2007_help_updated/ http://spreadsheetpage.com/index.php/blog/excel_2007_help_updated/ General 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.

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.

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

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.

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)

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?

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?

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.

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.

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

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.

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)

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.

This has bugged me for a long time, and I finally figured out a solution.

When you type a date (without a year) into a cell, Excel displays the date using the "d-mmm" number format. For example, I type 6/12 into cell B3 and this is what I get:

If I type 6-12 or even jun 12, Excel also displays 12-Jun.

As far as I can tell, there is no way to change this behavior. Supposedly, Excel's default date format is determined by the "Short date" setting in the Customize Regional Options dialog in the Windows Control Panel. But if you omit the year when entering a date, this setting is ignored.

If a user enters a date without a year, Excel determines the year based on the Calendar settings in the Customize Regional Options dialog box. If you omit the year, it would be helpful if Excel showed you how it interpreted that date. But it doesn't.

The solution, of course, is to format (or pre-format) the cells with your desired date format.

But why does Excel have such a useless default? I think Excel has always worked this way, but I don't recall very many people complaining about. I guess it's not really a big deal.

Spreadsheet provides a scrollable zoomable view, along with cell formatting features and a host of standard spreadsheet functions. It includes the following features:

1. Multiple cell types, including Numeric, Date, Time, Currency, Percentage and Text
2. Cell formatting options, including bold, italic, text and background color, cell sizes and alignments.
3. Copy/cut/paste operations.
4. A range of functions, including date/time, math, statistical, string and trigonometric functions.
5. Export (via email) to external spreadsheet applications.

It includes a subset of the Excel functions:

DATE, DATEADD, DAY, DAYABBREV, DAYNAME, HOUR, LASTDAY, MINUTE, MONTH, MONTHABBREV, MONTHNAME, NOW, SECOND, TIME, TODAY, YEAR, COL, COUNT, ROW, ABS, CEILING, EVEN, EXP, FACTORIAL, FLOOR, LOG, LOG10, MAX, MIN, ODD, POWER, ROUND, SQRT, SUM, AVE, MEDIAN, MODE, CONCATENATE, LEFT, LOWER, MID, RIGHT, UPPER, ACOS, ASIN, ATAN, COS, COSH, PI, SIN, SINH, TAN, and TANH.

No charting. And no ribbon.]]> 2008-10-04T15:52:30-07:00 http://spreadsheetpage.com/index.php/blog/poor_guy/ http://spreadsheetpage.com/index.php/blog/poor_guy/ Books Debra, at Contextures Blog, periodically searches Twitter for entries about Excel. She found one that mentions one of my books.

Old man on the subway is reading Excel 2000 Power Programming with VBA. Poor guy.

Indeed. He really should upgrade to a later version of Excel.

A bingo-style game where participants prepare bingo cards with buzzwords and tick them off when they are uttered during an event, such as a meeting or speech. The goal of the game is to tick off a predetermined number of words in a row and then yell "Bingo!".

I posted an Excel workbook that generates this type of bingo card: Generate Meeting Bingo Cards. This workbook contains 186 business buzzwords and phrases, and generates a new random 5X5 card whenever you press F9. It can easily be customized for other situations.