Tonight we picked up an iPod Touch from our local Save-On Foods with some reward points that we’d been accumulating. This is pretty cool for a variety of reasons, including the fact that now my kid will try and scoop Mommy’s iPod Touch to play games, rather than my iPhone. J

In setting up the iPod Touch for Dee I ended up exploring the calendaring settings. Of the whole device, this was probably the most exciting part to her as she now has a device that she can take with her to check the family calendar. She was most impressed, and couldn’t care less that it was only keeping a local copy of her calendar on the phone.

Having my iPhone linked to our Exchange server at work though, I know how calendaring is supposed to work. It’s supposed to sync to the server so that it works with Outlook, ensuring another copy of the data exists too in case the device dies. So this got me thinking… we don’t have an exchange server at home, and I don’t really want to maintain one, but I wonder if there is a way to do this for the home user. A quick search (with Bing) turned up that you can integrate a Google calendar right into the iPhone or iPod Touch. And what’s even better is that in version 4 you can have multiple calendars. Holy crap!

This is truly awesome, as it solves an issue that I’ve been wanting dealt with for a long time… synching the family calendar. I lamented this to my buddy Steve French a while back, and he pointed me towards a product called Cozy to do this. It’s true that Cozy works, but I found it kind of cludgey, and it requires the Outlook is opened before it syncs. It also meant that Dee would have to put her entire calendar in Outlook, which she actually doesn’t use… she uses paper.

With her getting her hands on the iPod Touch though, she now has an input device that is much more portable than her massive appointment book. And with Google calendar, this gets even better. I’ve now linked her device up to two Google Calendars… hers and mine (both set up for this purpose.) So now I can book personal appointments into my calendar and they’ll show up on her phone under my name, and likewise the reverse is true too. VERY COOL!

The method is fairly simple. Set up a Google Calendar first. Then go to set up a new email account on the iPhone/iPod Touch, choose other, then add a CALDAV account. From there it’s pretty much follow the steps with the server being www.google.com.

Nice stuff!

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 thought I’d written this down somewhere, but apparently not, so I figured I’d make a blog post out of it.

Despite the fact that it’s been a long time since I’ve blogged, I don’t think it’s a secret that I’m a fan of virtualization. The other day, after posting on some VBAX threads, I started feeling that I needed an Excel 2003 install to test some things. Now I’ve detailed the steps I used to set up an Office Sandbox before, but what I really wanted here was the ability to run multiple VM’s at once. So it was obviously time to recreate my environment.

This time, rather than set up a single virtual machine with all kinds of snapshots on it, allowing me one testing ground at a time, I decided to go with linked clones in VMWare. This allows me to set up one main Windows XP hard drive, then spin off just a linked clone for each Office installation. They’re separate, can run at the same time, and I don’t need to create a large hard drive for each image. Each linked clone expands to incorporate the office install, but that’s about it.

In addition, I also set up a Windows 2003 master and am currently in the process of setting up a domain controller and a file server. This will let me fool around with some features that require a domain, and also means that I can set up a file structure similar to the office. I’m hoping that I won’t have to deal with any more of those “your network add-ins aren’t available right now” messages.

At any rate, here’s the steps I went through to create it using VMWare 6.5.

Initial VM creation

1. I made a new VM and installed Windows XP. Nothing really magical there (at least not in my opinion)
2. Installation of VMWare Tools is also essential

Patching, patching and more patching

1. I installed all the various service packs and windows updates. Tons of them. Then more.
2. Next I installed Microsoft Update (after all, I plan on installing Office here) then checked for even more updates
3. I also installed some optional updates that I thought I might need (.NET 3.5, etc…)
4. Check for more windows updates
5. Basically keep checking for Windows updates until there are none left.

Performance Tuning

1. Download and install BGInfo so that the desktops get tagged with their system info.
   1. Download it from http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx.
   2. I create a folder in C:\Program Files\BGInfo and extract the pieces there
   3. Right click the Start Menu and Explore all Users.  Drill into the startup for all users and place a shortcut to BGInfo.exe there
   4. Modify the “Shortcut to BGInfo” in the statup menu to read “BGInfo”, go into it’s properties and append /timer:0 to the target path
   5. Trash the zip file as it’s no longer required.
2. Install any programs I know I’ll need in all machines (PDFCreator)
3. On Windows Server 2003 uninstall the enhanced browing security.  (Control Panel–>Add/Remove Programs–>Add/Remove Windows Components–>Uncheck Internt Explorer Enhanced Security Configuration.)
4. If space and performance is a concern, uninstall any other unnecessary windows components
5. Go to Internet Explorer and set the Home Page to something I’ll use.
6. Empty the recycle bin
7. Run disk cleanup (StartàAll ProgramsàAccessoriesàSystem ToolsàDisk Cleanup) and clean up EVERYTHING
8. Go into System Properties (Right click My Computer in the Start Menu) and adjust the following
   1. Turn off System Restore (System Restoreàcheck the box)
   2. Adjust for Best Performance (AdvancedàPerformanceàSettingsàAdjust for Best Performance)
   3. Drop the page file (AdvancedàPerformanceàSettingsàAdvancedàChangeàNo Paging File)
9. Restart the computer (to confirm the page file deletion)
10. Run defragmentation in the OS until it is almost instant complete
11. Go back into system properties and create a page file. I set the initial and max size to the Recommended value at the bottom of the page.
12. Shut down the guest OS.
13. Run the VMWare disk defragmentation
14. Uncheck all the “Connect at startup” settings for the floppy drive, CD ROM & Bluetooth

Preparing for next step

1. With the machine shut down, create a snapshot. I label mine “Golden Master” and put in the notes what date it is patched to.

Creating the Office PC’s

1. From the VMWare VM menu, I chose Clone, and set up a Linked Clone from the snapshot
2. Install Office
3. Install utilities like freewheel, mztools, smartindenter. The reason I didn’t do these earlier is that some require office be installed first, and some aren’t necessary (Freewheel) in later office versions.
4. Patch it (until no more patches show up)
5. Run defragmentation in the OS until it is almost instant complete
6. Shut down the VM
7. Run the VMWare disk defragmentation
8. With the machine shut down, create a snapshot. This one I’ll call Office XXXX base image, patched to yyyy-,mm-dd

At this point I’m set up to use that VM. It may sound like a lot of work, but the bonus is that if I can roll back to a fresh office install at any time. Likewise, I can spin off a new linked clone from my Golden Master at any point in time too.

You’ll notice that I don’t have any antivirus software on the VM’s. This is because I’m not using these for production, just for testing. I don’t really want to have huge AV downloads every time I want to make a quick test, and I don’t plan on surfing the net on these machines.

As far as Windows servers, I basically follow the same steps. Once I’ve got the base OS installed, patched and tuned, then I snapshot the golden master, create the linked clones, and off I go.

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!