Macros

Hi Richard,

Thank you for the forum question.

Do you have specific information you would to know?

Here’s a practical overview of macros, where they’re saved, and the best ways to make them available to other team members.
What is a macro?
A macro is a recorded or coded set of actions that automates repetitive tasks. In Excel, Word, PowerPoint and other Microsoft 365 desktop apps, macros are usually written in VBA — Visual Basic for Applications. For example, a macro could format a report, clean imported data, create sheets, copy information, or run a monthly process with one click. Microsoft describes macros as a series of commands used to automate repeated tasks.
Macros can be very useful, but they also carry security risk because malicious macros can run harmful code. Microsoft recommends only enabling macros when you trust the source and understand what the macro does.
________________________________________
Where macros can be saved
1. Save the macro inside the workbook
If the macro is only needed for one specific Excel file, save the file as an Excel Macro-Enabled Workbook:
.xlsm
If you save a workbook containing macros as a normal .xlsx file, the macro code will not be preserved. Microsoft’s guidance is to save macro workbooks using Excel Macro-Enabled Workbook (*.xlsm).
Best for:
• A report template
• A shared workbook where the macro belongs to that file
• Team members who only need the macro when using that workbook
________________________________________
2. Save macros in your Personal Macro Workbook
Excel also has a hidden file called:
PERSONAL.XLSB
This opens automatically in the background whenever Excel starts, making its macros available across your own Excel files. Microsoft explains that the Personal Macro Workbook is stored on your computer and opens every time Excel starts.
Important: macros saved in your Personal Macro Workbook are not automatically shared with other people when you send them an Excel file. They remain on your computer unless you export or copy them. Microsoft notes that Personal.xlsb can be copied to another computer’s XLSTART folder, or selected macros can be copied into another user’s Personal.xlsb.
Best for:
• Personal productivity macros
• Macros you use across many workbooks
• Not usually the best option for team-wide distribution unless managed carefully
________________________________________
3. Save macros in an Excel Add-in
For team use, a good approach is to save the macros in an Excel add-in file:
.xlam
An add-in can be installed by users and can expose macros, custom ribbon buttons, or functions. This is often cleaner than asking everyone to copy code into their own workbooks.
Best for:
• Shared tools used regularly by a team
• Macros that should be available in many workbooks
• A more controlled “internal tool” setup
________________________________________
Best ways to share macros with team members
Option A — Share a macro-enabled workbook
This is the simplest method.
1. Open the workbook containing the macro.
2. Go to File > Save As.
3. Choose:
Excel Macro-Enabled Workbook (*.xlsm)
4. Save it to a shared location such as Teams, SharePoint, or a network drive.
5. Tell users to open the file and enable macros only if they trust it.
This works well when the macro is tied to that specific workbook. Microsoft confirms that if the macro is needed in the current workbook, it should be saved as a macro-enabled workbook.
________________________________________
Option B — Export and share the VBA module
This is useful if you want to share just the macro code.
In Excel:
1. Press Alt + F11 to open the VBA Editor.
2. In the Project Explorer, find the module containing the macro.
3. Right-click the module.
4. Choose Export File.
5. Save the module as:
.bas
6. Send the .bas file to a colleague.
7. They can import it through the VBA Editor using File > Import File.
This is better than sending your whole PERSONAL.XLSB, because replacing someone’s Personal Macro Workbook could overwrite their existing macros.
________________________________________
Option C — Create a shared add-in
For a more professional team setup:
1. Put your macros in a clean workbook.
2. Save it as:
Excel Add-In (*.xlam)
3. Store it in a shared location.
4. Ask users to install it via:
File > Options > Add-ins > Manage Excel Add-ins > Go > Browse
5. Select the .xlam file.
This is the best route if the macro is a reusable team tool.
________________________________________
Option D — Use a trusted SharePoint/Teams/network location
If your organisation allows it, files containing macros can be stored in a Trusted Location so users do not need to enable macros every time. Microsoft notes that trusted locations can be used so Microsoft 365 apps do not check those files with the Trust Center in the same way.
However, this may be controlled by IT policy. If devices are managed by your organisation, administrators may prevent users from changing macro or Trust Center settings.
________________________________________
Security guidance for team macros
Before sharing macros widely:
• Only enable macros from trusted sources.
• Avoid asking people to enable all macros globally.
• Prefer “Disable macros with notification” rather than “Enable all macros”.
• Store macro files in a controlled SharePoint, Teams, or network location.
• Add comments in the VBA code explaining what each macro does.
• Consider asking IT whether macros should be digitally signed.
• Keep a master copy and version history.
________________________________________
Recommended approach
For most teams, I’d suggest:
1. If the macro belongs to one file:
Save and share it as an .xlsm workbook.
2. If the macro should be reused across many files:
Package it as an .xlam Excel add-in.
3. If it is only for your own use:
Store it in PERSONAL.XLSB.
4. If sharing with colleagues:
Avoid sending your whole PERSONAL.XLSB; export/import the relevant module instead.
________________________________________
Quick checklist before sharing
☐ Save as .xlsm or .xlam
☐ Test the macro on a copy of the file
☐ Remove personal or sensitive data
☐ Add instructions for users
☐ Store in a trusted shared location
☐ Confirm macro policy with IT if needed
☐ Keep a backup/version-controlled master copy



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector