Three Porting Issues

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:

sh.Range(“E43”).Select
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:

Set pic = sh.Pictures.Insert(sPICFILENAME)
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.

Public Function GetUsedRange(sh As Worksheet) As Range
   
    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)
   
End Function

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.

Posted in Uncategorized

12 thoughts on “Three Porting Issues

  1. Yeah, there are a bunch of “gotchas” that one runs into when converting to 2007.

    I haven’t encountered the UsedRange problem you describe. But, given that you can find the real last cell what would happen if you used Intersect(UsedRange, getUsedRange())? Or changed getUsedRange to return Range(UsedRange.cells(1),{lastcell})

  2. Good one Tushar. I could easily limit it by intersecting with UsedRange. I wrote this anyway

    Public Function GetUsedRange(sh As Worksheet) As Range
       
        Dim lLastRow As Long
        Dim lLastCol As Long
        Dim lFirstRow As Long
        Dim lFirstCol As Long
        Dim rFirstCell As Range
       
        lFirstRow = 1: lFirstCol = 1
        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
            If Not IsEmpty(sh.Range(“A1”).Value) Then
                Set rFirstCell = sh.Range(“A1”)
            Else
                Set rFirstCell = sh.Cells.Find(“*”, sh.Cells(1), , , , xlNext)
            End If
            lFirstRow = rFirstCell.Row: lFirstCol = rFirstCell.Column
            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 = rFirstCell.Resize(lLastRow – lFirstRow + 1, lLastCol – lFirstCol + 1)
       
    End Function

    But I should have written this

    Public Function GetUsedRange(sh As Worksheet) As Range
       
        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 = Intersect(sh.Cells(1).Resize(lLastRow, lLastCol), sh.UsedRange)
       
    End Function
  3. Dick, about 10 years ago, I came across this:
    DSOFILE.EXE is a self-extracting executable that provides an in-process ActiveX DLL for Visual Basic programmers to use in order to read or modify the Document Summary Properties for an OLE Structured Storage file. Because Word, Excel, and PowerPoint use OLE Structured Storage to save their files to disk, this DLL lets programmers view and change document properties without the need to open the
    document in Office.

    I still have this file, plus sample code, if you are interested. And yes, it retrieves custom properties.

  4. Thanks for the DSOFile tip. I had enough trouble getting .xla file past IT. We thought getting them to accept a dll wasn’t worth the trouble. My understanding is that this file is not installed by default and I would have to distribute it. I hate dependencies.

  5. Excel’s UsedRange includes cells with formatting other than the Normal style even if they have no contents. Maybe not a problem for you, but harder to locate.

    Possibly more of a problem, Finding * won’t find cells that ‘contain’ only the label prefix ‘ but have nothing else or cells in which the formula =”” was converted to a value. Such cells also aren’t blank. They’re degenerate text constants.

    More robust to use something like

    Dim lc As Range, r As Range

    On Error Resume Next

    Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
    If Not r Is Nothing Then
      Set r = r.Areas(r.Areas.Count)
      Set lc = r.Cells(r.Cells.Count)
    End If

    Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    If Not r Is Nothing Then
      Set r = r.Areas(r.Areas.Count)
      If lc Is Nothing Then
        Set lc = r.Cells(r.Cells.Count)
      Else
        Set r = ActiveSheet.Range(lc, r.Cells(r.Cells.Count))
        Set lc = r.Cells(r.Cells.Count)
      End If
    End If

    With ActiveSheet
    If lc Is Nothing Then
      Set r = .Range(“A1”)
    Else
      Set r = .Range(.UsedRange.Cells(1), lc)
    End If

    r is then the portion of the used range with contents.

  6. The DSOFile does need to be distributed. Therefore I use it on my machine, but I let other people wait for their machines to open files to read the properties. It’s still good for saving a build number to a custom doc prop prior to creating a new installation exe, and it conveniently also works on pdf files.

  7. As I recall, the Find method for usedrange also comes up short if there are merged cells at the edge of the range.
    For the DSO thing see ttp://www.cpearson.com/excel/docprop.htm
    ‘Document Properties Of Closed Files DSO Version 2.0
    ‘The following code illustrates how to read document properties using DSO version 2.0.
    ‘You can download DSO Version 2.0 from Microsoft at
    ‘http://www.microsoft.com/downloads/details.aspx?FamilyID=9ba6fac6-520b-4a0a-878a-53ec8300c4c2&DisplayLang=en
    Function DSOODP()
    Dim DSO As DSOFile.OleDocumentProperties
    ‘ requires project to have reference to DSO OLE Document Properties Reader 2.0
    Set DSO = New DSOFile.OleDocumentProperties
    DSO.Open sfilename:=”F:dirfile.xls”
    Debug.Print DSO.SummaryProperties.ApplicationName
    Debug.Print DSO.SummaryProperties.Author
    ‘ lots of other properties, cannot be accessed by text of name in a variable
    DSO.Close
    End Function

  8. @fzz:
    I tried your alternate technique, but it did not find the actual used range – in my test example, there is only one cell in the first column in the last row. Your lc range found this cell and then returned the used range as just the first column. Only if the last cell is at the most rightmost edge of the range will the technique work – any other alternate approaches – I can not think of how to improve on yours.

  9. Issue 4: I have a range named “rep4? which is perfectly fine in 2003. But with the expanded columns in 2007, REP4 is now a cell reference. Excel 2007 seems to fix this automatically by changing the name to “_rep4?, but it won’t check VBA code. A quick Find in the VBE determined that I wasn’t using that range name in code.

    Normally, I prefix my range names, such as rngRep4 for a range reference or conRep4 for a constant value. For some reason I didn’t prefix this one.

  10. @fzz

    Also I just added
    Set ActiveSheet.UsedRange = r
    ActiveWorkbook.save

    It did not reset the UsedRange (Ctr+End – still takes me to the wrong used range

  11. Love your postings!

    Based on an example of capturing commas in text in CSV files, I looked up the “instr” command. I noticed many of the commands for manipulating text also hav a “b” or “$” or both. Can someone explain the difference between “instr” and “instrb” or “rightB” and “rightB$”.

Leave a Reply

Your email address will not be published. Required fields are marked *