Daily Dose of Excel

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.

Sometimes when I write code, I feel as if I’m writing on a cloud with a unicorn’s horn dipped in angel tears. And sometimes I feel as if I’m using a sledge hammer. This is the latter.

There have been some PivotTable shortcuts posted about (here, here, and here). When I get too many shortcuts, I’m inclined to put them on a userform (like I did with cell formatting). To that end, I want a userform to appear whenever the Pivot Table Field List task pane appears, that is, whenever the activecell is within the boundaries of a PivotTable.

I start with a class module to capture three events. I created a class called CAppEvents that looks like this:

When the cell selection is changed, a different worksheet is selected, or a different workbook is activated, the ShowHideForm procedure is called. It looks at all the PivotTables on the sheet and sees if the ActiveCell is inside one of them. If it is, it shows a userform.

Globally scoped userforms can be tricky. Just because you close a userform, doesn’t mean that global variable is Nothing and you get an automation error. Now for the sledge hammer part: I hide the userform, destroy it, and recreate it every time. It avoids the error, but isn’t exactly pretty. I need to work on retaining its position too, as now it simply shows it in the same spot.

The userform has two special properties set. I set ShowModal to False so that code execution continues and so that the user can work with the PivotTable while the userform is visible. And since I have multiple monitors, I set StartUpPosition to 3 - Windows Default. With a name like “Windows Default”, you’d think it would be the default in Excel, but it’s not so.

To make the magic happen, I have a module called MEntryPoints that holds the global userform variable, the global class variable, and the code to set it all up.

It seems to work reasonably well. I’m sure I’m missing an event that has to do with the ActiveWindow, but I’m not too worried about that. I really detest using the SelectionChange event. It’s constantly firing even when I’m not using a PivotTable. When I find myself using SelectChange, I usually abandon the code and trigger it manually, which I may end up doing here.

Then there’s the issue of what to put on the userform. I initially want three things. I want some pivot field formatting options. I want to toggle between Sum and Count and maybe a couple of others. I want to group by Years and Months and Years, as those are the two I group by the most. Anything else?

In one of the social.answers.microsoft.com web forums (Microsoft’s replacement for the newsgroups no longer hosted on its own server) someone wanted to see a regular chart version of a sparkline when s/he clicked on the cell. I thought that was an interesting idea since one can see much more detail in a large object than a tiny sparkline. Here’s my take on it. The version below improves on what I posted in social.answers by correctly handling cases where the source of a sparkline is another sheet / book and by hiding the chart if the selected range contains multiple cells or if the selected cell does not contain a sparkline. This version is also more modularized.

In the code module of the workbook that contains the sparklines:

Then, the code below goes in a regular module.

The function getBigChart returns a reference to an existing chart that was previously created by the code or creates a new one, if necessary. If it finds an existing chartobject named SparklineBigSis it does not adjust any format or size attribute. This allows the user to customize the chart once it is created.

The subroutine setChartType selects a chart type based on the type of sparkline in the selected cell.

The setChartSource routine adds a series to the chart and sets the series’ source to that of the sparkline. It also accounts for the different formats in which a sparkline returns its SourceData — if the data are in the same sheet as the sparkline there is no sheet information.

The AutoBigChart routine then essentially just calls each of the support routines as needed.

On an ongoing project, I had to create lighter shades of a color. Since we were working with Excel 2007, I decided to use Range.Interior.TintAndShade to achieve the desired result. TintAndShade is a number between -1 and 1 that lightens or darkens a color. Initial tests showed it seemed to work fine. In the image below, the first row contains 5 ‘base’ colors. Each subsequent row contains a slightly lighter shade of that color until eventually we get white.

The code to get the shades for 1 column is below. Select any number of cells in a column with the ‘base’ color as the first cell and run doTintAndShades. The above example used a selection of 8 cells in each column.

Done, one would think. Well, not quite. With Yellow as the base color I got:

After convincing myself that it wasn’t me nor my understanding of TintAndShade, I decided to separately lighten each of the primary colors Red, Green, and Blue.

with the result:

I then decided to check Excel 2010. The TintAndShade code works just fine. So, apparently, Microsoft fixed whatever ailed the Excel/Office 2007 TintAndShade property.

Rob wrote a white paper called Spreadsheet Addiction: The True Cost to the CPG Industry. In it, he lists the reasons why Excel is so popular and widely used and he lists the reasons why it costs more than you think. I’d excerpt some of that text, but I don’t know how to select text in a pdf that opens in my browser and I don’t care to learn. So you’ll have to go read it for yourself. It’s only five pages.

When I worked for a commercial general contractor in the construction industry, I was fortunate to be able to use Timberline accounting software. It was written by accounts specifically for general contractors. Similarly, Dexter+Chaney makes software that subcontractors rave about. When a company has to change their processes to accommodate either of these packages, they’re probably doing themselves a favor.

When I moved to a manufacturing company, one of my first objectives was to dump Quickbooks and find the Timberline of the manufacturing industry. What I found was a bunch of applications that did about 75% of what I wanted them to do. I already had Quickbooks, which does about 50%. So the value proposition was paying more than $20,000 for the next 75%. The remaining 25% would still have to be done outside of the package. Needless to say, five years hence I’m still using Quickbooks.

Outside of a few industries, it’s very difficult to find software that meets all of your needs. Property management commpanies, attorneys, public accountants, and doctors are all examples where you probably can find such a package. But manufacturing, like most other industries, is just too diverse for any company to produce great software; great for your business, that is.

That leaves custom applications that bolt on to your multi-thousand dollar package. Small companies certainly don’t have the resources to write their own in-house .NET-backed-by-SQLServer application. And it’s not just small companies. Many people reading this blog make their living because companies large and small don’t have the resources to do it or haven’t set those priorities for their IT departments.

That leaves accountants, engineers, production managers, and owners to do the job. What do they use? You guessed it: Excel and Access.

So let’s stop trying to convince people to eschew these fine programs. Rather, let’s educate them in how to develop responsibly by using best practices and understanding the risks and limitations. Now I’ll step off my soapbox and hear what you have to say about it.

Carrie laments:

Is there a way to change the default in a pivot table so sums the values when created rather than count? It is very time consuming if you have several data points.

I hear ya, sister. Excel does a pretty good job guessing, but sometimes it gets stuck on Count like that’s all I want to do. I don’t know of any way to force it to Sum or Count or any of the other functions available. So I wrote this little macro the make the job easier.

Select a cell in the PivotField and run this macro to toggle between counting and summing.

In Microsoft Word’s print dialog, there’s a textbox called Pages. In it, you can specify which pages you want to print by separating individual pages and page ranges by a comma.

I have a small app wherein I enter a starting serial number and an ending serial number. The output is a string of serial numbers in between. I wanted to be able to deal with nonconsecutive serial numbers a little more elegantly, so I set out to replicate Word’s Pages textbox.

The Split function returns a zero-based array of elements after it splits a string by the delimiter. If the delimiter doesn’t exist, Split return a single element array with the original string. Because of this, I don’t have to test if there’s a hyphen (meaning a range) or not (just a single number). If it’s just a single number, UBound(vaHyphSplit) will be zero and lUpper and lLower will be the same. That means the inner loop will execute exactly once and put lLower in the result array.

Error checking needed: I put a Val() in there to help with non-numerical stuff, but that needs to be beefed up. I also need to make sure the number before the hyphen is less than the one after. Sounds like version 2 “features”.

In the office, I sometimes deal with a table of values. In that table, there are good rows, and bad rows.
I want to quickly put the good rows to the top of the table, and leave the bad rows at the bottom of the table.

So I take the first unused column, give it a rubbish column header, such as “qwe”, and build a formula in the next row down ready to autofill.
The formula will return TRUE for the goodies, and FALSE for the baddies.
After an autofill, I’ll sort descending by that column, which puts the goodies at the top.
Usually the formula will be something simple, like

In that way, all of the bad rows can be dealt with as a group, rather than scanning through the rows fixing one at a time.

There’s another one I regularly do. It’s “is this value in my other list”. Kind of like an integrity check.
I’ll have two tables - a primary table and a secondary table. I need to know if rows in my primary table are linked to rows in my secondary table.
For example, Employee not in Attendees list. Purchase Order not in Invoices list.
I’ll build a column on the side of the primary table that returns either TRUE or FALSE and use a lookup formula to do the work.
Once I have a column of TRUEs and FALSEs, it’s a simple matter to sort, then focus on the baddies.

In fact, I do these types of yes/no lookups so often that I remember the pattern to building the formula off by heart. Sad, but true.

1. NOT followed by ISNA followed by MATCH

2. Value to look up

3. List to look up

4. 0 for exact match

5. three closing brackets

6. Highlight the “list to look up” text and hit the F4 key to make it absolute.

Now that I think about it, I’ve probably typed that line of text over 1000 times and I really should write a macro for that.

While I’m writing about the lookup, here’s a way of doing a lookup all without an external list.
It’s possible to bring, for example, B3:B9 into the formula while still retaining the MATCH function - what I call an inline lookup.
Simply highlight the B3:B9 text, and press F9 on your keyboard.
By pressing F9, it brings the list into your formula as an array.

Usually you’ll want to leave the list as a range of its own, but depending on the occasion, it could be tidier to have it inline.