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
On Sep 26, 11:07 am, Greg Lovern <gr...@gregl.net> 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 <gr...@gregl.net> 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).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
Similar Threads
