The Ken Puls Blog

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!

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.