Advanced VBA and Macros

Introduction to VBA

Introduction to Programming, VBA Overview and the IDE, Quick Review of Macros, Working with Macros, How to use Visual Basic Editor (Programming Environment)

Advantages of VBA

An easy-to-use and versatile language, Integrated with off-the-shelf products

Understanding the VBA Editor, Module and Procedures

The Visual Basic Editor, Parts of the VBA Editor, Modules and Macros, About VBA Forms, Sub Procedures & Modules, Creating Forms, Using Controls and their Properties, Running VBA Forms in Excel

VBA Syntax and Grammar

Objects – The Grammar, Using Properties and Methods, Understanding Parameters, The Object Browser, Understanding object hierarchy, Tools for better coding, Moving to Other Cells, Editing Specific Cells

Variables, Arrays & Constants

Using the Immediate Window, Gaining greater control through the immediate window, Formatting Cells, Using Variables, Constants, Object Variables, Dimensioning and usage of variables, Displaying data and obtaining data from the user

Storing data in variables

How and when to declare variables, Selecting data types, Fixed and dynamic arrays, Constants

Control Structure and Program Flow

IF (Else If, Else), Select Case(), For Next Loop, For Each Loop(), Do Until Loop and Do While Loop, With, End With, Nesting Code

Building procedures

Creating Sub and Function procedures, Calling procedures, Passing arguments to procedures

Building Intuitive User Interfaces

Communicating with the user through the message box, Gathering user information with the input box, Utilizing the File Dialog object and Dialogs collections

Creating customized dialogs with User Form objects

Command buttons, List and Combo boxes, Labels, Option buttons, Check boxes, Text boxes, Frames, Adding more functionality with advanced ActiveX control

Fonts & Background

Using Font Property in cells, Using Various Background Properties in Excel VBA, Creating a Counter , Range Selection and Manipulation

Objects and Collections

Workbook and Worksheet Object, Range Object and Cell Properties, Methods and Properties, Assigning Objects, Using Name Arguments

Array

What is Array& why use array, Declaring an Array, Sorting Data in Array, 2-Dimention Array, Dynamic Array, UBound and Lbound Properties, Storing Data in a range of an Array Elements

Using various array functions

Using Excel Worksheet Functions in VBA, VBA Functions along with their applications in different scenarios

1. String Functions like left, Mid, Right, Instr, Instrrev, Format and many more 2. Numeric/Mathematical Functions like Abs, Int, Round, Exp and many more 3. Logical Functions like Isdate, Isnull, Isnumeric and many more 4. Date and Time Functions like Date, Dateadd, Datediff,Datepart and many more 5. Data Type Conversion Functions like Chdir, chdrive,Dir,Curdir and many more 6. Lookup and Reference Functions like Choose,Switch and many more

User-Defined Functions, Adding Help to User Defined Functions, Declaring Variables in User Defined Functions, Using Ranges in User Defined Functions, Creating and Managing an Add-in for User Defined Functions

Internet and Excel

Copy Hyperlinks and Text from a webpage to Spreadsheet

Interacting with Other Office Applications

Communicating with Word and PowerPoint using Excel VBA, Running SQL Queries within Excel Environment

Building Dynamic Pivot table using VBA

Create an Excel Pivot Table report using VBA, Referencing an Excel Pivot Table Range using VBA, Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA, Excel Pivot Table Address, Location, Move & Copy using VBA, Excel Pivot Table Layout and Design, using VBA, Excel Pivot Table Properties & Settings, using VBA, Refresh Excel Pivot Table and Cache of PivotTable, using VBA, Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA, Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA, Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA, Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA, Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA, Create & Customize Excel Pivot Table Charts, using VBA

Zapping the Bugs

Split Screen and Basic Debugging, Stopping a Macro Prematurely, Ways to Step through Code in VBA, Changing the Next Executable Line, Setting and Removing Breakpoints, Permanent Breakpoints, Conditional Breakpoints, Using Debug. Print and Immediate Window, Discussion about Call Stack, Locals Window and Watch Window

Error Handling

Using On Error To create an error trap, Resetting Error Handling to the default, Customizing Error Handling using err. Number and err. Description, Error Handling with Input Box, Raising Errors and Error Bubbling

Building Dynamic Dashboards Using VBA

Adding New chart using VBA, Aligning the chart position to a particular range and changing the chart position, Assigning charts to an object in vba, Differences between Embedded chart and chart sheet, Sending Excel Chart to an Outlook email using VBA, Exporting Excel chart to word and PowerPoint using VBA, Building Interactive dashboards with user forms integration using VBA

Linking to ADO Databases with VBA

ADO Object Library, Connection Strings,Record Sets,Cursors and Locking, Viewing,Adding,Editing and Deleting Records from Databases using VBA, Finding Record and Multiple Records meeting a Criterion

Security

Four levels of Macro security, Trusted Documents and Locations, Using Digital Certificates in Excel VBA

Event Handling in VBA

Workbook ,Worksheet, Chart Events, Application Events, User form and ActiveX Control Events

Folders and File Handling in VBA

Check if Folder Exists using Excel VBA, Opening Creating and Deleting Folders , Copying Folders From One Location to Another , Move Folder From One Location to Another , Make File Read Only , Copy all Excel Files One Folder to Another, Opening Files Using File Dialog Box , Customize File or Folder Dialog Box , Reading and Writing Text Files using Text Streams, Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Modifying Menus and Toolbars

Simplifying user interaction with the Command Bar object, Adding and removing Command Bar Control objects