VBA Pro by Jon Acampora (16 CPE)

• Outline

  • Module 1: Intro to VBA Part 1
  • Module 2: Intro to VBA Part 2
  • Module 3: Setting up the VB Editor
  • Module 4: VBA Coding 101
  • Module 5: VBA Coding 201
  • Module 6: Designing Awesome Apps
  • Module 7: Userforms Part 1
  • Module 8: Userforms Part 2
  • Module 9: Customize the Excel Ribbon
  • Module 10: Creating Excel Add-ins
  • Module 11: Spreadsheet Based Apps

• Author

  Jon Acampora

• Subject

  VBA Pro

• Benefits

  Begin Now: begin training immediately
  Certificate: download and print your certificate instantly upon completion
  Pause and Resume: pause and resume training as needed
  Exam: unlimited exam attempts

• Software

  Featuring Microsoft Excel for Windows

• Format

  On-Demand

• Access Begins

  Immediately

• Duration

  Online enrollment provides access for one year.

• Certificate of Completion

  Download instantly once a passing score on the final exam is achieved. You may re-attempt the final exam as many times as necessary.

• Enrollment Period

  Your enrollment is valid for one year.

---

 

Module 1: INTRO TO MACROS & VBA PART 1 1 Create a Macro Enabled Workbook (2:58) 2 Write a Macro and Step through the Code (4:20) 3 Ways to Run a Macro (6:35) 4 The Excel Object Model (5:05) 5 Reference Workbooks in VBA (5:14) 6 Reference Worksheets in VBA (6:27) 7 Reference Ranges (Part 1) (5:10) 8 Reference Ranges (Part 2) (5:27) 9 Working with Object Properties (5:00) 10 Working with Object Methods (4:09) Module 2: INTRO TO MACROS & VBA PART 2 1 Declaring Variables and Data Types (5:24) 2 Set Variables in VBA (6:30) 3 Use Variables as Parameters (5:02) 4 Sub Procedure vs Function in VBA (5:09) 5 Use the Macro Recorder to Get Code (4:18) 6 Copy and Paste Cells - Part 1 (4:11) 7 Copy and Paste Cells - Part 2 (2:36) 8 Copy and Paste Cells - Part 3 (7:41) 9 If Statements in VBA - Part 1 (3:27) 10 If Statements in VBA - Part 2 (5:18) 11 MSsgBox Function Explained (9:47) Module 3: SETTING UP THE VB EDITOR 1 Developer Tab in Excel (4:40) 2 VB Editor (7:12) 3 Shortcuts for VB Editor Part 1 (6:39) 4 Shortcuts for VB Editor Part 2 (5:56) 5 Comment Blocks Explained (3:40) 6 Option Explicit (6:12) Module 4: VBA CODING 101 1 Intro to Coding in VBA (2:31) 2 List Sheets Macro (3:06) 3 Part 1 - Message Box (5:37) 4 Part 2 - If Then Statement (2:07) 5 Part 3 - For Loop (3:38) 6 Part 4 Compile Code (1:32) 7 Coding Tip - Using Syntax (1:18) 8 Variable Scope (3:28) 9 Procedure Scope (2:55) 10 With Statements (3:06) 11 User Defined Functions (5:07) Module 5: VBA CODING 201 1 Error Handling Part 1 (6:43) 2 Error Handling Part 2 (3:14) 3 Error Handling Part 3 (4:33) 4 For Next Loop for Cells and Ranges (3:45) 5 For Next Loop with Numbers (3:35) 6 For Next Loop for Collections (4:06) 7 Undo History (4:54) 8 Input Boxes (10:29) 9 VBA Arrays Part 1 (3:22) 10 VBA Arrays Part 2 (2:14) 11 VBA Arrays Part 3 (7:53) 12 VBA Arrays Part 4 (4:42) 13 VBA Arrays Part 5 (3:15) 14 How to Improve Performance (5:03) Module 6: DESIGNING AWESOME APPLICATIONS 1 Overview of Designing Applications (9:26) 2 Macro Enabled vs Add-in Files (7:37)

Module 7: USERFORMS PART 1 1 Intro to Userforms (1:41) 2 How to Insert a Userform (3:35) 3 Overview of the Listbox Control (10:23) 4 Working With Selected Items in the Listbox (6:47) 5 Remove Items From a Listbox (2:51) 6 How to Launch (Show) a Userform (4:26) 7 How to Make Code Reusable - Listbox Example (4:56) 8 Stepping Through Userform Code (2:20) 9 Userform Cancel Button (2:36) 10 How to Populate a Combobox (4:18) 11 Using Constants to Reduce Coding Errors (2:04) 12 Combobox Change Event and Select Statements (6:49) 13 Aligning Userform Controls and Buttons (1:38) 14 Programming the Command Buttons (4:12) 15 Programming the Userform (2:40) Module 8: USERFORMS PART 2 1 Google-like Search Box (2:15) 2 Search Box with the KeyUp Event (8:35) 3 Userform ShowModal Property (2:24) 4 Flow with the Tab Index (5:22) 5 Set Focus on a Control (1:30) 6 The Accelerator Property (2:09) 7 Adjusting Form Properties at Run Time (3:13) 8 Radio Buttons (3:06) 9 Checkboxes (3:54) 10 Cleaning Up Your Code (5:12) Module 9: CUSTOMIZING THE RIBBON 1 Customizing the Ribbon (2:38) 2 Ribbon XML (3:23) 3 Adding Buttons with the Ribbon Designer (10:33) 4 Additional Buttons and Controls (4:00) 5 Custom Images for the Ribbon Buttons (3:21) 6 The Custom UI Editor (2:03) 7 Customizing the Ribbon based on Version (4:14) 8 Customizing the Right-Click Menus (8:06) Module 10: CREATING ADD-INS 1 Creating an Add-in File (4:50) 2 Tips for Working with Add-in Files (6:31) 3 Saving Versions of Add-in Files (2:32) 4 The IsAddin Property (2:29) 5 How to Save User Settings (4:40) 6 Save User Settings to the Add-in File (5:25) 7 Save User Settings to an INI or Text File (4:33) 8 Save User Settings to the Windows Registry (6:41) 9 Different Ways to Install Add-in Files (3:12) 10 Distributing and Updating Add-in Files (2:44) 11 Windows APIs and 32-bit vs 64-bit Excel (3:18) 12 Password Protect VBA Project Files (4:18) Module 11: SPREADSHEET BASED APPLICATIONS 1 Working with Tables in VBA - List Objects (7:21) 2 Looping Through Tables - List Objects (6:20) 3 How to Create Files from a List or Table (8:20) 4 Improving Performance (3:19) 5 Replace or Overwrite Files on Creation (4:08) 6 Add a Timer and File Count Message (3:13) 7 Adding Data to Files (5:25) 8 Flag or Enable Files in the List (2:04) 9 Run Actions on Existing Files (4:19) 10 Add a Timestamp to the File List (4:10) 11 The Actions Table (7:11) 12 Add Hyperlinks to the File List (2:41)

Materials

The course includes the following course materials:

• Online lecture videos
• Sample Excel files
• Glossary
• Index
• Download videos to your computer

Comments from participants

• “I've learned so much and applied it to my day to day responsibilities. I was recently promoted to Finance Manager and I believe your class and my application of this things you taught within that class influenced that decision to promote me. I thank you! - William A, Finance Manager”


• “I have really enjoyed the course you put together! The progression of the course is very logical and the challenges are always a lot of fun! - David H, Senior Accountant”


• “The course is amazing. I jumped into the deep end of the pool, but your lessons are clear and concise. - Barbara H, Product Quality Coordinator”


• “When I started this course I was only recording macros and avoiding variables like the plague. Now, I am writing my own VBA code and using variables to make it more efficient. - Grant B, Revenue Analyst”


• “Overall, a professionally developed course, excellent interface and an easy-to-listen-to instructor. As you can tell, I highly recommend the course. - George R, Systems Analyst”


• “I am really enjoying your course. I haven't learned this much VBA since I started over a year ago. VBA is Awesome!!! - Peter C, Quality Control Manager”

Objectives

• Recognize the VBA keyword used to start a macro
• Identify the best place to declare a variable
• State with VBA function can be used to prompt the user to enter text

Program Level

Basic

Prerequisites

None

Advance Preparation Needed

Ensure you have access to Excel for Windows, preferably versions 2010 or later. Excel for Mac is not supported.

Delivery Method

QAS Self-study

Recommended CPE Credits

16

Field of Study

Computer Software & Applications

Expiration

Program enrollment is valid for one year and participant must complete the final exam within this time period.

Registration

To register, add this course to your cart by clicking the Buy Now button and then complete the checkout process.

Refund Policy

Full 30-day refund; refund for any course cancellations or registration into upcoming session if preferred.

Complaint Resolution

For information regarding adminstrative policies such as complaint resolution, please contact Jeff Lenning CPA CITP at 949-200-7688 or via email info@excel-university.com

Official Registry Statement

Excel University, Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.