Power BI


Test Category

Share


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 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

4 Lectures 12 week

Course Details