×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Can I save a spreadsheet without the VBA code?

Can I save a spreadsheet without the VBA code?

Can I save a spreadsheet without the VBA code?

(OP)
I have a small VBA progam in an Excel spreadsheet that helps the user set up the worksheets. Once they save the spreadsheet to a new file there is no use for the program but it still gives the Disable/Enable Macros warning. Is there a way to save the Excel workbook without the VBA program?

RE: Can I save a spreadsheet without the VBA code?

If you're saving a copy of an existing workbook that contains macros, I don't know of a way to exclude/remove the macro modules.

Instead could you use your macro to create a New file? Or look into calling the macro from the personal.xls file or creating a Add-In (.xla).

Ken

RE: Can I save a spreadsheet without the VBA code?

You could move or copy the worksheet to a new workbook.

RE: Can I save a spreadsheet without the VBA code?

Get into the VB Editor. (Tools>Macros>VisualBasicEditor will do this for you, as will the Alt-F11 shortcut.)

Code associated with "Microsoft Excel Objects" (ie worksheets etc) has to be deleted explicitly, by selecting then deleting the lines of code.

Code in modules can be similarly deleted, but that leaves the module behind, and the module's presence will cause your enable/disable message to appear on opening.  To delete the module itself (as well as its contents) right-click on the module's name where it appears on the right hand side of the screen, then select "Remove..." from the options offered.
 

RE: Can I save a spreadsheet without the VBA code?

If you have Excel 2007 you could save as xlsx format, which does not save macros.  You could then re-save as xls for compatibility if you wanted to.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Can I save a spreadsheet without the VBA code?

Perhaps modify your macro to create the sheet in a new workbook that has no macros.

RE: Can I save a spreadsheet without the VBA code?

with your original file open, open a new excel file.
in the new file go to tool--macros and click run macros. your original macro in the OPEN ORIGINAL FILE will show up. run it and save your new file with what ever name you want. close your original file

RE: Can I save a spreadsheet without the VBA code?

(OP)
I'm sending these files out to customers so I don't have that kind of control over the file. The customer needs the macro in the original to perform the function that is needed to hide/unhide the correct worksheets. When they save the copy of the worksheet they don't need it anymore. I change a setting so that when the customer opens the copy again the macro doesn't run, but it still gives the macros warning screen. I guess that will have to work for now. Thanks

RE: Can I save a spreadsheet without the VBA code?

(OP)
I recently found this which deletes a macro from a file. I haven't tested it, but here it is anyway. I found it at
http://www.ozgrid.com/VBA/delete-module.htm

Excel VBA: Delete Module After Running VBA Code. Deleting Modules via VBA Code

Delete Module via Code. See Also Delete Sheet Event Code With Code & Delete Workbook Event Code

The code below can be used to delete the module which houses the code. In other words, it deletes itself after running once.

You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code. Change "Module1" to suit.

Sub DeleteThisModule()

Dim vbCom As Object
MsgBox "Hi, I will delete myself "
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")

End Sub
 

RE: Can I save a spreadsheet without the VBA code?

That does work. But it won't delete code from the "ThisWorkbook" or any of the "Sheet"...uuummm...I'll call them Modules (because I'm not sure if that is the correct terminology or not).

Ken

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources