Macros
Your workbook is saved as .xlsx file, which implies you cannot have VBA macros directly in your workbook. If you need to use external macros, you must supply your own Add-In, e.g. an .xlam file.
The following approach is advised instead:
- Start Excel. Create a new Workbook. Open the VBA Editor. In the new workbook, create your macro modules. Declare functions that need to be accessed by the unit operation as Public.
- Change the VBA Project name in the Project properties from "VBAProject" to a unique name.
- Save the workbook as Excel Add-in (*.xlam) or Excel Macro Enabled Add-in (*.xlam); the preferred locations for storing the file is in the Excel\XLStart folder of the user's Application Data folder, or in the XLStart sub-folder of the Office installation folder. This will cause the Add-In to be automatically loaded.
- Close the workbook.
- If you have not saved the Add-In in the XLStart folder, click the Office button, click Office options, go to the Add-ins tab, and select Manage Excel Add-ins to get to the Add-in dialog. Click the Browse button, find your saved Add-In, add it to Excel, and make sure it has a check mark in front of it.
- Close Excel. Start your Excel unit operation. Click Edit. Excel will start. You can now use your macro functions.
If you transfer the Excel Unit Operation that uses Add-ins to a different system, make sure that the Add-In is also transferred to the different system. Again, preferred is to put it in the XLStart folder (in the Office installation folder, or in the Excel sub-folder of the user's Application data folder). You may or may not get a warning from Excel that the Add-Ins cannot be found, the first time you open the unit operation. Ignore the warnings; the Excel Unit Operation will resolve the references. If you are asked to update links, answer No. If you are asked to enable automatic links resolution, answer Disable:
Introduction
Function reference