Using macros in microsoft excel part 2

Using macros in microsoft excel part 2

• 1.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.1 © NCC Education LimitedV1.0 Read the Code Generated by a Macro in the VB Editor • When using the macro recorder, it is worth taking a look at the code that is written in in the VB Editor. • The following are the steps involved in creating a macro that is assigned to the keys Ctrl + m, so that when Crtl + m are pressed in cells containing whole numbers, the numbers will be displayed as currency.
• 2.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.2 © NCC Education LimitedV1.0 Reading VBA Code - 1 • A spreadsheet is opened that displays the following data: 1.Click to select a cell. 2.Open Record macro. 3.Enter the macro name. 4.Assign short cut keys Ctrl + m. 5.Save to the current workbook.
• 3.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.3 © NCC Education LimitedV1.0 Reading VBA Code - 2 6. Click OK. 7. Format the cells to Currency > set to 2 decimal places and select the £ symbol. 8. Click OK. 9. Stop the Macro Recorder. 10. Apply the macro by pressing the Ctrl + m keys. 11. The numbers are now displayed as currency.
• 4.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.4 © NCC Education LimitedV1.0 Reading VBA Code - 3 • Let us have a look at the code in which that this macro is written. • To do this, we select module1 in the VBA Project window. • Then we open the View Code window.
• 5.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.5 © NCC Education LimitedV1.0 Reading VBA Code - 4 • The code for the macro is then displayed: • You can look at the code for all the macros that you create if you want to learn more about VBA.
• 6.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.6 © NCC Education LimitedV1.0 Create a Macro that Uses Absolute Cell References - 1 • When a macro is recorded, it will run in exactly the same sheets/cells/columns/rows that are specified by the user – it is has been provided with absolute cell references. The following slide shows the instructions to create a macro to make column A in a spreadsheet bold.
• 7.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.7 © NCC Education LimitedV1.0 Create a Macro that Uses Absolute Cell References - 2 1. Open the Record Macro dialogue box, name the macro BoldColumn, save it in This Workbook and assign it to the shortcut keys Ctrl + a. 2. Click OK and click on the column header for column A to highlight the whole column. Click on the Bold icon and stop recording. 3. When you click on Ctrl + a, you can see that column A is highlighted; however, if you click on a cell in any other column, the macro does not work, because it uses only an absolute cell reference.
• 8.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.8 © NCC Education LimitedV1.0 Create a Macro that Uses Relative Cell References • The Use Relative References button must be clicked on to be activated. • Once this is done, the macro recorder can be opened, a macro named, saved and recorded using the same steps as for the macro that used absolute cell referencing. • When recording is complete, the macro can be tested and this time, when the short cut keys are used, any column in the spreadsheet can be made bold.
• 9.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.9 © NCC Education LimitedV1.0 Assign an Icon to Run a Macro - 1 1. Open a spreadsheet in which you have already created a macro. 2. Click on the Customize Quick Access Toolbar.
• 10.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.10 © NCC Education LimitedV1.0 Assign an Icon to Run a Macro - 2 3. Select More Commands from the bottom of the drop down menu. 4. Select Macros from Choose Commands from. 5. Select the name of the spreadsheet.
• 11.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.11 © NCC Education LimitedV1.0 Assign an Icon to run a Macro - 3 6. Select the macro that you want to add to the Quick Access Toolbar, click Add and the macro is moved to the box on the right. 7. Click on Modify and the Modify Button dialogue box appears.
• 12.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.12 © NCC Education LimitedV1.0 Assign an Icon to Run a Macro - 4 8. Select an icon from the Modify Button box, click on OK twice and the icon will appear on the Quick Access Toolbar: 9. The macro can now be run by simply clicking on the icon!
• 13.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.13 © NCC Education LimitedV1.0 Printing Data Using a Macro • The following macro can be created to automate printing a spreadsheet: 1. Open the Record Macro dialogue box. 2. Name the macro and assign it to the shortcut keys Ctrl + j. 3. Click on OK. 4. Select the print commands. 5. Stop recording. 6. Test by pressing Ctrl + j.
• 14.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.14 © NCC Education LimitedV1.0 • If a user attaches a digital signature to a file, it helps to certify that he or she is the owner of the file and no attempt has been made to send it fraudulently. 1. Open the VB Editor. 2. Select Tools 3. The Digital Signature box appears 4. Select Choose Adding a Digital Signature to a Macro
• 15.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.15 © NCC Education LimitedV1.0 Digital Signatures 5. Choose a certificate and click OK twice. 6. Click Save 7. Click File, choose Close and return to the spreadsheet. 8. If you suspect that a digital signature has problems, select Show Signature Details which will indicate a problem with a signature by a X
• 16.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.16 © NCC Education LimitedV1.0 References • Gonzalez, J., Meister, C., Ozgur, S., Dilworth, B., Troy, A. and Brandt, T. (2006). Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook & Access, Holy Macroi Press. • Microsoft Office: Digital Signatures. (2011). [Available Online] http://office.microsoft.com
• 17.
  Using Macros inMicrosoft Excel Part 2 Topic 9 - 9.17 © NCC Education LimitedV1.0 Topic 9 – Using Macros in Microsoft Excel Part 2 Any Questions?