Over the past… ages… it seems like I haven’t been able to my site, but I got a new article up today. It’s a variation on my Count Files (with a specific extension or not) in a folder entry. It doesn’t seem at first like a big departure, but I’ve posted a version now that will also count files in both the folder and subfolders. It was an easy fix, requested by a reader. The big difference is really that it uses recursion to keep calling itself to dig deeper in the folder structure to get the info.

At any rate, if you’re interested, you can find it here: http://www.excelguru.ca/node/106

Dave writes:

I found your excellent code to run a procedure on a target spreadsheet from
an originating spreadsheet on VB Express Forum.  It works fine if both
files are on the same jump drive but fails on our work network and even if
the originating file is in Documents on my home computer.  Is there any
security setting that you can think of that would cause this to happen?

For others following along, the source KB entry (you may need to create FREE login to get it) can be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=279

With regards to the security question, it would depend if this is an Office 2007/2010 instance. If so, then the folders where the target spreadsheets reside would need to be included in the “Trusted Locations”. (Office button->Excel Options->Trust Center->Trust Center Settings->Trusted Locations.)

If this is a version of Excel prior to 2007, I can’t think of anything that would be a security concern to prevent this. One thing that could potentially be an issue is using UNC paths instead of mapped drives, but I haven’t tested that to be sure.

I can say that I use a variant of this macro every month to roll forward 12 inventory files in one shot. The source and the destination workbooks all reside on our network, but in different drives. The key parts of this:

sRollMacro = “‘2009-07-31 16030-900 (Hotel Beverage).xls’!InventoryRollForward_Liquor”
Workbooks.Open (ThisWorkbook.Worksheets(”Master”).Range(”rngSourcePath”) & _
“2009-07-31 16030-900 (Hotel Beverage).xls”)
Application.Run (sRollMacro)

I first used this in Excel 97, and it still works in 2007 today. (Haven’t tested it in the 2010 beta yet, but I will in a few days.)Hope this helps!

My sister-in-laws’s status on Facebook this morning: “Francine Street is wondering what to do today”

Hopefully Francine gets the joke…

Personally, I will be lying back and thinking of England. (For real, not like this.) Although I haven’t made much public mention of this yet, in 8 hours I’ll to be on a plane on my way to London.

We actually have a pretty large number of family all over England. My dad has two brothers and a sister, all of whom have 2 kids each, some of whom have kids. With the exception of my immediate family and one cousin, (who lives in Japan,) all of them make their home somewhere in the UK. And that doesn’t cover my mom’s side who naturally factor in there too. And while the bulk of mom’s immediate family is in one place, she’s got close cousins scattered across the country. Naturally, we’re going to be visiting as many of them (on both sides) as we can, and we’re doing the grand tour of the UK to do it. And when I say the grand tour, I mean it… check this out:

We fly into London, stay a couple of days in Romford, then head on to Nottingham, Edinburgh, and Inverness. Then back down through the Lakes District to Coventry, and west to Newport for a few days. Why Newport? Well… it’s the reason for the whole trip. August 19, 2009 marks my grandparents 70^th wedding anniversary and on the 22^nd Granddad turns 96. The entire Puls family, with the exception of only my brother, will be coming together to celebrate. It will be quite the event.

Now, from the “I can’t believe how small this world is” book, it turns out that Roger Govier lives all of about 20 miles from Granny & Grandad. And during our four day stretch, we have a day off so we’ve got the chance to hook up with Roger, who is going to give us the local’s tour of Cardiff. Dee and I are quite looking forward to being able to go out and spend the day with a friend, something we’re not going to have nearly enough opportunities to do.

Our next stop after Newport is to head down to Poole, where we’ll be staying for 3 days. Now there’s something funny about Poole, and I’m sure there’s a comment coming about this… Pretty much all of my mom’s immediate family live in Poole, as does our great friend Bob Phillips. Nick Hodge isn’t too far away from there, and nor is Richard Schollar. So there seems to be some kind of gravitational pull about Poole for some reason. Bob has graciously offered to host us while we’re there, as there’s not enough beds with family, and is going to show us what makes Poole the centre of the universe.

After Poole we head back to Romford, and have the opportunity to head into London for a day of sightseeing before we have to head home.

So now, for the disappointing part…

I have friends all over England/Scotland and, as I mentioned at the top, I’ve been pretty silent on my trip till now… As I think everyone can see, we have tons of family to see, many of whom we’re only going to get to see for one night. The trip is going to be a hectic one to say the least. So if you haven’t received an email from me, please don’t be offended by it… it’s not personal at all, just survival. I’d like to see all of my friends, many of whom I’ve never had the chance to meet in person, but time just doesn’t afford me that luxury.

At this point it’s looking a lot like we’ve got one opportunity to get together with friends… dinner on August 28^th in London. If you can make it to London, please drop me a line and we’ll see if we can hook up.

Last but not least, I also would like to throw out a big “Thank You!” to my buddy Ted who will be house-sitting for us while we’re gone. Much appreciated, buddy!

A friend just asked for some help with making a getSize callback work with some buttons. I figured this should be an easy one, and dove into the RibbonX book to check the table on page 172. Aha! There it is in the middle of the table:

So I whipped up a quick example to prove it out and… wtf? I started getting an error message:

Interesting… it returned a value that could not be converted to the expected type. What the heck is that about?

When you set up your XML code, you use normal or large in the code. So far as I know, they are the only allowed values, although I can’t say I tested that. What I did test, however is both normal and large in VBA callbacks. Both return the error listed above.

Interestingly enough, I decided to try passing a value of 0 or 1 to the callback via VBA and it worked! So then I gave it a whirl with true and false. Likewise, it worked.

So it looks like, in order to use a getSize callback with a button, you actually need to pass one of the following to the callback:

Just in case anyone wants to have a play with this, I’ve also attached a workbook here: getsize.zip. It also shows how to use getLabel on a tab, as well as getImage on a button. (It’s in a zip file, so you’ll need to unzip it first as my blog won’t accept xlsm uploads.)

Is it just me, or if the Find and Replace tool in Excel (actually in Office) really limited?

The first deficiency is that I want to be able to select “Within Selection”, not within “Sheet”. I know that it defaults to the selection if you try this on a multi-cell selection, but if you want to replace references in a single formula in a single cell… watch out! I usually copy my formula to notepad and do the work there.

Now, the options above are fine for really simple find/replace scenarios, and have served us well for years, I suppose. Yet it still leaves me wanting, especially right now. Consider this formula:

=ROUND($D$69*(SUM($AG66,$E66:AQ66)+SUM($AG93,$E93:AQ93)+SUM($AG120,$E120:AQ120)+SUM($AG157,$E157:AQ157)-SUM($AG163,$E163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AG69,$E69:AP69),0)

It’s pretty hideous, but I need to modify it to read as follows:

=ROUND($D$69*(SUM($AP66:AQ66)+SUM($AP93:AQ93)+SUM($AP120:AQ120)+SUM($AP157:AQ157)-SUM($AP163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AP69:AP69),0)

So pretty much, what I need to do is

• Replace all the $AG references with $AP
• Get rid of the ,$E##? pieces

The existing tools works fine for the first part, but can’t do the second since the ## pieces change.

Wouldn’t it be nice if Excel had pattern matching? I’d love to be able to knock up a search to check for a pattern like comma dollar E number number [number] colon and replace it with a colon.

Even though this might seem a little strange to have a variable pattern here, I’m pretty sure that you can do exactly this with Regular Expressions, and quite quickly too.

What do you think? Something you could see value in?

If you filter data out of large data sets, you may have run into an issue where you can hit 8192 non-contiguous ranges, which Excel couldn’t handle. While this issue has been in Excel for many versions, I can honestly say that I haven’t run into it in years, as I’ve always sorted my data first, to avoid this issue.

To replicate the issue, try this in any version of Excel prior to 2010:

Enter the following data in a blank sheet:

• A1:    Row
• B1:    Index
• A2:    =ROW(B2)
• B2:    =IF(A1=1,2,1)

Now copy row 2 down about 20,000 rows…

Next, select row 1 and Filter your data on Column B to only show 1’s

Now, select from cell A3 to the end of your data in column B and try to copy it… You’ll get a message like this one…

Well guess what… that’s no longer necessary! In Excel 2010, the Excel team has removed this limit. And while I haven’t tested this, the new limit is apparently imposed by the memory in your computer, not a hard coded limit.

It’s great to see that such a long standing bug has finally been resolved.

Cherisse asks:

Is there a way to set a conditional format for a cell or row to look at the date and format if that date falls on a specific day of the week.  IE—date input as 7/21/09, cell formatted to show “Tuesday, July 21, 2009″, want to format all cells that contain the word “Tuesday” as red.

I know from experience that many users seem unaware that dates in Excel are actually numbers. It drives me crazy, as it can easily bugger up formulas later on down the line, but fortunately we can deal with this.

So Cherisse, the answer is… Of course! One thing I would advise though… if you can, prevent the user from entering an invalid date to begin with using Data Validation first, then you don’t have to worry about the garbage ever getting in to your spreadsheet. But, because you asked, I’ll walk through the conditional format route first…

Re-Active Date Validation Using Conditional Formats

To validate this data, I would go through two steps.

The first step is to set up the format on the cell for the correct input type. So right click the cell, choose “Format Cells”. On the Number Format tab, choose Date, and choose the date format:

Second, we set up a conditional format. In Excel 2003 go to FormatàConditional FormattingàFormula Is. In Excel 2007 go to HomeàConditional FormattingàNew RuleàUse a Formula…

Set up the rule as follows (assuming the input cell is in B5):

• Formula:    =IsNumber(B5)=False
• Font:    Bold, White
• Fill:        Red

This will highlight the cell in red if the input is not numeric, as shown below. (I copied the format to cell B6 as well.)

Okay, so this is great, but what if we want to change this so that anything that isn’t a valid Tuesday is highlighted? We do this by modifying the conditional format’s formula:

• Excel 2003    =OR(ISNUMBER(B5)=FALSE,IF(ISERR(WEEKDAY(B5)<>3),TRUE,WEEKDAY(B5)<>3))
• Excel 2007    =OR(ISNUMBER(B5)=FALSE,IFERROR(WEEKDAY(B5)<>3,TRUE))

Pro-Active Date Validation Using Data Validation

As I mentioned above, I’d actually approach this a little differently, and prevent a user from even entering invalid data in the first place. The method to do that is Excel’s Data Validation tool.

In Excel 2003 go to Dataà Validation. In Excel 2007 go to DataàData Validation

I usually set up the rule as follows:

• Allow:        Date
• Data:        Greater Than
• Start Date:    =Today()-1

This rule means that you can enter today’s date or higher.

We want only want the user to enter a valid Tuesday though, so let’s change this a bit and go with the following rule:

• Allow:        Custom
• Formula:    =WEEKDAY(B5)=3

The real slick thing here is that you don’t need a conditional format, as the user will be prevented from ever entering anything that is not a valid date AND a Tuesday.

One word of advice though… the default error message isn’t really intuitive:

You can customize this error by filling in the Error Alert tab. I usually put a nice descriptive error in there to tell the user what to do. Something like:

This field only accepts Tuesday’s that are entered in a valid format. Please enter a Tuesday in the format MM/DD/YY.

The effect will look like this:

And if you want to be even more helpful, fill in the Input Message tab as well…

Conclusion

Wherever possible, you should try pro-actively validate your data before the user can even commit it. The Data Validation tool is great for this purpose. You will find, however, that there will be times where you just can’t pro-actively evaluate everything… and that is where conditional formatting can be really handy as a Re-Active Data Validation tool. I often use the two in combination to generate a really robust interface.

As I mentioned in this post, I started working my way through the .NET Hello World example in PED 2nd Edition. I ran into a funny issue though, where it kept opening up my application in Excel 2003 instead of 2007. I fired off a quick email to Dennis who was kind enough to give me some pointers.

As it turns out, my “Current Version” of Excel on my laptop was set to 11 (Excel 2003). My attempts at running Office 2007’s Detect and Repair feature, to reset this to 12, were causing the Microsoft Bootstrapper to crash. (So it wasn’t completing.)

Today I decided to install Acronis TrueImage to take a backup before I mucked with this any further and, lo and behold, it pointed out that it couldn’t install until it killed the suspended install of Office 2007. I gave it the assent to kill the job, and then my Detect and Repair ran fine. I’m now back up to Excel.12 as my current version, and my “Hello World” application works nicely.

I’m only about ½ a chapter into PED 2^nd edition, but the .NET stuff is working for me so far. I’m looking forward to digging a little deeper into this now, and trying my hand at a full managed COM Add-in.

I don’t know how often this happens to you but it drives me crazy…

Assume that I’ve got a formula set up in a conditional format, named range or some other place that has a “RefEdit” control on it. I highlight the last character in $T$52 and change it to $T$51, then press the right arrow so I can arrow over and change the $AE$51 to $AE$52. What happens? I get a cell reference in my formula!

The problem is that the refEdit control (signified by the little box to the right of the typing area) is still in formula creation mode, not editing mode.

To change this, press F2, just like you’d do to edit a cell from the grid. At that point you’re toggled into edit mode and you can move around using the mouse as much as you’d like.

The biggest challenge is remembering to do it BEFORE you hit your arrow key. Unfortunately there isn’t a way to set the default behavior to be in edit mode. (At least, not that I’m aware of.)

On Friday night, I began reading my way through PED 2^nd Edition. After something weird happened with Excel, (I think because I have Excel 2003 and 2007 installed,) I got the bright idea to create a very simple Windows Forms application. I figured that I could make a really easy math game for my daughter. (I’ll see about posting it over the next few days.)

So cool thing here… with very minimal VB.NET experience, I was able to knock up a fully working simple math game in about 3 hours that will install on Windows XP and Windows Vista. I was pretty damn impressed.

At any rate, here I am today, back working in Excel. And I can now say that I’m rather disgruntled with the state of the IDE for Excel VBA.

Despite gnashing my teeth on certain stuff in VB.NET, the IDE in VS2008 is awesome. Maybe it’s for the big things, and I’m sure that there will be VBA IDE things I’ll miss, but it’s the little things that made my day. Little things like this:

• You can collapse procedures/function and even entire regions of code. That seriously rocks!
• You type in With “whatever”, and it automatically puts in the End With. Actually, all constructs are like this… For Each, Select Case, Try/Catch and so on. Very, very cool!
• The indenting everywhere is automatic.
• The intellisense always suggests available items. I find this somewhat hit and miss in Excel’s IDE for some reason.

I really wish that they add the features above into our tired old IDE. It would make things so much faster for typing up code from scratch!