Macros

There are three possible workbooks where you can save your macros:
This Workbook - This is the default location and is often the best place if you are relatively new to macros. A macro that has been saved into a specific workbook is only available when that particular workbook is open. The currently active workbook is also referred to as the current workbook or active workbook.
New Workbook - A completely new workbook will be opened for you automatically. This can be useful for quickly examining the generated code but remember that the workbook you store the macro in must be open in order for the macro to be run.
Personal Macro Workbook - Storing your macros here will mean that they are available every time Excel is open and are not reliant on any one particular workbook.

alt text

Visual Basic Editor

You access the VBA code using the Visual Basic Editor window.
This window can be displayed by clicking on the Developer Tab and pressing Visual Basic or by pressing (Alt + F11).

alt text

Inside the Visual Basic Editor select (View > Project Explorer) to display the Project Explorer window.
There should be a project there called VBAProject followed by the name of the workbook in brackets. Expand this project.
Select the Modules node and expand it. There should be a module called "Module 1". Double click this module to display the code window.
Your macro will appear here and all the VBA code is contained within the "Sub" and "End Sub" statements.
You will often find that the Macro Recorder will generate more code than you really need although it is easy to remove any unnecessary code.


Changing Macro Security

When you open workbooks that contain existing macros they might be getting disabled by the Macro Settings.
If you want to run any macros, you need to have the macro security level set to either Disable all macros with notification or Enable all macros.
The default setting is currently Disable all macros with notification.

alt text

Important

It is not possible to record a macro and have the generated code placed within an existing macro. You will have to cut and paste the lines of code manually.
The code generated by the macro recorder often contains a lot of unnecessary arguments and lines and should really be cleaned up afterwards.
Be aware that the Macro Recorder cannot create any of the following: Loops, Variables, Error Handling or Conditional statements.
The code generated by the macro recorder can often be quite inefficient and will run quite slowly compared to code that has been optimised.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopNext