Create a macro in Excel

Getting the most out of Eileen's Lounge!
User avatar
Rudi
gamma jay
Posts: 25167
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Create a macro in Excel

Post by Rudi »

How to integrate macros into Excel
------------------------------------------------
If you are provided with a solution that involves Visual Basic code (VBA or macros) and are unfamiliar with the process to integrate it into your workbook for use, this short step by step will show you how. First locate the version of Excel you are working with, and then apply the steps as illustrated.

In the lounge, VBA code or macros are presented to you in Code Tags. These tags will ensure the code is compatible with the VBA Editor into which you will paste the code. It also conveniently provides a Select All option to easily copy the entire block of code without having to drag to select it. Try it with this example:

Code: Select all

Sub Hello()
   MsgBox "Hello, world!"
End Sub
Steps to use VBA code
------------------------------
Excel 2007 / 2010 / 2013
Please note: Screenshots below are taken from Excel 2013. Older versions might have small variances.

1. Use the Select All option to select the VBA code from the post
2. Copy the selected code
3. Open Excel and the workbook where the code must be placed
4. Press ALT+F11 (to open the VBA Editor) - as seen in screenshot below
VBA Editor (1).jpg
5. Select Insert, Module (unless specified otherwise in the post)
6. Paste the code into the module on the right
7. Click the Save button
8. Close the VBA Editor

To Use/Run the macro in Excel
1. Access the VIEW tab of the ribbon
2. Click on the Macros button (and select View Macros) - as seen in screenshot below (or press ALT+F8)
Use or Run Macro (2).jpg
3. Select your macro and click on Run - as seen in screenshot below
Macro Dialog (3).jpg
4. An optional extra is to use the Options button to assign a shortcut key and description for the macro
--- A shortcut key allows you to trigger the macro from the keyboard without using the Ribbon
--- You can assign any letter to the shortcut. The shortcut will be Ctrl+that letter. (Tip: Use uppercase letters to avoid overwriting common/existing hotkeys)
--- A description helps identify the macro's purpose, esp. if you have a dozen or more in the workbook


Excel 2003

1. Use the Select All option to select the VBA code from the post
2. Copy the selected code
3. Open Excel and the workbook where the code must be placed
4. Press ALT+F11 (to open the VBA Editor) - as seen in screenshot below
VBA Editor (1).jpg
5. Select Insert, Module (unless specified otherwise in the post)
6. Paste the code into the module on the right
7. Click the Save button
8. Close the VBA Editor

To Use/Run the macro in Excel
1. Access the Tools Menu
2. Click on Macro, and then Macros, to open the Macro dialog (or press ALT+F8)
2003%20Run%20Macro.jpg
3. Select your macro and click on Run
2003%20Macro%20Dialog.jpg
4. An optional extra is to use the Options button to assign a shortcut key and description for the macro
--- A shortcut key allows you to trigger the macro from the keyboard without using the Ribbon
--- You can assign any letter to the shortcut. The shortcut will be Ctrl+that letter. (Tip: Use uppercase letters to avoid overwriting common/existing hotkeys)
--- A description helps identify the macro's purpose, esp. if you have a dozen or more in the workbook
You do not have the required permissions to view the files attached to this post.
Last edited by Rudi on 15 Feb 2014, 14:04, edited 9 times in total.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.