This course is designed for users who have leveraged the rich set of features in the standard Excel user interface BUT are looking for an easier and faster way to perform mundane, repetitive data manipulation, analysis, and reporting processes. By using Excel VBA for automation, you can significantly cut down the time you spend repeating these steps and improve your overall productivity. Further, you can use VBA to build new capabilities into Excel for enhanced data visualization and building professional dashboards and interfaces.
The ideal candidate for this module needs to be thorough with concepts covered in our Advanced Microsoft Excel course.
Recording, storing a macro, using VB Editor, understanding data types, code presentation, working with Message and Input Boxes, difference between a sub routine and a function.
Declaring variables and constants, scope of variable/constant, declaring, assigning and using an array, Illustration: using variables, constants and arrays.
Properties and methods, Object Hierarchy & Collections.
Applications of Excel VBA Control Statements, exiting control structures.
Defining a range/cell, the Range and the Cell property, the offset property of a range, wows and columns, reading values from one cell to another or to a variable.
Referencing workbooks and worksheets, get the path and full name, Importing sheets/data.
Understanding event handlers, workbook, worksheet and chart events.
Creating customized dialog boxes, launch and close a user form, assigning codes to user form controls.
Common errors and how to troubleshoot, on Error GoTo, On Error Resume Next.