How to remove all macros (hidden+visible+VBA) from an excel file.

[MASOODAHMAD]

Hi,

I am facing one problem with one Excel file. While opening it displays a message that "This workbook contains macros.....". However, I have checked all the macros, VBA modules etc, but hardly find any macros or VBA. It seems that the creator has inserted some macro in a hidden format.

I am using Office 2011 (Mac) and MS Office 2004 (Mac). I also tried it in PC, but didn't get any success.

I guess there could be some software which can clean up the Excel file in one single click. Any help will be appreciated.

Thanks,
Masood

 

[scott_n_phnx]

One of the easiest ways that I can think of is to save the Excel file as .xlsx instead of .xlsm. That will remove all macros.

Besides that, open the Visual Basic Editor and go into each sheet within the project and delete everything and then make sure that all modules are deleted. If there are hidden macros, they also may be password protected, so option 1 may be best.

 

[MASOODAHMAD]

Hi Scott,

Thanks, but I already tried saving the file in xlsx format from xls. However, re-saving the file in xls is re-creating the problem. I also checked for the modules, but there isn't any.

Any other idea(s)..... please

 

[scott_n_phnx]

One other option to try.


Unhide any worksheets that may be hidden.
Select all the worksheets in the workbook. (Click on the first worksheet tab, then hold down Shift as you click on the last worksheet tab.)
Right click on one of the worksheet tabs. Excel displays a Context menu.
Choose Move or Copy from the Context menu. Excel displays the Move or Copy dialog box.

Using the To Book drop-down list, choose (new book).
Make sure the Create Copy check box is not selected.
Click on OK.
Rehide any worksheets you unhid in step 1.

Removing All Macros (Microsoft Excel)

 

[Zack Barresse]

Why would you save from an xlsx to an xls format? Keeping the format as xlsx, as Scott says, will ensure no macros can be saved to the file.

Excel 2011 can be an odd creature.

 

[MASOODAHMAD]

@Zack, @Scott, the reason for down-saving the xlsx file to xls is that the end user uses Office 2004 (Mac).

Scott, I have already tried your suggestions, but the error continues as soon as I down-save the file to xls. I leave this topic here. I have something else for your genius minds. I am currently working on Office 2007 and wants an equivalent formula to work in Office 2003.

=SUMIFS(Data!$I$8:$I$4000,Data!$A$8:$A$4000,">="&$M$8,Data!$A$8:$A$4000,"<="&$N$8)/60

=(SUMIFS(Data!$I$8:$I$4000,Data!$A$8:$A$4000,">="&$M$8,Data!$A$8:$A$4000,"<="&$N$8) -SUMIFS(Data!$I$8:$I$4000,Data!$D$8:$D$4000,"E-AWC",Data!$A$8:$A$4000, ">="&$M$8,Data!$A$8:$A$4000,"<="&$N$8))/60


Can anyone help me out and give me the equivalent 2003 formula for the above twos.

Thanks in advance.

 

[MASOODAHMAD]

Solved it myself:

=SUMIFS(Data!$I$6:$I$4000,Data!$A$6:$A$4000,">="&$M$6,Data!$A$6:$A$4000,"<="&$N$6)/60
=(SUMIFS(Data!$I$6:$I$4000,Data!$A$6:$A$4000,">="&$M$6,Data!$A$6:$A$4000,"<="&$N$6) -SUMIFS(Data!$I$6:$I$4000,Data!$D$6:$D$4000,"E-AWC",Data!$A$6:$A$4000, ">="&$M$6,Data!$A$6:$A$4000,"<="&$N$6))/60

equivalent for 2003
=SUMPRODUCT((Data!$A$6:$A$4000>=$M$6)*(Data!$A$6:$A$4000<=$N$6),(Data!$I$6:$I$4000))/60
=(SUMPRODUCT((Data!$A$6:$A$4000>=$M$6)*(Data!$A$6:$A$4000<=$N$6),(Data!$I$6:$I$4000))-SUMPRODUCT((Data!$A$6:$A$4000>=$M$6)*(Data!$A$6:$A$4000<=$N$6)*(Data!$D$6:$D$4000="E-AWC"),(Data!$I$6:$I$4000)))/60

Thanks to all

 

[hippiehacker]

do you have any strange named rangeslike below

Name: Sheetnames
refersTo: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

this would produce an warning like " This workbook contains one or more microsoft 4.0 macros...."

 

[Zack Barresse]

Goodonya with the formula.

Take a look at this link for removing all code from a project programmatically...

VBA Express : Excel - Remove All VBA Code Programmatically
http://www.mrexcel.com/forum/excel-questions/80638-macro-delete-all-macros.html

Or if you have ASAP Utilities (ASAP Utilities for Excel - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools) there is an option to remove all macros from a file.

 

[MASOODAHMAD]

Thanks Zack, this was The thing I was looking for. Many thanks...