Excel VBA Tutorial

Facebooktwitterredditpinterestlinkedinmailby feather

What is VBA?

Introduction to Excel VBA: Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate operations in applications, such as Excel, Word, PowerPoint, etc. It is a tool that can read instructions and operate Excel very fast and accurately. This application comes along with Microsoft Office suite package, so no additional software and no additional cost is required to install VBA.

Excel VBA Tutorial for Beginners

VBA assists the developers to build customized applications and solutions to enhance the capabilities and functioning of those applications. It provides many inbuilt functions. VBA becomes very useful and an obvious solution where MS-Excel may not be enough to perform complex calculations.

History of Excel VBA

VBA was first launched with Ms- Excel 5.0 in 1993, which became an instant success among developers to automate reporting and solutions using Excel. With VBA4.0 released in 1996, VBA that was written in C++; became an ‘Object-Oriented Language’. Then in 1997, it was launched along with all of MS Office 97 products except for Outlook 97 that used VB Script to automate things. The latest version, which is VBA 7.1, included in Office 2013, Office 2016, Office 2019, and Office 365.

Excel VBA Index

Function Procedures and VBA Object Model

  • Introduction to Sub and Function Procedures
  • Excel VBA Object Model
  • Properties and Methods
  • Properties of Application Object
  • Working with Ranges of Objects
  • Working with Comments
  • Variables, Data Types and Constant

Finding / Selecting 

VBA Loops/ Conditional Statements

  • Working with Conditional Statements
  • Types of Conditional Statements
  • IF and nested IF conditions
  • IF with AND, OR & NOT
  • Select CASE statement (Alternative of IF)
  • Working with Loops
  • For Next Loop
  • For Each Loop
  • Normal Do- Loop
  • Do Until
  • Do While Loop
  • While Wend Loop
  • Working with Message & Input Box
  • With-End with Constructs
  • Controlling Program Flow- GoTo statements
  • Introduction to Arrays
  • Declaring Arrays
  • Declaring Multidimensional Arrays
  • Static Arrays vs. Dynamic Arrays
  • Dynamic Arrays

VBA Excel Formulas and Customized Functions

  • Introduction to VBA Functions and Worksheet Functions
  • Difference between R1C1 and A! style referencing
  • Worksheet Functions vs. Active.cell Formula
  • VBA Excel Functions
  • IF
  • UCase
  • LCase
  • StrReverse
  • Split
  • RemoveVowels
  • InStr
  • Date
  • DateSerial
  • MonthName
  • Working with WorksheetFunction
  • Working with Function Procedure
  • User-Defined Functions (UDF)
  • Working and Creating Customized Functions
  • Error Handling & Debugging Techniques

 User Form

  • Basics of UserForm
  • Creating, Displaying, Uploading UserForms
  • Properties
  • Form Controls & ActiveX Controls
  • List Box Control
  • Scroll Bar Control
  • Check Box
  • Exploring UserForm Controls
  • Customizing control Toolbox
  • Filling Data into controls
  • Adjusting UserForm controls
  • Cycle/Sequence to create UserForm
  • Validating UserForm
  • User Form Tricks
  • UserForm Events

Pivot Tables

  • Recording & Modifying macro to create Pivot Table
  • Codes for creating Pivot Table Report
  • Pivot Table Fields and Items
  • Updating Pivot Tables
  • Multiple Pivot Table reports using VBA

Charts

  • Embedded Chart vs Chart Sheets
  • Chart Object Model
  • Excel Chart using VBA
  • Changing Chart Type and Series Type
  • Modifying Chats, Spark-Line Chart in Excel
  • Adjusting Size and aligning ChartObjects

Sorting & Filtering

  • Recording Macro to Sort Table
  • Modifying the recorded macro
  • Performing Simple Sorting
  • Dynamic and multilevel Sorting

Prerequisite

Before learning the Excel VBA Tutorial, the reader must have some knowledge of Computer Fundamentals and well-versed with Microsoft Excel. Knowledge of programming will surely add an advantage to the reader. 

Audience

Our Excel VBA Tutorial is designed to help beginners and professionals. It would also be useful for enthusiasts in the fields of data analytics, managing data, calculating reports, schools, colleges, and Business Fields.

Problem We assure you that you will not treasure any issue with this VBA Tutorial. But if there is any mistake, please do post the question/error in the contact form.

Facebooktwitterredditpinterestlinkedinmailby feather