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.

Inconsistent ListRow Copy

Here’s a cautionary tale for you. Let’s say you want to let users copy rows from a Source table row by row to a Dest table, by pushing a button:

…so you whip up a bit of code like this, and assign it to that button:

And when you run it, it works just fine:

…until that is, someone hides an entire column in the source table, sets a filter that similarly hides some rows, and leaves a cell in that Table selected before running the code again, in which case you get this:

As you can see from the above:

  • For any ListRows in your source that happen to be visible, only the cells from the visible columns get copied, to a contiguous block in the Dest table, but
  • For hidden ListRows, all cells get copied

Add to this the fact that everything works just fine if the user happened to select a cell outside the table before triggering the code:

…and you’ve got the makings of a hard-to-diagnose bug that will eat up hours of your time trying to replicate.

The fix? Don’t use the .copy method. Just set the values of the second range directly to the values of the first:

…which works fine, and is faster anyhow:

In case you’re wondering what happens if you bring the whole DataBodyRange through from the Source Table using that dangerous .Copy method i.e using code like this:

…then again the results depend on whether a cell is selected in the Source table:

…or not:

Again, avoid the inconsistency by setting the values of the second range directly to the values of the first:

…which works fine, fast:

Here’s a sample file:LRCopy Test