Daily Dose of Excel

I recently did an interview with Ross at MIE. Check it out.

I’m able to subscribe to MIE podcasts via iTunes now. So if that’s your preferred method, have at it.

Hi all

I create a 2010 section on my Tip index page with the first pages about 2010.
http://www.rondebruin.nl/tips.htm

Load different RibbonX when opening file in Excel 2007 or 2010
Download to find the names of the Excel 2010 ControlIDs (819 kb)
Backstage view in Excel 2010

I not say they are good but this are my first examples that I want to share.
If you have useful content or have links to articles about 2010 please post them.

And a PDF article on MSDN is published :

Creating and Sending PDF Files from Excel 2007 and Excel 2010
http://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx

Merry xmas and happy new year
Regards Ron de Bruin

When I copy and paste this Excel range

into an Outlook email, it doesn’t look so good.

I thought I would use JoinRange to put some tabs in between the text. I used a function like this

A tab is ASCII code 9. It will show up as squares in Excel

but will turn into real tabs in Outlook

Still looks bad and now there’s quotes around everything. I didn’t know the ASCII code for tabs, so employed my usual technique for finding out. I get some words in a cell that contain the character I want, then use a formula to see what’s what.

The formula in G10 (text is in F10) is =CODE(MID($F$10,ROW()-9,1)). I fill that down and see the ASCII codes for all the characters. Sadly, I didn’t know how to type a tab in an Excel cell. Tabbing takes me to the next cell rather than inserting a tab character. So I resorted to VBA. From the Immediate Window

which is really dumb. As long as I’m in the Immediate Window, I could just use ?asc(vbtab), which returns 9. Is there a way to get a tab into a cell without knowing the ASCII code? I mean other than the way I did it.

While I was putting this post together, I discovered something. I write all my email in plain text rather than HTML. I don’t have Hello Kitty stationary or a need to put an image of my business card in my signature, so plain text works the best for me. I assumed that pasted range would look better in an HTML email.

Format email as HTML, then paste

Paste range, then format email as HTML

And the winner is: Paste range, format email as HTML, change format of email back to plain text

In regard to Multiple Substitute Formula, here’s one way to do it in VBA.

I predict that someone will post some code in the comments that does this in three lines, so stay tuned.

MaryAnn asks an interesting question.

In column A there is text that may or may not contain the name of a US state. In column B there is a list of US states. In column C, we want the column A text without the state names. I think this would be a pretty trivial piece of VBA, but I wanted to see if I could do it in a formula. Here’s what I came up with:

Got that? OK, maybe a little explanation is in order. As usual, I will explain from the inside out. Let’s start with this little nugget

Normally with FIND, you pass a find_text argument and a within_text argument and you get a number showing the position of find_text within within_text, like =FIND("Kus","Dick Kusleika") would return 6 because the find_text “Kus” first appears in the 6th position of within_text. If find_text is nowhere to be found in within_text, you get an error. What I’ve done above is pass an array of find_text’s and checked to see if any of the states exist in A1. The result of this is a 56 element array that kind of looks like this {TRUE;TRUE;TRUE....TRUE}. If there is no state name, all 56 elements will be TRUE. If there is a state name, at least one of the elements will be FALSE. That brings me to

MATCH returns the position of the first argument in the list (the list is the second argument). This MATCH function is looking for FALSE in the 56 element array described above. If it finds a FALSE in the list, it returns the position in the list of the first one it finds. If A1 is “Chairs Idaho”, the MATCH function will return 15 because Idaho is the 15th name in the list in column B. MATCH will return an error if there are no FALSE elements in the list, so I have to use the old IF(ISNA trick

I set the TRUE condition to 0. Since there are no state names if ISNA is true, I don’t really care what position it returns. Now that I know the position of the state in the list, I can return the state name with INDEX

I substitute and empty string for the state name

And TRIM the whole thing to remove leading and trailing spaces.

As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running.

With Excel 97 to 2003, this was simple: hold the shift key when you click “Enable macros”.

With Excel 2007 and 2010 it is less obvious how to do this, holding shift whilst clicking the enable button does not help. In the article mentioned below I have detailed out the exact steps required to prevent these macros from running under all possible circumstances.

Link: Preventing auto_open and Workbook_Open events from running

Enjoy the read and let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

A few years ago, I wrote a Formula Formatter add-in to present a long formula for easier reading. It does it through a process called Tokenizing, which is basically a process of putting the components of a formula into labelled boxes.
That add-in was compatible with Excel 2003 formulas, but Excel 2007 brought us extra formulas which meant my add-in was incomplete.

It was the first time I’d ever developed a tokenizer, so after a while I thought of better ways of doing it.
From my Oracle days, I recalled those diagrams in SQL manuals. Kind of like bubbles with lines leading in and leading out detailing the syntax of a statement. At the time I figured I could reuse this technique to document Excel Formulas. Of course, now that I’ve done a bit of reading, I’ve learned these are called Syntax Diagrams, and that’s exactly what they were intended for… visualising the syntax of formulas, or really, visualising BNF. BNF itself is a way of documenting a programming language’s grammar.

After some weeks of ripping apart formulas, I completed a BNF document describing Excel Formulas.
Probably the very next day, I stumbled across this document on Microsoft’s website: Excel Binary File Format (.xls) Structure Specification. It’s got BNF galore.
I felt like the guy who spent a weekend learning to program his VCR without a manual, only for the manual to turn up the next day.

So, with syntax in hand, I developed a tokenizer, which was quite a lot of fun.
It does Excel 2007 Tables, which was really my goal from the start. I also tidied up the bits around external named references.
It should work with International versions, but I use English only.
Ron de Bruin gave me some advice in this area (thanks Ron!), but still feel there might be problems with errors such as #VALUE!. You’ll find them defined once at the top of the module if you want to play.

Now that I’m finished, I’m sharing… You can download the code here (a zip file at 75 KB)
I’m not really interested in developing a user interface for formula formatter this time around. I’m happy to leave that to anyone else.

I wrote a proof of concept userform. In the image below, I’ve doubleclicked the IF function, and the whole IF, including it’s content, is highlighted.

Remember DOS? Back in the day, we had to limit our file names to eight characters plus a three digit extension (8.3) and we liked it. Today I needed to add Google Chrome to my startup batch file. Normally I can convert long Windows paths to short DOS paths in my head, but I couldn’t remember what do with spaces (just eliminate them), so I wrote this to figure out the short path.

Don’t forget to set a reference to Microsoft Scripting Library (Tools - References)