Category: excel Macros

Excel Macros – Running a Macro ”; Previous Next There are several ways of executing a macro in your workbook. The macro would have been saved in your macro enabled workbook or in your Personal macro workbook that you can access from any workbook as you had learnt earlier. You can run a macro in the following ways − Running a Macro from the View Tab Running a Macro by pressing Ctrl plus a shortcut key Running a Macro by clicking a button on the Quick Access Toolbar Running a Macro by clicking a button in a Custom Group on the Ribbon Running a Macro by clicking on a Graphic Object Running a Macro from Developer Tab Running a Macro from VBA Editor Running a Macro from View Tab You have already learnt running a macro from the View tab on the Ribbon. A quick recap − Click the VIEW tab on the Ribbon. Click Macros. Select View Macros from the dropdown list. The Macro dialog box appears. Click the macro name. Click the Run button. Running a Macro with Shortcut Key You can assign a shortcut key (Ctrl + key) for a macro. You can do this while recording the macro in the Create Macro dialog box. Otherwise, you can add this later in the Macro Options dialog box. Adding a Shortcut Key While Recording a Macro Click the VIEW tab. Click Macros. Select Record Macro from the dropdown list. The Create Macro dialog box appears. Type a macro name Type a letter, say q, in the box next to Ctrl + under Shortcut key. Adding a Shortcut Key in Macro Options Click the VIEW tab. Click Macros. Select View Macros from the dropdown list. The Macro dialog box appears. Select the macro name. Click the Options button. The Macro Options dialog box appears. Type a letter, say q, in the box next to Ctrl + under Shortcut key. Click OK. To run the macro with the shortcut key, press the Ctrl key and the key q together. The macro will run. Note − You can use any lowercase or uppercase letters for the shortcut key of a macro. If you use any Ctrl + letter combination that is an Excel shortcut key, you will override it. Examples include Ctrl+C, Ctrl+V, Ctrl+X, etc. Hence, use your jurisdiction while choosing the letters. Running a Macro through Quick Access Toolbar You can add a macro button to the Quick Access Toolbar and run the macro by clicking it. This option would be useful when you store your macros in personal macro workbook. The added button will appear on the Quick Access Toolbar in whatever workbook you open, thus making it easy for you to run the macro. Suppose you have a macro with the name MyMacro in your personal macro workbook. To add the macro button to the Quick Access Toolbar do the following − Right click on the Quick Access Toolbar. Select Customize Quick Access Toolbar from the dropdown list. The Excel Options dialog box appears. Select Macros from the dropdown list under the category- Choose commands from. A list of macros appears under Macros. Click PERSONAL.XLSB!MyMacro. Click the Add button. The macro name appears on the right side, with a macro button image. To change the macro button image, proceed as follows − Click the macro name in the right box. Click the Modify button. The Modify Button dialog box appears. Select one symbol to set it as the icon of the button. Modify the Display name that appears when you place the pointer on the Button image on the Quick Access Toolbar to a meaningful name, say, Run MyMacro for this example. Click OK. The Macro name and the icon symbol change in the right pane. Click OK. The macro button appears on the Quick Access Toolbar and the macro display name appears when you place the pointer on the button. To run the macro, just click the macro button on the Quick Access Toolbar. Running a Macro in Custom Group You can add a custom group and a custom button on the Ribbon and assign your macro to the button. Right click on the Ribbon. Select Customize the Ribbon from the dropdown list. The Excel Options dialog box appears. Select Main Tabs under Customize the Ribbon. Click New Tab. The New Tab (Custom) appears in Main Tabs list. Click New Tab (Custom). Click the New Group button. The New Group (Custom) appears under New Tab (Custom). Click New Tab (Custom). Click the Rename button. The Rename dialog box appears. Type the name for your custom tab that appears in Main tabs on the Ribbon, say – My Macros and click OK. Note − All the Main tabs on the Ribbon are in uppercase letters. You can use your discretion to use uppercase or lowercase letters. I have chosen lowercase with capitalization of words so that it stands out in the standard tabs. The new tab name changes to My Macros (Custom). Click New Group (Custom). Click the Rename button. The Rename dialog box appears. Type the group name in the Display name dialog box and click OK. The new group name changes to Personal Macros (custom). Click Macros in the left pane under Choose commands from. Select your macro name, say – MyFirstMacro from the macros list. Click the Add button. The macro will be added under the Personal Macros (Custom) group. Click My Macros (Custom) in the list. Click the arrows to move the tab up or down. The position of the tab in the main tabs list determines where it will be placed on the Ribbon. Click OK. Your custom tab – My Macros appears on the Ribbon. Click the tab – My Macros. Personal Macros group appears on the Ribbon. MyFirstMacro appears in the Personal Macros group. To run the macro, just click on MyFirstMacro in the Personal Macros group. Running a Macro by Clicking an Object You can insert

Excel Macros – UserForms ”; Previous Next At times, you might have to collect information repeatedly from others. Excel VBA provides you with an easy way of handling this task- UserForm. As any other form that you fill up, UserForm makes it simple to understand, what information is to be provided. UserForm is user friendly in the way that the controls provided are self-explanatory, accompanied by additional instructions where necessary. Major advantage of UserForm is that you can save on time that you spend on what and how the information is to be filled. Creating a UserForm To create a UserForm, proceed as follows − Click the DEVELOPER tab on the Ribbon. Click Visual Basic. A Visual Basic window for the workbook opens. Click Insert, Select UserForm from the dropdown list. The UserForm appears on the right side of the window. Understanding the UserForm Maximize the UserForm.xlsx – UserForm1 window. You are in the design mode now. You can insert controls on the UserForm and write code for the respective actions. The controls are available in the ToolBox. Properties of UserForm are in the Properties window. UserForm1 (caption of the UserForm) is given under Forms in the Projects Explorer. Change the caption of the UserForm to Project Report – Daily in the properties window. Change the name of the UserForm to ProjectReport. The changes are reflected in the UserForm, properties and project explorer. Controls in the ToolBox A UserForm will have different components. As and when you click on any of the components, either you will be provided with instructions on what and how the information is to be provided or you will be provided with options (choices) to select from. All these are provided by means of ActiveX controls in the ToolBox of the UserForm. Excel provides two types of controls – Form controls and ActiveX controls. You need to understand the difference between these two types of controls. Form controls Form controls are the Excel original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets. You can run macros by using Form controls. You can assign an existing macro to a control, or write or record a new macro. When the control is clicked, the macro. You have already learnt how to insert a command button from Form controls in the worksheet to run a macro. However, these controls cannot be added to a UserForm. ActiveX controls ActiveX controls can be used on VBA UserForms. ActiveX controls have extensive properties that you can use to customize their appearance, behavior, fonts and other characteristics. You have the following ActiveX controls in the UserForm ToolBox − Pointer Label TextBox ComboBox ListBox CheckBox OptionButton Frame ToggleButton CommandButton TabStrip MultiPage ScrollBar SpinButton Image In addition to these controls, Visual Basic provides you with MsgBox function that can be used to display messages and/or prompt the user for an action. In the next few sections, you will understand these controls and MsgBox. Then, you will be in a position to choose which of these controls are required to design your UserForm. Label You can use Labels for identification purpose by displaying descriptive text, such as titles, captions and / or brief instructions. Example TextBox You can use a TextBox that is a rectangular box, to type, view or edit text. You can also use a TextBox as a static text field that presents read-only information. Example List Box You can use a List Box to display a list of one or more items of text from which a user can choose. Use a list box for displaying large numbers of choices that vary in number or content. Insert a ListBox on the UserForm. Click on the ListBox. Type ProjectCodes for Name in the Properties window of the ListBox. There are three types of List Boxes − Single-selection List box − A single-selection List Box enables only one choice. In this case, a list box resembles a group of option buttons, except that a list box can handle a large number of items more efficiently. Multiple selection List Box − A multiple selection List Box enables either one choice or contiguous (adjacent) choices. Extended-selection List Box − An extended-selection List Box enables one choice, contiguous choices and noncontiguous (or disjointed) choices. You can select one of these types of List Boxes, from the Properties window. Right click on the UserForm. Select View Code from the dropdown list. The code window of UserForm opens. Click Initialize in the top right box of the code window. Type the following under Private Sub UserForm_Initialize(). ProjectCodes.List = Array (“Proj2016-1”, “Proj2016-2”, “Proj2016-3”, “Proj20164”, “Proj2016-5”) Click the Run tab on the Ribbon. Select Run Sub/UserForm from the dropdown list. Next, you can write code for actions on selecting an item in the list. Otherwise, you can just display the text that is selected, which is the case for filling the Project Code in the Report. ComboBox You can use ComboBox that combines a text box with a list box to create a dropdown list box. A combo box is more compact than a list box but requires the user to click the down arrow to display the list of items. Use a combo box to choose only one item from the list. Insert a ComboBox on the UserForm. Click the ComboBox. Type ProjectCodes2 for Name in the Properties window of the ComboBox. Right click on the UserForm. Select View Code from the dropdown list. The code window of UserForm opens. Type the following as shown below. ProjectCodes2.List = Array (“Proj2016-1”, “Proj2016-2”, “Proj2016-3”, “Proj20164”, “Proj2016-5”) Click the Run tab on the Ribbon. Select Run Sub/UserForm from the dropdown list. Click the down arrow to display the list of items. Click on the required item, say, Project2016-5. The selected option will be displayed in the combo box. CheckBox You can use check boxes to select one or more options that are displayed

Excel Macros – Editing ”; Previous Next You have learnt how to write macro code in VBA editor in the previous chapter. You can edit the macro code, rename a macro and delete a macro. If you master Excel VBA, writing code or modifying code for a macro is a trivial task. You can edit the macro code however you want. If you want to make only few simple changes in the macro code, you can even copy macro code from one place to another. Copying a Macro Code You have created two macros – MyFirstMacro and Button1_Click in the macro enabled workbook MyFirstMacro.xlsm. You have created the first macro by recording the steps and the second macro by writing code. You can copy code from the first macro into the second macro. Open the workbook MyFirstMacro.xlsm. Click the Developer tab on the Ribbon. Click Visual Basic. The Visual Basic editor opens. Open the code for Module1 (MyFirstMacro macro code) and Module2 (Button1_Click () macro code). Click the Window tab on the Ribbon. Select Tile Horizontally from the dropdown list. You can view the code of the two macros in the tiled windows. Copy the MsgBox line in the Module2 code. Paste it above that line. Modify the string as − MsgBox “Hello World!” Copy the following code from Module1. Paste it in the Module2 code in between the two MsgBox lines of code. Click the Save icon to save the code. Click the Button in the Excel sheet. A Message box appears with the message – Hello World! Click OK. The table data appears (according to the code that you copied) and message box appears with message – Best Wishes to You! You can modify the code in just a few steps. This is the easiest task for a beginner. Renaming a Macro Suppose you want to run the edited macro from any worksheet other than the one that has the command button. You can do it irrespective of button click by renaming the macro. Click the VIEW tab on the Ribbon. Click Macros. Select View Macros from the dropdown list. The Macro dialog box appears. Click the macro name – Button1_Click. Click the Edit button. The macro code appears in the VBA editor. Change the name that appears in the Sub line from Button1_Click to RenamedMacro. Leave Sub and parenthesis as they are. Open the Macro dialog box. The macro name appears as you renamed. Click RenamedMacro. Click the Run button. The macro runs. Now a button click is not necessary. Deleting a Macro You can delete a macro that you have recorded or coded. Open the Macros dialog box. Click the macro name. Click the Delete button. The Delete confirmation message appears. Click Yes if you are sure to delete the macro. Otherwise, click No. Print Page Previous Next Advertisements ”;

Excel Macros – Macros in a Single Workbook ”; Previous Next Excel provides you with a facility to store all your macros in a single workbook. The workbook is called Personal Macro Workbook – Personal.xlsb. It is a hidden workbook stored on your computer, which opens every time you open Excel. This enables you to run your macros from any workbook. There will be a single Personal Macro Workbook per computer and you cannot share it across computers. You can view and run the macros in your Personal Macro Workbook from any workbook on your computer. Saving Macros in Personal Macro Workbook You can save macros in your Personal Macro Workbook by selecting it as the storing option while recording the macros. Select Personal Macro Workbook from the drop down list under the category Store macro in. Record your second macro. Give macro details in the Record Macro dialog box as shown below. Click OK. Your recording starts. Create a table as shown below. Stop recording. Click the VIEW tab on the Ribbon. Click Macros. Select View Macros from the dropdown list. The Macro dialog box appears. The macro name appears with a prefix PERSONAL.XLSB! indicating that the Macro is in the Personal Macro Workbook. Save your workbook. It will get saved as an .xls file as the macro is not in your workbook and close Excel. You will get the following message regarding saving the changes to the Personal Macro Workbook − Click the Save button. Your macro is saved in the Personal.xlsb file on your computer. Hiding / Unhiding Personal Macro Workbook Personal Macro Workbook will be hidden, by default. When you start Excel, the personal macro workbook is loaded but you cannot see it because it is hidden. You can unhide it as follows − Click the VIEW tab on the Ribbon. Click Unhide in the Window group. The Unhide dialog box appears. PERSONAL.XLSB appears in the Unhide workbook box and click OK. Now you can view the macros saved in the personal macro workbook. To hide the personal macro workbook, do the following − Click on the personal macro workbook. Click the VIEW tab on the Ribbon. Click Hide on the Ribbon. Running Macros Saved in Personal Macro Workbook You can run the macros saved in personal macro workbook from any workbook. To run the macros, it does not make any difference whether the personal macro workbook is hidden or unhidden. Click View Macros. Select the macro name from the macros list. Click the Run button. The macro will run. Adding / Deleting Macros in Personal Macro Workbook You can add more macros in personal macro workbook by selecting it for Store macro in option while recording the macros, as you had seen earlier. You can delete a macro in personal macro workbook as follows − Make sure that the personal macro workbook is unhidden. Click the macro name in the View Macros dialog box. Click the Delete button. If the personal macro workbook is hidden, you will get a message saying “Cannot edit a macro on a hidden workbook”. Unhide the personal macro workbook and delete the selected macro. The macro will not appear in the macros list. However, when you create a new macro and save it in your personal workbook or delete any macros that it contains, you will be prompted to save the personal workbook just as in the case you saved it first time. Print Page Previous Next Advertisements ”;

Excel Macros Tutorial PDF Version Quick Guide Resources Job Search Discussion 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 done 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 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. Print Page Previous Next Advertisements ”;

Excel Macros – Relative References ”; Previous Next Relative reference macros record an offset from the active cell. Such macros will be useful if you have to repeat the steps at various places in the worksheet. Suppose you are required to analyze the data of voters collected from 280 constituencies. For each constituency, the following details are collected − Constituency name. Total population in the constituency. Number of voters in the constituency. Number of male voters, and Number of female voters. The data is provided to you in a worksheet as given below. It is not possible to analyze the data in the above format. Therefore, arrange the data in a table as shown below. If you attempt to arrange the given data in the above format − It takes substantial amount of time to arrange the data from the 280 constituencies It can be error prone It becomes a mundane task not allowing you to focus on technical things The solution is to record a macro so that you can complete the task in not more than a few seconds. The macro needs to use relative references, as you will move down the rows while arranging the data. Using Relative References In order to let the macro recorder know that it has to use relative references, do the following − Click the VIEW tab on the Ribbon. Click Macros. Click Use Relative References. Preparing the Data Format The first step in arranging the above given data is to define the data format in a table with headers. Create the row of headers as shown below. Recording a Macro Record the macro as follows − Click Record Macro. Give a meaningful name, say, DataArrange to the macro. Type = row ()- 3 in the cell B4. This is because the S. No. is the current row number – the 3 rows above it. Cut the cells B5, B6, B7, B8 and B9 and paste it in the cells C4 to C8 respectively. Now click in the cell B5. Your table looks as shown below. The first data set is arranged in the first row of the table. Delete the rows B6 – B11 and click in the cell B5. You can see that the active cell is B5 and the next data set will be placed here. Stop recording the macro. Your macro for arranging the data is ready. Running a Macro You need to run the macro repeatedly to complete the data arrangement in the table as given below. The active cell is B5. Run the macro. The second data set will be arranged in the second row of the table and the active cell will be B6. Run the macro again. The third data set will be arranged in the third row of the table and the active cell will become B7. Each time you run the macro, the active cell advances to the next row, facilitating the repetition of recorded steps at the appropriate positions. This is possible because of the relative references in macro. Run the macro until all the 280 data sets are arranged into 280 rows in the table. This process takes a few seconds and as the steps are automated, the entire exercise is error free. Print Page Previous Next Advertisements ”;

Excel Macros – Security ”; Previous Next The macros that you create in Excel would be written in the programming language VBA (Visual Basic for Applications). You will learn about the Excel macro code in later chapters. As you are aware, when there is an executable code, there is a threat of viruses. Macros are also susceptible to viruses. What are Macro Viruses? Excel VBA in which the Macros are written has access to most Windows system calls and executes automatically when workbooks are opened. Hence, there is a potential threat of the existence of a virus written as a macro and is hidden within Excel that are executed on opening a workbook. Therefore, Excel macros can be very dangerous to your computer in many ways. However, Microsoft has taken appropriate measures to shield the workbooks from macro viruses. Microsoft has introduced macro security so that you can identify which macros you can trust and which you cannot. Macro Enabled Excel Workbooks The most important Excel macro security feature is – file extensions. Excel workbooks will be saved with .xlsx file extension by default. You can always trust workbooks with .xlsx file extension, as they are incapable of storing a macro and will not carry any threat. Excel workbooks with macros are saved with .xlsm file extension. They are termed as Macro Enabled Excel Workbooks. Before you open such workbooks, you should make sure that the macros they contain are not malicious. For this, you must ensure that you can trust the origin of this type of workbooks. Ways of Trusting Macro Enabled Workbook Excel provides three ways to trust a macro enabled workbook. Placing the macro enabled workbooks in a trusted folder Checking if a macro is digitally signed Enabling security alert messages before opening macro enabled workbooks Placing the macro enabled workbooks in a trusted folder This is the easiest and best way to manage macro security. Excel allows you to designate a folder as a trusted location. Place all your macro-enabled workbooks in that trusted folder. You can open macro-enabled workbooks that are saved to this location without warnings or restrictions. Checking if a macro is digitally signed Digital signatures confirm the identity of the author. You can configure Excel to run digitally signed macros from trusted persons without warnings or restrictions. Excel will also warn the recipient if it has been changed since the author signed it. Enabling security alert messages before opening macro enabled workbooks When you open a workbook, Excel warns you that the workbook contains macros and asks whether you wish to enable them. You can click the Enable Content button if the source of the workbook is reliable. You can set any of these three options in the Trust Center in the Excel Options. If you work in an organization, the system administrator might have changed the default settings to prevent anyone from changing the settings. Microsoft advises that you do not change security settings in the Trust Center as the consequences can be loss of data, data theft or security compromises on your computer or network. However, you can learn the macro security settings in the following sections and check if they are to be changed. You have to use your own instinct to decide on any of these options based on the context and your knowledge of the file origin. Macro Security Settings in Trust Center The macro settings are located in the Trust Center in the Excel Options. To access the Trust Center, do the following − Click the FILE tab on the Ribbon. Click Options. The Excel Options dialog box appears. Click Trust Center in the left pane. Click the Trust Center Settings button under Microsoft Excel Trust Center. The Trust Center dialog box appears. You will see various options available in the Excel Trust Center in the left pane. You will learn about the options related to Excel macros in the following sections. Macro Settings Macro settings are located in the Trust Center. Under Macro Settings, four options are available. Disable all macros without notification − If this option is chosen, Macros and security alerts about macros are disabled. Disable all macros with notification − Macros are disabled, but security alerts appear if there are macros present. You can enable macros on a case-by-case basis. Disable all macros except digitally signed macros − Macros are disabled but security alerts appear if there are macros present. However, if the macro is digitally signed by a trusted publisher, the macro runs if you trust the publisher. If you have do not trust the publisher, you will be notified to enable the signed macro and trust the publisher. Enable all macros (not recommended, susceptible to macro viruses) − If this option is chosen, all macros run. This setting makes your computer vulnerable to potentially malicious code. You have an additional security option under Developer Macro Settings with a Check box. Trust access to the VBA project object model. This option allows programmatic access to the Visual Basic for Applications (VBA) object model from an automation client. This security option is for code written to automate an Office program and manipulate the VBA environment and object model. It is a per-user and per-application setting, and denies access by default, hindering unauthorized programs from building harmful self-replicating code. For automation clients to access the VBA object model, the user running the code must grant access. To turn on access, select the check box. Defining a Trusted Location If you think that a macro-enabled workbook is from a reliable source, it is better to move the file to the trusted location identified by Excel, instead of changing the default Trust Center settings to a less-safe macro security setting. You can find the trusted folder settings in the Trust Center. Click the Trusted Locations in the Trust Center dialog box. The Trusted Locations set by Microsoft Office appear on the right side. You can add new locations, remove the existing

Excel Macros – Understanding Codes ”; Previous Next When you record a macro, Excel stores it as a VBA code. You can view this code in the VBA editor. You can understand the code and modify it if you have substantial knowledge of Excel VBA. You can refer to the Excel VBA tutorial in this tutorials library to obtain a grasp on the language. However, you can still view the macro code in Excel VBA editor and match it to the steps that you recorded in macro. You will learn how to view the code and understand it for the first macro that you created in this tutorial – MyFirstMacro. Viewing a Macro Code in VBA Editor To view a macro code, do the following − Open the workbook in which you stored the macro. Click VIEW tab on the Ribbon. Click Macros. Select View Macros from the dropdown list. The Macro dialog box appears. Click MyFirstMacro in the macros list. Click the Edit button. The VBA editor opens and the code of the macro MyFirstMacro appears. Understanding the Recorded Actions as Parts of Code You can browse through the macro code and map them to your recorded steps. Start reading the code. Map the code to the recorded steps. Scroll down the code to view more code. Alternatively, you can enlarge the code window. Observe that the code is simple. If you learn Excel VBA, you can create the macros by writing the code in the VBA editor. You will learn how to write a VBA code to create a macro in the chapter – Creating a Macro Using VBA Editor. Print Page Previous Next Advertisements ”;