• logo
  • 9861600203
  • 5555545
  • info@mentorit.com.np
header-logo.png

Overview

The purpose of the course is to gain an understanding of how Excel VBA can save you time through automation of repetitive Excel tasks. Day one is a core skills day, delivered with tutor-led mini exercises, designed to build up programming confidence. Here we spend a good amount of time on language structure and syntax. Day two applies the concepts to a real-life business problem, where delegates are given freedom to design a solution with tutor guidance.

Key Learning Outcomes:

  • Record, write, edit, test and run VBA macros with confidence
  • Automate repetitive Excel business tasks and streamline workflow 
  • Write and apply User Defined Functions (UDFs)
  • Implement event procedures

Course Content

  • Recording Macros
  • Recording and Running Macros
  • Recording a Macro
  • Running a Macro
  • Adding a Macro/Procedure to the Quick Access Toolbar
  • The Personal Macro Workbook
  • Saving the Personal Macros
  • Editing a Personal Macro
  • Assigning Personal Macros to the Toolbar
  • Introducing Visual Basic for Applications
  • Editing Macros in Visual Basic Editor
  • Understanding the Development Environment
  • Protect/Lock Excel VBA Code
  • Using Help
  • Understanding and Creating Modules
  • Defining Procedures
  • Naming Procedures
  • Creating a Sub-Procedure
  • Working Using the Code Editor
  • Defining Control-Of-Flow structures
  • Using Boolean Expressions
  • Using the IfEnd If Decision Structures
  • Using the Select CaseEnd Select Structure
  • Using the DoLoop Structure
  • Using The ForNext Structure
  • Using the For EachNext Structure
  • Guidelines for Use Of Control-Of-Flow Structures
  • Understanding Errors
  • Using Debugging Tools
  • Identifying the Value of Expressions
  • Setting Breakpoints
  • How to Step Through Code
  • Working with Break Mode during Run Mode
  • Using the Immediate Window
  • Defining Objects
  • Examining the Excel Object Hierarchy
  • Defining Collections
  • Referencing Objects in a Collection
  • Using the Object Browser
  • Working with Properties
  • The With Statement
  • Working With Methods
  • Event Procedures
  • Defining Expressions and Statements
  • Expressions
  • Statements
  • How to Declare Variables
  • Naming Variables
  • Assigning Values To Variables
  • Declaring Variables Explicitly
  • Determining Data Types
  • Programming with Variable Scope
  • Harnessing Intrinsic Functions
  • Defining Constants and Using Intrinsic Constants
  • Using Intrinsic Constants
  • Creating a Function Procedure
  • Calling a UDF
  • Using a function within an Excel Workbook
  • Adding Message Boxes
  • Return Values
  • Notes Error! Bookmark not defined
  • Using Input Boxes
  • How to Declare and Use Object Variables
  • Defining VBA’s Error Trapping Options
  • Capturing Errors with the On Error Statement
  • Determining the Err Object
  • Coding an Error-Handling Routine
  • Using Inline Error Handling
  • Defining UserForms
  • Utilising the Toolbox
  • Using UserForm Properties, Events And Methods
  • Properties
  • Events
  • Understanding Controls
  • Naming Conventions
  • Setting Control Properties in the Properties Window
  • Using the Label Control
  • Using the Text Box Control
  • Using the Command Button Control
  • Using the Combo Box Control
  • Using the Frame Control
  • Using Option Button Controls
  • What is a Range?
  • Range Property of the Application
  • Cells Property
  • The SpecialCells Method
  • Naming Ranges
  • Working with Collections
  • Creating charts from worksheet data
  • Key Properties and methods of the chart object
  • Creating Charts from Arrays
  • Understanding PivotTables
  • Creating A PivotTable
  • Procedure
  • Using the PivotTable Wizard Method
  • Using PivotFields
  • What is an Array
  • Array Sizes
  • One Dimensional Arrays
  • Arrays with Multiple Dimensions
  • A word about index numbers
  • Ubound and Lbound
  • Saving arrays in names
  • Procedure Arguments
  • Passing Arguments
  • Optional Arguments
  • Default Values
  • Passing arguments by value and reference
  • VBA Password Protection
  • About Macro Security
  • Macro security settings and their effects
  • Change Macro Security Settings

About the instructor

DIPU MAHARJAN

BI Solution Architect

Hi I am Dipu Maharjan. I am working as BI Solution Architect. I have experience more than 10 years as Data Analyst, Database Programmer, Developer and Trainer.

No reviews for this course


Write a Message

Security Check
2 + 2 =

    Course Features

  • Course Cost18,000
  • Duration50 hrs