Navigating Outlook Favorites

I have these four favorites defined in Outlook:

From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’s land and couldn’t figure out how to get back into the inbox. So, god forbid, I had to use my mouse. I wrote this macro:

And added it to the fourth position on my QAT. Now I can press Alt+4 to cycle through my favorites. And I’ve only hit Alt+F4 accidentally about a dozen times.

AutoHotkey in VBA

In response to https://nolongerset.com/all-about-indenting/

Macros with 32-bit API Calls

I have a fairly slow running macro (~20 seconds) that works fine on one computer, but never finishes on another. If finally decided to figure out why. (Big Daddy is the nickname of the guy who started this golf league way before my time. I am not Big Daddy, although maybe I should be.)

First I noted that Windows Defender was using a lot of CPU and Excel almost none.

I disabled Windows Defender realtime under Windows Security. Then I got a green leaf next to Excel that says “This UWP process is suspended to improve system performance.”

I don’t know what that means, but the internet told me to increase the priority of Excel.

That got rid of the green leaf, but the macro never finished. I read some stuff about 32-bit APIs and Win 10 trying to prevent malicious code. I use the CopyMemory API referenced in Rob Bruce’s comment to Terminating Dependent Classes

I rewrote all the dependencies to simply point to the other classes rather than use CopyMemory and all the problems vanished.

Opening a File from a Userform Disables Ribbon

In Excel 2019 16.0.10361.20002 32-bit, when I open a file from a userform (which for me is always), the hotkeys on the Ribbon don’t work. Here’s how I reproduce:

In a new workbook, add a userform named UserForm1. Add a commandbutton to the userform. Paste this code in the userform’s code module.

Add a standard module to the workbook and past this code into it.

Close the VBE and return to Excel. Press Ctrl+F8 and run the macro and click the button. Now press the Alt key. I don’t get these guys:

If I Alt+Tab away and back, they show up again. If you launch the form from the VBE, you have to Alt+Tab to get to Excel so the test doesn’t work.

Also, I hadn’t tried this until just now, but I can’t even click on the Ribbon with mouse. Nothing happens. My next tack is to close and unload the userform before opening the file and see if that takes care of it. It’s odd though. It’s the only odd thing in 2019 and building up a pretty nice rant about it. Two posts in one year? Yep, it could happen.

International Keyboard Shortcut Day 2019

The first Wednesday of every November is International Keyboard Shortcut Day. This Wednesday, people from all over the world will become far less efficient for a short time in an effort to be far more efficient the rest of the year.

WASD V2 88-Key ISO Custom“WASD V2 88-Key ISO Custom” by Bitboxer is licensed under CC BY-NC-SA 2.0

This year I’ll be starting all of my programs with the keyboard. First, I’m going to hide the Cortana search bar because that’s just a waste of real estate. Ironically, the best way to do this is with your mouse. Right-click on the taskbar, choose Cortana, and Hidden.

Now let’s pin some frequently used applications to the taskbar. Start the application however you normally would. Find the icon in your taskbar, right-click on it, and choose Pin to Taskbar.

I have eight applications pinned to my taskbar. I can open these using the Windows key and the number representing that applications position in the taskbar. For me Outlook is first, so I would use Windows+1 to open Outlook.

Every morning, I unlock my computer, hold down the Windows key, and press 2, 3, 5, and 7. This opens all the programs that I leave running all day. I never close Outlook because of all the client side rules I have, but I include Win+1 to that process after a reboot. I used to open #4 every day, but that’s fallen out of favor. But I don’t want to remove it and change the positions or all the other icons.

In addition to opening the program, Win+number switches to that application if it’s already running. If that application already has the focus, it gets moved to the back of the line and hidden. Just press the shortcut again to bring it back to the front. If you have multiple instances running, like multiple Word docs or Excel files, continue holding down the Windows key and pressing the number to cycle through all the documents. When the one you want is at the front, release the Win key.

One more thing about the taskbar: let’s say you have a bunch of File Explorer windows open, which you no doubt opened using Win+E. To close them all at once, press Win+Alt+number to get a context menu and choose Close All Windows. I don’t have File Explorer pinned to my taskbar, so its position in the list isn’t the same every time. But it’s usually ninth or tenth. In this example, it’s tenth so I use Win+Alt+0 to bring up the context menu.

You might have also noticed that you could have used the Win+Alt context menu to pin the application to the taskbar instead of right clicking. If you did, you get an IKSD bonus.

OK, last tip before you’re on your way to a great keyboarding day. If you want to open a program that’s not pinned to the task bar, press and release the windows key and start typing the application’s name. For instance, if I want to open Powerpoint, I press and release Win and type power.

Windows shows you everything that has power in the name or description. The “best match” is usually what I’m looking for, but you can see that you can also get to PowerShell or the power settings control panel using this method. If you have some recently used documents, those will also appear. When I type ues to open Ultra Edit Studio, it shows me a few documents I used recently.

I hope you use these techniques on Wednesday to open your applications using your keyboard and become a little more productive. Happy keyboarding.

Putting Statistics

Because of my recent terrible putting, I decided to keep track of every putt to see what I can see about it. Here’s how I compiled the stats.

I wrote down how many paces each putt was and assumed one step was 2.5 feet. The Made formula in the table is

For that date and hole, see if there’s a putt with a higher number. If not, I must have made that one. The Left formula is

Same as the last except that it sums the length of the putt that’s one higher. Over in the stats table, I use these formulas

Made:

If it’s greater than or equal to the first number and less than or equal to the second number and I made it, count it.

Made %:

Take the number made and divide it by the total putts in that distance range. The IFERROR is for divide by zero errors.

Avg Left:

This sums all the “Left” numbers for putts not made and divides by the number of putts not made.

Max Left:

This one’s an array formula, so enter with Ctrl+Shift+Enter, not just enter.

In retrospect, I should have noted putts that went past the hole as negative distances and putts that were short of the hole as positive distances. I could have got the same stats as above plus I would know if I had a tendency to be short or long. It feels like enough bookkeeping for the golf course already, but maybe I’ll implement that change in the future.

So, what did I learn? I learned that I like numbers and statistics, but I don’t think I learned anything useful for my golf game.

You can download PuttingDistance.zip

Keyboard Metrics III

In the grand tradition of Keyboard Shortcut Metrics and More Keyboard Metrics, here’s this:

Procedure 2016 2017 2018 Grand Total
MakeComma 20.54% 18.67% 19.01% 19.19%
WrapSheetsDown 13.80% 17.15% 9.93% 14.93%
WrapSheetsUp 8.60% 15.36% 7.33% 12.16%
FillVirtualScreen 10.50% 11.04% 8.42% 10.40%
CopyPasteValues 14.00% 4.84% 2.20% 6.54%
ShowFormatting 5.85% 5.13% 7.04% 5.68%
IncrementDate 4.25% 4.08% 9.77% 5.23%
MarkYellow 4.55% 5.44% 5.29% 5.20%
SelectAdjacentCol 4.87% 4.71% 6.19% 5.04%
DecrementDate 3.56% 1.87% 11.93% 4.24%
GetMappedAddress 2.39% 2.21% 2.73% 2.35%
FrozenHome 2.26% 2.39% 2.32% 2.34%
MakeTable 0.00% 1.80% 4.07% 1.81%
ChangeSign 2.03% 1.35% 0.77% 1.40%
FillSeries 0.49% 1.38% 1.99% 1.28%
CopySum 2.22% 1.18% 0.33% 1.27%
ConvertColumnToText 0.00% 0.63% 0.00% 0.36%
SelectPrecedents 0.00% 0.46% 0.12% 0.29%
SwitchAggregate 0.00% 0.24% 0.49% 0.23%
MatchColumnWidths 0.10% 0.06% 0.08% 0.07%
Grand Total 100.00% 100.00% 100.00% 100.00%

Look at DecrementDate making a charge.

Incrementing Months and Years

As you know, I love keyboard shortcuts. But I hate entering dates. So I created this little helper. In my new accounting system, they also have shortcut keys for incrementing weeks, months, and years. I don’t have much use for incrementing weeks, but I could kick myself for not thinking of the others. Plus Alt+semi-colon is just sitting there doing nothing.