MS Excel is well-known and probably the most widely used spreadsheet application and arguably a very powerful tool specially when VBA is unleashed.
VBA or Visual Basic for Applications enables building User-Defined Functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word’s WordBASIC. It can be used to control many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. – Wikipedia
At the end of the training, participants shall be able to:
- create automated tasks and processes within Excel workbooks, for the purpose of simplifying repeated manual tasks, and in so doing enable the Excel user to focus on the essence and insight of the data that they are processing.
Who Should Attend
Anyone who desires to use Visual Basic for Applications (VBA) macros to create automated tasks/functions within MS Excel workbooks.
3 days, 4 hours each day
Program Outline/Course Overview
The following topics would be discussed and practiced during the actual webinar sessions. All sessions will be provided with guided instructions on how to create the non-recorded macros. For each session, several macro exercises shall be allocated, to enable the participant to hone their skills in VBA programming.1. Overview of Lean Six Sigma
Day 1 Topics
- Changing Perspective – People-loving
- Reasons for automating your Excel tasks.
- Understanding VBA Concept for Excel
- Difference between Recorded Macro, and Created Macro (built with logical statements and formulas)
- Setting up security level for workbooks with macros
- Creating user-defined functions (UDFs).
- Understanding and switching from the Macro Environment to the Excel workbook environment (and vice versa).
- Understanding the user interface in the Macro Environment
- Understanding and creating modules
- Understanding the Project and Properties tab in the VBA environment.
- An overview of object-oriented referencing techniques (Properties, Events and Methods) in Excel Macro statements
- Creating your first macro using built logical statements and formulas
- Understanding and learning from recorded macros (to identify macro elements which can be reused in programming).
- Creating and using user-defined Macros for non-existent feature of MS Excel.
Day 2 Topics:
- Creating an interactive form (User Form)
- Applying data validation to your user forms
- Embedding various “controls” to your forms (increments, fields, drop down elements, textbox, combo box, checkbox, option button, frame, command button, multi-page, scroll bar, spin button, and image)
- Running or Executing Macros
- Stopping A macro program execution
- Debugging a macro program
- Setting a breakpoint
- Using Trace Window
- Using Trace Window for debugging
- Performing Error Checking.
- Understanding Error Handling.
- Proper use of VBA’s Error trapping options.
- Trap any errors using on error statement.
- Detecting and fixing bugs.
- Specifying breakpoints
Day 3 Topics:
- Using Conditional Statements in Excel VBA (IF statements)
- Inserting and performing conditional loops in Excel VBA (looping functions)
- Using the For Next statement
- Using the While [doing] statement
- Understanding the Do until.
- Attaching Macros to buttons in the Excel Environment
- Calling sub-routines
- Understanding the Data Explorer
- Understanding and using the Object viewer.
- Creating responsive message boxes with text
- Creating responsive message boxes with values
For a complete course outline, email firstname.lastname@example.org.