I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn’t want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately there is no setting in the user interface to allow for expanding/collapsing the outlining tools when the sheet is protected. In fact, trying to do so gives you the following message:

I found this a little frustrating, but gave up on it. I expanded the model completely, protected the sheets and let the users have at ‘er.

Tonight at VBAExpress.com though, I was posting on a thread where the user had included the following in their code:

Sh.EnableOutlining = True

Wow! So obviously there IS a way to enable the outlining tools when the worksheet is protected, right? I ran the macro I had modified for the user and sure enough it worked. Cool!

So then I opened up a copy of my model and:

• Ran the following code: Activesheet.EnableOutlining = True
• Protected the worksheet

I didn’t work. What the hell?

After a little sleuthing I found out what the issue was. In order for the EnableOutlining to take effect, you must run the code that protects your worksheet with the userinterfaceonly:=true argument.

The unfortunate part of this is that userinterfaceonly:=true doesn’t stick between sessions. So that nice macro free workbook is now going to have to be saved into an xlsm format with the following code in it:

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

With ws

.Protect userinterfaceonly:=True

.EnableOutlining = True

End With

Next ws

Application.ScreenUpdating = True

End Sub

That shouldn’t be necessary in my opinion, but whatever. A macro laden file is a small price to pay for the functionality. Man I love VBA!

Today I realized that one of my GETPIVOTDATA formulas was returning an error, since there wasn’t any result in the Pivot Table it was checking for data. Unfortunately, during the design of the spreadsheet I never wrapped the function with IFERROR to avoid this.

The challenge I had was that I had several different formulas that needed to be wrapped, and didn’t want to spend the time adjusting each one manually. So I wrote a little macro to adjust the existing formulas. Basically what this does it checks each cell in the selection and, if it has a formula in it, wraps it within the following construct: =IFERROR(existing formula, 0) The point? Now if an error is returned, it will return 0 instead.

Public Sub WrapWithIfError()

Dim cl As Range

For Each cl In Selection

If cl.HasFormula Then _

cl.Formula = “=IFERROR(” & Right(cl.Formula, Len(cl.Formula) – 1) & “,0)”

Next cl

End Sub

Just a heads up here though… you’ll need Excel 2007 or later to make use of the IFERROR function.

I know it’s been forever since I’ve blogged here, and I’m sorry to have to revive this with a rant, but…

I was writing an article up for how to customize the Office Ribbon in Excel 2010, and thought I’d build a custom Auditing Tab. Using the built in groups is REALLY easy, but there were commands there I didn’t need. So I figured that I’d try to make some custom groups, insert the commands I wanted, and see how it worked. Even better, I know that you can now export the customizations, so I could even share them!

To build a custom Ribbon tab:

• Right click the Ribbon and choose “Customize Ribbon”, or go to FileàExcel OptionsàCustomize Ribbon.
• Click New Tab
• Select the tab and choose Rename
• Go to the “Choose commands from” dropdown and choose Main Tabs
• Find the groups you like, select them and click the Add>> button

To add a new group to a tab, first select the tab then:

• Click New Group
• Select the group and choose Rename
• Drill down to the commands you like (left window), select them and click the Add>> button

Overall it’s not too complicated really.

So here’s the setup I did for my Auditing Tab… (I shrunk it so that you only see stuff pertaining to that tab):

As you can see, I created custom Formula Auditing, Other Tools and Sort & Filter groups. (I don’t know why I use the default Sort & Filter as well a custom one, but there you have it. At any rate, it doesn’t change this… the tab looks like crap!

The icons for Trace Precedents, Trace Dependents and Remove Arrows are all large and very blocky looking. Same with the Sort buttons on the custom group. Why is it that they are shown nice and small on the built in group, but not the custom one? And isn’t it interesting how Microsoft broke the 1 or 3 to a row rule with the Sort group? Makes it very obvious here!

If you want the customization file to try this yourself, click here.

Personally, I’m happy enough with the way the built-in groups work, but I think there should have been a bit more control on the sizing of the icons here. It seems that the need for my book (RibbonX – Customizing the Office 2007 Ribbon) isn’t dead yet, but honestly, it should be!

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.