Do More with VBA in Excel (Advanced)

Duration: 3 Days
Contact Hours: 21

---

Course Mode

• Lectures
  
• Hands-on
• Mini-project

---

Target Audience

• Senior Office Personnel
• Operation Manager
• Data Analyst
• Prior programming knowledge is an added advantage (not compulsory)

---

Benefits

• Cover the fundamental of VBA programming to advanced level.
• Delegates are shown the strategy to uncover the solution, as they might forget the exact details later.
  
• At the end of this, each delegate is required to put what they learned into action - by showcase a mini-project of their choice.

---

Course Overview

Introducing Visual Basic for Application (VBA)

• Introducing object-oriented programming
  
• Examining the Excel object model
  
• Working in the Visual Basic editor
  
• Setting VBA project properties
  
• Creating, exporting, and deleting code modules
  
• Creating a subroutine or function
  
• Controlling procedure scope and visibility
  
• Adding comments to the code
  
• Running a VBA routine
  
• Adding code to a recorded macro
  

Defining Variables, Constants, and Calculations

• Introducing Excel VBA data types
  
• Declaring variables and requiring declaration before use
  
• Managing variable scope
  
• Defining static variables and constants
  
• Creating a calculation using mathematical operators
  
• Defining arrays
  
• Defining and using object variables
  
• Streamlining code references using With...End With statements
  

Adding Logic to Your VBA Code

• Repeating a task using a For...Next loop
  
• Stepping through all items for a collection using a For...Next loop
  
• Repeating a task with a Do loop
  
• Performing a task when conditions are met using If...Then
  
• Selecting which action to take using a Case statement
  

Debugging Your VBA Code

• Managing errors using On Error statements
  
• Stepping through a subroutine or function
  
• Setting breakpoints in the code
  
• Verifying output using the Immediate window
  
• Watching a value in a routine
  

Managing Workbook Elements and Data in VBA

• Writing a value to a cell
  
• Cutting, copying, and pasting cell data
  
• Finding values in cells
  
• Referring to cells using the Offset function
  
• Concatenating text strings
  
• Returning part of a string
  
• Managing worksheets with VBA
  
• Managing workbooks with VBA
  

Adding Advanced Elements to Your Workbook

• Turning off screen updating within a macro
  
• Using worksheet functions in a macro
  
• Acquiring values using an input box or message box
  
• Calling a subroutine from another subroutine
  

Using Excel Events in Your VBA Code

• Running a procedure when you open, close, or save a workbook
  
• Running a procedure when a cell range changes
  
• Triggering a procedure using a specific key sequence
  

Putting It All Together

• Applying techniques in a Capstone project (sample project)
  

---