1 / 68

11. Visual Basic Macro Recorder

Learn how to streamline repetitive Excel tasks using the Visual Basic for Applications (VBA) Macro Recorder. By recording your actions, you can automate formatting, data manipulation, and other routine operations without performing each step manually. This guide walks you through creating a macro that formats tables, including changing font styles and sizes. You'll also discover how to modify the recorded VBA code in the Visual Basic editor to customize your macros further. Save time and enhance productivity by mastering Excel macros!

kadeem
Télécharger la présentation

11. Visual Basic Macro Recorder

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 11. Visual BasicMacro Recorder

  2. Open Excel

  3. Click File -> Save As

  4. Save As a Excel Macro-Enabled Workbook

  5. Click File -> Options

  6. Click Customize Ribbon

  7. Tick Developer

  8. Click OK

  9. Select Developer menu

  10. Macro Recorder • A job may require you to do the same repetitive task every day in Excel • You can make Excel complete some of these tasks automatically by recording a macro • All you have to do to repeat the action is to click a button

  11. Formatting Cells • If you are creating tables of results you may need to format them in a particular way e.g. font, border, background • Rather than doing each step repeatedly we can record the actions once, and then make Excel repeat them

  12. Type in this table of information

  13. Highlight these cells

  14. Click Record Macro

  15. Macro name: Format TableShortcut key: Ctrl+o

  16. Click OK

  17. Every action we take from now on will be recorded until we click Stop Recording

  18. Right-click with mouse and select Format Cells

  19. Click Font and select Times New Roman,Bold, Size 14 and Colour Dark Blue

  20. Click Border and then the Double Lines

  21. Select Top and Bottom borders

  22. Click OK

  23. Click Stop Recording

  24. Click Visual Basic

  25. Select Module 1

  26. Excel has recorded all of the tasks which you have just completed, and written a program

  27. You can make changes to this program by typing into the script

  28. Click Close

  29. Type in the following table

  30. Highlight the new table

  31. Press Ctrl + oThis is the shortcut key we assigned to our macro

  32. The macro is run and all of the tasks are completed automatically

  33. Challenge • Go into Visual Basic editor • Change the Font Name to “Verdana” • Change the Font Style to “Italic” • Change the Font Size to 12 • Save and Close the Visual Basic editor • Select the table again and press Ctrl+o to reformat the table

  34. Trial and Error • The best way to learn specific commands in Visual Basic is to use the Macro Recorder • You can look at the code that is generated and then try to make some changes

  35. Stock Screener • By using some creativity you can create complex programs using Visual Basic • In the following example we will download data from Yahoo Finance, and then filter it to find companies with a low P/E ratio

  36. Select Sheet 2

  37. Click Record Macro

  38. Type Macro name: StockScreener Shortcut key: Ctrl + l

  39. Click OK

  40. Click Data -> Import From Text

  41. Type in the following web addresshttp://finance.yahoo.com/d/quotes.csv?s=MSFT+GOOG&f=snpryv

  42. Click Open

  43. Click Cancel if any passwords are requested

  44. Click Next

  45. The original data is separated by commas so tick Comma

  46. Click Next

  47. Click Finish

  48. Click OK

  49. Click Cancel if any password is requested

  50. Imports the data which we asked for

More Related