I've found a workaround -- the problem does not occur in the
ExcelApp_WorkbookBeforeSave event.

Of course, that requires setting up application-level events:

http://www.cpearson.com/excel/AppEvent.aspx

I'm using his "Application Events In An New Class Module" way. I
haven't tested whether this workaround also works with his
"Application Events In An Existing Object Module" way.


I'd still like to know why the problem occurs in the
Workbook_BeforeSave event. Any ideas?


Greg




On Sep 26, 10:17 am, Greg Lovern wrote:
SpecialCells(xlCellTypeConstants) reports that *all cells* are
occupied by constants during the Workbook_BeforeSave event.

Try this:

-- In Excel 2007, create a new workbook, and save it as a macro-
enabled workbook.
-- Create a standard module, and enter this:

Sub test()

Debug.Print "Before save:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print
ThisWorkbook.Save
Debug.Print "After save:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print

End Sub

-- In the Workbook_BeforeSave event, enter this:

Debug.Print "During Workbook_BeforeSave Event:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Add ress
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Cou nt
Debug.Print

-- To avoid getting a runtime error while keeping the test simple,
enter any data in any one cell in Sheet1. I entered text in cell D7.

-- Run the test macro. Here's what I get, in compatibility mode:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$1:$65536
16777216

After save:
$D$7
1

What's up?

Again, that's in compatibility mode. If I close the workbook and
reopen it to get into normal Excel 2007 mode, I get this, and then an
overflow error:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$1:$1048576

I guess that's because Count is trying to return a Long, and the Count
would be 16,384 x 1,048,576 = 17,179,869,184; okay for a Double but
too big for a Long. I get the same overflow error even if I wrap it in
CDbl.

If I narrow down the range of cells (still in normal Excel 2007 mode)
to a single column to avoid the overflow error, like this:

Debug.Print
Sheet1.Columns(4).SpecialCells(xlCellTypeConstants ).Address
Debug.Print Sheet1.Columns(4).SpecialCells(xlCellTypeConstants ).Count

Then I get this:

Before save:
$D$7
1

During Workbook_BeforeSave Event:
$D:$D
1048576

After save:
$D$7
1

So -- why are all cells considered to be occupied by constants during
the Before_Save event?

BTW, the problem does not occur with SpecialCells(xlCellTypeFormulas);
only with SpecialCells(xlCellTypeConstants).

Thanks,

Greg