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

Story: MS Office encryption flaw uncovered

It’s explained in detail in the PDF The Misuse of RC4 in Microsoft Word and Excel. Greek to me.

I was in and out for the webcast, but I did catch:

Format XY chart axis
Use picture as a data point
Change data source by changing chart data points
Adding a linked text box to a chart
Interpolate
Combine chart types
Multiple axis
Pivot table review
Pivot charts

It looked like there were approx. 110 people viewing. I thought Stephanie did a good job, here’s a few related links I found at her site:

Is there anything Excel can’t do?
Sizing Excel Charts for Word
Linking Text Boxes, Chart Titles, and Data Labels Dynamically to Data (Excel)
Show the Data’s Exact End Date on a Line Chart X-Axis (Excel)

Anybody have favorite tips or tricks for using a dual monitor setup with Excel?

I just purchased a basic video card (PNY Verto GeForce FX 5500(89$ at Best Buy)) that handles two monitors. After an hour long struggle I’m up and running with a 17″ and an old beat up 15″ monitor (my next upgrade is replacing the 15″).

I’ll post if I find anything unusually different using Excel in a Dual Monitor setup. After only a few minutes I found you can make an extremely large chart with two screens, useless:

And a very practical use,
Visual Basic Editor on the left screen, Workbook on the right:

Dual backgrounds look like fun. I also see Aaron Blood has a few VBA routines for a dual setup. Off to climb the learning curve.

Finally, Excel will make sense! O’Reilly found Excel’s Missing Manual, the second Excel book realesed by O’reilly in the past month.

I’m guessing: “The Missing Manual” is their equivalent of the Excel Bible (the Table Of Contents look similar), “Excel Annoyances” is the equivalent of a “Dummies” book, and “Excel Hacks” is a cliff notes of both.

I believe public opinion says go with The Excel Bible.

In a Worksheet you can use View->Zoom->Fit Selection to automatically adjust the Zoom to fit a selected Range.

You can “Fit Selection” in VBA by selecting the Range and setting Zoom equal to True.

Sheet1.Range("A1:F15").Select 'set range zoom
ActiveWindow.Zoom = True

There is an Excel Charts Webcast happening this Thursday January 20, 2005 at 9am Pacific/ 12pm Eastern Time. It’s titled: Microsoft Office System Webcast: Tips and Tricks for Fast and Fabulous Excel Charts (Level 200)

You need to follow the previous link and register to participate. I’ve watched a couple on-demand webcasts, this might be my first “live” one, hopefully I’m not too busy Thursday.

The Presenter is Stephanie Krieger.

To delete all Named Ranges in a Workbook, you can use the following code:

Sub DeleteNamedRanges()
Dim MyName As Name

For Each MyName In Names
    ActiveWorkbook.Names(MyName.Name).Delete
Next

End Sub

For determining whether a name exists, adding a named range, and a few other examples, Chip Pearson has a nice tutorial on working with Named Ranges.

Excel Pragma is a brand new Excel Blog, as in 3 or 4 days old. Good luck to those behind the blog.

I’ve been getting a “Device I/O Error” while running VBA code or working in the VBE editor the past couple of days. At first I thought possibly I had a corrupt file, then I realized I hadn’t installed the Service Pack 1 since I’ve reinstalled Office 2003.

A quick download and install of Office 2003 Service Pack 1 and the “Device I/O” Error” problem seems to be cured. If you’re seeing the same problem I recommend giving SP1 a shot.

I’m not sure why most people dislike the automatic conversion of URL’s to Hyperlinks in Excel, in most cases it’s a good time saver for me.

However, If you don’t like the Automatic Hyperlink conversion, the code to remove Hyperlinks from a spreadsheet is rather simple. The following code deletes all of the Hyperlinks from Sheet1:

Sub DeleteHyperLinks()

Sheet1.Hyperlinks.Delete

End Sub

And if you don’t need a reusable Sub routine, rather a one time fix, use the Immediate Window:

1. Select the Sheet to remove Hyperlinks from
2. Press Alt+F11 to open the VBE
3. On the VBE Main Menu choose View->Immediate Window
4. In the Immediate Window paste the following code then hit Enter

ActiveSheet.Hyperlinks.Delete