All cells "occupied" during BeforeSave event
 

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

All cells "occupied" during BeforeSave event
 

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

All cells "occupied" during BeforeSave event
 

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_xlCellTypeConstant s"

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_xlCellTypeConstant s"
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



On Sep 26, 11:07 am, Greg Lovern wrote:
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