How can I best "scrub" a wkbk of "phantom macros"?

[Dave Braden]

Hi, I hope one of you can help me here -

I've a large workbook (several worksheets within; no chart-sheets or
otherwise) that I've created pro bono for local public agencies. Problem
I am having is that when loading it on a system with moderate macro
security, it issues the "do you want to really do this" sort of msg,
with "Enable Macros", "Eat More Burritos", etc. sort of pane. Further,
even though no change might have been made to the wkbk, Excel (2003 and
later) prompts for a "Save Changes?" when closing.

While I thought of embedding macros, I didn't finish, nor finalize,
such. When looking at the wkbk with VBA, I see no code traces. Rob B's
excellent Code Cleaner found no code to clean.

Can someone plz give me guidance as to how to get this workbook back to
its "virgin" state (i.e., plain ol' workbook that won't trigger macro
alerts)? Normally I wouldn't bother, but because I am dealing with
governments, well, the current behavior is a real drag.

TIA, and regards to the "old-timers" here-
Dave Braden
Excel MVP for a few years, some time ago

 

[FSt1]

hi
wild guess. does the vb editor have any empty modules? this would trigger a
macro alert.

Regards
FSt1

 

[Susan B.]

Thanks "FSt1" - But nope, no empty modules I can detect. Although I'm
not quite in the league of Excel programmers per se of Bovey and Bullen
(say), I am darn close - well, my Mom thinks so, at least. So did
Microsoft. If someone has an idea of how I might transfer a workbook's
sheets, formulas intact, to a new one, that might do the trick.
Problem I have is that my workbook (usual practice) has a sheet for core
assumptions, which feed other sheets. Some of these "fed" sheets are
very, very large.

Regards,
Dave B

 

[Dave Peterson]

Jan Karel Pieterse has a "rebuild bad workbooks" product that he sells.

http://www.jkp-ads.com/ProductsRemediation.asp

You may want to see if he has a special discount for former MVPs who are working

 

[mxyzpltk]

I might be able to assist, but you'd have to send an email to
(e-mail address removed).

Conrad Carlberg

 

[mxyzpltk]

More seriously, have you checked for code associated with objects such
as worksheets -- for example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Modules that contain this sort of code do not show up in the VBE
project explorer's Modules folder.


C^2
Of course, that's just my opinion. I could be wrong.

 

[Dave Braden]

Thanks for the lead, Dave. I'll let you guys know how it works out for
my particular case.

Dave B

 

[JKP]

Hi Dave

Can someone plz give me guidance as to how to get this workbook back to
its "virgin" state (i.e., plain ol' workbook that won't trigger macro
alerts)? Normally I wouldn't bother, but because I am dealing with
governments, well, the current behavior is a real drag.

I expect the fastest way is to do a save-as to 2007's xlsx file
format.
BTW: if you want the tool Dave referred to, send me an email. Address
is on the bottom of each page of my website.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com