Some programming notes: Reinvigorate has been down for the count. Supposedly they have been working on a new version, but they don’t indicate when they will be back. I’ve installed ExtremeTracking in their place. I’ve been looking forward to the new Reinvigorate, but I can’t wait forever, even for a free service. You’ll notice the purple thingamabobber near the bottom of the sidebar. Click on it if you want to see stats for this site.

If you’ve tried to search on this site in the last week or so, you were probably disappointed. That’s fixed. The down time is a testament to my regular expression skills – not so good. I’ve been using Atomz for the search engine and I’ve been really happy with the product. I don’t know how long they’ll let me stick around, though, so I may be searching for a new search engine. (Searching for a search engine? How delicious.) Atomz says they’ll only index 500 pages, but the fine folks have been indexing over 500 for me for a few weeks, and I love ’em for it. This week, only one page got indexed, so maybe I’ll get some credit. They will index more pages if I pay them money, but that’s not going to happen unless this site makes me money – not going to happen.

Thanks for reading and keep the emails coming. If you’ve sent me an email, rest assured that I will read it. I’m less than a month behind and charging fast.

Edward asks a question about extending formulas with the keyboard. By extending formulas, I’m referring to copying a formula down a column as far as there is data in another column. Here’s how I do it:

1. First, enter the formula and copy it
2. Select A2
3. Hold down Shift, and press End, then press the Down Arrow
4. Hold down Shift and press the Right Arrow
5. Press the Tab key to make the ActiveCell B2
6. Hold down Shift and press the Right Arrow
7. Hold down Control and press V

Edward thinks there’s an easier way, but can’t quite remember it. It seems that if the ActiveCell is in Column B when you press Shift+End+Down, that you go all the way to the last row of the worksheet. I’ve always done it this way, where column A is active. If you know of a simpler way, post is it as a comment.

If you want to use the mouse, you can double click on the fill handle to get the same results. The mouse sure is easier for this operation.

Excel Pragma blogs:

I think the time has come for me to go down the same route as Jwalk and Andrew and start writing my own set of utilities. … if you have any ideas for utilities that you would like to have but donít, send me a comment.

Rob comments:

VB | Tools | References editor. One with favourites ability – Iím forever adding the same reference into new workbooks.

I couldn’t agree more. I started one of these a few months ago but got bored with it. Every time I set a reference I think how I should just finish the darn thing. I think I’ll post it Monday and get some comments on it so I can finish it in a way that people will want to use it.

Professional Excel Development, the long-awaited book by Bullen, Bovey and Green, arrived at my house yesterday. That makes two, count ’em, two books in which my name appears. Watch out Rupert Murdoch, I’m well on my way to dominating all media.

I did some technical reviewing for this book, thus the mention. I didn’t review all the chapters, so I will hold my detailed review of the book until I read the ones that are new to me. What I have read is absolutely amazing. If you have ever, or plan to ever, write a macro with more than 50 lines, you should own this book. As the title implies, the book will show you how to make professional applications in Excel. It is full of useful information for the intermediate level programmer. Don’t think this book covers just the advanced stuff. The real gems in the books, in my opinion, are applicable to all VBA – naming conventions, error handling, and the list goes on.

On the other hand, if you don’t write macros or the most complex macro you write assigns a hot key to the Paste Special operation, don’t bother with this book. If you want to start using VBA, start with a different book (like Walkenbach’s Power Programming) and move up to this when you’re ready – it won’t take as long as you might think.

Doing a technical review for this book was quite a bit different than the one I did for Excel VBA Programming for Dummies. In the latter, there were only a few topics with which I wasn’t 100% comfortable in my knowledge. In this book, there were a only few with which I was. It wasn’t just a matter of opening Excel and testing the code that was written there. I had to load software, like Visual Studio Tools for Office, to test some of the code. I had to learn stuff, like how to create an ActiveX DLL, to test some of the code.

It’s pretty sad when the technical reviewer has to learn the material before he reviews it, but I think it gave me an interesting perspective. If I couldn’t learn the material by reading the text, then I had a pretty legitimate review comment. It’s one thing, I think, for an expert on the subject to assert that the text is technically accurate. It’s another to have a neophyte look a the material and say “It may be accurate, but I don’t get it.” Both are beneficial in there own way. I hope my wide-eyed innocence was helpful in that regard.

I’ve been complaining privately about my mouse lately. I think I could be more efficient if I didn’t have to ever use my mouse, but there seems to be some tasks that are just impossible with keyboard-only. And others that are very cumbersome. I looked at some alternatives, but was unimpressed.

The foot activated mouse seems like the way to go. I can steer my car and operate the petals at the same time, so a mouse on the floor would be as natural as driving a car. I found exactly two foot mice and both were US$300. No thanks. Other mice I found were camera activated, bio activated and all kinds of crap that was too expensive and more hassle than it’s worth. I was looking for the simple, elegant solution. The solution where I don’t have to wear a headband to move my hands-free mouse.

I got some really good suggestions and I thought I would share them:

• Put the mouse on the left side of the keyboard – this is a good idea, but will definitely take some practice. The idea is that you cut down keyboard-to-mouse travel time by not having to go over the numeric keypad. I’m planning on trying this, but I’m not sure which day to do it. My productivity will be at an all time low that day. I liken it to throwing a football with the opposite hand – you get there eventually, but it’s neither quick nor pretty.
• MouseKeys – it’s under Accessibility in the Control Panel. You can use your numeric keypad to control the mouse. I tried this for a couple of days and I’m not sure that I could do it even with practice.
• Built-in Trackpads – I thought this would be a winner, but it didn’t get good reviews from people who’ve tried it. They say the pad is too small and you don’t get the precision you expect. It seems, however, that at least one person who’s tried this uses a regular size, non-built-in trackpad and highly recommends them for ease of use and precision.

A couple of people suggested that I use keyboard shortcuts. Of course, I already use keyboard shortcuts, but I’ll admit that their may be some of which I’m not aware. I think I use them rather extensively.

If I had to guess, I would say that I use my mouse 40 times in a typical 9 hour work day. Fortunately, I don’t have to guess, I can just count. Here’s a list of every time I used my mouse from 4:00PM to 4:00PM and why I felt I had to leave the keyboard. This is only work computing, not home computing. Also, once I’m at the mouse I don’t count multiple tasks that I do there. My only goal is minimizing the times I have to reach over to use the mouse. If I could do everything with a mouse, that would be fine too. After this experiment, I realized that I need to get a laptop with either the trackpad below the keyboard or the eraser head in the middle of it. That pretty much solves my problem, although it introduces another problem – I’m too cheap to buy a laptop.

My contribution to the wealth of useless information on the web: The 26 times I used my mouse in 24 hours.

1. 16:01 – SpamBayes – an Outlook add-in to manage spam. No menus, just custom toolbars.
2. 16:02 – Moderate comment spam – Everything to do with this blog takes mouse clicks.
3. 17:12 – Logon to bank website – I could have hit tab 57 times until I got to the control for username, but it’s just not worth it.
4. 17:45 – Scroll in Outlook Express window – I can’t read the subjects in a newsgroup with just the keyboard because as I select each message it marks it as read.
5. 07:36 – Click on link in email message
6. 07:41 – Click on link on web page
7. 07:52 – Scroll in Outlook Express window
8. 08:29 – Scroll in Outlook Express window
9. 08:34 – Online calculator
10. 09:33 – Type in Firefox’s Google Search Bar
11. 09:55 – Type in Firefox’s Google Search Bar
12. 09:56 – Copy text from website to paste in Excel
13. 10:23 – Click on link in Outlook Express post
14. 10:48 – MS Access commandbutton – I programmed the form, so the fact that there’s no accelerator is my own fault
15. 13:41 – Click on link on web page
16. 13:42 – PageDown in PDF – reading a pdf in FireFox, I couldn’t page down without using the Acrobat Reader toolbar to change the cursor to Select Text. Then the arrow keys and PgUp and PgDn worked fine.
17. 13:50 – PageDown in PDF – same problem different pdf.
18. 13:58 – Click on link on web page
19. 14:00 – Click on link on web page
20. 14:14 – Scroll in Outlook Express window
21. 14:46 – Enter a URL in FF’s address bar – I’m sure there’s a shortcut for this – I’d better learn it.
22. 15:32 – SpamBayes (see 1)
23. 15:39 – Check the Intellisense on a VBA variable
24. 15:43 – Click on link on web page
25. 15:51 – Click on link on web page
26. 15:56 – Click on link on web page

Mark Garratt suggests this post – thanks for the suggestion.

You can use just about any picture as the face of a commandbarbutton. The better the picture looks when it’s small, the better it will look on your commandbar, so using that hot Jennifer Garner photo may be a bit much for a tool face. Tool faces are 16 x 16 and you can see what your image will look like by converting it in your image editing software. Or, if you’re like me, you just put on a toolbar in Excel and see what it looks like.

To get that picture on a tool, you first need to put in on a worksheet that will be accessible when the code is run. I generally put in on a hidden sheet in the same workbook as the code. Use Insert > Picture to get the picture on the worksheet.

Now that you have access to the picture, you can use the PasteFace method of the CommandBarButton object. You must first copy the picture into memory. This example creates a commandbar and a single button with a custom face.

Mark also has a way to get at images that are stored externally. He uses the AddPicture method to get the picture into the sheet.

I posted a comment to the first Comment Spam post to see if it still works. It appears to have. I wanted to layout the rest of my comment spam strategy here.

Today I installed HashCash. I uses JavaScript to thwart the bots. I guess if you don’t have JS enabled on your browser, you won’t be able to comment here.

I was also going to use Kitten’s Spaminator. When I opened the php file in Notepad, there were no line breaks. Until and unless someone tells me what I’m doing wrong, I won’t be using this method. Since I have WordPress v1.2.2, I need to edit the php directly instead of using the interface.

If HashCash isn’t satisfactory, I’m going to try periodically renaming the commenting php file. The problem is that anyone that uses WordPress has the same file name that handles comments. The bots know this name and edit it directly (or something like that). Changing the file name prevents the bot from finding the file. This all assumes that I can find the instructions to do it – a cursory search of the WP forums didn’t turn it up for me.

Next, I might try adding the code from this post that warns commenters if a forbidden word was used.

Then I might try a few things from Combat Comment Spam.

If none of that works or looks good, I’ll be going with CAPTCHA, Preview, and User Registration in that order. I don’t think it will come to that, though. This guy defeated CAPTCHA and you may be interested in reading about it. If you like capitalization and punctuation and much as me, you might not want to read this page – it has neither. Defeated or not, I don’t lose much by having as many defenses as are out there.

I plan to implement these one at a time until I get a manageable amount of spam or until the good folks at Spambayes come out with something for comment spam.

Thanks, everyone, for the comments on the first post.

Larry points out that next year’s SuperBowl will by XL (=ROMAN(40) in Excel). Then I read the same thing on j-walkblog. In true Daily Dose style, I post the news only after everyone else on Earth has heard it.

Based on the comments at j-walkbog, and other experiences I’ve had, I conclude that people who like computers generally don’t like American football. I don’t know why, that just seems to be the way it is. I, for one, like football more than any other sport. I like college football more than professional, but I’d take the NFL over anything else.

I’ve been a Cincinnati Bengals fan since 1984-ish. If you follow the NFL at all, you know that I must be the most loyal fan of all time. (If you don’t follow the NFL, it’s because the Bengals had the worst decade (’90’s) in the history of football.) I started following the Bengals when Dave Rimington played followed a couple of years later by Jim Skow (they were my favorite Nebraska Cornhuskers at the time). I don’t want to be one of those guys who gives up on a team just because a certain player leaves, or the team has a couple of down years, or the team paints their helmets really funky.

In 2004, however, I decided that this was the last year for the Bengals. If they didn’t show some spark, I was giving up on them and finding a new team. Well, they did reasonably well and I actually think they’ll make the playoffs in 2005. Now I have to stick with them. There’s nothing better than having a team do well and being able to say “I’ve been a fan for years.”.

I watched a bit of the half time show, and I generally liked it. It may be the only half time show I’ve ever seen that I even liked a little, so I guess it was pretty good from my perspective. If I were Paul McCartney, I would insist that people not call me Sir. I’m sure it’s a big honor and all that, but it makes him seem pompous. It’s like someone who gets a PhD in philosophy and insists on being called ‘doctor’.

This would have been a better post if I would have said something funny about the football and Excel. But there simply isn’t anything funny about that combination.