All cells "occupied" during BeforeSave event

[Greg Lovern]

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).Address
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Count
Debug.Print
ThisWorkbook.Save
Debug.Print "After save:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Address
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Count
Debug.Print

End Sub

-- In the Workbook_BeforeSave event, enter this:

Debug.Print "During Workbook_BeforeSave Event:"
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Address
Debug.Print Sheet1.Cells.SpecialCells(xlCellTypeConstants).Count
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

 

[Greg Lovern]

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

 

[Greg Lovern]

I was wrong; the problem occurs in ExcelApp_WorkbookBeforeSave too.
I'm not sure why I thought it worked there before.

BTW, I found that the problem does not occur in the BeforeClose event.
But I'd rather do it on save than on close.

I found that it works fine to do it in a function called by a timer in
the BeforeSave event:

Application.OnTime Now + 0.000000001,
"MyFunctionThatUsesSpecialCells_xlCellTypeConstants"

That runs as soon as the save is done, and the problem does not occur.
But it isn't ideal, because it would put me always one save behind,
unless I saved in the target function, which would annoy the user with
double saves.

So now I'm doing this in the BeforeSave:

If bDoSave Then
bDoSave = False
Else
bDoSave = True
Cancel = True
Application.OnTime Now + 0.000000001,
"MyFunctionThatUsesSpecialCells_xlCellTypeConstants"
End If

bDoSave is a public function declared in a standard module. And in
MyFunctionThatUsesSpecialCells_xlCellTypeConstants, the last line is
ThisWorkbook.Save.

That seems to work fine as a workaround, and the workbook is only
saved once each time the user or the code does a save.


So -- any idea why the problem occurs?


Greg