B
BillCPA
Is there some problem with having shapes on spreadsheets in Excel 2007?
I have a bank reconciliation program that matches book entries with bank
deposits. Both book and bank data comes in electronically, and I use macros
to match the entries. In many cases there are multiple book entries that
total to one bank deposit. The macro groups these multiple book entries,
draws a box (borders) around them, and then draws an arrowline (shape) from
the boxed group to the corresponding bank deposit. There are anywhere from
$200-300M dollars in deposits each month, so the spreadsheet is quite large.
In Excel 2003 it could easily take an hour to run.
In Excel 2007 this same process takes anywhere from 10-12 hours to run. It
appears to have something to do with the shapes, because if I go into in the
spreadsheet after it has been created, and page scroll from/to a page with a
lot of the arrowlines on it, you can literally count the rows on the screen
as it refreshes. If I page scroll from/to a page with no shapes on it, it is
fairly instant. Or if I look at a worksheet with no shapes on it, the page
scroll is also regular speed.
My assumption is that as the macro is creating the shapes at the appropriate
places, it is somehow slowing down the processing, but that is only an
assumption. But I have no other clues. I do have Screenupdating turned off
during processing.
Any ideas?
I have a bank reconciliation program that matches book entries with bank
deposits. Both book and bank data comes in electronically, and I use macros
to match the entries. In many cases there are multiple book entries that
total to one bank deposit. The macro groups these multiple book entries,
draws a box (borders) around them, and then draws an arrowline (shape) from
the boxed group to the corresponding bank deposit. There are anywhere from
$200-300M dollars in deposits each month, so the spreadsheet is quite large.
In Excel 2003 it could easily take an hour to run.
In Excel 2007 this same process takes anywhere from 10-12 hours to run. It
appears to have something to do with the shapes, because if I go into in the
spreadsheet after it has been created, and page scroll from/to a page with a
lot of the arrowlines on it, you can literally count the rows on the screen
as it refreshes. If I page scroll from/to a page with no shapes on it, it is
fairly instant. Or if I look at a worksheet with no shapes on it, the page
scroll is also regular speed.
My assumption is that as the macro is creating the shapes at the appropriate
places, it is somehow slowing down the processing, but that is only an
assumption. But I have no other clues. I do have Screenupdating turned off
during processing.
Any ideas?