J-Walk & Associates, Inc.

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.

Tip Posted: Resetting The Text To Column Delimiters

I posted a simple VBA procedure that can be useful when Excel tries to be too helpful: Clearing the Text To Column Parameters.

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

Buzzword Bingo Card Generator Posted

Meeting Bingo (also known as Buzzword Bingo) is...

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.

Nth *Day Of The Month Tip Posted

Today is the third Friday of the month -- which means the Tucson Old Time Music Circle is happening.

I just posted a tip that contains formulas to calculate the nth occurrence of a particular day in any month. I also include a formula to calculate the last occurrence. The tip is here: Formulas To Perform Day Of Month Calculations.

As a bonus, I threw in a fancy UI that lets the user specify the data by using drop-down lists. A humongous megaformula calculates the date, based on the input parameters (three of which are specified as text).

Extraneous Plus Sign Removal

I posted a new Excel oddity today, submitted by Joe Rosebrock: Excel Gradually Deletes Multiple Extraneous Plus Signs.

I wonder how he noticed that?

ForceFullCalculation Oddity Posted

I posted a new item in the Oddities section about a new property in Excel 2007 called ForceFullCalculation. Maybe somebody knows what it's good for, but one thing is certain: Something Gets Forced.

The first thing I noticed is that when you set this property in a workbook, it causes the status bar Calculate indicator to stay on permanently. The only way to get rid of that indicator is to restart Excel.

Any clues about why this setting might be useful?

New Feature: Excel Blog Headlines

I added something new to the site: Excel Blog Headlines.

It lets you see the titles of the most recent posts at 21 Excel-related blogs. The titles, of course, are links to the actual posts. If a blog provides a feed for comments, you'll also see links to the most recent comments. The blogs I've identified are:

• AJP Excel Information
• Andrew's Excel Tips
• ASAP Utilities Excel Blog
• Cellmatrix.net
• Contextures Blog
• Daily Dose Of Excel
• Excel Blog @ TVMCalcs.com
• Fresh Excel Tips
• Information Ocean
• JKP's Excel Pages
• Jorge Camoe's Charts
• Ken Puls Blog
• Methods In Excel
• Microsoft Excel Team Blog
• Newton Excel Bach
• Nick Hodge's Excel Blog
• Pointy Haired Dilbert
• PTS Blog
• Smurf On Spreadsheets
• Spreadsheet Page Blog
• Spy Journal

The best way to keep up with blogs is to use an RSS reader. Many are available. I prefer Bloglines, but Google Reader is also very popular. If you use an RSS reader, you can read the actual posts (not just the headlines) all in a single browser tab.

I also created an OPML file that contains information on the 21 Excel blogs. You can download this file and import it into your RSS reader, and you'll have an instant subscription to all of those blogs.

There are other Excel blogs, but they aren't included in my list because they've either been abandoned, or they don't provide an RSS feed. If I overlooked your Excel-related blog, please let me know.

Workbook Taskbar Add-In Posted

My PUP 2000 product had a utility called Workbook Taskbar, which displayed a toolbar with a button for each open workbook. It was just a quick way to jump between workbooks. Here's what it looks like when it's floating (rather than docked):

I eventually discovered that it did not play nicely with some other add-ins that use event procedures. I never thought it was all that useful anyway, so I removed Workbook Taskbar from subsequent versions of PUP.

However, I get a surprising number of requests from upgraders who miss the Workbook Taskbar. Most of them find it preferable to Excel's 'Windows in Taskbar" option. So I've made it available as a stand-alone add-in, and is available here: Workbook Taskbar Add-In. When installed, it creates a new menu item on the Tools menu, which you can use to toggle the Taskbar on and off.

And yes, I realize that the name is very confusing because Windows also has a taskbar.

Use it at your own risk -- especially if you use other add-ins that monitor events. If things start acting screwy, just uninstall it.

Using Notepad To Copy Formulas

I just posted a tip, provided by Matthew D. Healy, that describes how to make an exact copy of a range of formulas: Making An Exact Copy Of A Range Of Formulas, Take 2.

I use Notepad frequently, but it never occurred to me that I could use it for this purpose.

A Function To Extract Email Addresses Posted

I was looking through some old Excel files, and found one from 2002. Apparently, six years ago I had a need to extract emails addresses from text strings. I don't remember it at all. Maybe I was contemplating a new career as a spammer?

In case someone else needs to do this, I posted the VBA function code as a tip: Extracting An Email Address From Text.

I'm sure it's not perfect, but it's probably reliable enough for most uses. The main limitation is that it returns only the first email address in the text. I considered modifying it so it returns all of the email addresses. But then it gets a lot more complicated because the function would need to return an array. So I scrapped that idea.

Color Contrast Functions Posted

How many times have you seen a worksheet that looks something like this?

I get quite a few Excel workbooks from others, and poor choice of colors seems to be a fairly common problem.

Today I posted a tip that describes two VBA functions that can quantify the legibility of foreground and background color choices: Quantifying Color Choices.

It's based on formulas from the W3C, to determine accessibility of Web pages. But it seems to work just as well for Excel worksheet.

Missing File Posted

If you purchased a copy of Excel 2007 Formulas, you might have discovered that the CD-ROM is missing a workbook file that lists and describes all of the worksheet functions. You can download it here: Excel 2007 Function List.

The data is arranged in a table, with the following columns:

• Function Category
• Function Name
• Description
• New?
• Help Topic ID
• Help Link

Click the link in the Help Link column, and your browser opens to the corresponding Help topic at Microsoft's site.

Just for fun, I created a pivot table that counts the functions by category, and applied the Data Bars formatting:

Simple Ribbon Example Posted

I posted a new Excel 2007 tip today: Add The Speech Controls To The Ribbon.

It describes an add-in ( available for download) that creates a new group on the Review tab of the ribbon. This group contains the five text-to-speech controls that -- for some reason -- were omitted from the UI. In addition, the ribbon group contains a custom button that displays the Speech Properties dialog box. This dialog box lets you choose a voice and set the speed.

Writing the RibbonX code was straightforward, but it still took me many save-and-test cycles before I got it right. Here's what it looks like:

For the record, I had to consult my Excel 2007 Power Programming With VBA book several times when working on this project.

The most difficult part was writing the VBA code to display the Speech Properties dialog. This simple task required about 70 lines of code, including four API functions. As it turns out the sapi.cpl file is not located in the same place as the other Control Panel apps. Therefore, you need to consult the registry to find the location of the file. At least that's the conclusion I came to. If there's a simpler way, please let me know.

Word Frequency Generator Posted

I just posted a new tip (with a demo file) that some people might find useful: A Macro To Count Word Frequencies.

Paste some text into column A and run the macro. You'll have a frequency count of every word in the text. The download includes three sets of text:

• All of the Beatles song titles
• The first few paragraphs of Moby Dick
• A George W. Bush acceptance speech from 2000

Disk Drive Functions Posted

I posted a new download that contains VBA functions to determine information about the disk drives on your system: Displaying Disk Drive Information.

Click the button, and a table is created -- something like this: