Daily Dose of Excel

When you’re working with userforms, don’t forget about the Userform toolbar in the VBE.

It has some handy tools on there, particularly for lining up controls. Here’s a userform with four checkboxes on it.

The same form after using the Align Lefts and Make Width Same Size buttons. (This sounds like a commercial for detergent.)

In Access, you can use the Control and Shift keys in combination with the Arrow keys to move and resize controls. As far as I know, there’s nothing similar for Excel userforms. It sure would be nice, though.

WorksheetFunction is a method of the Application object. It gives you access to many of the worksheet functions available in the user interface. As a general rule, you don’t get access to worksheet functions that have a VBA equivalent. For instance, you can’t get to the NOW() function because VBA has its own Now function. You can, however, get to the SUBSTITUTE() function even though VBA has Replace which almost does the same thing.

There is another way to access those functions besides WorksheetFunction. You can use them as methods of the Application object directly. Instead of writing

Application.WorksheetFunction.VLookup(“Me”, Range(“A1:A10?), 1, False)

You can simply write

Application.VLookup(“Me”, Range(“A1:A10?), 1, False)

I used to use WorksheetFunction all the time because it has one big benefit over Application alone: Intellisense.

But I never use it anymore. The benefits of using just Application are too good to pass up. The biggest benefit is in error handling. If you use a function like VLOOKUP() and the lookup value doesn’t exist in the lookup range, you get the N/A error. In VBA, the WorksheetFunction method throws a run time error when this happens. The error is trappable, so you can use an On Error statement to avoid it, but there’s a better way. If you use the function as a method of the Application object directly, and dimension your variable as a Variant, the variable will hold the error value and no error will occur.

Sub TheWFMethod()

When Sally doesn’t exist in A1:A10, the first sub throws a run time error. The second sub does not, but prints Error 2042 to the Immediate Window. You can test the variable with the IsError function to see if Vlookup errored. I prefer the second method and use it exclusively. I don’t like ever having to declare variables as Variants, but in this case I let it slide.

In case you were wondering, the other benefits to avoiding WorksheetFunction is that it’s just too long of a word which makes the code lines too long. And the Intellisense you get with WorksheetFunction isn’t all that great. It saves typing the function name, but look at these really helpful arguments you get.

You may have noticed that my posts have been few and far between this week. I’ve been battling a cold for going on six days. Boy, do I hate summer colds, even in Autumn. Anyway, I’m taking a day off to see if I can whip this.

I’ve been getting a lot of good suggestions for posts via email, so thanks for that. And keep them coming. I will get to them. Right now, I’m too hopped up on NyQuil to do anything meaningful.

Check back tomorrow for more Excel fun!

Custom Views under the View menu is a quick way to hide/show unwanted columns (among other things). There are two situation in which I find Custom Views useful: First, if there are certain columns that I want shown, but not printed; and second, if there are columns/rows that will be hidden in the final product, but I want to be able to quickly hide/unhide them during development.

Here’s a simple example: Create a custom view that shows everything and call it Develop.

Next, hide some columns and define another custom view, call it Final

You can use View > Custom Views to quickly toggle between the views. You can also add the Custom View Toolbar dropdown to one of your toolbars.

You can show the contents of a cell inside a shape. Whether it be the caption of a commandbutton from the Forms toolbar or a rectangle from the Drawing toolbar, a simple formula will do the job. Just select the shape and type the formula in the formula bar.

This example shows both a rectangle and a commandbutton.

Last Week: 2 for 5
YTD: 4 for 10

For those of you who don’t know what this is: It’s college football. I try to pick five games every week by the spread. My goal is to pick 3 winners, on average, per week.

The spread is what the Las Vegas handicappers determine the margin of victory will be. For instance, they think Notre Dame will beat Michigan by 2 1/2 points. If you wanted to bet Notre Dame, they would have to win by 3 points in order for you to win the bet. If you bet Michigan, they would have to lose the game by less than 3 points (or win the game) for you to win your bet. If the margin of victory is exactly the spread, then the bet is off – it’s called a push. All my points are 1/2 points so that I never have pushes. If I were betting in Vegas, I could not, of course, change the spread. But since I do it just for fun, I make sure there can be no pushes.

I’m picking the teams on the left, the spread is in the middle, and their opponent is on the right. So for my first pick to be a winner, Iowa State has to beat N. Illinois by 2 or more.

Bernie Deitrick showed a cool way to insert rows into a spreadsheet in answer to a newsgroup post this week.

First, create a new column A.

Next, fill numbers down for each row. Then copy those numbers to the blank rows below.

Sort on Column A

Then you can delete Column A and your done. Obviously it would be quicker to just insert rows when you only have 10 rows. But if there were more rows, this method would be quicker. I would have probably written a macro to do it, but I like these clever user-interface methods. Particularly if you just need to do it one time.

The PrintOut method applies to a lot of different objects. You can print a Workbook, Worksheet, Chart, Collection of Worksheets, Collection of Charts, Window object and even a Range.

PrintOut has some useful arguments including the From and To arguments. These let you define which pages will print. If, for instance, you want to print every worksheet in your workbook, but only want to print the first page, you might use a sub like this:

Sub PrintPage1()

This will create a separate print job for every page it prints. If you want to print a lot of pages in one print job, you can use this method. You won’t be able to specify pages, though. If you tried to specify only the first page using that method, it would only print the first page of the whole print job, not the first page of every worksheet.

Brett points out that Excel has a rudimentary icon editor. Right click on a toolbar button and choose Customize to enter Customize Mode. Then right click on the button again and choose Edit Button Image…

You get a 16 x 16 grid in which you can create your own icon. Here’s one that I created for my dictionary add-in. It’s supposed to be a book with the world behind it.

If you use this method to create your own icons, take a look at how some of the built-in icons look in this editor. It will give you some ideas on how to create yours.

I saw a post in the newsgroups this week about looping through the sheets of a workbook to delete them. There were several sheets that the poster didn’t want to delete. The answer provided was to use a Select Case statement with all the excluded sheets’ names in a Case statement.

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case “Save1?, “Save2?, “Save3?
        Case Else
            ws.Delete
    End Select
Next ws

I’ve run into this problem before, but never found what I would consider an elegant solution. I don’t like the Select Case solution because I will invariably have to perform some other action later in the code that excludes those same members. That means that I’ll have to retype all the sheet names in a Case statement. I prefer to have all the excluded members in one place near the top of the procedure. It makes for easy editing when the list needs to be changed.

Typically, the way that I handle this is with a String and the Instr function. Like in this procedure:

Sub exclusion()

I don’t particularly like this method, but it’s the best I’ve been able to do. I like the fact that all the excluded sheets are in one place at the top. If I need to adjust the list, I only change it in one place. How do you exclude certain members of a collection when you loop through them?