Excel VBA Tutorial

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.

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.

Advantages of VBA

One might wonder the use of VBA in Excel as MS-Excel itself provides plenty of inbuilt functions. MS-Excel provides only limited features and comes with only basic inbuilt functions, which might not be enough to perform complex calculations. Hence, VBA becomes a prominent solution. VBA comes with many advantages which are listed below:

Automate Repetitive and Recurring Task – VBA is often used for automating Repetitive and Recurring Tasks. It can be used across all other Microsoft Office applications. VBA is used for processing various data efficiently in Excel, and even copy and paste work becomes easy.

User-Defined Functions (UDFs)– It enables user building User Defined Functions (UDFs), automating processes, accessing Windows API, and controlling Excel in countless way that users cannot do it manually.

Enables Macro – You can repeat the set of instructions multiple times, run a macro automatically, customize the workbook’s look, and can also control other office applications using Excel.

Reduces the usage of formulas – When it comes to presenting data to non-professionals, you should consider hiding some of the long formulas as the presentation may be difficult for them to understand. VBA allows you to add user-friendly variables and customized functions that other users can also modify to a certain degree. Overall, there is quick access to information from other users.

Protects and Hides Worksheets– VBA enables the features of protecting the excel workbook from unauthorized users and hence, prevents the manipulation of data. You can even hide any unwanted worksheet from the user.

Accessibility to other users – Accessibility to information is an important feature for distributing information. With VBA, other users do not have to install anything provided you write a script for everyone in the department. If things have been set up correctly, any user who doesn’t have proper knowledge about Excel can also perform the task by running the macro.

In short, anything you can think of doing manually, you can do with VBA, but faster and with a minimized risk of human error. You can also do those things with VBA, which Excel does not let you do manually.

Applications for VBA

VBA is likely running within applications that you use every day, whether you’re aware of it or not. Some businesses in this sector need former knowledge of VBA, and some do not. The applications where VBA is most likely to be used are given below:

1. Computer Professionals – Computer Programmers, use macros for replicating large pieces of code, merging existing program functions, and designing specific languages. It is used to solve in more complex tasks is often used for automating Repetitive and Recurring jobs and create applications. It helps programmers to build customized applications to enhance the capabilities of those applications.
2. Companies and Organizations– Many firms have implemented VBA within their own applications, both proprietary and commercial, including AutoCAD, ArcGIS, CATIA, Corel, raw, and SolidWorks.
3. Financial Markets – Finance management is all about manipulating large amounts of data and information. VBA is prevalent in the financial services sector. If you work in finance, VBA is running within almost all applications that you use every day, whether you’re aware of it or not.
4. Business Presentations– Automate interactive charts for your daily used business presentations.
5. Sales User-Form– language provides a user interface in the form of UserForms, which can host ActiveX controls for added functionality.
6. Banks- In Banks, it is very hard to calculate the monthly repayment of a loan using Excel’s built in formulas and features. Hence, under those circumstances, it is easy to program a VBA for such a calculation.

Excel VBA Index

• What is VBA?
• Difference between VBA and Macros
• Introduction to Visual Basic Editor Window
• The VBE Environment
• The Macro Recorder
• Executing/ Modifying Recorded Macros
• Saving Macro Workbook

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 

• Finding last Row or Column
• Selecting Rows/Columns Normally
• Selecting Rows/Columns Dynamically

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.