MS Excel is a well-known and probably the most widely used spreadsheet application and arguably a very powerful tool especially 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 are expected 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
- Anyone who desires to use Visual Basic for Applications (VBA) macros to create automated tasks/functions within MS Excel workbooks.
- 3 days, 3 hours each day
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 macroelements that can be reused in programming).
- Creating and using user-defined Macros for the 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, options 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 an 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