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.


 


Visual Basic Editor

You can view the VBA code is through the Visual Basic Editor window.
This window can be displayed by selecting (Tools > Macro > Visual Basic Editor) or by pressing (Alt + F11).
You will need to locate the Project in the Project Explorer window. This should appear in the top left corner. If you cannot see it, select (View > Project Explorer).
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

The macro security only comes into play when you open workbooks that contain existing macros.
It is possible to record (and run) a macro in any open workbook even when your security setting is set to Very High or High.
A lot of books and other websites give you the impression that you need to change the macro security level before you can record a macro. This is not the case.
You will however need to change the security level to Medium if you want to run any macros.


Insert or Write the Code

The Visual Basic for Applications window will be opened automatically.
You will see that a code module has been added to the active workbook and that the following code has been added:
You are now ready to insert or paste in you own VBA code.
Once you have entered your VBA code select (Debug > Compile) to make sure your code does not contain any syntax errors.
After you have finished you can return to Excel by selecting (File > Close and Return to Microsoft Excel).


Do not use the Range objects as arguments to procedures or function. Use the address instead.
Try to use Range("") with column letter references instead of Cells() with numerical references. If you have to use Cells() the always use a column enumeration so it is very quick to identify individual columns.
Use worksheet code names, rather than referring to the Sheets("---") descriptive names that can be easily changed by the user.
Never use ActiveWorkbook as you can never guarantee what workbook will be the active workbook.
Always use .Cells() instead of .Range(col_letter) when using enumerations
Conditional Formatting - no option on paste special but selecting "formats" will paste conditional formats
User defined functions - you can't use the same name for a module and a user defined function - otherwise the UDF is not recognised.
How to definitely update workbook/just one worksheet (do not use Application.Calculate) - do you have to go through each worksheet ?
Undo functionality is session specific, not workbook specific.


Important

If you are writing VBA code then make sure the following two options are selected in your (Tools > Options)(Editor tab) of the Visual Basic Editor window: "Auto List Members" and "Auto Quick Info".
There is no comment provided automatically when you create a macro in Excel, only when you record a macro.
The code that is generated is normally very specific and often dependent on local settings or options.
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.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Next