Advanced Excel

Microsoft excel vba macro mastery course 2018

Everything you want to know about power programming with vba

excel-macro

What is VBA?

VBA stands for Visual Basic for Applications. It’s a programing language used by excel to record a macro.

What is Macro and its advantages?

Macro is a Microsoft tool that records user action and stores it within the Excel environment.

Macros are mostly used to eliminate repetitive task.

For example:

Let’s say you receive around 10 worksheets on a daily base from different verticals.

You have to consolidate all these worksheets into one master sheet with proper formatting.

Doing this manually that too on daily base could be time consuming and boring.

Here’s where we can use a macro to record the steps needed to perform the task.

The next time you just need to run the recorded macro to get this task done automatically.

Do I need to know VBA Programing language to create a Macro?

No, you don’t need to know VBA language to create a macro. Excel macro recorder writes the code for you.

It’s a boon for the people who don’t want to write a single line of code and still want to create a macro that could solve their purpose.

Is there any limitation to the macro recorder?

Yes, there is a certain limitation to a macro recorder.

For Example:

Macro Recorder cannot make any changes to already recorded macro. Though it may be a very simple change.

Macro Recorder cannot record a macro based on conditions. Even there will be a time where a macro recorder will not be able to record some steps.

I guarantee you that a moment will come where you may feel a need for manual code interruption and if you don’t have a better understanding of VBA code you will be stuck.

You will be forced to look for someone who can do the same for you.

excel-vba

Do we need to have a programming background to learn VBA?

No, you don’t need to have any programming knowledge to learn VBA code.

VBA code is easy to learn and implement.

With little efforts, you can master VBA Programming.

Which means you will not be just limited to macro recording…,

you will have even more control on your code and can really extend and customize the macro to your imagination.

You can extend the excel functionality by creating your own custom functions and Add-ins.

In our Microsoft Excel VBA Macro Mastery Course, you will learn how to write your own macro codes from scratch step by step using VBA text editor.

Will understand what modules are and why we should use it to write macros?

How to debug or troubleshoot the code?

What are Object, Properties, and Methods?

Furthermore, we will learn how to write a condition-based code using IF, IF Then, and CASE functions

Will understand what Variables are, how to declare a Variables and its scope.

How to use a loop function to keep our Micro code well organized and dynamic?

We will also look at different type Events and its procedures.

How to create forms using User forms?

Finally, we will learn how we can share our code with others, at the same time keeping it safe and secure.

Who should take this course?

This course is for anyone who has a sound knowledge of advanced excel and wishes to go beyond Macro recording, by writing their own VBA code.

You may refer to the complete course curriculum below.

Shailesh

Fill out the details below and be a part of our VBA MACRO Mastery Course

Course Curriculum


Introduction
  • What are Macros
  • The Advantage of VBA Macros
  • A walk-through Developer Tab
  • Record your first Macro
  • Absolute and Relative References
  • View and Edit VBA Macro codes

Explore Visual Basic Editor
  • Why Modules
  • Export and Import Modules
  • Debug, Edit, and Standard toolbar
  • Toggle Breakpoint
  • Indent
  • Comments

Comparison Operators
  • Equal To
  • Not Equal To
  • Less Than
  • Greater Than
  • Less Than OR Equal To
  • Greater Than OR Equal To

Arithmetic Operators
  • Addition
  • Subtraction
  • Multiplication
  • Division
  • Exponentiation
  • Modulus

What Is
  • Object
  • Properties
  • Methods

Range Object
  • What is Range object
  • Write your own macro using a Range object
  • Using Name Range object
Cell Object
  • What is Cell object
  • Difference between Cell and Range object
  • Write macro using Cell object
  • Using Range and Cell object together

Explore other useful objects
  • Worksheet object
  • Font object
  • Command button object
  • Objects Browser

Properties
  • VALUE Property
  • TEXT Property
  • ROW and COLUMN Properties
  • SELECT Property
  • COUNT Property
  • FORMULA Property
  • NUMBER Format Property
  • BOLD, UNDERLINE, ITALIC Properties
  • OFFSET Property

Short Hand and Other logic building
  • Range and cell property shorthand
  • With and End With
  • Calling procedure
  • Go To
  • Offset
  • Find the last Row
  • Find the last Column
  • IF Then one-liner

VBA IF function
  • Intro to IF function
  • How to use IF function
  • IF Then
  • IF Then Else
  • IF Then ElseIF (Nested IF)

CASE an Alternative to IF
  • Intro to CASE
  • SELECT CASE
  • Case IS
  • Case Else
  • End Select

Variables
  • What are Variables
  • Declaring a variable
  • Declaring multiple Variables in one line
  • Declaring a variable data type
  • The Scope of a Variable
  • Constant Variable
  • Passing Variable

Loop
  • What is Loop
  • Advantage of Loop
  • Type of Loop
  • For Loop
  • For Loop Going Backwards
  • FOR EACH Loop
  • DO LOOPS
  • DO Until
  • DO While
  • EXIT DO
Custom Functions
  • Intro to Custom Functions
  • How to create your own custom function
  • How to leverage excel inbuilt function to create your own custom function
  • How to add additional description to our custom function

AdIns
  • What are Adins
  • Advantage of Adins
  • How to Create your own AdIns
  • Protect and share your Adins

Events
  • What are Events
  • Types of Events
  • Worksheet Event
  • DoubleClick Event
  • Right Click Event
  • Calculate Event
  • Disable Events
  • Workbook Events
  • Workbook Open Event
  • Select Event
  • Before Save
  • Before Close Event
  • New Sheet Event
  • DoubleClick Event

Explore MSGBOX
  • vbYes | vbYesNo
  • vbOKOnly | vbOKCancel
  • vbNoCancel

User forms
  • What are User forms
  • The Advantage of User form
  • Event-based User form
  • User form Controls
  • Create Data Entry Form