From Andrew’s Excel Tips

There’s a new Excel Question and Answer forum in town…(well, maybe not so new, but the site itself is new)

It’s called JMT Microsoft Excel QA Board. One could argue that we need another web based Excel forum about as much as we need another Brittney Spears fan site. But considering the guys who are running things over there, I’ve registered and will be checking it out from time to time.

They need to figure out how to get an RSS feed in there so I can read it from Bloglines.

In Excel, the Like operator is used with ? and * to designate one or many missing characters, respectively. Someone recently asked me about using the Like operator in MSQuery. Since I didn’t know off the top of my head, I went to the help file. If there’s anything in the help file about this, I couldn’t find it.

In MSQuery, Like works just how you’d expect it to, that is if you’ve used Like in some other program. The difference is that MSQuery’s Like appears to have only one wildcard character, namely the percent sign (%). It stands in for multiple characters, similar to the asterisks (*) in Excel. The criterion

will return all the records that start with”KU”. Like appears to be case insensitive. That is, the above criterion would also return records that start with “ku”, “Ku”, and “kU”.

To use Like in a parameter, simply concatenate the percent sign with your parameter with the ampersand (&). For example, to find all the customers whose CustomerID started with ‘S’ and whose City contained a certain letter, you might set up your criteria like this:

This uses the Northwind sample database’s Customer table. I entered an ‘a’ when the parameter prompted me and got all the CustomerIDs starting with ‘S’ whose City contained an ‘a’.

Similar to using Conditional Data Validation, you may have a need to change the contents of one listbox based on what the user selects in another listbox. Assume you have two listboxes on a userform: one for sides and one for players. When the user selects a side, the list of players changes. It will look like this:

Start with three named ranges. The first name is “Sides” and covers A1:A2 (which contain Chelsea and ManU). The other two range names are “Chelsea” and “ManU” and cover the lists of players in columns B and C. It’s important for this example that the named ranges for the players exactly match the values in A1 and A2. You can set up a situation where they don’t match, but it takes a bit of extra programming.

The code behind the userform is pretty simple. In the Initialize event, you load up the Sides listbox. Then, whenever the user changes the value of that listbox, you load up the Players listbox.

Microsoft’s KB article 161518 discusses this very issue. I have two problems with their solutions: First, it appears they use the RowSource property. I never use this property, so if you’re like me their solution won’t be optimal. I don’t have anything against RowSource, I just like the flexibility that List, AddItem, and RemoveItem afford. Second, the code sample they provide looks like this

when viewed in FireFox. Well, the code on my site can be viewed from any browser. You still can’t copy and paste it if it contains quotes, but at least you can read it.

From Jim Jazwiecki:

An Excel quiz. Look at ten words and decide if the word is an Excel function or the name of a foreign currency. I didn’t do so well. I could identify three Excel functions. One of the function was WEIBULL. I was a little skeptical, so I had to look it up in help.

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure.

No, YOU use it in reliability analysis. I’m perfectly happy just fixing the device when it breaks.

Whenever I make major changes to the layout, I like to put a post out there so you have an easy way to comment on it. You can always send an email too. You may notice that I’ve made some changes to the menu (sidebar). Namely,

• Recent “x” lists are now javascript expandable lists
• Categories are now in a dropdown and alphabetical and hierarchical
• The archive list is gone and replaced with a link to an archive page

Expandable Lists

In style.css under the only theme I use, I added this

I copied the “ul ul” right above it because I wanted all the stuff to be the same, I just wanted a way to differentiate between menu lists that I wanted to hide and those I didn’t. The “display:none” line is the only difference between this and the one above it. All of this is based on the Gemini template.

Then in index.php, I added this between the already existing script tags. If you don’t have script tags you can but them within the head tags.

Then in rap div, I have this:

The function comes from Adrian Holovaty. It has too many arguments because I modified it from the original. I modified it because I never want an expanded list to shrink because another list is expanded.

By adding ‘class=”expand”‘ to the ul tag, I was able to choose which lists are expandable and which aren’t.

If you know anything about php and css, then you know that I don’t know anything. I’m happy to take your suggestions for the proper way to do this. If you don’t know anything, then assume this is the correct way at your own peril.

The Recent x lists are from Customizable Post Listings and Recent Comments/Recent Posts

Categories

The new categories dropdown is provided by Typedby.com.

Archives

I never use the date archives, so I’m happy that Jon pointed out that they suck. I’ve left the calendar part because it looks cool, but replaced the monthly listing with sortable archives provided by nicer-archives-for-wordpress. It looks pretty cool and I think will come in handy. I use the Search box almost exclusively. The Atomz search works really well and I can always find the post I want with a quick keyword search.

As always, tell me what you think. If I killed your favorite navigation method, be sure to speak up. We may be able to make a deal. With all this real estate savings the Google ads are now above the fold. I may be able to retire soon. Although I’m considering putting the search box up higher to get people to use it – it’s just that good.

Recently, I got this comment:

Dick,

Just browsing and I saw that Jason posted a link to a site that can unprotect projects.

[See the censored comment here]

I’m not sure if you edit/censor the comments, but I don’t like that site. (Yes, I’ve used it!)

But there are too many [Excel developers] who make their living with protected .xla projects.

Yours in censorship,

I hardly think it’s necessary to post the entire comment, but there were two things I loved about it: The admission that he’d used the site that he hates; and the closing “Yours in censorship”. They were just too funny not to share.

To answer the question, I’ve never censored a comment until this one. I want people, like Jason, to feel comfortable that they can comment on this site. Actually, I just don’t want the extra work of monitoring comments. Although I read them all, I don’t want to be the comment police.

I thought about this for a couple of days and I decided to remove the link that Jason provided. There’s no question that I am against people stealing other people’s work. There’s also no question that the method provided by the link could be used to do that. It could also be used to get at your own code for which you lost the password, a reason about which I have no objection. I don’t advocate the outlawing of CD-RWs because they may be used to steal music. So what’s the difference?

The difference is that I and other authors of this site are Excel developers. It is in our best interest that as few people as possible know how to crack code. If we were professional musicians, maybe we would be against CD-RWs. I’m not going to crusade to have that information removed from the internet, I simply choose not to spread it.

I imagine one or two of you has an opinion on this subject and I’d like to hear it. And to Jason: I’m sorry I censored you. I’m sure you meant nothing malicious and were only trying to help. You can call me a communist if you want (but nobody else can).

Dean found this bug and I found Dean in the .programming newsgroup. From what I can tell, if you have two or more functions from the Analysis Toolpak, you get a #NAME? error when you try to calculate in the formula bar.

To calculate in the formula bar, you can use F9 or Cntl+= while a cell’s in edit mode. Here’s how it works, when it works right:

When the calced portion (or if the whole formula is calced) contains two ATP functions, you get this:

I’ve tested this only in Excel 2000. Maybe someone can test it in some other versions and post a comment. Also, I have a hard time believing this is only in the ATP. I tried it on a UDF that’s in a regular old xla file and did not have a problem. Honestly, I don’t know what kind of add-in the ATP is, but I’ll bet dollars to donuts it’s not written in VBA. Perhaps this is true of all COM add-ins or all xlls. If someone has those handy to test, let me know what you find.