Excel VBA Macros

SC
SITH Computer Institute
Last Update 11/11/2021

About This Course

INTRODUCTION TO ADVANCE EXCEL

  • VBA enables you to use English like statements to write instructions for creating various applications. VBA is easy to learn, and it has easy to use User Interface in which you just have to drag and drop the interface controls. It also allows you to enhance Excel functionality by making it behave the way you want.
  • For personal use, you can use it for simple macros that will automate most of your routine tasks. Read the article on Macros for more information on how you can achieve this.
  • For business use, you can create complete powerful programs powered by excel and VBA. The advantage of this approach is you can leverage the powerful features of excel in your own custom programs.

COURSE CURRICULUM

Helpie FAQ

  • VBA Introduction
    • Excel VBA - Introduction
    • Excel VBA - User-Interface of VBA Screen
    • Excel VBA - Developer Tab
    • Excel VBA - Record a Macro
    • Excel VBA - Save a Macro Workbook
    • Excel VBA - Write a Simple Macro
  • Userform
    • Excel VBA - Create User Form
    • Excel VBA - Command Button
    • Excel VBA - Radio Button
    • Excel VBA - Check Box
    • Excel VBA - List Box
    • Excel VBA - Frames
    • Excel VBA - Multipage
    • Excel VBA - Background
    • Excel VBA - Text Box
    • Excel VBA - User Form Event
  • Cell Methods & Properties
    • Excel VBA - Cell Referencing
    • Excel VBA - Copy Paste
    • Excel VBA - Font
    • Excel VBA - With Block
    • Excel VBA - Borders
    • Excel VBA - Alignment
    • Excel VBA - Font Color
    • Excel VBA - Background Color
    • Excel VBA - Paste Special
    • Excel VBA - Orientation
    • Excel VBA - Wrap Text
    • Excel VBA - Merge Unmerge
    • Excel VBA - Clear Cells
    • Excel VBA - Delete Cells
  • Excel VBA - Rows & Columns
    • Excel VBA - Rows & Columns Insert
    • Excel VBA - Rows and Columns Delete
    • Excel VBA - Column Width
    • Excel VBA - Row Height
    • Excel VBA - Activate & Select
    • Excel VBA - Columns Hide & Unhide
    • Excel VBA - Rows Hide and Unhide
  • Sheet Methods & Properties
    • Excel VBA - Sheets Introduction
    • Excel VBA - Add Sheets
    • Excel VBA - Add Sheet With Names
    • Excel VBA - Rename Sheets
    • Excel VBA - Get Sheet Names
    • Excel VBA - Copy Sheets
    • Excel VBA - Move Sheets
    • Excel VBA - Change Sheet Tab Color
    • Excel VBA - Hide and Unhide Sheets
    • Excel VBA - Sheet Protection
    • Excel VBA - Activate Sheet
  • Workbook Methods & Properties
    • Excel VBA - Create Workbook
    • Excel VBA - Get Workbook Name
    • Excel VBA - Save & Close Workbook
    • Excel VBA - Open and Close Workbook
    • Excel VBA - Delete Workbook
    • Excel VBA - Create Folder
  • Variable
    • Excel VBA - Variable Usage
    • Excel VBA - Comment
  • Excel VBA Loops
    • Excel VBA - For Loop Example 1
    • Excel VBA - For Loop Example 2
    • Excel VBA - For Loop Example 3
    • Excel VBA - For Loop Example 4
    • Excel VBA - For Loop Example 5
    • Excel VBA - For Loop Sheet Name
    • Excel VBA - For Each Next Loop
    • Excel VBA - Do While Loop
    • Excel VBA - Do Until Loop
  • VBA ADDON
    • Excel VBA - Types of Errors
    • Excel VBA - Error Handling
    • Excel VBA - Debugging
    • Excel VBA - Immediate Window
    • Excel VBA - Number to Text Function
  • Conditional Statement
    • Excel VBA - If Statement
    • Excel VBA - if Else Statement
    • Excel VBA - If Elseif Else Statement
    • Excel VBA - If Elseif Else With AND Operator
    • Excel VBA - If Else Using For Loop
    • Excel VBA - Select Case Statement
  • In-Built Functions
    • Excel VBA - Message Box 1
    • Excel VBA - Message Box 2
    • Excel VBA - Message Box 3
    • Excel VBA - Message Box 4
    • Excel VBA - Message Box 5
    • Excel VBA - Input Box
    • Excel VBA - Rows & Columns Count
    • Excel VBA - String Functions Left-Right
    • Excel VBA - String Functions UCase-LCase
    • Excel VBA - String Reverse
    • Excel VBA - Create Functions
  • Date & Time
    • Excel VBA - Date Add
    • Excel VBA - Date Part
    • Excel VBA - DD/MM/YYYY
    • Excel VBA - Dates
    • Excel VBA - Time Function Part 1
    • Excel VBA - Time Function Part 2
  • Excel VBA Arrays
    • Excel VBA - 1D Array Static
    • Excel VBA - 2D Array Static
    • Excel VBA - Dynamic Array
  • Events
    • Excel VBA - Worksheet Selection Change
    • Excel VBA - Double Click
    • Excel VBA - Sheet Activate
    • Excel VBA - Workbook Open
  • Using Names, Sort and Filter:
    • Named Ranges:Naming Ranges, Using the Name Property of the Range Object, Working with Named Ranges, Determining which Names Overlap a Range
    • Sorting:Structuring the Data, Sorting a Range, Sorting a Table,
    • Filter:AutoFilter Object, Filter Object, Date Custom Filter, Adding Combo Boxes, Copying the Visible Rows, Advanced Filter
  • PivotTables and Pivot charts with VBA Macros:
    • PivotTables and Pivot charts:Creating a PivotTable Report, PivotCaches, PivotTables Collection, PivotFields, CalculatedFields, PivotItems, Grouping, Visible Property, CalculatedItems, Updating Pivot Tables, Synchronizing multiple pivot tables, PivotCharts, External Data Sources
  • Advanced VBA Functions:
    • User Define Function (UDF):Detailed discussion on User Defined Function: What is User Defined Function, Use of User Defined Function and How to create any Function OR Formula which is not available in Excel and you want it to work for you by the help of Macro.
    • Create Your Own Formulas With VBA Macros Coding.
    • ADD-IN:Discussion on ADD-IN: What is ADD-IN, Use of ADD-IN and How to create ADD-IN.?
    • Working With VBA Events:Discussion on VBA Events, What are Events, How and when to use the VBA Events.
  • Charts and Dashboards with VBA Macros:
    • Charts:Chart Sheets-Adding a Chart Sheet Using VBA Code, Embedded Charts, Using the Macro Recorder, Adding an Embedded Chart Using VBA Code, Creating charts, Defining Chart Series with Arrays, Converting a Chart to Use Arrays, Changing chart type and series type, Adding secondary axis, Changing chart marker options, Determining the Ranges Used in a Chart, Creating Dynamic Charts, Chart Labels, Copying Charts as picture/chart/excel objects into other Microsoft application.
    • Creating Interactive Dashboards :Introduction to dashboard and interactive dashboards, Creating dashboard using Named ranges, Charts, Tables, Pivot Tables and user forms, Choosing the right chart – Bullet Chart, Thermo meter chart, Using Alerts in dashboards, Interactive Dashboard Examples.
  • Interacting with Other Office Applications like MS Word, MS Access and Outlook (Mail Merge):
    • Establishing the Connection, Late Binding, Early Binding.
    • Data Connectivity from MS Word:Activating the Word Application, Opening a Word Document. Creating a New Word Document, Import of Data from Word to Excel.
    • Data Connectivity from MS Access:An Overview of ADO, the Connection Object, the Recordset Object, the Command Object, Using ADO in Microsoft Excel Applications, Using ADO with Microsoft Access.
    • Interacting with Outlook:Drafting and sending mails via Outlook, Adding the recipients’ mail address and subject lines, Adding ranges, charts, tables, text in the mail body, Adding attachments.
  • VBA Project
    • Creating an interactive VBA application
  • Learning Objectives

    You can automate tasks by using macros, which are a series of commands and instructions that you group together to perform a task automatically.
    Create dynamic reports by mastering one of the most popular tools.
    VBA allows much more complex navigation, advanced execution, and many more conditions to be imposed on the tasks you wish to perform.

    Requirements

    • You don’t need any prior programming experience to understand this module.
    • Basic to intermediate familiarity with Microsoft Excel
    • A willingness to try new things