This course on Excel 2019 VBA Advanced Tutorial aims to teach users how to automate repetitive processes and build tools in Excel using Visual Basic for Applications (VBA). By the end of the course, learners will be able to create macros using essential VBA concepts such as Loop, If Then, and other complex functions. The course covers topics like creating forms and controls, coding form controls, working with pivot tables in VBA, and finalizing forms. The intended audience for this course includes Business Analysts, Data Scientists, and individuals interested in expanding their Excel skills to include automation and customization using VBA.
Overview
Syllabus
Start
Introduction
Introduction to Forms and Controls
UserForm Object-Controls-Properties-Events-Methods
Reviewing Types of Modules and Inserting UserForm Objects
Changing Form Properties
Creating a Label Control and Adjusting its Properties
Copying a Label Control to Create a New One
Adding Two Text Box Controls to Forms
Adding Two Command Button Controls to Forms
Adding a Combo Box Control and Label
Adding Two Frame Controls with Three Option Buttons in Each
Adjusting Option Button Properties
Aligning and Grouping Form Controls
Testing and Adjusting Form Tab Order
Coding the Combo Box to Display Months
Coding Other Form Controls Using Text Files
Reviewing Coding in Text Files
Displaying the Form Programmatically
Forms and Controls Recap
Introduction to Pivot Tables in VBA
Introduction to Creating Pivot Tables
Creating Pivot Tables by Recording a Macro
Creating Pivot Tables
Testing CreatePivot Sub-Procedure
Creating Sub-Procedures to Set Pivot Table Fields
Finalizing Forms
Creating Sub-Procedures to Code Options in the Period Frame on Forms
Reviewing GetLastDate and GetFirstDate Code Blocks from Text Files
Coding Options in the Sales Frame on the Form
Reviewing Text File Procedures - ConsolidateData-GrabCells-FinishReport
Completing Code Modifications for Form Objects
Testing Completed Forms
Adding Icons to the QAT to Run the Form
Pivot Tables Recap
Conclusion
Taught by
Learnit Training
