I’m porting an application to Excel 2007 and thought I’d share some problems I’ve run into. First, there’s FileSearch. I use FileSearch to read CustomDocumentProperties from closed Excel files. That way I can determine if the file is a part of my app and relevant to the context before I open it up. FileSearch was discontinued in Excel 2007. I’ve heard it’s because there was some bugs that would cost more to fix than it was worth. Now I simply open the file to read the CDPs. It’s not as efficient if the user opens the wrong file, but for a correct file, it’s faster. Instead of reading the properties then opening the file, I just open the file. Reading properties from an already opened file has to be quicker that FileSearch.
Once I eliminated the reference to FileSearch, I could actually open my add-ins and test them. In one procedure I insert a Picture object onto a sheet with code similar to this:
Set pic = sh.Pictures.Insert(sPICFILENAME)
I’m very vocal about my objections to selecting ranges. It’s almost never necessary. So why do I do it here? Good question. In Excel 2003, the Insert method will place the picture at the ActiveCell. In 2007, it seems to place the picture in the same place on the sheet regardless of the ActiveCell. So instead of Selecting, I change the code to something like:
pic.Top = sh.Range(“E43”).Top
pic.Left = sh.Range(“E43”).Left
That also works in 2003, so there was really no need to select anything.
The last problem I encountered was with the UsedRange property. We all know how Excel can lose track of where the last cell is and give you a UsedRange bigger than actual. I have one sheet where I loop through all the cells. I never add or delete rows or columns and the UsedRange is never wrong. Even if it was wrong by a few rows or columns, it wouldn’t impact performance. So I iterated through the cells in UsedRange.
When I opened the template in Excel 2007 the UsedRange was huge. The last cell was something like CW1024 when it should have been G40. If I open it in 2003, no problem. I stopped using UsedRange and rolled my own.
Dim lLastRow As Long
Dim lLastCol As Long
lLastRow = 1: lLastCol = 1 ‘in case the sheet is empty
‘Finding “*” will find anything. I look backward from the first
‘cell to get the last cell with anything in it
On Error Resume Next
lLastRow = sh.Cells.Find(“*”, sh.Cells(1), , , xlByRows, xlPrevious).Row
lLastCol = sh.Cells.Find(“*”, sh.Cells(1), , , xlByColumns, xlPrevious).Column
On Error GoTo 0
Set GetUsedRange = sh.Cells(1).Resize(lLastRow, lLastCol)
This doesn’t give the same results as UsedRange. This will always start at A1, whereas UsedRange will show the smallest rectangle that includes all non-empty cells and that may start somewhere other than A1.