VBA Macros - Innozant MIS & Data Analytics & Data Science

Administrator Expert

Description

Project Based Course on Excel VBA (Visual Basic for Applications) and Excel Macros. Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports.

 What you will learn:

  • Learn how to automate daily routine tasks within Excel
  • Learn best practices when creating macros and streamline your VBA code
  • Determine when it’s best to use the Macro Recorder versus VBA
  • Learn how to write custom VBA Macros to create more robust automation
  • Learn VBA programming concepts to create dynamic, re-usable code
  • Complete real world Macro/VBA projects from beginning to end

Download Brochure

Topics for this course

107 Lessons44h 59m 59s

Training Duration Regular: 2.5 Months

Introduction to VBA & Macro?

Introduction to VBA & Macro
  • What is VBA and Why learning VBA is so important
  • What can you do with VBA
  • What is Macro & VBA

Creating a Macro?

Creating a Macro
  • Swap Values
  • Run Code from a Module
  • Macro Recorder
  • Use Relative References
  • Formula R1C1
  • Add a Macro to the Toolbar
  • Macro Security
  • Protect Macro

MsgBox?

MsgBox
  • MsgBox Functions
  • Input Box Functions

Workbook and Worksheet object?

Workbook and Worksheet object
  • Path and Full Name
  • Close and Open
  • Loop through Books and Sheets
  • Sales Calculator
  • Files in a Directory
  • Import Sheets
  • Programming Charts

Range Object?

Range Object
  • Current Region
  • Dynamic Range
  • Resize
  • Entire Rows and Columns
  • Offset
  • From Active Cell to Last Entry
  • Union and Intersect
  • Test a Selection
  • Possible Football Matches
  • Font
  • Background Colors
  • Areas Collection
  • Compare Ranges.

Variables?

Variables
  • Option Explicit
  • Variable Scope
  • Life of Variables
  • VBA (VISUAL BASIC FOR APPLICATION) & MACRO

IF THEN STATEMENT?

IF THEN STATEMENT
  • Logical Operators
  • Select Case
  • Tax Rates
  • Mod Operator
  • Prime Number Checker
  • Find Second Highest Value
  • Sum by Color
  • Delete Blank Cells.

LOOP?

LOOP
  • Loop through Defined Range
  • Loop through Entire Column
  • Do Until Loop
  • Step Keyword
  • Create a Pattern
  • Sort Numbers
  • Randomly Sort Data
  • Remove Duplicates
  • Complex Calculations
  • Knapsack Problem

MACRO ERRORS?

MACRO ERRORS
  • Debugging
  • Error Handling
  • Err Object
  • Interrupt a Macro
  • Macro Comments

STRING MANIPULATION?

STRING MANIPULATION
  • Separate Strings
  • Reverse Strings
  • Convert to Proper Case
  • Count Words

DATE AND TIME?

DATE AND TIME
  • Compare Dates and Times
  • DateDif Function
  • Weekdays
  • Delay a Macro
  • Year Occurrences
  • Tasks on Schedule
  • Sort Birthdays

EVENTS?

EVENTS
  • Before DoubleClick Event
  • Highlight Active Cell
  • Create a Footer Before Printing
  • Bills and Coins
  • Rolling Average Table

ARRAY?

ARRAY
  • Dynamic Array
  • Array Function
  • Month Names
  • Size of an Array

FUNCTION AND SUB?

FUNCTION AND SUB
  • User Defined Function
  • Custom Average Function
  • Volatile Functions
  • ByRef and ByVal

APPLICATION OBJECT?

APPLICATION OBJECT
  • Status Bar
  • Read Data from Text File
  • Write Data to Text File

ACTIVEX CONTROLS?

ACTIVEX CONTROLS
  • Text Box
  • List Box
  • Combo Box
  • Check Box
  • Option Buttons
  • Spin Button
  • Loan Calculator

USER FORM?

USER FORM
  • User form and Ranges
  • Currency Converter
  • Progress Indicator
  • Multiple List Box
  • Selections
  • Multicolumn Combo Box
  • Dependent Combo Boxes
  • Loop through Controls
  • Controls Collection
  • User form with Multiple Pages
  • Interactive User form
5,999.00
  • Course level: Expert
  • Duration 44h 59m 59s