Excel Macro (VBA)


Excel Prince Archived Courses

Share

5000


What you will learn

Scope of Excel Macro

VBA language, Excel can do what you tell it. Thus enabling you to program Excel so that you can automate a boring report, format a big and ugly chart, clean-up some messy data. A macro is nothing but a set of instructions you give Excel in the VBA language and help automate common repetitive tasks. Macros are one of Excel's most powerful, yet underutilized feature. Using macros, you can save hours and boost productivity manifold.

What will it cover?

  • VBA Editor, Project Explorer, Code Pane, and Properties Window 
  • The Object Browser
  • Assign Macro
Recording Macro

  • How to record Macro
  • Shortcut keys
  • Limitations of Recoding
  • How to Optimize Recorded Macro

Recording Macro R1C1 Style

  • Convert R1C1 Style into general

What is Variable?

  • Type
  • Scope
  • Assign
  • Value transfer
  • Declaration (Implicit / Explicit)
  • Local, Static, Global

Object Model

  • Hierarchy
  • Property/Event/Method


Difference Between Subroutine and Function with example


Worksheet Concept

  • Different way to refer the Sheet (By Name, By Index)
  • Visibility
  • Renaming
  • Deletion (Single/Multiple)
  • Insertion (Single/Multiple)
  • Referring multiple sheets
  • Copy
  • Moving
  • Workbook and Windows Concept
  • Opening
  • Activation
  • State (Minimize/Maximize/Resize)
  • Active window
  • Not Operator


Refer to Cell, Range, Columns, Row

  • Consecutive Range
  • Split range
  • Column Selection (Single/Multiple)
  • Row Selection (Single/Multiple)
  • Difference between Cell and Range

With Statement

Current Region and Used Range concept

Special Cells

  • Comments
  • Formula
  • Visible
  • Constant
  • Last Row
  • Blank

Filter Data

  • Single Criteria
  • Multiple Criteria
  • AND operator
  • OR Operator
  • Like Operator
  • Top item
  • Bottom item

IF Statement

  • Simple if
  • If and Else
  • Sorting example
  • Msgbox
  • If else with Msgbox integration
  • Multiple Elseif
  • Nested if

String and Conversion Functions

  • Concatenation
  • Split
  • Ucase
  • Lcase
  • Instr
  • Validation
  • Len
  • Cint
  • Val

Offset Statement

  • Current region
  • Resize Range
  • Set

While Loop

  • With Conditions
  • Play with variable
  • Inputbox
  • Increment Decrement
  • Is Empty

For Loop

Nested For Loop

For Each loop

Nested For Each Loop

Function Procedure

  • Function through looping (for and For each)
  • Inbuilt Function (Multiple Examples)

Select Case Statement

  • Difference between if and select case
  • Select case with Function Procedure
  • Calling Procedure

Array

  • Option Base
  • Fixed Size
  • One Dimensional
  • Dynamic array
  • LBound, UBound
  • Redim

Error Handling Techniques

  • Error Type
  • On Error Resume Next
  • On Error Goto Label
  • On Error Resume Again
  • On Error Goto 0

Calling Function in a Procedure

  • Through File Handling Example
  • Through Function Procedure

Userform

  • Label
  • Combobox
  • Listbox
  • OptionButton
  • Checkbox
  • Image
  • Frame
  • ToggleButton
  • Scrollbar
  • SpinBar
  • Property Window
  • How to handle Event (On File opening; Click, DoubleClick; Initialization; Activate; before Entry; before Exit etc.)
  • Validate User input (Multiple Examples)
  • Form as per Application Size (Hiding Excel)
  • Disable Application Visibility

Additional features

  • Android Application to learn VBA - Macro through Videos
  • We will provide you dummy data for your practise; Assignments through which you can judge to yourself, Interview preparation question, platform where you can discuss you excel related queries/Job openings.
  • We will provide you all kind of study material related to this Function and supporting Videos along with the files.
  • You will also get video of the class which you attended; you can grab that in you Pan Drive or any storage device. So the content of the class remain with you for life time.
  • You can Attend your class by staying anywhere in the world through video conferencing.
  • Life time support / Assistance from our side
  • We will provide you dummy data for your practise; Assignments through which you can judge to yourself, Interview preparation question, platform where you can discuss you excel related queries/Job openings.
  • 100% assistance in job placement.


Overview

6 Lectures 10 week

Course Details