Daily Dose of Excel

CR rants:

One thing about Excel 2007 that really bugs me are the tab colors. Most of the choices look so washed out, rather than a nice solid color.

One does not have a choice when Excel makes the tab text white or black. Sometimes, Excel chooses a white font for the text when the tab color is light, making the text hard to read. For the most part, I would be happy if the text was always black.

I wish that Microsoft had chosen flat, solid colors for the tabs, rather than this “3-D” sort of look, which also interferes with reading the tab text. Also, the tabs are not tall enough.

I know they say you control the look of the tabs at the Windows level, but this affects the way that the rest of your system looks, and sometimes this look isn’t particularly attractive.

Has anyone else voiced these complaints?

A sample of tab colors in 2007

A sample of tab colors in 2003

I’ve never heard complaints about the tab colors. I tend to like the 2007 colors better than 2003, but I see the point about looking washed out. I’ve never noticed it before, but now it will bug me forever. The 2003 colors look, I don’t know, like they were done in MS Paint in 1986 by me. However, I rarely use tab colors. I’m an accountant, damn it. I like my data in tables and I like my spreadsheets in black and white.

What do you think of the Excel’s tab colors?

Hi all

FYI

I upload a new add-in named RDBMail for Excel/Outlook 2007-2010 this week
http://www.rondebruin.nl/mail/add-in.htm

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options
and a option to insert a worksheet template to your workbook so you can send more then one
mail with one click because the info for each mail is saved in the worksheet.

You have the option to send as workbook or PDF for every mail option.

If you have problems let me know

Regards Ron de Bruin

PHD did a post recently about consolidating data. I do this every other week with timesheets, so I thought I’d share my story.

I get timesheets as Excel workbooks via email and I save them to a special folder. I open them one-by-one and record the data on a summary sheet. Here’s the main entry point:

It’s a typical scheme for my code: create a class, read data in, calculate, push data out. The clsTimeSheet class has the property LastNameFirst. This is a read-only property. The employee name is stored in the class as it appears on the timesheet (first name first), and LastNameFirst is used to match that to what’s recorded on the summary sheet.

What makes this a read-only property is that there isn’t a Let statement, only a Get statement. You can’t store the name in the last name first format, but you can retrieve it in that format. This isn’t a foolproof algorithm for switching names around, but with only 25 employees I don’t need foolproof.

Here I’m just pulling data out of predefined ranges. For example, I know sick hours are stored in P20, so I hardcoded the address. The proper thing to do would be to make named ranges. “Do as I say…” and all that.

Finally, I spit the data back out via another read-only property, TimeArray.

I like having these read-only properties that return arrays. I only write this to a range, so I create a two-dimensional, base-one array with 1 row and 6 columns. In other applications, I need an array for a range and one for a listbox. The listbox will return a base-zero array and I usually make a separate read-only property for that.

When I wrote the TimeArray property, I definitely liked working with the module level variables (like mdVacation). I don’t do that anymore. Now I would write

That should be slower, which is why I preferred accessing the variable directly. It has to be slower. Yet, when I use the property rather than the variable, I notice no difference. I guess the apps I write just aren’t big enough for it to make a difference. And if I can’t tell the difference, I’ll take the autocomplete and increased readability. And in case you’re wondering, Regular is another read-only property so there’s no variable to read there.

I came across a couple of interesting web applications recently. The first is AccountingASAP.com.

Offering free invoicing is a great way to get their name out there and encourage users to check out their for-pay stuff. It reminds me a little of the free version vs. pro version model, but there are differences. They’ve pulled a small part of an overall accounting package and made it fully functional and available. There will certainly be people who use the invoicing and that’s all they want or need. It also shows off their programming and design ability, which may entice some customers.

The next is TrackVia. It’s an online database, but seems to be more than just that. I really need to get a few apps on the web so that employees around the country have easier access to them. For instance, we use Trac and Subversion, both of which offer a web-based front end. Trac is a bug tracker and Subversion is document management.

I have a few other Excel based apps that people would actually use properly if they didn’t have to VPN in and deal with using Excel over the VPN. I’ve installed Ruby on Rails and it’s all ready to start serving apps, I just don’t have enough time to port them from VBA to RoR. TrackVia, it seems, can do about 95% of what I want and it would take no time at all to set up.

I’ve got three problems with TrackVia. Their $250 per month plan isn’t big enough for my super small company. Their $250 per month plan costs $250 per month. And then there’s the matter of that last 5% of functionality I want. I watched the demos and I’m very intrigued by what it can do. If this kind of thing interests you, the 15 minutes of demo video is worth watching.

I’ve never needed to use an OLAP cube before recently. I’ll admit that I didn’t see what all the fuss was about. But I came upon a problem for which an OLAP worked perfectly. Theoretically.

In practice (Excel 2003), I experienced error after error. I rebuilt the cube from scratch several times. And, in the end, was unable to get it to work on any machine except the one I created it on. And even then, it didn’t work the next day.

I’ll admit that I’m not an OLAP expert. I’ll even admit that I’m mediocre at best with regular pivot tables. So it’s possible that I just don’t know what I’m doing. But if I can’t figure it out, I weep for those with less Excel experience trying to make it work.

Despite my problems, I really, really wanted to make a blog post about it. I went through the tremendous trouble of creating sample data that was not only psuedo-random, but relational and normalized. No small feat, that. And just when I was that close…

I give up. I’ll try it on 2007 later, but for now I am 100% beat. You’ve squashed my soul, Excel. I hope you’re happy.

Al sent me a formula to revise. Before:

After:

What the formulas do and what they are supposed to do is not why I am sharing them with you. There are few techniques that can be demonstrated with these formulas that might help you become a better formula writer. This isn’t a comprehensive guide to writing formulas, just a few tips.

Formula Length

Making a formula shorter doesn’t necessarily make it better, but usually it does. I’d guess about 5% of the time, a longer formula is preferable to a shorter one. If the longer formula makes it easier to read and understand and doesn’t introduce volatility or undue calculation time, then it’s good. But verbosity for it’s own sake doesn’t appeal to me. If it’s shorter, that’s less stuff I have to keep in short term memory while I’m deciphering.

Long formulas are hard to read. I don’t care who you are or how long you’ve been writing Excel formulas, fifty character formula take time to understand (unless you wrote it). So if you get a workbook with some monster formulas, don’t feel bad that you can’t trace through it in no time.

Terminology

Formulas are anything in Excel that starts with an equal sign (=). Examples of formulas are =1+1, =A1+1, and =SUBTOTAL(9,A1:A100). Formulas, like the last one, can contain functions. Functions are the built-in Excel keywords like SUM, SUBTOTAL, IF, SUMIF, OFFSET, and many others. When you’re referring to those keywords, call them functions. When you’re referring to the whole schmear, call it a formula.

Nesting

Functions take arguments (sometimes called parameters) and spit out a return value. Arguments can be many things like numbers, strings, cell references, and even other functions. When a function is used as an argument in another function, it’s called nesting.

Not nested: SUBSTITUTE("Richard J. Kusleika", ".","") returns “Richard J Kusleika” and says ‘replace all the periods in the string with nothing (an empty string)’.

Nested: SUBSTITUTE(SUBSTITUTE("Richard J. Kusleika",".","")," ","") returns RichardJKusleika and says ‘replace all the periods in the string with nothing, then take that result and replace all the spaces with nothing’.

The result of the innermost SUBSTITUTE is the first argument to the outer SUBSTITUTE. Excel always solves formulas from the inside out.

Error Proof Functions

Some functions are error proof. SUBSTITUTE is one of them. =SUBSTITUTE("Some Text","blah","") will return “Some Text”. It replaces every instance of “blah” with nothing. It’s just that there aren’t any instances. But it doesn’t return an error, and you can use that fact to make your formulas leaner.

TRIM is another function that doesn’t error out. TRIM will remove any spaces from the start and end of a string. If there aren’t any spaces, it just returns the string. You can use TRIM with any string you want and not worry that an error will be returned.

Other functions like FIND do return errors. FIND returns the position of the string-to-find in the string-to-be-searched. If the string-to-find isn’t in there, an error is returned.

Knowing what functions return in error cases like this can help you write more succinct formulas.

Booleans

Booleans are TRUE and FALSE. If you’re testing whether something is TRUE, you don’t have to say IF(Something=TRUE,… because Excel knows how to evaluate whatever you use as the first IF argument. Instead of =IF(A1=TRUE, A2, B2) write =IF(A1, A2, B2)

Instead of =IF(ISERR(FIND("blah","Some Text"))=TRUE, "Not found", "Found") write =IF(ISERR(FIND("blah","Some Text")), "Not found", "Found")

When Excel expects a Boolean, it will do the conversion for you.

Patterns

Look for patterns in your formulas and try to call functions the fewest number of times. In the first formula at the top of this post, the TRIM function is called over and over. Whatever else this formula does, it could first be improved by moving the TRIM function to the outside:

Because TRIM is error proof, we can call it one time on the result of the rest of the formula and simplify. If you find you’re using the same function over and over, ask yourself if you can’t move it more to the outside and call it less.

Multi-cell Formulas

I’m a big fan of huge one-cell formulas, but I shouldn’t be (an neither should you). If you’re typing the same sequence over and over in a formula, move that sub-formula out to a new cell and refer to the cell in your main formula. The classic case of this is the VLOOKUP function. In certain cases, VLOOKUP returns an error when it can’t find the value. To catch this, you call it twice, like this:

If you put the VLOOKUP in another cell, like B1, you could say

Share your formula tips in the comments.

Patrick O’Beirne sent me a link to Alex Palfi’s VBA Assessment. I assumed I would ace it, but I got 7 wrong. That’s 80%. I knew I was in trouble when I read the first question.

The following figure illustrates a help system in Excel.

The user can click on a tab topic and see the help for that topic. How would this functionality be achieved?

• A:In Excel - by using conditional formatting
• B:In Visual Basic - by detecting a SheetSelection Event
• C:In Visual Basic - by adding an in-sheet menu.

I’m not sure what “in-sheet menu” means. There was some others that I simply had to guess on. For instance, there were two questions testing what keyboard combinations you could assign to recorded macros. I assumed that meant through the Options button on the Tools - Macros - Macro dialog box. And I honestly don’t remember what keys it limits you to. Of course I could use Application.OnKey to set it to whatever I want. Maybe I was over-thinking the question. I did not refer to Excel or anything else during the quiz, relying solely on my memory.

There are some questions that are worded such that I don’t understand them. And there are some questions that are worded such that, to me, it gives the answer away. That might just be me, though. Also, I can’t find where it tells me which questions I got wrong. I’m probably not going to look them all up, but there are few I’m curious to see if I guessed right.

I think writing an assessment like this is very difficult. This one seems to be pretty well done. While I don’t consider myself to be at the 80th percentile of VBA users, I think a passing grade on this assessment would give you an indication of someone’s familiarity with VBA.

If you take it, post your score in the comments. If you know of other online assessments for VBA, post a link.

DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want.

Here’s a macro to list all the Custom Lists to the Immediate Window.

Apparently I’ve used custom lists in the past because there are two there that I made. Number 5 is clearly payroll item types from Quickbooks, but I don’t recall why I would put them in a Custom List. And the last one is the first 9 days of every month of the year; also a complete mystery as to why I would need that.