1 / 13

Excel Macro VBA Overview

Excel Macro VBA Overview. Kevin Fujinami 4/26/2013. What are Excel Macros?. S tep-by-step procedures written in Visual Basic Making code Recording macros Writing code from scratch. Components of VBA Code. Option Explicit Sub UnhideSheets () ‘ Declaring a variable Dim i As Long

daire
Download Presentation

Excel Macro VBA Overview

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. Excel Macro VBA Overview Kevin Fujinami 4/26/2013

  2. What are Excel Macros? • Step-by-step procedures written in Visual Basic • Making code • Recording macros • Writing code from scratch

  3. Components of VBA Code Option Explicit Sub UnhideSheets() ‘ Declaring a variable Dim i As Long ‘ Loop that makes each worksheet visible For i = 1 To Worksheets.Count Worksheets(i).Visible = True Next i End Sub

  4. Setting up Excel for VBA • Developer button in ribbon • Saving files as .xlsm instead of .xlsx • Security

  5. Adding Developer to Excel Menu

  6. Security Warning

  7. VBA Editor

  8. Writing a macro from scratch • Coloring cells based on value • Unhidingworksheets • Pivot table macro

  9. Running Macros • Run from VBA window • Select worksheet before running • Adding a button to Excel Menu

  10. Troubleshoot VBA coding • F8 – Stepping through code • Add watch to view value of variables • Beeps • Start/End time stamps

  11. Code Snippets • Option Explicit • Find last row in table • Make copy of worksheet then run VBA on that copy • Speeding up macro by turning off screen update • Time Stamps • Beeping when done

  12. Tips for becoming proficient • Learn basic programming fundamentals • Read books to discover what Excel VBA functions are available • Google for answers • Write tests for quality assurance • Comment your code

  13. Resources • Books • VBA and Macros: Microsoft Excel 2010 (MrExcel Library) ISBN-13: 978-0789743145 • Online resources • http://www.mrexcel.com/ • http://ozgrid.com • http://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

More Related