Those "Duh!" Moments
(Note: technical post is technical. If you don't understand anything I'm saying, just politely nod, say "That's nice," and move along.)
So you know that thing where you did something many years ago, and then later found a solution that was more efficient and in other ways just a better solution?
For the last 12 years or so, I have essentially operated as my own personal IT department at work. I have written several dozen solutions using Microsoft Office VBA functionality of Excel, Outlook, Word, and Access. I just take a task that is tedious, time-consuming, repetitive, and error-prone, and try to see if I can program a VBA module to handle it.
I often find that I need to know how many pallets need to be pulled from the warehouse and where they're going so I can best allocate resources to where they are needed the most.
Many years ago, I requested this function from the IT department through management. Management responded that they didn't see what purpose such a thing would serve even after I had explained it to them.
So I made my own.
Unfortunately, my employer still operates in the 19th century, so they have an AS400 system that I'm convinced was purchased sometime during the Hoover administration and I'm not entirely convinced it doesn't have a pull-start. This is slowly changing, with a capital "slow." That means that I do not have direct access to the SQL engine, but rather connect to the mainframe via VT52 emulation. Fortunately, the emulator we use (the Personal Communication suite developed by IBM) exposes objects to the VBA host that allows for remote access to the emulator via VBA, and thus indirectly the AS400.
Here's the other problem: the list that tells me what's coming from the warehouse does not tell me where it's going, specifically. That's on another program and each division has their own (I deal with several divisions simultaneously). My macro (I know what I'm describing is not technically a macro, but it's something that automates a process, which is close enough for corporate work) polls the list of pull downs, gets the SKU and division it's for, and goes to the picking location program for each division to look them up one-by-one so I know generally where it will end up. I then schedule an update every n minutes (this defaults to every 15 minutes, but can be changed), updates a Pivot Table with the results, then can email me the results to my iPhone if I choose.
The problem is that while it is doing this one-by-one location lookup, it will lock up that instance of Excel to the point where it is effectively unusable while it is doing this. Additionally, and I don't know if it's a problem with my code, IBM's reference objects, or Microsoft's VBA host, but there are times when it will slow down to an execrable crawl and then die without so much as a whimper. The process must then be restarted, and if I'm in a remote location, it can be nearly impossible to do so.
I originally developed this solution several years ago, and it has served me fairly well, but its problems have bugged me and I began searching for better solutions.
I experimented with creating a stand-alone VBA module, but there wasn't an IDE that I could find that would give me the functionality I need such as syntax checking, function auto-fill (that thing where you start typing and it usually can come up with a helpful suggestion), and so on.
The other day, I was thinking about this problem and the solution hit me like a bolt out of the blue. I can create reports on demand that get exported to a Excel Workbook on SharePoint that cross references SKUs with picking locations for each of the divisions I deal with. Then it would be a simple matter of a
VLOOKUP on the report appropriate for each division, and I could be looking up all of them simultaneously instead of one at a time.
Today, I started implementing this change (it was a slow day), and I was right: it is much faster, by several orders of magnitude. It still needs to get the kinks worked out (the importation process of one of the reports is not going the way I thought it would, and may require a re-think).
What is odd, and what is making me go "Well, duh!" is that I have had access to these reports for several years, and have used them in a similar capacity for other tasks. Why I didn't think of it sooner, I'll never know.