Macro to wrap an existing formula with IFERROR

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.

My VMWare Setup Steps

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.