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