1、 Excel Macros i About the Tutorial An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be d
2、one frequently. Audience This guide targets novice developers and those new to Excel Macros. After completing this tutorial, your firm foundation in creating macros will allow you to use macros efficiently. Prerequisites When you record a macro, Excel stores it as a VBA code. You can view this code
3、in the VBA editor. You can understand the code and modify it if you have substantial knowledge of Excel VBA. However, if you do not have sufficient knowledge, then we will suggest you to go through our short tutorials on VBA. Copyright & Disclaimer Copyright 2016 by Tutorials Point (I) Pvt. Ltd. All
4、 the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We stri
5、ve to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including thi
6、s tutorial. If you discover any errors on our website or in this tutorial, please notify us at Excel Macros ii Table of Contents About the Tutorial . i Audience . i Prerequisites . i Copyright & Disclaimer . i Table of Contents . ii 1. Excel Macros Overview . 1 2. Excel Macros Creation . 4 Recordin
7、g a Macro . 4 Running a Macro . 6 Storing a Macro . 7 Saving a Macro Enabled File . 8 3. Excel Macros Macros in a Single Workbook . 10 Saving Macros in Personal Macro Workbook . 10 Hiding / Unhiding Personal Macro Workbook . 13 Adding / Deleting Macros in Personal Macro Workbook . 14 4. Excel Macros
8、 Security . 16 What are Macro Viruses? . 16 Macro Enabled Excel Workbooks . 16 Ways of Trusting Macro Enabled Workbook . 16 Macro Security Settings in Trust Center . 17 Macro Settings . 19 Defining a Trusted Location . 20 Digitally Signed Macros from Reliable Sources . 21 Using Warning Messages. 22
9、Enabling / Disabling Security Alerts on the Message Bar . 22 5. Excel Macros Absolute References . 25 Absolute References . 25 Ensuring Absolute References . 27 Recording a Macro . 28 Running a Macro . 30 6. Excel Macros Relative References . 31 Relative References . 31 Using Relative References . 3
10、2 Recording a Macro . 33 Running a Macro . 35 7. Excel Macros VBA . 37 Developer Tab on the Ribbon . 37 Developer Commands for Macros . 38 VBA Editor . 39 Projects Explorer . 41 Excel Macros iii 8. Excel Macros Understanding Codes . 42 Viewing a Macro Code in VBA Editor. 42 Understanding the Recorde
11、d Actions as Parts of Code . 43 9. Excel Macros - Assigning Macros to Objects . 45 Assigning a Macro to a Shape . 45 Assigning a Macro to a Graphic . 49 Assigning a Macro to a Control. 49 10. Excel Macros - Running a Macro . 53 Running a Macro from View Tab . 53 Running a Macro with Shortcut Key . 5
12、4 Running a Macro through Quick Access Toolbar . 56 Running a Macro in Custom Group . 63 Running a Macro by Clicking an Object . 73 Running a Macro from the Developer Tab . 73 Running a Macro from VBA Editor . 74 11. Excel Macros Creating a Macro Using VBA Editor . 75 VBA Objects and Modules . 75 Cr
13、eating a Macro by Coding . 77 Running the Macro from VBA Editor . 80 Running the Macro from Worksheet. 81 12. Excel Macros Editing . 82 Copying a Macro Code. 82 Renaming a Macro. 84 Deleting a Macro . 86 13. Excel Macro UserForms . 88 Creating a UserForm . 88 Understanding the UserForm . 89 Controls
14、 in the ToolBox . 90 Message Box Icon Displays . 106 14. Excel Macros Debugging a Code . 107 VBA Debugging . 107 15. Excel Macros Configuring a Macro . 111 Recording an Auto_Open Macro . 111 Limitations of Auto_Open Macro . 112 VBA Code for Open Event of a Workbook . 112 Excel Macros 1 An Excel macr
15、o is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. When you create a macro, you are recording your mouse clicks and keystrokes. When you run a saved macro, the recorded mouse clicks and keystrokes will be executed in the
16、 same sequence as they are recorded. Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently. Macro and VBA You can record and run macros with either Excel commands or from Excel VBA. VBA stands for Visual Basic for Appl
17、ications and is a simple programming language that is available through Excel Visual Basic Editor (VBE), which is available from the DEVELOPER tab on the Ribbon. When you record a macro, Excel generates VBA code. If you just want to record a macro and run it, there is no need to learn Excel VBA. How
18、ever, if you want to modify a macro, then you can do it only by modifying the VBA code in the Excel VBA editor. You will learn how to record a simple macro and run it with Excel commands in the chapter - Creating a Simple Macro. You will learn more about macros and about creating and / or modifying
19、macros from Excel VBA editor in the later chapters. Personal Macro Workbook A macro can be saved in the same workbook from where you recorded it. In that case, you can run the macro from that workbook only and hence you should keep it open. Excel gives you an alternative way to store all your macros
20、. It is the personal macro workbook, where you can save your macros, which enables you to run those macros from any workbook. You will learn about Personal Macro Workbook in the chapter - Saving all your Macros in a Single Workbook. Macro Security Macros will be stored as VBA code in Excel. As with the case of any other code, macro code is also susceptible to malicious code that can run when you open a workbook. This is a threat t
