How to Edit an Excel XLA or XLS Add-in Using VBA
Question details
The user needs to make an Excel XLA or XLS add-in editable to access and modify its VBA project and hidden worksheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Editing the source code or hidden sheets of an existing Excel add-in file.
- Observed behavior
- The add-in's VBA project and worksheets are typically hidden or locked from standard view, requiring a property change in the Visual Basic Editor to become editable.
Ensure you have a backup copy of the XLA or XLS add-in file before making modifications to its VBA project, as incorrect code changes can cause the add-in to malfunction.
Change the IsAddin Property in the VBA Editor
Modify the workbook properties within the Visual Basic Editor to make the add-in editable as a standard workbook.
By default, Excel add-ins (XLA/XLAM) run invisibly in the background. To edit their underlying worksheets and macros, you must temporarily disable their add-in status through the Visual Basic Editor's properties window.
Open Excel and load your add-in. Press the Alt + F11 shortcut on your keyboard to open the Visual Basic Editor (VBE).
In the top menu, navigate to View and select Project Explorer. Then, go to View again and select Properties Window to ensure both panels are visible on your screen.
In the Project Explorer panel on the left, locate your add-in's VBA project. Expand the project tree and click on the 'ThisWorkbook' module.
In the Properties Window below the Project Explorer, find the 'IsAddin' property and change its value from True to False. The hidden worksheets and VBA code will now be accessible and editable in the main Excel window.
Manage Add-ins and Macros with WPS Spreadsheet
WPS Spreadsheet provides robust support for VBA and macros, allowing you to seamlessly open, edit, and manage your Excel add-ins (.xla, .xlam) in a familiar interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the WPS Spreadsheet application.
- 2. Load the Add-in: Navigate to the Developer tab on the ribbon and click on Add-Ins to browse and load your XLA file.
- 3. Access the VBA Editor: Click the Visual Basic button in the Developer tab or press Alt+F11 to open the built-in VBA editor.
- 4. Edit Properties: Select 'ThisWorkbook' in the Project Explorer and toggle the 'IsAddin' property to modify your add-in's code and worksheets.
Frequently Asked Questions
Why can't I see the VBA Project Explorer in Excel?
If the Project Explorer is missing, you can display it by opening the Visual Basic Editor (Alt+F11) and selecting View > Project Explorer from the top menu, or by using the Ctrl+R keyboard shortcut.
How do I save my changes back to an XLA add-in?
After editing the VBA project and hidden worksheets, go back to the Visual Basic Editor, select 'ThisWorkbook', change the 'IsAddin' property back to 'True', and save the file using Ctrl+S.
What is the difference between an XLS and XLA file?
An XLS file is a standard Excel workbook that displays visible worksheets when opened. An XLA file is an Excel Add-in; it runs in the background, its worksheets are hidden by default, and it provides custom functions or macros to other workbooks.
Can I edit a password-protected VBA add-in?
To edit a password-protected VBA project, you must first know the password. When you try to expand the project in the Project Explorer, a prompt will appear asking for the password to unlock the source code.
