Excel macros and VBA are utilized to automate repetitive tasks, customize Excel's functions, and streamline data processing. With VBA, custom functions and procedures can be created, making complex calculations and data manipulation possible, extending Excel's capabilities, and making it a more powerful and efficient tool for data analysis and reporting.

Program Level: Intermediate

Prerequisites: The student must be a current Excel user and have taken at least Excel I and II or have equivalent experience.

Contents:

• Understanding Macros 
  • What is a Macro?  
    • What is VBA  
      • Displaying the Developer Ribbon Tab  
      • Macro Security 
      • Recording a Macro  
    • Storing Macros  
      • The Personal Macro Workbook  
      • Using Macro-Enabled Workbooks  
    • Running Macros  
  • Creating a Macro Icon for the Toolbar   
• Creating Macros 3 ways 
  • Absolute Macros 
    • Create a macro for a report or repetitive data set 
  • Relative reference macros 
    • Create a macro that will work on any column 
  • Relative reference macro starting from a selection  
    • Create a macro that will format any selection 
• Create Functional tools 
  • Create a macro to clean and trim a column 
  • Create a macro to add leading zeros to a column 
• Introduction to the VBA interface 
  • Visual Basic button / Developer Ribbon 
  • Setting up and understanding the VBA Editor 
  • Using the Project Window / Hiding the Properties Window 
  • Opening / closing and saving your work 
• Using pre-built code to do what excel does not 
  • Saving a workbook before closing 
  • Create a backup copy with today’s date in the title 
  • Create a new workbook for each worksheet 
  • Create a Table of Contents for you worksheets in a workbook 
  • Moving data from multiple sheets to a single sheet 
  • Text to columns on all columns to apply number format 
  • Create a new sheet for each item in an Autofilter 
  • Copy all Charts to PowerPoint 
• Visual Basic Forms  
  • What is a VB Form?  
  • Building a Basic Form 
  • Working with Modules 
  • Renaming Modules  
  • Changing Form Properties  
  • Form Controls  
    • Text Boxes  
    • Labels  
    • Combo Boxes  
    • Check Boxes  
  • Displaying the forms 
    • Make your form appear 
    • Closing the form 
    • Adding VB Code to your Form  

Note: This course will be taught using the windows operating system environment

• Contact:
  Catapult Learning Team