Brightmoon Consulting & Training

  • Home
    • Gallery
    • Testimonial
  • Contact Us
  • Home
    • Gallery
    • Testimonial
  • Contact Us
Microsoft Excel VBA

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)


    I'm interested. Let's get connected.

Submit
Powered by Create your own unique website with customizable templates.