J-Walk & Associates, Inc.

Excel 2010 VBA Enhancements

At the official blog of the Microsoft Excel product team: Migrating Excel 4 Macros to VBA.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA.

The article lists quite a few things that, in the past, required an XLM macro. The most important new feature is the ability to provide argument descriptions for user-defined functions. These are the descriptions that appear in the Function Arguments dialog box.

I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.

Analyzing Free Money With A Pivot Table

I've been an Amazon Affiliate for many years. When I link to an item at amazon.com, I include my affiliate ID in the URL. Then, if anyone buys something, I get a piece of the action. For example, all my books have links to Amazon. Plus, I usually use my Amazon ID when I link to items from my other blog (that accounts for most of the non-book items).

Today I ran an Amazon report for the year 2009. It shows every sale made with my affiliate ID, with all of the backup data (except information about the buyer). It occurred to me that the report is a perfect candidate for a pivot table. So I imported it into Excel and create a few pivot tables. I made it available as a download, for people who want to learn pivot tables, but don't have access to a good set of data to play around with. See: Pivot Table Demo Workbook.

If you're curious, my total Amazon affiliate income for last year was $2,524.84. That's almost $7 per day! Some people make a living from it, but I just view it as free money that pays my Web hosting bills. Here are the top items sold via my links:

New Version Of Bible Is Available

About four years ago, I found a text file with the complete King James Version of the Bible. I wrote a few macros and dumped it all into an Excel workbook. Each book is on a separate worksheet, and each verse is in a separate cell. Then I added a bunch of stuff, and posted it. A lot of people downloaded it.

Today I spent some time and updated it for Excel 2007 and 2010. I added a few new features, including a Ribbon Interface.

If you'd like to take a look, download it here: King James Bible.

Fixing Excel’s Dual Monitor Problem

I posted a tip that solves an annoying problem: Dual Monitors and UserForms.

One of the things I'll be doing in PUP v7.1 is to add this fix to all of the UserForms.

Posted: Unlinking A Pivot Table

I posted a new tip: Unlinking a Pivot Table From Its Source Data.

It's a way for Excel 2007/2010 users to convert a pivot table into a normal range, and retain the original pivot table style formatting. It's not exactly a life-changing tip, but it could be useful. And it uses the otherwise useless Office Clipboard.

Posted: Old-Style Menus In Excel 2007

I just a posted a simple VBA macro that creates a toolbar containing the old Excel 2003 menu system. Click here to view the code: Old-Style Menus In Excel 2007.

It looks like this:

It might be useful in a pinch. Some of the commands don't work, but most of them do.

Posted: Light Box Demo

I just posted a workbook that demonstrates how to get a "light box" effect in Excel. You've probably seen Web sites that use this technique when displaying images or pop-ups. Now you can do it in Excel. I tested it with Excel 2003 and 2007.

The workbook has an empty UserForm with a black background. That UserForm is resized to match Excel's window dimensions, and an API function gives it a transparent look. Then, another UserForm (or message box) is displayed on top.

It's pretty simple, and it works well -- but not perfectly. On my system, there's an annoying flicker before the lights go out. If anyone can figure out how to avoid that, please let me know.

Download it here: Light Box Demo.

Sub Subscripts

I got an email asking if it's possible to format a cell with a sub subscript (also known as a double subscript). It's not.

But you can do it in a Text Box (Excel 2007 or later).

And you can do a few other text tricks too. See Text Effects in Text Boxes.

Word Clock Posted

If you're a fan of useless Excel apps, here's one for you: Word Clock.

It's a very impractical way to get the current date and the time of day. It's basically a VBA implementation of a screen saver written by Simon Heys.

It uses the Application.OnTime method to update the text in a Shape every second. Writing the code was a lot easier than I thought it would be.

This download is for Excel 2007 and later. I was going to re-write it for previous versions, but then I realized that it's not worth the effort.

Improving A Function

Today I was humbled by a fellow MVP. Rick Rothstein found my ExactWordInString VBA function, and sent me a much simpler function that accomplishes the same thing. My function took 12 statements; his takes one statement.

I updated my post with his improved version: Is A Particular Word Contained In A Text String?

New Tip: Searching For Words

I posted a new tip today, a VBA function that answers the question: Is A Particular Word Contained In A Text String?

I wrote this function to solve a practical problem. At my other blog, I've been posting lists of songs that contain a particular word. I have my music list in an Excel workbook, but none of the built-in tools are able to let me search for a particular word without getting false hits. For example, if I search for the word friend, I'll also get song titles that contains friends, friendly, friendship, and so on.

The ExactWordInString function, listed in the tip, solves this problem and lets me identify only the songs that contains the exact word I specify.

New Tip: Understanding Three Properties

I posted a new tip, The Value, Formula, and Text Properties.

It was in response to a question I got from a teacher. I thought others might benefit from the answer.

New Tip: Listing File Names In A Range

Today, by accident, I discovered another way of getting a list of file names into a range. I posted it in a tip called Getting A List Of Files Names - Another Method.

It's pretty simple, and involves listing the files in your browser, then copying a pasting. Internet Explorer users need not apply. It doesn't work with that browser.

Data Entry Oddity Posted

I posted a new oddity: Data Entry Quirks.

You can get some unexpected results if you enter a time value that begins with a space character.

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.