Happy Gerbitz Day

This is the first year you can sing “Happy Birthday” to Excel without having to pay a royalty, so that’s nice.

Here’s my uninteresting Excel story: My first spreadsheet program was SuperCalc. I remember we had orange screens on our PCs. Eventually we graduated to VGA monitors and Lotus 1-2-3 v.1A. I stuck with that version for a long time. I had tons of keystroke macros – whatever the heck those were called in Lotus – and I wasn’t giving them up. Excel burst onto the scene and I barely blinked. I don’t need that fancy new stuff. I used v.1A until 1992 and I think 1-2-3 was on v4 by then.

In 1992, I miraculously got a job at KPMG (nee Peat Marwick). Apple was a client of KPMG, so everyone got a Mac and used Office. It was like living in hell. I was a PC, Lotus 1-2-3 guy and I was forced to use these toys in business. Over time, I got used to my Mac Plus, then my SE30, then my PowerBook. And, of course, I got used to Excel and its obviously superior features to the version of 1-2-3 I was using. I don’t remember what version of Excel that was, I just know that most people in my office sucked at using it. Thankfully 25 years later, every office worker is, at a minimum, competent at Excel. What? That’s not true, you say? There are still people who work with Excel and aren’t competent? What the hell have they been doing for the last 25 years? It’s not like learning Excel is exactly cutting edge. End rant.

Being forced to use Office was probably a pivotal point in my life. (Using Macs in an accounting firm in the early ’90s was just stupid.) Pivotal though it was, I think what really turned me into an Excel geek was an intranet message board that KPMG had. I think they called it the KPMG Knowledgebase, but my memory isn’t so good. I could go on the message board and answer people’s questions about Excel. And I was hooked. Then it was on to newsgroups (nntp), a blog, and the crazy post-Microsoft newsgroup period that has mostly meant StackOverflow.com for me. What the heck did I do after MS closed the newsgroups but before SO? I don’t remember. I know I visited answers.microsoft.com once, saw a terrible answer from a moderator, and saw that the moderator had selected his own answer as “the” answer. I haven’t been back.

When I first started on the newsgroups, I was more of an Access guy than an Excel guy. I was surely answering more Access questions than Excel at the beginning. It was when I started reading Chip Pearson, Rob Bovey, Stephen Bullen, and others posting about VBA that the tables turned. I realize that Access has VBA, but the Excel object model was, and is, a thing of a beauty. I still do plenty of Access work, but it pales in comparison to the time I spend in Excel.

Other random memories:

  • At my first MVP Summit, everyone thought I was going to be a 60-year-old guy and I was in my mid-thirties. I guess I came off as cantankerous mature in my newsgroup postings.
  • I remember after a year of DDoE, a bunch of fellow Excellers joined as authors. There were some great posts back then.
  • I remember applying for a job and taking an Excel and an Access test. I aced them both. The secretary was looking at me like I was a witch. (If you’re reading this blog, you could ace them too.)
  • I remember planning an Australian Excel conference over beers and actually going through with it. If I had a nickel for every plan I made over beers that came to fruition, I’d have a nickel.

Connection Properties of External Data Ranges

I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties.

I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty likely that I want to see the properties of that particular connection and not just a list of all connections. Of course I’m awesome at naming my connections so I don’t have to guess which is which, but if you weren’t so awesome you might have trouble distinguishing them.

The long-term answer is to write my own interface to change the things I want to change. But in the mean time, I want to open the connections dialog and highlight the connection related to the table I’m in, if any.

When I open the Connections dialog, I can start typing the name of the connection to get down to it. For example, I could start typing “dup” and it will highlight the first connection that starts with those keys.

With SendKeys, I can type the entire name. First I see if the ActiveCell is in a QueryTable. If it’s not, I just open the dialog. If it is, I open the dialog, wait a couple seconds, then send all the keys in the connection’s name. SendKeys can be very dangerous, but we’re just experimenting here.

What the above code actually does is open the Connections dialog, wait for it to close, then send all those keystrokes into the ActiveCell. Dangerous. And not helpful. Apparently the Connections dialog is modal and all code is suspended until it’s closed. I did a little searching and found this command, which does not help.

Maybe the old CommandBars behave differently than the Ribbon.

Nope. Same as ExecuteMso. One last try. This opens the dialog with SendKeys.

And it works. For some reason sending Alt+A+O opens the Connections dialog modeless, the SendKeys executes, and takes me to the “active” connection. I have a couple of applications on my machine that like to steal the focus, so I try to avoid SendKeys whenever I can (which is always). In this code, I’m using it twice, so I won’t be using it all. Interesting, though, that it seems to be the only way to get what I want.

Along the way, I discovered I could get to the “active” connection’s property sheet with this key sequence:

  1. right-click key
  2. b
  3. a
  4. tab
  5. tab
  6. enter

I guess that will work. It’s a lot of keystrokes, though.

MaxMinFair Rewrite

I read Charles William’s MaxMinFair algorithm and I didn’t like his approach. That’s typical. I’ll read somebody’s code and think “They’re making that too hard”. Then I’ll set about rewriting it. In this case, as in most cases, it turns out that it is that hard, but I wasn’t going to convince myself until I tried it. I ended up with a different approach that’s not shorter, not easier to read, and not easier to follow. Oh well, here it is anyway.

In Charles’s implementation, he allocates an equal amount of the supply to each node, then takes back what that node didn’t need and puts it back in the available pool. When I was looking at the results, I was thinking that the smallest n nodes simply get their demand and only when there’s not enough to go around do we need to do something different than allocate the full demand.

In my implementation, I start by giving everyone what they demand. Then I start with the smallest demand, and if I can accommodate that amount for everyone, I just reduce the amount available and move to the second smallest demand. At some point (the sixth smallest demand in Charles’s data) I can’t meet that demand and still give everyone an equal share. At that point, I give anyone who hasn’t had their demand met an equal amount – the amount that’s already been distributed plus an equal share of what’s left.

Rank Demand Incremental Demand Allocated Remaining
        18.30
7 0.70 0.70 4.90 13.40
6 1.00 0.30 1.80 11.60
5 1.30 0.30 1.50 10.10
4 2.00 0.70 2.80 7.30
3 3.50 1.50 4.50 2.80
2 7.40 3.90 7.80 (5.00)
1 10.00 2.60 2.60 (7.60)

In the first iteration, I hand out 0.70 to everyone because I have enough supply to do that. In the second iteration, I had out the differential, 0.30, to everyone who’s left because I have enough supply remaining. When I get to #2, I can’t hand out 3.90 to the remaining two nodes because I don’t have enough supply. I’ve allocated up to 3.5 to anyone who’s demanded it, so the last two get the 3.5 plus half of the 2.8 that remains.

Although I didn’t accomplish anything, it was still a fun exercise.

From True and False to Yes and No

I’m writing some code to turn the contents of class modules into an XML file for Affordable Care Act compliance purposes. The XML file spec says that my flag for whether the dependent is a spouse is “Y” or “N”. In my class, I have a Relation property that can be “Son”, “Daughter”, or “Spouse”. I made a new property to return the “Y” or “N”.

I hate writing all those lines to convert a Boolean into something else. I know it’s not that big of a deal, but it just bugs me. So I fixed it.

Now that’s fancy. The comparison is made and the True or False is converted to a Long via the Abs() function (to turn True to 1 instead of -1) and the proper element of the array is selected. It’s still not good enough.

Yeah, that’s better. But it’s so specific to spouses. Spouse is a dependent that gets special attention, so I don’t mind having a dedicated property to it. It’s appropriate for the domain, I think. But if I wanted to really generalize the hell out of it, I might make an IsRelation property and then take my conversion property into a function.

Now I can have complete customization of the return string.

Searching Text Files in a Directory

I have several years of vendor invoices, in text file format, in some directories on a share. I need to search through these text files to find an order number, manifest number, or some other piece of information. I can’t search everything because it would take too long. And I don’t have control over the server, so if there is some indexing that could be done, I can’t do it. I’m stuck with good old VBA.

The folders are yyyymmdd (ex: 20150725 for July 25th) and corresponds to the invoice dates for any invoices in the file. Each file starts with a three letter abbreviation of the vendors name. Invoice date and vendor name are the only two pieces of information I can use to limit the search. The final piece of information is, of course, the search term. Here’s what the form looks like

I have a table of vendors and codes to populate the Vendor combobox. The QuickDate combobox populates the Date Range textboxes and contains common date ranges, namely, Last Month, This Month, Last Quarter, This Quarter, Last Year, This Year. I can change the dates to whatever I want if there isn’t a Quick Date that suits me. The Search Terms textbox takes a space separated list of terms to search for.

And now the fun part. The code. This converts the Quick Dates into real dates

This makes sure a real date is entered, but provides for 6 or 8 digit date entry.

And the big one, the actual search. This is pretty long and needs to be refactored, but it works for now.

It takes about 60 seconds per month to search the files. That’s a long time so it’s necessary to entertain the user while he waits. The top entry in the results listbox is whatever the current file is. It rapidly changes the display as it loops through the folder. When there’s a hit, that file becomes the second entry and any prior hits move down. This little animation lets the user know that it’s still working and gives him a list of what hits have been found already.

You can download SearchTextFiles.zip

Setting the Tab Order of Userform Controls Programmatically

I hate designing userforms. I mean the part where I’m lining up controls, renaming controls, and all the other super-fiddly parts of making a form look and act right – like setting the tab order. I always wait until the very end so I don’t have to do it twice. And that Tab Order dialog? Forget about it.

When I’m finally ready to set the tab order, I find the first control and click Move Up until it’s at the top. Next, I find the second control and click Move Up until it’s just below the first control. Then I go to Whammyburger and force them to serve me breakfast. Then I go back and do the rest of the controls. It’s maddening. I decided finally to do something about it.

First I set a reference to the VBA Extensibility Library.

Then I put this code in a standard module

This is pretty brute force, but it only take a few seconds to run, so who cares. The i and j variables move through the userform point-by-point, left to right, top to bottom (that’s the typographical point, not the agenda items point). For every point on the userform, it loops through every control to see if that control starts at that point. If it finds one, it sets the controls TabIndex to the next number. At the end it just prints out the control names and tabs.

Even if that’s not exactly how I want my tab order, it gets it close and I can make other modifications manually.

If you have a super-wide or super-long form, it will take longer. The right way to do this is to put all the controls in an array and sort them by their Top and Left properties. If this method is too slow, you should write that up. Then send me a copy.

DDoE Servers

Don’t call it a streak, but it’s been almost five days since MySQL crashed. I made a change late last week that masks the problem appropriately. I still don’t know what causes the problem, and probably never will, but as long as the server stays up, I’m in a better place. I’ve been fighting this for over a year.

I run DDoE on a 1GB virtual server at Digital Ocean. The heart of the problem is that a memory usage spike causes the Ubuntu kernel to shut down the MySQL service and it never gets started again. Memory management is one of the things a kernel does, so it’s not strange that services get kicked out of RAM when there’s a spike. MySQL will try to restart itself, but it’s not able to because it doesn’t have enough memory. I don’t know what’s causing the memory spike. I do know that the only services of consequence that are running are MySQL and a bunch of Apache2 services. Apache2 is the web server that directs the traffic.

I don’t recall where I read it, but someone on the internet said that it’s likely Apache2 is spawning too many processes. I don’t know how to verify that. I followed the advice on ServerFault on how to tune Apache on Ubuntu. When I shut down Apache, I could see that I was using about 660MB of memory, leaving 340MB free. I was surprised how much memory it was using without Apache, which I assumed would be the biggest component. Using the

command, I could see that an Apache2 process was using 30MB on average. That means if I limit my Apache2 processes to 10, I should not run out of memory. I changed the MaxRequestWorkers parameter from 150 to 10, and so far it’s worked.

There must be a cost to that change. I imagine that when the memory spikes, for whatever reason, and Apache isn’t allowed to spawn more processes, that the user gets an error and is unable to reach the site. As bad as that is, it’s better than what was happening – MySQL would shut down and nobody could reach the site until I got up the next morning and rebooted.

Now that we’re seemingly stable, maybe I’ll make some posts. I’ve been working in SQL Server a lot lately and have a lot to say about it.

Document Not Saved Error on ExportAsFixedFormat

Recently, I was accusing ExportAsFixedFormat of causing a problem with some code. It turned out to not be the culprit. But I was suspicious because I very often get a Document Not Saved runtime error when I export to PDF in a loop. When I get that error, I can click Debug and F5, and it happily continues until it errors again. Of course, watching something loop eighty-six times to make sure it doesn’t error kind of defeats the purpose of the loop. Today, I took a stand.

I have this code that changes the page fields on a pivot table and exports a range. The result is eighty-six PDFs in a folder that I sew together into one big report. There are two page fields, so there are two loops. Here’s the inner loop.

You see my pathetic DoEvents attempt at avoiding the error. The error stops on the ExportAsFixedFormat line, so the DoEvents doesn’t actually help. When the error happens, a .tmp file is left in the directory. And when I do the Debug, F5 thing, the .tmp file stays there forever. Clearly this is a temporary file that would someday become a PDF if not for this error.

The .tmp file is my evidence that the processed finished. I can ignore the error and as long as there is no temp file, I’ll know the error never occurred. I rewrote the loop thusly:

As long as there’s a file with a tmp extension, I keep trying to export. The Kill statement needs to be inside the On Error because there won’t be anything to Kill the first time.

After a rigorous test of one time, it worked. I should have put a little loop counter in there to see how many times it errored. And maybe to exit out in case it gets into an infinite loop situation.