Author Archive

Hardware Recommendations

It seems I have server-issue reprieve, so I’ll take advantage of it by throwing up the first post in weeks. Don’t worry, it’s not Excel related. I mean, who can expect that from this site. I do have a couple posts in the hopper, I just can’t seem to login when I want to write the up.

I got an email from an old friend asking for my recommendations for a new desktop computer. I wrote a longer reply than I expected and figured I might as well post it here. That way you can give your two cents in the comments. First, I’ll summarize his question/requirements:

I want “serious scientific/number-crunching/math-oriented”, and things labeled “home”, “office”, and “gaming” don’t seem like the right fit. I would like to use this machine primarily for compiled Python, S Plus, Mathematica, and C++ dll’s I am coding for Excel and OpenOffice. Specific points:

  1. Is 8 GB RAM enough? Speed-wise, will 16 or more make a “significant” difference?
  2. Which processor: i7?
  3. Will a hybrid drive make any difference? Incidentally, I keep all of my documents on my NAS; I would, of course, keep development versions on the local HD, but really don’t need more than 150 MB (if that!) on it.
  4. Dell looks good – highly configurable — what’s your take?
  5. Monitor/Video Card: One of my Macs is 27″, and is awesome for my wife’s photo/cine avocation. I am writing you from a 17″ Mac ProBook. Works for me. So I won’t need a particularly large monitor (which one?!?); what I should do re the video card?
  6. I/O: Several USB 3.0; Wired Ethernet, of course; Bluetooth; Thunderbolt. DVD and SD drives would be great.

Here’s my reply:

I don’t profess to be an expert, but I’m happy to give you my opinion. Just take it for what it’s worth. My last three desktops were 1) custom built 2) HP HPE-150f and 3) custom built.

My Last Three Computers

  1. I read about how he built his own home theater PC. I was cutting cable and needed something to run Windows Media Center for over the air TV. I somewhat followed his lead, but since mine would be out of sight, I didn’t worry about making it small or quiet. I wouldn’t recommend my particular build for your purposes because it was built with the new (at the time) Intel chipset that had HDMI out built-in.

    I was in a situation where I didn’t trust Dell anymore, didn’t know who to trust, and was interested in trying my hand at building hardware just for the experience. I made some errors along the way, but all-in-all it has been an outstanding machine. My biggest mistake was not buying a big enough power supply. I also had a hard drive go bad in the first year. Even with the missteps, the whole cost was about $1,400.

  2. I was ready to replace my Dell desktop because it was 7 years old. I had a reasonably good experience building a PC so I was going to go that route again. It was a different build, so I there was still some chances to make mistakes, but I priced it out on New Egg anyway. Then I went to HP to see what a comparable machine would cost. It was a few hundred dollars cheaper and it didn’t really make sense for me to build it myself. I got an i7 2.8Ghz with 8 GB.

    That was four years ago, or so, and this machine has been terrific. Zero problems and it does everything I want performance wise. My only problem with HP is that they have a lot of craziness going on – they announced they were getting out of the PC business, then retracted. It just gives me pause. We use HP exclusively at work (~1,000 desktops) and they are probably still my go-to manufacturer.

  3. The third desktop was for my son. It was still a better deal not to build it, but I wanted to give him the experience of building a PC from srcatch, so we went that route. It was a pretty good experience and it has been a good machine. If you’re interested in building your own, I’ll send you the component list.

Operating Systems

I run Win7 and my son runs Win8. He prefers Win8, but kids are stupid, so what are you going to do. On my HTPC, if I upgrade to Win8 I have to pay extra for Windows Media Center. In other words, I’m never upgrading. And when Win7 doesn’t work anymore, I’m going Linux/XMBC for the HTPC. Sorry Microsoft, but when you stopped building problems for guys like me you should have expected this.

Your Specific Questions

  1. I bought my HP with 4 GB (the stock amount) and bought another 4 from You can save some pretty good money that way, but price it both ways. Just make sure you get enough slots if you’re going to add after market RAM. If I were buying today, I wouldn’t get less than 12 GB because RAM is so cheap that even an incremental increase in performance is worth it. I’d look at 16 too. But really, I’ve been running 8 GB for a while, and I have no problems, so it might be a waste of money.
  2. I like my PCs to last 5-7 years, so I tend to over buy on CPU and RAM. I don’t what’s newer and better than i7, but I definitely would not go with i5.
  3. Another misstep on building my own was not having an SSD for a boot drive. If I were building again, I would get an SSD for a primary and a Western Digital for storage. SSDs are getting more reliable at a pretty quick pace.
  4. Six years ago, or so, I stopped buying Dell. I bought them exclusively for home and work for the 10 years prior, but the quality of the components was going down and I’d had enough. I’ve heard they’ve made a bit of a comeback, quality wise, but I’m not sold yet. Concurrent with them buying cheaper parts, they moved their customer service to India. Back in the day when I didn’t have to talk to Dell customer service because their shit just worked, I wouldn’t care where it was. But with failing parts, it was a problem. I’m not trying to denigrate an entire sub-continent. It was just that the company Dell hired really sucked. I’m sure the rest of India is very knowledgeable.
  5. I have two 22″ monitors side-by-side and love it. I can’t imagine coding in Excel without the VBE on one screen and Excel on the other. Even when I code in Ruby, Python, Java or whatever, I need to have stackoverflow on one screen and the IDE on the other. I will never have one monitor again – it is the single best productivity boost in my computing career (including my keyboard shortcut mania). Both my monitors are Acer that I got from Newegg. They’re slightly different heights, but I find that piano sheet music books level them up nicely. If you can get identical, that’s even better. My work monitors are identical 22″ ASUS and I’m very happy with those too.
  6. Don’t sacrifice on USB ports. They don’t all have to be 3.0, but make sure you have double what you think you need. Even if you don’t go with two monitors, you should consider a dual output video card in case you change your mind.

I’m a couple years away from replacing my desktop, but if I were forced to do it, I would price out HP, Lenovo, and Asus. And probably Dell for good measure, but I would have to be really convinced to go back to Dell. Lenovo and Asus have awesome reputations in laptops and I don’t know why that wouldn’t translate to desktops. I bought a used Asus convertible tablet several years ago and it still runs like a champ.

End of reply

If you have any opinions on desktop hardware, please leave them in the comments.

Servers Suck

Hey, did you notice that this page loaded in a few milliseconds instead of timing out? Me too!

I don’t know what caused that period of slow down because I don’t know much about servers. I know my CPU percentage has been higher lately. Here’s what it looks like over the last 30 days.

And over the last 24 hours.

Last night I created a support ticket with Digital Ocean. And today it miraculously works. It makes me suspicious, but I can’t imagine a business model where you throttle your customers until they call you on it. I mean except for the cable and cell phone industries.

So I’m going to enjoy it while it lasts. I’m also going to get in an Excel post today before I have more problems.

More AutoHotkey Sugar

Ken Puls, the Excel Guru, sent me some nice AHK stuff and I’m trying to put a post together to share it with you. But it’s not happening so far. In the mean time, I didn’t want you to have to wait for these gems that I’ve been enjoying.

Definition and Last Position in the VBE

If you right click on a procedure call in the VBE and choose Definition, it takes you to that procedure. If you then right click and choose Last Position, it takes you back. If you’re like me, the thought of right clicking is abhorrent to you. Instead, I use the context menu key, but alas there’s a problem. It takes a few microseconds for that menu to show up. So while I press menu+D, all I’m really doing is inserting the letter ‘d’ in my procedure call.

As any good keyboard hound knows, you can use Shift+F2 and Ctrl+Shift+F2 to accomplish the task without the context menu delay. That’s great except that my sausage fingers don’t bend that way. Shift+F2 isn’t bad, but Ctrl+Shift+F2 is too much work for something I use as much as this. Without further ado

    Send +{F2}
    Send ^+{F2}

Now Ctrl+D gives me the definition and Ctrl+L takes me back. Sometimes hotkeys take a little getting used to. It’s easy to forget they’re there. This one, however, took about five seconds to become a natural part of my VBE workflow.

Switching tabs in Access

There’s so much about Access that isn’t keyboard friendly that it sickens me. But the worst is switching tabs. Ctrl+F6 cycles forward through the tabs and Ctrl+Shift+F6 cycles backward. Never you mind that Ctrl+Tab does that job in every other Windows program every released. Ever.

#IfWinActive ahk_class OMain
    Send ^{F6}
    Send ^+{F6}


Changing Field Size in Access

Speaking of crappy Access keyboarding, how about changing the field properties as you create a table? Sucks, huh? Here’s the scenario: I’m entering a field and changing the Data Type to Number. If I want a Long Integer, I’m good because that’s the default. If I want a Double, however, I have to press F6 to get to the Field Properties and change the field size to Double. Then, instead of Shift+F6 to back, I have to F6 five times to cycle through all of the windows/panes/screen areas. That’s just stupid. So

    Send {F6 5}

That works for me because I don’t vary my Access window. If you hide your Navigation pane, or have some other configuration than me, you might have to change that 5 to something else.

Windows APIs

Ken has a nifty API generator that I’ll be posting about later. While mine is a little more pedestrian, I think it offers some advantages. I have a separate hotstring for every API that I care about. Here’s one

    SendInput !to!i{Enter}
+3If VBA7 Then
    Public Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As LongPtr
        hInstance As LongPtr
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As LongPtr
        lpTemplateName As String
    End Type

    Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
            "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
    Public Type OPENFILENAME
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type
+3End If
SendInput !to!i{Enter}

That’s a big one. I type declaregetopenfilename in a module and this little honey appears. One of the advantages of this method, verbose though it is, is that the API is mostly intact in the AHK file. That makes it really easy to edit if the need were to arise. Making a new API hotstring is as easy as setting up declareapiname, pasting Jan Karel’s code, and changing a few things.

The first thing you have to change is the # signs have to be change to +3 or they won’t come across. The other thing is a little more subtle. Did you notice that I send Alt+TO, Alt+I, Enter before and after the API stuff? That turns off, then back on, the Auto Indent feature in the VBE. AHK sees all these tabs in my string and it dutifully returns them. That means that every line is indented one too many times from the line above it. Cumulatively. By the time you get to the last line, there’s 30 tabs in there.

By turning Auto Indent off and back on, I can keep my API looking clean in the AHK file, which I like.

Formulas for Sale

101 Ready to Use Excel Formulas

It’s for sale. If you pre-ordered, it’s probably on the way. If you didn’t, well, you know what to do.

I got my copy last night. My wife, who is not an Excel geek by any stretch of the imagination, is excited to read it. At 240 pages, it’s not the usual phone book sized tech book, and it seems more accessible to her. What she doesn’t realize is that it’s packed with awesome formulas and she’s actually going to learn something. That’ll show her.

After being on sale for a few hours it’s the 643,122nd ranked book in the world. Let’s see if we can get that up to 643,121.

Centennial Weekend Birthdays

On this episode of the BBC’s More or Less podcast, they discussed big, round birthdays that fall on a weekend. A listener said that she had to wait until her 60th birthday for it to fall on a weekend. The guy who figured out how unlucky she was tested every birthday from January 1, 1900. Since he picked that date, I assume he used Excel, but he never said.

They did include the caveat “as an adult” so that leaves off the 10th birthday. Here’s how I did the math.

I started with 1/1/1900 is cell A2 and used the formula


copied down to today. Then in B1:J1, I entered the values 20-100. The formula in B2 is


I added the value in row 1 to the year to make the centennial birthday and fed that into the WEEKDAY function. WEEKDAY returns 1 through 7 representing the day of the week. I used ‘2’ for the second argument so that Monday is 1 and Saturday is 6. Then I return TRUE or FALSE depending on whether the weekday is greater than or equal to 6.

Column K finds the minimum age that has a TRUE under it


That’s an array formula, so I entered it with Ctrl+Shift+Enter.

Next, I repeated 20-100 in column N. These formulas complete the table

    O3        =COUNTIF($K$2:$K$41832,N3)
    P3        =O3/SUM($O$3:$O$11)
    Q3        =Q2+P3

As if that wasn’t enough, I wanted to make a single formula that could accept a date and return the earliest major birthday that was on a weekend.


That’s also an array formula, so you know what to do. I celebrated my 30th birthday on a weekend.

Listing Calling Procedures

I have this awesome machine with 64-bit Office sitting under my desk. I don’t use it to code because MZ-Tools doesn’t work on 64-bit Office and I need that (and a few other things) to be productive. I only use a few features from MZ-Tools, so I think I’ll just write them in VBA. I took my first stab at the Procedure Callers feature.

Public Sub ListProcedureCallers()
    Dim vbProj As VBProject
    Dim vbModule As VBIDE.CodeModule
    Dim vbComp As VBIDE.VBComponent
    Dim i As Long
    Dim lActiveLine As Long
    Dim sProc As String
    'get the name of the current procedure
    Application.VBE.ActiveCodePane.GetSelection lActiveLine, 0, 0, 0
    sProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(lActiveLine, vbext_pk_Proc)
    'only look in the active project
    Set vbProj = Application.VBE.ActiveVBProject
    'loop through the code modules
    For Each vbComp In vbProj.VBComponents
        Set vbModule = vbComp.CodeModule
        'print the procedure for any line that contains the name of the active procedure
        If vbModule.CountOfLines > 0 Then
            For i = vbModule.CountOfDeclarationLines To vbModule.CountOfLines
                If InStr(1, vbModule.Lines(i, 1), sProc) > 0 And vbModule.ProcOfLine(i, vbext_pk_Proc) <> sProc Then
                    Debug.Print vbComp.Name, vbModule.ProcOfLine(i, vbext_pk_Proc), vbModule.Lines(i, 1), i
                End If
            Next i
        End If
    Next vbComp
End Sub

I just wanted to get something down and not be too worried about how well it works. This procedure just prints to the Immediate Window rather than a fancy userform that let’s you go directly to one of the procedures.

One of the things I don’t like about MZ-Tools is that it searches for callers in all open projects. I can see that value in that, I just personally have never needed it. And for procedures with common names, it shows a crap ton of stuff. I made my procedure only search the current project.

One of my property procedures in one of my class modules is named Active. When I looked for its callers, I got every procedure that uses ActiveWorkbook or ActiveSheet. My code does not discriminate – if the name of the procedure appears in the line of code, it’s a hit.

How do I avoid that? For the Active property, all I have to do is look for a space after the word Active and I should be good to go. Except for comments, perhaps. That’s fine for a property with no arguments, but if it has arguments or is a method with arguments, there won’t be a space after it but a parenthesis. Can I search for either a space or a paren? Seems like it, but I’ll have to think it through.

Another thing I don’t like about MZ-Tools is that it doesn’t care what class module you’re in when you look for calling procedures. Every one of my Collection Classes has an Add method. When I search for procedure callers for Add, I get every call to every Add method in every class.

That’s a little tougher proposition. I could be very opinionated, as I am, by looking for clsPlural.Add rather than just Add. I always name my class instance variables clsXXX. That would work for me, but wouldn’t be very general purpose. While I’m a well-known selfish prick, I do still care about you, dear reader. Even if I were so inclined, I’d have to still look for With blocks. I can’t just look for clsPlural.Add, I have to also look for .Add, then I have to search up the lines of code for a With before I hit an End With, then I have to determine the variable… My goodness that sounds like a lot of work. This is probably why MZ-Tools doesn’t care which Add method I’m looking for – it’s just not worth it.

Here’s some things I’d like to do:

  • Find actual callers, not just the procedure name
  • Omit finds in comments
  • When I’m in a class, only find properties/methods from that class
  • When I’m on a Property Get, don’t return Property Let assignment statements
  • Go to the first caller automatically, but still list the rest somewhere
  • Other stuff I haven’t thought of

What say you?

Listing Conditional Formatting Redux

Back in the day, I posted some code to list conditional formatting. It didn’t contemplate having multiple conditional formats for the same range. Because who would ever do that right? Of course that happens all the time and was very short-sighted of me. I aim to atone.

I used a Collection object because Collection objects can’t have two Keys that are the same. It’s a good way to get a unique list out of a list that contains duplicates. I used the range to which the FormatCondition applies as the key (and that was my downfall). My thought was this: I’m checking each cell individually and a FormatCondition that spans two cell would be counted twice. A FormatCondition that applied to L9:M9 would be counted for L9 and M9. By using the address as my unique key, it would only be counted once – the first time for L9 and it would error out and not be counted for M9.

Except you can have two FormatConditions that apply to L9:M9 and only the first would every be counted. I needed a way to identify what was a duplicate and what was a legitimate second FormatCondition. I cleverly devised (read stole from Bob Phillips) that I would add the count to the end of the address. But I got lucky in that it failed for my particular setup. The way my FormatConditions were created, they weren’t in the same order for all the cells. So even though an FC was the same for a later cell, it was the 3rd FC instead of the 2nd, and that made it seem unique.

I set out to find a better way to uniquely identify FCs, and here it is

Public Function CFSignature(ByRef cf As Variant) As String
    Dim aReturn(1 To 3) As String
    aReturn(1) = cf.AppliesTo.Address
    aReturn(2) = FCTypeFromIndex(cf.Type)
    On Error Resume Next
        aReturn(3) = cf.Formula1
    CFSignature = Join(aReturn, vbNullString)
End Function

It’s still no guarantee of uniqueness, but if you have two FCs with the same range, the same type, and the same formula, well, you gets what you deserves. Now I can use the ‘signature’ instead of the address.

Public Sub ShowConditionalFormatting()
    Dim cf As Variant
    Dim rCell As Range
    Dim colFormats As Collection
    Dim i As Long
    Dim wsOutput As Worksheet
    Dim aOutput() As Variant
    Set colFormats = New Collection
    For Each rCell In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
        For i = 1 To rCell.FormatConditions.Count
            With rCell.FormatConditions
                On Error Resume Next
                    colFormats.Add .Item(i), CFSignature(.Item(i))
                On Error GoTo 0
            End With
        Next i
    Next rCell
    ReDim aOutput(1 To colFormats.Count + 1, 1 To 5)
    Set wsOutput = Workbooks.Add.Worksheets(1)
    aOutput(1, 1) = "Type": aOutput(1, 2) = "Range"
    aOutput(1, 3) = "StopIfTrue": aOutput(1, 4) = "Formual1"
    aOutput(1, 5) = "Formual2"
    For i = 1 To colFormats.Count
        Set cf = colFormats.Item(i)
        aOutput(i + 1, 1) = FCTypeFromIndex(cf.Type)
        aOutput(i + 1, 2) = cf.AppliesTo.Address
        aOutput(i + 1, 3) = cf.StopIfTrue
        On Error Resume Next
            aOutput(i + 1, 4) = "'" & cf.Formula1
            aOutput(i + 1, 5) = "'" & cf.Formula2
        On Error GoTo 0
    Next i
    wsOutput.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
End Sub

And in case you forgot, here’s how I got the type.

Function FCTypeFromIndex(lIndex As Long) As String
    Select Case lIndex
        Case 12: FCTypeFromIndex = "Above Average"
        Case 10: FCTypeFromIndex = "Blanks"
        Case 1: FCTypeFromIndex = "Cell Value"
        Case 3: FCTypeFromIndex = "Color Scale"
        Case 4: FCTypeFromIndex = "DataBar"
        Case 16: FCTypeFromIndex = "Errors"
        Case 2: FCTypeFromIndex = "Expression"
        Case 6: FCTypeFromIndex = "Icon Sets"
        Case 14: FCTypeFromIndex = "No Blanks"
        Case 17: FCTypeFromIndex = "No Errors"
        Case 9: FCTypeFromIndex = "Text"
        Case 11: FCTypeFromIndex = "Time Period"
        Case 5: FCTypeFromIndex = "Top 10?"
        Case 8: FCTypeFromIndex = "Unique Values"
        Case Else: FCTypeFromIndex = "Unknown"
    End Select
End Function

Now this

gets you this

Updating the For Next AutoHotkey in the VBE

Last month I posted about some AHK scripts I was starting to use to make the VBE a little less gross every day. There were some awesome comments. I took Hubisan’s comment and ran with it through a few iterations. First, let’s go to the video.

I use a program called CamStudio. For some reason it’s blurry for the first 30 seconds. I really need to get Techsmith’s Camtasia. But it gets the point across for now.

I’m seriously digging the AHK stuff. Here’s the script:

:*:for ::
;when you type for{space}, replace it with caps so you know you're in AHK mode
SendInput FOR{Space}
;wait for the next word and store it in counter
Input, counter,I V T10,{Space}{Escape}
;finish with ESC and you thwart AHK
;but finish with a space and more stuff happens
if (ErrorLevel = "EndKey:Space")
    ;if the next word is each, it's a for each loop
    if (counter = "each")
        ;wait for the next word and store it in eachctr
        Input, eachctr, I V T10,{Space}{Escape}
        if (ErrorLevel = "EndKey:Space")
            ;Once you know eachctr, fill in the Next line and go back up to the For line
            SendInput +{HOME}{DELETE}{Enter}Next %eachctr%{Up}For Each %eachctr%{Space}
    ;if the next word is one of these, you're opening a text file
    else if (counter = "Append" or counter = "Binary" or counter = "Input" or counter = "Output" or counter = "Random")
        ;get the next word - it really should only be 'As'
        Input, askeyword, I V T10,{Space}{Escape}
        if (ErrorLevel = "EndKey:Space")
            if (askeyword = "As")
                ;the word after 'As' is the file number
                Input, filenum, I V T10,{Enter}{Escape}
                if (ErrorLevel = "EndKey:Enter")
                    ;complete the close statement, because I always forget that.
                    SendInput {Enter}Close{Space}
                    ;you got to send this part raw because there may be a # in there and that's special
                    SendRaw %filenum%
                    SendInput {Up}
        ;and finally if it's not all that special stuff, it's just a for next
        SendInput +{HOME}{DELETE}{Enter}Next %counter%{Up}For %counter%{Space}

I put in comments so hopefully you can follow along. All I’ve done is copy Hubisan’s code, so if I took something nice and made it total crap it’s because I don’t know what I’m doing.

Bob Phillips made a good point in the last post about how he doesn’t prefer the automation. The automation gets in the way sometimes and typing the code slows things down so you can use your brain a little more. Good points, I thought, but I still like the automation. I can relate to the point that it gets in the way sometimes. In a previous iteration, I would type For i and it would put Next i, plus a blank line, plus a tab. That means when I’m done with the For statement, I have to arrow down. I don’t want to arrow down. I want to hit enter, then tab. So I made the automation fit the way I want to work and now I’m very happy with it.

Pre-Order 101 Ready-To-Use Excel Formulas

The venerable Mike Alexander and I wrote a book last Spring and now you can pre-order it at Amazon (ships next month). 101 Ready-To-Use Excel Formulas

This book is not a list of worksheet functions and a description of their arguments. It contains fully formed formulas that solve real world problems. For example, there’s a whole chapter on financial formulas like creating an amortization schedule and calculating depreciation. I know financial stuff isn’t everyone’s cup of tea, that’s why we included 101 formulas. Mike wanted to write a book called One Ready-to-Use Excel Formula, but I said “Wait, what if we increased that number so there’s something for everyone?” I’m always looking out for you, dear reader.

You definitely want to buy one for yourself. But you should also buy one for that person in the office that needs it. You know that person who adds up the numbers in a spreadsheet on a calculator and then types the sum into Excel? Leave a copy of this book anonymously on his desk. Your boss might even reimburse you for it.

Adding a New Worksheet to the End of the Workbook

I can think of five ways to add a new worksheet to a workbook. There may be more, but I can only think of five.

  1. Alt + i + w – this is the way I do it now. I’m trying to get away from the 2003 keyboard shortcuts, but this one remains.
  2. Alt + h + i + s – this is what I should be using because it’s on the Ribbon, but it’s also one extra key.
  3. Click the Insert Worksheet “tab” to the right of all the real sheets.
  4. Use the Shift + F11 keyboard shortcut for the Insert Worksheet “tab” that inexplicably behaves differently than clicking the tab.
  5. Right clicking on a sheet tab and choosing Insert… and going through the dialog box.

Only one of these five methods inserts the worksheet to the right of the active sheet, kind of. #3, the mouse only one, inserts a worksheet at the end of all sheets. All the other methods, including Shift + F11, insert a worksheet to the left of the active worksheet. I’m not much of a clicky guy as you know, preferring the keyboard. But sometimes I want the new worksheet to be at the end. So what’s a guy to do? Acquiesce and reach for the mouse? I don’t think so.

I have an add-in called UIHelpers.xlam. In that add-in is a CAppEvents class for controlling application level events. One event that I’m now using is the Application_WorkbookNewSheet event. It listens for when a new sheet is added to any workbook.

Private Sub mxlApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

    If Sh.Index = Wb.Sheets.Count - 1 Then
        Sh.Move , Wb.Sheets(Wb.Sheets.Count)
    End If

End Sub

If the new sheet is the penultimate sheet, move it to the end. When I’m on the last sheet and insert a new sheet, more often than not I want the new sheet to be to the right. There are a few times when that’s not true and I’ll have to move them. But this will cut down on manually moving worksheets significantly.