VBA to Save Current Workbook Without VBA Code

0

In some cases, you may want to save a copy of your current Excel workbook without retaining any VBA code. This is useful when sharing files, protecting sensitive macros, or ensuring compatibility with systems that don’t support VBA. This article provides two methods to achieve this goal, with step-by-step guidance, VBA code explanations, and practical advice.

Method 1: Use VBA to Save a Workbook Without VBA Code

If you want to automate the process or handle more complex requirements, you can use VBA to create a copy of the current workbook and remove all VBA code.

VBA Code:

Sub SaveWorkbookWithoutVBA()
    Dim vbComp As Object
    Dim wb As Workbook
    Dim savePath As String

    ' Define the save path for the new file
    savePath = ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & "_NoVBA.xlsx"

    ' Create a copy of the current workbook
    ThisWorkbook.SaveCopyAs savePath

    ' Open the copied workbook
    Set wb = Workbooks.Open(savePath)

    ' Remove all VBA modules in the copied workbook
    On Error Resume Next
    For Each vbComp In wb.VBProject.VBComponents
        If vbComp.Type = vbext_ct_StdModule Or vbComp.Type = vbext_ct_ClassModule Or vbComp.Type = vbext_ct_MSForm Then
            wb.VBProject.VBComponents.Remove vbComp
        End If
    Next vbComp
    On Error GoTo 0

    ' Save the workbook in .xlsx format to completely remove VBA
    Application.DisplayAlerts = False
    wb.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook ' Save as .xlsx
    Application.DisplayAlerts = True

    ' Close the copied workbook
    wb.Close SaveChanges:=False

    ' Notify the user
    MsgBox "Workbook has been saved without VBA code at: " & savePath, vbInformation
End Sub

Key Steps in the Code

• Define Save Path: The new file is saved in the same directory as the original, with _NoVBA appended to the file name.
• Create and Open a Copy: The macro duplicates the current workbook using SaveCopyAs and opens the copied file for editing.
• Remove VBA Modules: Iterates through all VBA components in the workbook and deletes standard modules, class modules, and user forms.
• Save as .xlsx: Saves the workbook in .xlsx format, which inherently does not support VBA, ensuring all code is removed.
• Notify User: A message box informs the user that the workbook has been successfully saved without VBA.

This macro is efficient for creating VBA-free versions of Excel workbooks while preserving all non-VBA content.

Steps to Run the VBA Code:

Press Alt + F11 to open the VBA editor.

Insert a new module: Insert > Module.

Paste the VBA code into the module.

Press Alt + Q to close the editor and return to Excel.

Run the macro: Press Alt + F8, select SaveWorkbookWithoutVBA, and click Run.

Method 2: Save Workbook as .xlsx Without VBA Code

The .xlsx format does not support VBA, so saving the workbook in this format automatically removes all macros and VBA code.

Steps to Save as .xlsx Manually:

• Open the workbook containing the VBA code.
• Go to File > Save As (or press F12).
• Choose a location to save the file.
• In the Save as type dropdown, select Excel Workbook (*.xlsx).
• Provide a new name for the file (to avoid overwriting the original file).
• Click Save. Now the image below will appear.
• Select Save and erase features

When to Use VBA to Save Without VBA Code

• Sharing Files: When sending files to users who may not trust or need the embedded macros.
• Protecting Intellectual Property: To prevent others from accessing or modifying sensitive VBA code.
• Ensuring Compatibility: For systems or applications that do not support VBA-enabled formats like .xlsm or .xlsb.
• Automation: When handling large numbers of files or integrating the process into a larger workflow.

Key Considerations and Tips

Enable Trust Access to VBA Project:

• Go to File > Options > Trust Center > Trust Center Settings > Macro Settings.
• Check Trust access to the VBA project object model.

Backup Your Work: Always save a backup of your original file before running macros that modify file contents.

Verify Results: Open the saved file to ensure all VBA code has been successfully removed.

File Format: If you save the file as .xlsx, ensure that no VBA-dependent functionality is required in the new file.

Use Appropriate Method:

• Use the manual method for quick, one-time tasks.
• Use VBA for automated or repetitive processes.

Saving a workbook without VBA code can be achieved manually or programmatically using VBA. The manual method is straightforward but less flexible, while the VBA method offers automation and is suitable for advanced scenarios. By following the steps and considerations outlined above, you can ensure your files are properly saved without VBA, meeting security, compatibility, and sharing needs.