Category: Uncategorized

Incrementing Months and Years

Posted on by Dick Kusleika

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.

International Keyboard Shortcut Day 2018

Posted on by Dick Kusleika

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

The end of inefficiencies. Am I right?

To celebrate this year, I took my wall calendar off my wall and I’ll use Win+Alt+D to view the Windows calendar. My usual method to see an electronic calendar is to Alt+Tab to Outlook and Ctrl+2 to switch to the calendar (Ctrl+1 switches you back to the inbox (or wherever you were in Mail)). I also might Win+1 to get there because Outlook is the first icon on my taskbar. That’s less desirable, though, because Outlook doesn’t always play nice. Sometimes it’s not on month view and it’s a pain to get it to look like I want. So I’ll try out my new found Win+Alt+D to use the built-in Windows calendar.

If you just want to get your feet wet, head over to True Insights and download the The Ultimate Excel 2016 Keyboard Shortcut List. It’s an Excel workbook listing all the shortcuts you’ll ever need. And because it’s in Excel, it’s sortable and filterable. Find one you like and try to put it to use today. Thanks Jacques for putting the list together.

Want to kick it up a notch? Set aside some time today where you only navigate windows folders with the keyboard. Here are some tips to get you started:

  • Turn on autocomplete in File Explorer. Press the Win key and start typing Internet Options. When the app shows up, arrow down to it and press enter. Use Ctrl+tab to get to the Advanced tab. Page Down and arrow down about 1/3 of the way and select Use inline AutoComplete in File Explorer and Run Dialog. Finally tab to the OK button and press Enter.
  • Win+E will open a new Windows Explorer window. Once there, Alt+D will put you in the address bar.
  • As you start typing, Windows will autocomplete folder names. If you type C:\u, Windows will likely autocomplete to C:\Users. Then you can press Tab to complete the entry or arrow down to a different entry if there is more than one. It would be nice if Windows would insert a \ for you, put you have to type that yourself.

Really want to show off? Create your own shortcut key in Excel using Appliction.OnKey. Put it in your PMW or in your general purpose utility addin. Mine is called UIHelpers.xlam. I use 27 different Appliction.OnKey lines in my Auto_Open procedure. Here are a few of my favorites:

See Incrementing Dates and Times for what those do. I don’t think I ever posted GetMappedAddress, so here it is.

It just puts the full path of the active workbook in the clipboard. I use it all the time to create a hyperlink in Outlook or to add it as an attachement. I just paste the full path in the File Name box and I don’t have to navigate to where the file is.

That’s a big long path pasted into File Name. Even though my current folder is My Documents, I just paste in that big path and hit enter and it’s done.

Finally, see Hyperlink Keyboard Shortcut Update and be sure to read the comments if you want to implement this one.

Have a great IKSD and leave a comment with how you’ll be celebrating.

Develop Excel Conference, London, October 18

Posted on by Charles Williams

On October 18 some of the world’s leading authorities on extending Excel using JavaScript, .NET, Python, VBA, and C++ will be flying in to London to present at our major community-driven not-for-profit conference on Excel Extensibility, “Develop Excel”.

If you are a developer or responsible for building Excel-based addins and solutions you really owe it to yourself to come and meet the speakers, hear the latest thinking on Excel extensibility and discuss the impact to your addins and solutions of the changes that Microsoft is making to core Excel function (including any changes announced at Microsoft Ignite September 24-28).

This is a unique conference: never before have so many Excel world-class developers gathered together to share their knowledge.

For details of the sessions see here

Conference places are filling up fast: make sure you register soon!

 

Copying and Filling Table Totals

Posted on by Dick Kusleika

Suppose you have a table with a number of columns.

And suppose you wanted to sum the Num6 column. You’d select a cell in that column and use Alt+JT+T to add the totals row.

But Excel always sums the last column (that it’s able to) when it should really sum the one you’re on. That’s an easy enough fix

That won’t stop the last column from also being summed, but it’s easier to destroy than create. I’ll let that happen and go delete it if I don’t want it. But we’re not done yet. The craziness is just starting. By the way, I’m using Office 2010. If they fixed any of this stuff in later versions, bully for them, but I’m still not upgrading.

The formula to sum Num6 is: =SUBTOTAL(109,[Num6]). If I copy and paste that to Num5

The formula stays the same. That’s consistent with how copying formulas that use Structured Table Referencing (STR), but it really stinks in this case. If I want to sum all of the columns, I have to go select the appropriate aggregate function from the list. I could change the STR into normal cell references, but then I lose the table goodness.

One option is to select a cell in Num5 and press Alt+JT+T+Alt+JT+T. Turning the totals row off and then on again sums up for that column by calling the code I wrote above. The fact that this works means I should have check to see if there was already an aggregate in the cell before I overwrote it. That is, if I had a count formula in there and turned the total row off and then on again, I’d kill that row. If I fix that, this workaround doesn’t work. But it’s the right thing to do.

Well that doesn’t work. If I have =SUBTOTAL(103,[Num5]) in the cell and turn on and off the total row, it changes to =SUBTOTAL(109,[Num5]).

I think what’s happening here is that Excel tables have a good memory. Once I change the aggregate to sum, that becomes the default for that column. When you show totals for the first time, the event sequence is (with the Target in parentheses)

  1. Add the total row (A5:H5)
  2. Add the total label (A5)
  3. Aggregate the last column (H5)

When I re-show the total row after having changed the aggregate, the event code changes the default aggregate, and when the table restores the formula it’s using the that aggregate. Assume I’m in Num7 when I show totals the first time. Then I move to Num5, add a count, and hide and show totals.

Event Target Num5 Default Aggregate
Show Totals 1st time A5:H5 SUM
Add label A5 SUM
Aggregate Num7 (automatic) H5 SUM
Add Count to Num5 (manual) F5 COUNT
Hide Totals A5:H5 COUNT
Show Totals 2nd time A5:H5 COUNT
Change Event F5 SUM
Add label A5 SUM
Aggregate Num5 (automatic) F5 SUM
Aggregate Num7 (automatic) H5 SUM

The second-to-last change is Excel restoring the Num5 total from the last time. It doesn’t restore it to what it was, it restores it to whatever the last aggregate used was. Since I interjected some code between the adding of the row and the restoration of the aggregate, I changed the default for that column. At least I think that’s what happening.

Hyperlink Formula Events

Posted on by Dick Kusleika

I like to use hyperlinks as user interface elements. They sit nicely in a cell, blend nicely with the surrounding data, and are well understood by users (blue underline means click here). Typically I’ll create a hyperlink that points to itself and then use the FollowHyperlink event to do stuff when it’s clicked.

If a user were to insert rows or columns the hyperlink moves but not the cell it refers to. That is, inserting a column to the left will move the hyperlink to E3, but it will still point to D3. I don’t like it, but it’s not a deal breaker. It means that I can’t use the location of the link to determine which hyperlink was clicked. This is bound to fail:

I tend to use the caption, which, unlike the example above, is generally meaningful and unique. But not always.

If each link points to a different place, I could use the SubAddress property

If there’s a space in the sheet name, there needs to be quotes around it. And if someone changes the sheet name, it mucks up the whole hyperlink. So there are a few things that can go wrong.

My biggest pet peeve is that I can’t use the HYPERLINK() function and the FollowHyperlink event together. That event only responds to inserted hyperlinks, not to links created by a formula. The use case for this is that I could add a column to a Table that had the HYPERLINK() function in it and that formula would automatically expand as the table expanded. Here’s a really contrived example.

When the user clicks a Jump link, it should go to that page. So in the case, the data I need is in the first column (the name of the sheet). Here’s a method I’ve been working on using the SheetChange event. When the user clicks on C2, that cell is selected. Then the link takes the user to A2 and that cell is selected. So I’m looking for a combination of column C then column A.

The module-level variable holds the last cell that was selected. If the currently selected cell is in the first column of the Table and the last cell was two cells to the right, it’s a match. If you’re wondering why I included the seemingly meaningless data in the second column, I’ll tell you. This method fails a lot if the two columns are adjacent because any time you arrow to the left through the table, you’d fire off the code. Having a column B in between fixes that.

You’ve probably already guess that there are a few problems with this. You could arrow to somewhere in the Jumper column and then click in the Number column and trigger the code when you didn’t expect to. Plus whenever you’re dealing with selections, you need to account for multi-cell selections, which I don’t here. But I’m going to try it out and see how it goes.

One side effect of this was that it broke my keyboard hyperlink clicker. It used to look like this.

When you use a ROW() function in the hyperlink address, the Evaluate returns a Variant array and this results in a Type Mismatch error. The relevant parts have been changed to

Now if the result is a Variant array, it pulls the first element.

Office 365 Pro Plus meets Windows 10S, they don’t fall in love.

Posted on by Jeff Weir

I recently purchased a couple of Surface Laptops, that run Windows 10S. I went to install a couple of my Office 365 Pro Plus licenses on them. Now I’m wondering what that S stands for (apart from ‘Security’), after coming up against these three immutable laws:

  • If you’ve got Windows 10S, you can only install apps from the Office Store. Okay…
  • If you’ve got an Office 365 Pro Plus subscription, you can install Office 365 Pro Plus on up to 5 devices that are “compatible with Windows 7 or later”. Yipee!
  • Office 365 Pro Plus is not available via the Office Store. What the!

Well that’s just plain Windows 10Stupid. Can someone from the Windows Store team explain that to someone from the Office Marketing team, so they can add “…unless you have Windows 10S” to their Office 365 Pro Plus copy, please?

Note that you can ‘upgrade’ your Windows 10S devices to a non 10S version that lets your kids download viruses unassisted. But unless you’re willing to open that Pandora’s Box, Microsoft effectively stymie you from installing their own software on their own operating system. Nuts. Absolute nuts.

Ah well. In other Office 365 world news:

That is, unless they have Windows 10S and an Office 365 Pro Plus subscription, it seems.

Excel Version Control with Git

Posted on by Dick Kusleika

This is a guest post from reader Björn Stiel.

Let’s Git Excel Under Version Control!

Git has become one of the most popular and trusted version control systems. In fact, so popular that even Microsoft moved their Windows codebase to Git. Why is Git so popular? Well, Git’s branching model is simple and works (branches provide isolated environments for changes without interfering with production-quality code on master and other branches). Also, Git is very flexible. It can be extended and customized in a myriad of imaginable (and unimagibale) ways, which is the basis for any successful ecosystem.

So what about version-controlling Excel workbooks then? If Git works great for code and is customizable, what does it actually take to make it work for Excel workbooks, too? Let’s get started with what happens when we put an Excel workbook file under Git version-control (I’m assuming some basic Git knowledge here, so if you are an absolute Git noob, have a look the excellent Atlassian Git tutorial, or, if you are are very short on time, check out the simple Git guide.

Let’s hit the ground running and get started with an example repository that contains a simple Excel workbook named Book1.xlsb. Clone the repo…

…and have a look at Book1’s version history (aka the file’s commit log):

So, you might wonder what changes were actually made in the latest commit “Added new VBA Module”? That is, you want to see the changes (the diff) between commits 04b45b99c883e5d184a20cfd73e4556ef8d06bfd and 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1:

Well, that isn’t very helpful, is it? The problem is that Git does not understand Excel workbook files; to Git, an Excel workbook is just any binary file and therefore a black box.

Fortunately, thanks to Git’s extensibility and modularity, we can configure it to use another application to “diff” Excel file formats. Question is: Which application can diff Excel workbooks? Microsoft’s Spreadsheet Compare (which is part of Office Professional Plus and Office 365) is one option but there is quite a bit of technical DIY plumbing involved. If you are interested in the details, you can find them here.

In order to make Git Excel-ready almost out of the box, I created git-xltrail, a free, open-source Git extension. git-xltrail comes with a custom Excel workbook differ that understands the VBA code inside your workbook (handling spreadsheets is on the roadmap). And git-xltrail also takes care of the correct Git configuration so that all the Excel oddities (such as temporary files) are handled.

To get started, download and run the latest installer version. This installs the Git command-line extension, the workbook differ, and configures a few environment variables (more details can be found here). As soon as the installation is complete, open a new command line window:

Run git xltrail install once to make git-xltrail work across all your (existing and future) repositories (the fire-and-forget solution). Alternatively, git-xltrail can be installed on a per-repository basis. In the repository’s root folder, run:

When installing git-xltrail in local mode, git-xltrail creates .gitattributes (or modifies in case it already exists); make sure .gitattributes is tracked as part of your repository.

With git-xltrail installed (either globally or locally in the example repository), revisit the example repository’s
commit history and compare the latest two versions. This time, you actually get a meaningful diff:

Much more helpful this time round. You can try it yourself by editing the VBA inside your working copy’s Book1.xlsb (and/or the text file README.md in the repository to see how it works when you edit Excel- and non-Excel files in the same commit) and compare your working copy versus your working version:

With this under your toolbelt, you can cross-check expected versus actual code changes and use branching so that you don’t mess up your production workbooks. In short, you can write better VBA code. What’s next for git-xltrail? We are planning to support merging (and to make our diffs support not only VBA but also worksheets). If you would like to see a feature, you are very welcome to open an issue or contribute to the project.

Documentation is available:

Userform Textbox Autocomplete

Posted on by Dick Kusleika

I’m working on a project where the user types some stuff into a textbox. A good portion of the time, what the user will type will match one of the last few things he typed. I wanted the textbox to autocomplete if there was a match to a list. Pretty simple, I think. For purposes of this demonstration, I’m going to match to a list of random sentences in a listbox.

I had to use that old disable events in a userform trick otherwise setting the .Text property would call the change event again.

I only look at the first five characters. After that, you just have to type what you want. If there’s a match, I set the .Text property to the matching sentence and set the selection so that the user can continue typing. It all worked very nicely except for backspacing. In the above screenshot, I’ve typed He but the textbox contains the whole sentence. If I hit backspace in this situation, I delete the highlighted portion and I’m left with He. Backspace does nothing.

I was hoping to find a simple and elegant solution. Instead, I did this.

I’m using a module-level variable to determine if the backspace was pressed while in the textbox. If it was and there’s still at least one character, I simply shorten the sEntered variable by one character. That leaves the whole SelStart and SelLength mechanism working as expected.