Daily Dose of Excel

If you want your Excel app to default to a specific directory when opening or saving files, see Changing the Current Directory. Be sure to read the comments.

I have a slightly different situation. I want my app to open to a specific directory that will give me easy access to sub directories. However, if the current directory is already a sub directory of my base directory, then it’s pretty likely to be in the one I want and I don’t want to change it. For example, my base directory is

If I’m in S:\Flash\Payroll\2010\0824\", then that’s probably the directory I want and don’t want to change. However, if I’m in S:\Flash\Accounting\Reporting\, then I want to change to my base directory.

Pretty simple. If it’s at the base or a sub directory, don’t do anything. If it’s anything else, go to the base directory. Upon further reflection, though, it seems that a more general purpose procedure is in order.

Now I can call it from multiple locations and pass in the base directory. If the left x characters is the base directory, don’t do anything. Otherwise, change the drive to the first two characters of sBase (e.g. “S:”) and change the directory to the base. It won’t work with UNC paths. Any other problems with it?

Jake wants to know, given an active date and an inactive date, how to count the customers that were active in a certain time period.

The ones we want are highlighted in yellow. The formula is

It’s easier to figure out who is not active during that date range and subtract it from the total. The formula starts by counting everyone using the COUNT function. The SUMPRODUCT function is then subtracted from that. It gives the total of all the customers who became active after our End date plus the customers who became inactive before our Start date.

Note that this formula excludes the actual Start and End date. Customer 17 isn’t included because we’re really looking at 7/16/2010 to 7/24/2010. If you want the formula to be inclusive, simply change the <= and >= to < and >, respectively.

If you don’t like SUMPRODUCT, you can get there with COUNTIF

Sometimes it’s easier to turn the problem around and figure out who’s excluded.

A couple of weeks ago, I posted some code to print labels on a Dymo LabelWriter 450. I wanted to post the finished code because it has a few more tricks in it.

The procedure is now a function that returns a Boolean because I use the error logging scheme described in PED. But if you ignore all that stuff, there are a couple of changes worth noting.

That would prevent errors if someone moves or renames the label file. Always build in some potential errors for job security (just kidding, don’t do that). Someday when I have some more time, I’ll tighten up the code further. But for now, it will have to do.

Last week I was creating file folder labels with my new Dymo LaserWriter 450. The information on the folder label is serial number, part number, company name, and purchase order. Normally, this works great. However, I ran into one on Friday with a 30 character part name and a 29 character company name. With shrink-to-fit, the font came out at about 4 points, surprisingly readable, but too small for these old eyes.

When we did this manually on our old label maker, we would abbreviate the company name and I wanted to replicate that in my code. It’s easy to do for humans. Less so for VBA. I started by removing all of the lower-case vowels.

General Electric -> Gnrl Elctrc

Not bad. Not, of course, how I would abbreviate it manually (GE), but not bad. By only removing lower case, I preserved the first letter and any initials that would seem to be key to deciphering the name. I still had names that were too long. I was trying to get them to 15 characters or less, and this method only got about half of them to less than that. Next, I removed Inc., LTD, Corporation, Corp and all that extraneous stuff that’s not critical to identifying the name. You have to be careful with case sensitivity or you can end up with

Principle Technologies, Inc. -> Prcpl Tchnlgs

because of the embedded “inc” in Principle.

Finally, I removed punctuation and got

If you implement something like this, you want to make sure that the longer terms come before the shorter ones. If you remove Corp before you remove Corporation, you’ll end up with “oration”, which doesn’t make any sense.

In the end, and a little to my surprise, this didn’t work very well. Looking at the transformed names, I was able to identify about 80% of them. That’s not very good, in my opinion. Then my co-worker suggested I use two lines on the file folder label. I smacked my forehead and did just that, and of course, it works beautifully. It was a fun exercise nonetheless.

A favorite peeve of mine is code with “bushy trees.” I first saw this phrase in Kernighan and Plauger’s Elements of Programming Style.

Recently, I saw some code that checked if a RefEdit control referred to a single cell that contained a non negative integer. I cleaned up the formatting some since the original indentation style might be best described as “random tabs.” But, code formatting is not the subject of this post.

The IFs are fairly easy to understand since they essentially follow how most people would think to validate the string parameter. The tricky part comes in ensuring that one has the correct Else clause at the correct indentation level. In this case, that too might not be too difficult since the Else clauses are fairly trivial, each consisting of a single boolean assignment. But, imagine how much more difficult the task would be if there were further If clauses or loop structures in some of the Else clauses!

Code Sample 1

As a first pass, one could remove all the boolean assignments by first setting the function value to false and not to true as in Code Sample 1. Then only if we have an acceptable value do we return a True value.

Code Sample 2

However, this still doesn’t help with all the nested If and End If clauses.

So, how does one clean up this deeply nested code? How about if we reverse the tests? Instead of testing if the range is not nothing, test if it is nothing. Instead of testing if the range contains 1 cell, test if it contains more than 1 cell. And, so on. The result as shown in Code Sample 3 is a single If statement (with multiple ElseIf clauses) that is ‘flat’ — with no confusing nesting!

Code Sample 3

The code above uses a very powerful concept — that of a ‘null clause.’

In most cases, when we have a If…Then, we have some statement in the True branch of the If statement. It might be a series of assignments or it might be another If or a loop of some sort but there is something in the True branch. For example, in Code Sample 1 and Code Sample 2 above, there are two assignments.

However, in Code Sample 3, each Then is followed not by a statement but by the ElseIf clause. This results in a null statement (or empty block) in the True branch. This is perfectly legal in every programming language I’ve used and in this case it serves a very powerful role in simplifying the code.

For the sake of completeness, we will look at one more way of coding the above. In this particular scenario since there is no further processing after the string is validated, one could use use a series of Ifs that simply go to an exit point for bad data. But, this would not work for scenarios in which we wanted to do additional processing after the string of Ifs.

Code Sample 4

Hi folks,

Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites.

Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that!

Anyway, as I was looking at my web stats today I discovered a nice feat: We’ve just passed the 100,000 download count on the tool (this excludes the downloads from Charles’ site, so we can safely assume the true number is at least 50% more than that). Time for a celebration:

Hurray!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

A tip from Scott:

Windows - Unhide, from the menu, shows the Unhide dialog box for unhiding workbooks.

In vba, Application.Dialogs(xlDialogUnhide).Show gets the job done.

Where it gets tricky is unhiding worksheets. Via the menu, Format - Sheets - Unhide

In vba, the name of the dialog is not so intuitive.

Poorly named, for sure, but you’ve been warned. Thanks Scott.

I recently had to make some file folders at work. About five minutes after I was done, I purchased a DYMO LabelWriter 450. I was using one of those label makers where you punch in the text, hit print, and press down on a lever to cut the label. Then you have to take a pair of scissors and cut along the dotted line to get the right length. THEN you have to spend ten minutes fumbling with the backing. Brutal.

When I installed the software, it installed quite a few libraries. I wasn’t sure which one to pick, but after a little experimenting, I chose the Dymo Label Software v.8 SDK.

With my reference set, I tried a few different objects without much success. If there’s documentation for this, I haven’t found it. As usual, the object model leaves a lot to be desired. Nevertheless, I persevered and came up with this:

Let’s step through this and try to see what’s happening. I start with a DymoHighLevelSDK object - what an awful name. This object has two properties: DymoAddin (what an awful name) and DymoLabels. From what I can tell, DymoLabels is only one label, from which I can only conclude…wait for it…they gave it an awful name. I needed to create both the DymoAddin object and the DymoLabels object. I would have thought that I could get the current label from the DymoAddin object, but that doesn’t appear to be the case.

I set the printer I want to use using SelectPrinter and GetDymoPrinters. I guess if you have more than one Dymo printer, GetDymoPrinters returns some delimited string. But I only have one, so I don’t know. Either way, it’s stupid. I discovered that GetDymoPrinters returns my one printer, so I pass that to SelectPrinter and that seems to have worked.

Next I open my .label template file. There’s also an Open2 method, but I don’t know the difference. Am I done bashing the DYMO programmers yet? Not even close. If you have two Open methods, don’t freaking name them Open and Open2. Name them something understandable like Open and OpenPriorVersion.

I tried Set dyLabel = dyAddin.Open(etc...) but got a Type Mismatch error. As far as I can tell, when I call the Open method to the DymoAddin object, the DymoLabels object automatically becomes whatever was open. Idiotic.

My label has one object on it called “Text”, so the SetField method was pretty straight forward. It reminds me of Quickbooks in that there are few, if any, properties and everything is a method.

Finally I tried the Print method, but was rewarded with “Object doesn’t support this property or method.” One of my favorite errors. Out of desparation, I chose the Print2 method. I had to include a third argument for PaperTray, which is utterly ludicrous if you look at the LabelWriter 450. But it worked.

I wanted to set the ShrinkToFit property of the Textbox to TRUE, but I couldn’t figure out how to do it. It did it automatically because I set that “property” when I created the label template, but it would be nice to be able to set it in code.

If I had written the object model, my code would have looked like this:

Anyway, this post should get a million hits. Or maybe it will just get 100% of the hits from the 12 people who care about this.