Automate Excel

Here are a couple ways to make sure you aren’t sending the boss a spreadsheet with the obvious Formula errors in it.

What’s an obvious Formula error? #DIV/0!, #NAME?, #NULL!,#NUM!, #REF!, #VALUE!

Error Checking with the Auditing Toolbar
The first button on the auditing toolbar provides an “Error Checking” function. Simply click it and Excel will let you know if your spreadhsheet contains any errors.

To make the Auditing Toolbar visible: Click View->ToolBars-> and choose Formula Auditing

Error Checking with Conditional Formatting
Automatically turning cells with errors to a different color using conditional formatting is an easy way to help reduce spreadsheet errors while you work. Errors are easy to detect as they are created, and also easily viewable from a quick scan of the spreadsheet. Read how in the post Highlighting Spreadsheet Errors.

You can extract the year from a date in Excel by using the Year function.

Assuming a date in Cell A1, the following will display the Year:

=Year(A1)

When trying to resize or position objects such as charts, autoshapes, or controls on a spreadsheet, with the intent of aligning them to edges of cells, you can hold down the ALT key while doing so. This will have the effect of “snapping” the object to the cells.

In this image the grouped object is sized perfectly to a rang of cells. The howto:

To Resize an Object to the Size of a Range:
1. Click the Object once to turn on the Resize handles (You may need to enter design mode for some objects, try right clicking the object, or use the option on the VB toolbar)

2. Hold the ALT key down while you drag the handles

To Position an Object’s Edge to a Cell’s Edge
1. Hold the ALT key down while you move the object

For the VBA method to accomplish the same goal, have a look at VBA to Size an Object to a Range Size

Interesting name, good video content. Datapig Technologies is an Excel and Access site with over 50 Excel video tutorials. Links:

Spreadsheet Videos ¥ VBA Videos

Stephen Bullen posts at DDOE there are three free chapters of the soon to be released Excel programming book “Professional Excel Development” available to read. The chapters are:

Chapter 1 – Introduction
Chapter 9 – Understanding and Using Windows API Calls
Chapter 15 – Advanced Charting Techniques

Link to free chapters

I’ve only browsed the Advanced Charting chapter and it looks like it contains some interesting info, including details on creating a Zoomable/Scrollable chart.

You can Calculate a spreadsheet manually by pressing the F9 key.

But doesn’t Excel automatically Calculate a spreadsheet? By default yes, however here’s a couple reasons the F9 key has come in handy in the past:

UDF’s: After modifying code in a User Defined Function and returning to the spreadsheet, you can press F9 to make the Spreadsheet Calculate to see the results of your code changes.

Automatic Calulation is Turned Off: Once in a blue moon you may use Excel with the automatic calculation turned off via the Tools->Options->Calculation menu. Large spreadsheets can sometimes take a few seconds to calculate with each value change. With Calculation turned off you can work for awhile and manually calculate the spreadsheet with F9 when you’re ready.

The following is a picture of a spreadsheet using a Formula to convert a number to words:

You can find directions and the code to use this UDF in the Knowledge Base at:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360

If the link quits working you can also find a copy of the code at the original place I saw the tip:

The Planning Deskbook: Converting Numbers to Words

The MrExcel site is running a photo contest for people that have pictures of themselves or others using Excel in interesting places.

Evidently no Photoshop trickery is allowed: “Don’t crop yourself into the top of Mt. Everest if you don’t really use Excel there.”

You get a Book or CD if you win, the results will be posted Jan 31.

Colo has an interesting feature on his site that lets you get to know some “Cell Masters” better. In Colo’s words:

“There is a lot of really cool VBA code and formulas for Excel on the world wide web. You may wonder what kind of person created such great stuff, or how can they gain such skill? Maybe you would like to find out how today’s ‘Masters’ got their start – what did they do and how did they learn?”

Go to Cell Masters (Scroll down then click on the Notebook Icon next to each name.)

View some candid photos of MVP’s and Cell Masters

And finally, as Jwalk and John Mansfield have noted, the new MVP’s were announced this week. Congratulations if you recieved the award. You can view some of their profiles here.

I haven’t used this shortcut in a couple years. I was reminded of it when it was used in the Charts Webcast.

You can quickly create a copy of an Autoshape in a Worksheet, or a copy of Controls on a Userform:

1. Hold down the Ctrl key
2. Click and Drag the Object to copy