Free Stuff

Excel 2003 Formulas

Last week this blog turned three years old. Thank goodness the terrible twos are over. And yet, sometimes I pine for the days when it was just an infant. Back when I had time to post three times per day.

In honor of its third anniversary, I thought a give-away would be in order. I have the booty, as I’ve recently received my copies of Excel 2007 VBA Programmer’s Reference. The only thing that’s missing is a suitable contest. Regular readers can appreciate that I’ve run out of contest ideas since the entry fee for the last one was to simply send me an email. Even then, some people didn’t do it right.

This time I’m making the contest harder. First, I’m not providing a link to my email. That’s right, you’ll have to find out my email address on your own. To qualify, you must send me an email with the words “Stupid Contest” in the subject and the number of books listed on the Daily Dose of Excel Book List page that you own in the body. It’s OK if you own zero, the number is irrelevant. It’s not hard, just harder than doing nothing.

If you also include your favorite April Fools joke that involves Excel, you can be entered in the contest one additional time (that makes two times if you get the first part right).

The winner will be chosen randomly from a group of entrants. To be included in that group, you must have a proper subject line, a proper answer to the first question, I have to receive your email by Friday, April 6th at 2:00 PM CDT, and optionally an April Fools joke that involves Excel and is at least reasonably funny or cruel (as judged by me). All normal disclaimers apply. I’ll be announcing the winner’s name on this blog, so if you don’t want your name on the web, don’t enter.

If you include an April Fools prank, put it in a comment as well. If you get it from somewhere else, be sure to credit the source.

Posted in Uncategorized

18 thoughts on “Free Stuff

  1. It’s not fantastic, but it’s sure to fool those less excel-savvy folk around here.. ;-)

    Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
        x = Wb.FullName
        MsgBox “‘” & x & “‘ could not be found. Check the spelling of the filename, and verify that the file location is correct.” & vbNewLine & vbNewLine & _
        “If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.” , _
        vbExclamation, “Microsoft Excel”
    End Sub
  2. Putting this code in the ThisWorkbook module of personal.xls is always good for a few laughs.

    Private Sub Workbook_Open()
        MsgBox “You must reinstall Windows.”, vbCritical, “Catastrophic failure”
    End Sub
  3. Put this into a ThisWorkbook object of personal.xls or an installed add-in (or make a small add-in for this by itself):

    Option Explicit
    Dim WithEvents xlApp As Excel.Application
    Private Sub Workbook_Open()
    Set xlApp = Application
    End Sub
    Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
    Wb.Close False
    End Sub
    Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.FullName = ThisWorkbook.FullName Then Exit Sub
    Wb.Windows(1).Visible = False
    End Sub

  4. My favourite Excel April Fools joke is to wait until one of my
    colleagues is using Excel and steps away from their desk, I tap the
    Scroll-lock key and wait for them to find they can no longer navigate
    cells with the cursor keys. When they ask for assistance I make a show
    of picking up their keyboard and giving it a thorough checking over
    (while casually pressing scroll-lock again). I ask them which finger
    they used to tap the cursor keys, whichever it is I advise them to try
    another, and of course it works first time.

    I am under no illusions that this is a funny April Fools joke, nor is
    it at all subtle or clever, in fact on an Excel humour scale it isn’t
    far off “uninstall Excel when they aren’t looking” as far as the
    chuckleometer is concerned. I live in England, it’s nearly always cold,
    rains a lot and I have a very dull life so forgive me a little
    brainless pleasure at the expense of my colleagues. Then again, I
    suspect that Nebraska could see off any part of the British Isles in a
    brass-monkey contest, on any day of the week (and twice on a Sunday).

    Good luck and thanks for Daily Dose!!

  5. My favorite is a variation of the “purse on a string” trick. Find a hiding place just outside the parking lot at an accounting convention. Attach a copy of Office 2007 (preferably “Professional Plus” or higher) to a string and place it near the edge of the street where those driving away from the conference will see it. As they stop their car to grab the software, reel it in to your hiding spot and watch the hilarity ensue.

    And of course there’s always the old Excel in a burning bag on the front porch trick…

  6. For an April Fools joke I once saved a book.xlt in a co-workers startup directory and Custom formatted all cells to display a dirty word when ever they typed in a number. I know, it’s lame, but this person was not very savvy and was truly baffled even when they would exit out of XL and try over and over again to make it stop.

  7. I have always liked taking a screen shot of the entire screen and then displaying it in so that you can’t see the app’s borders, even works in excel
    or changing a mouse to left hand mode
    both simple and very effective

  8. A variation on Hui’s theme. Flip the screen shot vertically, so it’s upside down. See how long it takes for the user to stand on his/her head.

  9. Not exactly an April Fool Joke, but I wrote an attendance register template for all our team leaders to use. It was driven by userforms, and one guy kept trying to report non-existent bugs and eventually annoyed me enough to the extent that I introduced a simple labelling bug in his register, but put it on a timer such that by the time he’d managed to drag me over to see this, the label had been corrected. Then it was set to work infrequently but randomly. All the other team leaders were in on this so he could never get anyone to admit they could see the problem too. Cruel or what!

  10. I tip my hat to Roger… Great work!

    I entertain myself in the office with:

    Sticking down the button on phones so when someone picks up the handset it just keeps ringing.

    Moving keys around on keyboards so folks can’t type if they don’t know where the keys really are (I got in trouble for that one after IT replaced 4 keyboards in a day)

    Putting tape over the sensor under mice so they don’t work.

    I’m the only person in the office with any VBA knowledge so all your tricks will entertain me

  11. Not mine, I have had this in my archive so long that I had to google to find where it came from…

    I have adapted it slightly to open the CD tray when Excel opens, but I think the original came from Ivan F Moala as this was the only result for my search term, but if you recognise it from somewhere else plase say so:

    Place this into a standard module:

    Public Declare Function SendCDcmd Lib “winmm.dll” _
        Alias “mciSendStringA” ( _
        ByVal lpstrCommand As String, _
        ByVal lpstrReturnString As String, _
        ByVal uReturnLength As Long, _
        ByVal hwndCallback As Long) As Long
    Dim lRet As Long

    And this into ThisWorbook

    Private Sub Workbook_Open()
        lRet = SendCDcmd(“set CDAudio door open”, vbNullString, 127, 0)
    End Sub

    All of a sudden Excel controls that handy coffee cup holder…

    This also works with “set CDAudio door closed” so you could set a loop running to open and close repeatedly…

  12. Sorry, it’s not Excel related, but I like the one where you take a screenshot of the poor guy’s desktop, move all of the shortcut icons into a new folder then make your screenshot the desktop wallpaper. Gets them every time!

  13. i used this one on my secretary this week……. it was even more priceless since she’s been on some medication lately & she said she spent about 10 minutes staring at it trying to figure out if it was her or the machine. once she realized it was not her, she knew who to accuse!

    it is in a regular module…. once you run it you can delete it & then close down their excel & the menus stay messed up until you run the code again. in the sample workbook which i downloaded, the code is run by worksheet buttons to make it easier.

    Sub ReverseMenuText()
    On Error Resume Next
        For Each m1 In Application.CommandBars(1).Controls
            m1.Caption = Reverse(m1.Caption)
            For Each m2 In m1.Controls
                m2.Caption = Reverse(m2.Caption)
                For Each m3 In m2.Controls
                    m3.Caption = Reverse(m3.Caption)
                Next m3
            Next m2
        Next m1
    End Sub

    Sub ResetMenu()
    End Sub

    Function Reverse(MenuText As String) As String
    ‘   Returns menu item, backwards with original hot key
       Dim Temp As String, Temp2 As String
        Dim ItemLen As Integer, i As Integer
        Dim HotKey As String * 1
        Dim Found As Boolean
        ItemLen = Len(MenuText)
        Temp = “”
        For i = ItemLen To 1 Step -1
            If Mid(MenuText, i, 1) = “&” Then _
                HotKey = Mid(MenuText, i + 1, 1) _
            Else Temp = Temp & Mid(MenuText, i, 1)
        Next i
    ‘   Convert reversed string to Proper case
       Temp = Application.Proper(Temp)
    ‘   Insert & for hot key
       Found = False
        Temp2 = “”
        For i = 1 To ItemLen – 1
            If UCase(Mid(Temp, i, 1)) = UCase(HotKey) And Not Found Then
                Temp2 = Temp2 & “&”
                Found = True
            End If
            Temp2 = Temp2 & Mid(Temp, i, 1)
        Next i
    ‘   Transfer ellipses to end of string
       If Left(Temp2, 3) = “…” Then Temp2 = Right(Temp2, ItemLen – 3) & “…”
        Reverse = Temp2
    End Function


  14. My fav april 1 trick is to set in Tools options Edit – Move selection after enter as Up
    So when a user press enter key the selection goes up rather than down… The look on the users face makes my day…


  15. Most people don’t know how to use Styles. So I created one for Normal style that displays “April Fool” instead of zero. To do this:
    1) Open the Format…Styles menu item
    2) Select the Normal style from the Style name dropdown at top, if it isn’t already selected
    3) Click the Number format twice (the first click unchecks it, the second one rechecks it)
    4) Click the Modify button
    5) Choose Custom format type, then enter the following string in the Type field
    #;-#;”April Fool”;@
    6) Click OK twice

    If I were feeling really mean, I’d store it in Sheet.xlt like Roger suggested. That way the problem only pops up after you add a worksheet. Sporadic problems are always more vexing.


  16. Remember the PlaySound API? Use an add-in to play suitable sounds (asynchronously) for all workbook events. I recommend as a suitable .wav library. See if you can find some whirring machinery noises (or a cuckoo clock) for Application.Calculate

    Also, I used to work with a Goth girl – black clothes, black nail polish, black eye-shadow, huge black boots… I changed the Office menus to a third-party font called ‘Hammer House of Horror’, a splendidly Gothic font from low-budget horror movies set in Transylvanian castles. She was delighted with it!

  17. Several years ago my kids gave me a Monty Python Flying Circus cd containing a program when loaded turns your keystrokes into a plethora of farts and belches. Even when knowing it is on the machine it is absolutely hillarious to use. But when loading it on an unsuspecting secretary – it is side splitting….

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.