🏡

Excel VBA for Absolute Beginners

Course Outline Outline
Reading Material
Introduction Intro.pptx
DAYTopicsSharing
1
  • What is VBA?
  • Why need to do programming for MS-Excel
  • What can we do with Excel VBA? (Few interesting examples)
  • Using the development Integrated Development Environment
  • The project explorer - Introduction to the VBA project concept and project components
  • The property Window
  • The IDE main menu
  • Switching between Excel normal interface and IDE interface
  • Help system
  • Why we need automation in MS-Excel?
  • What is macro?
  • Recording macros
  • How to trigger macros from Excel normal interface?
  • How to trigger macros from VBA IDE?
  • The immediate window
  • What is instruction?
  • Evaluation instructions
  • Command Instructions
  • Dealing with Excel VBA objects and their properties
  • Single cell reference methods
  • Range reference methods
  • Inter-worksheets reference
  • Inter-workbook reference
  • The Procedure concept
  • Procedures (Subroutines)
  • Procedures (Functions)
  • Procedures (Event Handlers)
  • Pre-mature terminations with Exit keyword
  • Grouping instructions using With statement
2
  • Why module is needed?
  • Code Module
  • User Form in brief
  • Class module in brief
  • Worksheet module in brief
  • Workbook module in brief
  • Procedures scoping
  • Dealing with ambiguities
  • Type of errors
  • Dealing with compilation errors
  • Dealing with runtime errors
  • Dealing with logical errors
  • The debugger and debugging process
  • Why variables are needed?
  • Basic Data Types
  • Variable declaration and shorthand
  • Variable scoping and life cycle
  • Variable initialization
  • Option Explicit directive
  • MsgBox function
  • InputBox function
  • Number functions
  • String functions
  • Date/Time functions
  • Format function
  • RGB function
  • Comments
  • VBA Constants
  • Excel Constants
  • Defining constants
  • Selection keyword
  • Application object
  • ActiveSheet object
  • Sheets collection
  • Workbooks collection
3
  • What is parameter?
  • Optional parameters and techniques to handle default values
  • Arbitrary argument support using ParamArray declaration
  • Parameter passing mechanisms: ByVal vs.ByRef
  • Named arguments
  • What is operator?
  • Arithmetic operators
  • Comparison Operators
  • Logical Operators
  • Special Operators
  • Unconditional Branching with GoTo statement
  • Unconditional Branching with GoSub statement
  • If..Then..Else Statement
  • Select Case Statement
  • Unconditional Loop with GoTo statement
  • Using For Loop
  • Using For Each statement
  • Pre-test looping
  • Post-Test looping
  • Pre-mature termination using Exit keyword
 Case Studies:
  1. Data Entry.zip
  2. Automation.zip
  3. Data Consolidation.zip
  4. Calender.zip

Recommanded Books
TitleExcel VBA Programming: Task Optimization and Daily Work Automation
ISBN979-8-854-42615-2
AuthorKiet Huynh
Year2023
Publisher Independently published
TitleExcel VBA Programming For Dummies: 6th Edition
ISBN978-1-119-84307-8
AuthorDick Kusleika
Year2022
Publisher For Dummies
TitlePractical VBA For Practically Anyone: For When You Know What You Want To Do But Don't Know How To Do It
ISBNB09672RDM7
AuthorMcSweeney, Gerry
Year2021
Publisher
TitleMicrosoft Excel VBA and Macros (Office 2021 and Microsoft 365)
ISBN978-0-137-52152-4
AuthorBill Jelen; Tracy Syrstad
Year2022
Publisher Microsoft Press
TitleVBA Automation for Excel 2019 Cookbook-Solutions to automate routine tasks and increase productivity with Excel
ISBN978-1-789-61003-1
AuthorVan Niekerk, Mike
Year2020
Publisher Published by Packt Publishing Ltd.
TitleMicrosoft Excel 2019 VBA and Macros
ISBN978-1-509-30611-4
AuthorBill Jelen, Tracy Syrstad
Year2019
Publisher Microsoft Press
TitleMastering VBA 2019-For Microsoft Office 365, 2019 Edition
ISBN978-1-119-57933-5
AuthorRichard Mansfield
Year2019
Publisher Sybex Inc
TitleExcel 2019 Power Programming with VBA
ISBN978-1-119-51492-3
AuthorMichael Alexander, Richard Kusleika
Year2019
Publisher Wiley
TitleExcel VBA Programming for Dummies
ISBN978-1-119-51817-4
AuthorJohn Walkenbach
Year2018
Publisher Wiley
TitleProgramming Excel with VBA-A Practical Real-World Guide
ISBN978-1-484-22204-1
AuthorFlavio Morgado
Year2016
Publisher Apress
TitleExcel VBA 24-Hour Trainer
ISBN978-1-118-99137-4
AuthorTom Urtis
Year2015
Publisher Wrox