What I want for Christmas

…is for Microsoft to fix how Sheet Protection and Tables interact. I want it more than world peace. I even want it more than cold beer. See…I even put it on my Christmas wish list:

What am I talking about? That template above is a great example: Let’s say I want to send you all some templates for you to fill out and send back. Being a prudent developer, I’ll use Excel Tables, and those tables will have a mixture of Calculated Columns with the formulas locked down (because I don’t trust you one bit), and free-text fields (because I want you to think that I care about what you have to say).

That’s what I’ve done above: I’ve got a RANK column that automatically assigns an incrementing integer to each entry, and I’ve got a freetext field where you can put your own requests, if for some reason you don’t share my world view. And I also want to let you insert additional columns, in case you want more than three things.

I better push Ctrl + F1 to bring up the Format Cell dialog, and make sure those genius formulas in that Calculated Column will be protected when the sheet is locked:

…and I better make sure that the ‘Request to Santa’ column is NOT protected:

Awesome…all I need to do in order to activate this protection is to lock the sheet, while still allowing row inserts and deletes:

And all is good, until some moron decides that Cold Beer comes in a distant third behind some additional thing they would like to add to this Christmas wish-list, like “New President”. So they right-click within the Table Row while Tra-la-laa-ing, only to be stopped mid laa: the Insert > Table Rows Above option is greyed out. :-(

But being persistent, they decide to force the issue by selecting the entire row, and then right clicking. And indeed, this time they encouragingly see that indeed the Insert New President… option is still available to them:

…only to find that Excel has trumped their desire:

But weirdly, when they click OK, Excel decides that it can in fact do it, kinda:

It begrudgingly inserted the new row, but it didn’t autofill the formula in the protected column. Meaning while our hapless agitator can fill out the freetext field with their new second-greatest wish, they can’t rank it. Meaning Santa might simply ignore it as invalid input:

God forbid they would want to do something equally trivial, such as extend the Table by either trying to type underneath it, or by selecting the bottom right cell and pushing Tab or Enter, because that accomplishes nada. As does Sorting and a myriad of other things that the Protect Sheet dialog swears it will let you do.

I just cannot fathom why this bug hasn’t been fixed. I recall Zack Barrasse talking about it years ago, along with some other stuff he’s mentioned on the Excel UserVoice site. Sure, I could write some VBA to add a custom ‘Insert Row’ entry to the right click menu, but I try to keep my templates macro-free as much as possible. Some absolutely need to be macro free. And I wouldn’t need macros anyhow if this functionality actually did what it says right there on the box.

Please go vote for this. And Microsoft, please go fix it…It’s got 191 votes already, and counting. And it is just sooo broken.

BBj Developer Wanted

I’m looking to hire a BBj developer in the Omaha, NE area. Why would a reader of an Excel blog care? I’m glad you asked.

BBj used to be called BBx. And that was formerly known as Business Basic. If you’re a decent VBA or VB6.0 programmer, BBj will look pretty familiar to you. I’ve been coding in it for that last couple of months and, other than the constant trips to the documentation for syntax help, it’s going pretty well. The official IDE is Eclipse with a plugin, but we’ll be switching to UltraEdit very soon.

Here’s the basics

What you’ll do:

  • Fix bugs reported by users
  • Add new features, both large and small, as business needs dictate
  • Enhance existing features for stability and usability
  • Monitor system health and correct issues timely

Required Skills:

  • Bachelors degree and three years of software development experience or
  • Equivalent software development experience

Desired Skills:

  • Experience programming in BBj, BBx, or Business Basic
  • Experience programming in Visual Basic 6 or VBA
  • Experience in the travel center, c-store, or wholesale fuel industries
  • Experience with Subversion, UltraEdit, and iTop

Send me an email at dick@kusleika.com if you’d like more information.

International Keyboard Shortcut Day 2017

Another November. Another first Wednesday. Another International Keyboard Shortcut Day. The day when people from all over the world become far less efficient in an effort to be more efficient the rest of the year.

Let’s mix it up a bit this year. Instead of me listing various levels of participation, I want to turn you into an evangelist. No, you won’t be required to best the devil in a fiddle playing contest or anything like that.

Today, tell someone else about a keyboard shortcut you like. You can, for example, casually mention to a co-worker how much you enjoy using hyperlinks since you learned the Ctrl+K shortcut.

Even better, you could exclaim loudly throughout the office how you wish there was an easier way to switch worksheets in Excel. Someone may yell back “Just use Ctrl+PgUp and Ctrl+PgDn” thereby educating the whole office. If nobody yells back, find a willing confederate and give him the answer and instructions about how to yell back.

Dramatic reenactments are another effective method of communication. Stage a skit in the cafeteria about an office worker at her wit’s end. You see, she has such a long list of sub-folders under her Inbox and the one she wants to click is never in view. She always has to scroll. Then she learns about Ctrl+Y and, later that day, becomes the CEO.

You might hear things like “Get out of my office!” or “Stop shouting. We’re trying to work here!”. Don’t be discouraged. Our message must be heard.

Renumbering Arrays in Code

I’ve got this bit of code where I’m listing table fields that I’m going to eventually Join into a SELECT statement.

As you can see, I needed to add a new field in position 1. Now I’m faced with renumbering the rest of the array. Terrible. So I wrote this:

Now I can copy the code, run this procedure, and paste the results.

Ahhh. Satisfying. Here’s how the stuff inside the loop works.

This splits the line into:

vaLine
0 fields(17
1 = “BOLState”

This results in:

vaLineStart
0 fields
1 17

Then I just concatenate the relevant parts back together with a different number.

Table tools add-in

Based on an idea by fellow Excel MVP Frédéric le Guen I have created a new small Excel add-in which makes your life using tables in Excel slightly easier. It sports a ribbon tab which contains a drop-down containing all tables in your worksheet so you can quickly jump to them and another one which displays all columns in the current table. Both items are also available in the cell right-click menu. The former when you right-click on a cell not in a table, the latter when you click on a cell within a table.
It also has an enhanced “Insert table” dialog which enables you to enter the name of the table and has the Headings checkbox turned on by default no matter what:
Insert table dialog

Check out a slightly more elaborate description and a download link

Enjoy!

Jan Karel Pieterse

Counting Files by Date

Someone told me we are posting more frequently lately. (For non-accountants, posting means taking the entered transactions and updating other files with the information.) Ever the skeptic, I decide to see for myself. Whenever we post, we produce a pre-post report in the form of

Pre-Post_Sales_Journal_yyyymmddhhmmss.TXT

PATH is a module level constant pointing to the folder.

If this was more than a one-off program, I would have written this line in a way that you could read it. The inner Split creates an array like

[0] = Pre-Post_Sales_Journal_yyyymmddhhmmss, [1] = .TXT

and I take the first element (the zeroth index) of the array. Then I split that further

[0] = Pre-Post, [1] = Sales, [2] = Journal, [3] = yyyymmddhhmmss

and I take the fourth element (index = 3) of that array. That’s my date in string form.

I put a bunch of dates in column A of sheet1 for as far back as I wanted to go. Then I add 1 to the cell to the right of the date. It turns out we are posting more frequently.

Applications Settings v2

I made a new version of my Application Settings addin as per Sébastien’s comments in my last post.

Application Settings Version 2

As you can see, there are new settings for the following.

Application.Iteration

Application.MaxIterations

Application.MaxChange

In the case of the last 2 settings, you’ll notice that there are Set, Reset and Save buttons. This is how they work,

Set: Set to the number that is entered in the text box.

Reset: Reset to the real default or “alias” default.

Save: Save an “alias” default instead of Excel’s real default. For example, if you prefer 120 instead of 100 for Max Iterations, you can set the “alias” default so that the form does not appear when opening or saving the active workbook if Application.MaxIterations is set to 120. Also, clicking the Reset button thereafter will reset Application.MaxIterations to 120. And using the Set button to set Application.MaxIterations to any other value than 120 will show the value in red font to indicate it is not the “alias” default.

Hope this is useful. Download the new version here.

Application Settings

As posted on my blog a few days earlier,

Back in 2005, I noticed something that worried me.

You may know already that switching Application.Calculation to xlCalculationManual can make various code run faster. It can be a big time saver.

The problem, as I see it, is not switching it back to xlCalculationAutomatic. Given that some people for whatever reasons might use Manual Calculation all the time, most people don’t, especially the vast majority of average users who probably haven’t heard of this setting. With Calculation still set to Manual, they might be looking at values that haven’t be updated. Even experienced programmers might be temporarily confused until they figure out what’s going on. Imagine someone in a sales department quoting incorrect pricing to a customer or doing a faulty presentation at an important meeting. Not good.

And now for what really worries me – saving files with this setting. Let’s try something. Close all Excel files, except one to use for testing. Now switch to Manual Calculation. If you don’t know how to do it in code, you can click Calculation Options on the Formulas tab, then select Manual. Now save in that setting, close Excel, and reopen the file. If that file is the first one to be opened, Excel Calculation will be set to Manual by default, and all other files opened thereafter will be affected too. Save any of them with this setting, and the same thing will happen if they happen to be the first file opened…

So, how do you know Calculation is set to Manual without specifically checking?

You can’t. (Actually there is a way to make it more obvious. See the comment from Jake Collins)

Now, imagine sending one of these files to colleagues or customers, then realizing something is amiss days later. Again, not good. In fact, downright scary.

So, also in 2005(?), I made an addin called Calculation Checker. It checks Calculation when you save and prompts you to do so as Automatic if set as otherwise (including Automatic Except for Data Tables).

I’ve found it useful, but since then I’ve thought there’s room for improvement, so I made something new.

As you can see there’s 3 menu items. The bottom 2, when toggle to “On”, check files when opened/saved for the following settings.

Application.DisplayFormulaBar

Application.DisplayStatusBar

Application.Calculation

Application.ReferenceStyle

If any of those settings are not at their default, the Application Settings form will be displayed. Non-default settings are displayed in red. (Yes, the first 3 should be obvious, but easy enough to miss if you’re busy, tired or both!)

Click the form’s controls to reset them individually, or just click the Reset Everything button, then the Save File and Exit button if you choose to. Alternatively, click the X button not to save the file. Note that any other file that are open will also be saved with these settings (unless you change them later), because they are Application settings, not Workbook settings.

And because the form can be opened directly from the Ribbon, you can easily change any of the settings at any time for whatever reason. Click the Show Settings button and you can see other settings that can also be reset when clicking the Reset Everything button, if the Include other settings checkbox is ticked.

Note
Keep in mind that these additional settings aren’t checked automatically. The form only resets them if you click the Reset Everything button as mentioned above. Also, if Application.EnableEvents is set to False by VBA code, my addin won’t check files when opening or saving as these are the events that trigger it. In fact, you should be setting this False if any of your code does open or save workbooks to prevent my code from running, then set it back to True before the code ends.

Hopefully this tool will be of use. You can download it here.

PS. I’m going on holidays for a few days so I’ll reply to comments (if there be any!) when I get back.