Gerald asked if there was a way to get rid of all the macros in an Excel workbook, without the need to individually delete them. There are two ways you can accomplish this task. The first approach is used if you don't want to mess with the macros at all. Just follow these steps:
Figure 1. The Move or Copy dialog box.
Your worksheets have now been moved to a new workbook—one that does not have any macros attached to it.
The second approach is to simply work with the existing workbook, and is a viable choice if you feel comfortable with macros in the first place. Follow these steps:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2713) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Removing All Macros.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
As your macro is processing information, there will doubtless be times that it will need to compare information in ...
Discover MoreNeed to use a macro to select a specific cell in a different workbook? It's not as straightforward of a proposition as ...
Discover MoreDo your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-03-08 13:41:09
Essa
Thanks ... that is Help me .
2017-02-25 05:35:51
Petros
Simon
You may need to remove the code signature using a VBE menu, before removing all VBA code. I guess your code certificate is still valid
The Macro Mover add-in removes the entire VBA project, so there is chance of 'left-overs'. However, it does not remove signatures. Thanks, that is a feature I will add in the near future!
If you do not want your colleagues to tamper with your VBA, you can lock your code as unviewable.
http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html
2017-02-24 06:49:39
Simon
Hello, in my company we've set the security option, that only signed macros are allowed. Normaly, all macros which i've tested are working fine, when I attach a signature, but one document is putting me into trouble. Even if I attach a signature, I get a message, that makros are disabled for this project.
I spent a lot of time for troubleshooting and got the idea, that there can be some kind of code, which is not included in the signature, so that excel denys makros for this document in general. When I completely remove all macros with your second method and go to "check document for problems" (translated from my german installation, in english hopefully equal), the I get a message, that there is still some code inside. I think that this message is supporting my idea, that there is some code anywhere in the document, which I'm not able to find.
Do you have any suggestions for me to solve my problem?
By the way, your first method cannot work for my end scenario, because there the excel sheet is called from SAP and I cannot import a completly new generated document. I only can modify this dump document, which is not working with a signature.
2016-10-29 09:53:32
allen@sharonparq.com
Petros is correct, but he is providing an answer for newer versions of Excel. Remember that *this tip* (above) is for older versions of Excel (2003 and before) where you CANNOT save in a "macro-free format." For the older versions, you must follow the steps listed above.
If you are using a newer version of Excel (2007 and later), then follow the link at both the beginning and end of the tip, above, to find the proper instructions you need.
-Allen
2016-10-29 09:48:55
Petros
The easiest way to remove all VBA code from a macro-enabled Office file is to save the file in a macro-free format. For example, save a .xlsb workbook as .xlsx
However you should not open any workbook that you suspect that it has malicious macros or code with bugs that may damage your data or PC. If you disable macros, then you are left with no choice other than to delete macros manually or open and save each file in a macro-free format.
However, the Macro Mover add-in allows you to mitigate security risks and work more productively:
Macros are removed from closed files only! There is no need to disable all macros from the Trust center. Your files will not be opened in Excel, so VBA code cannot be executed.
Code is deleted from closed files, so you don't have to wait while opening/saving large files.
VBA code can be removed from any macro-enabled Office file (including add-ins), even from locked VBA projects.
Macros can be removed without changing the extension of the file. You can still take advantage of the high compression ratio offered by the Excel binary file format (.xlsb), while erasing macros fast.
Macros can be removed from multiple Office files (Excel, PowerPoint, Word) in a single operation.
Read more:
http://www.spreadsheet1.com/move-excel-vba-projects-from-one-workbook-to-another.html
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Comments