VBA macro automation

• Introduction of VBA & macro.
• Introduction of all types of windows (properties window, project window, code window, local window, watch window, immediate window)
• Introduction of sub & function procedure
• Write our first code.
• Public procedure and private procedure
• Naming of procedure

Working with variable & data types

• Introduction of variable & data types.
• Naming of variable
• Storing data in variable
• Introduction of public and private declaration.
• Introduction of object variable.
• Set the object variable – workbook object, worksheet object, and range object
• Introduction of static declaration.

Working with workbook object.

• Introduction of thisworkbook and activeworkbook
• Add workbook
• Rename workbook.
• Save workbook in different format (.xlsx, .xlsm, .xls, .xlsb, .xltx, .xltm, .xlt, .pdf and many more)
• Create workbook object

Working with worksheet object.

• Add worksheet
• Delete worksheet
• Rename worksheet
• Create worksheet object
• Move or copy the worksheet to another workbook.
• Activate worksheet.

Working with cells & range object.

• Create range & cells object
• Highlight cells & range
• Copy & paste
• Find the last column & row.
• How to activate cells
• Goto special.
• Cell formatting & number formatting.
• Sort & filter.
• Find & select.
• Basic & advanced filter.
• Hyperlink.
• Print excel sheet.

Condition logic

• Using if, else if.
• Use and, or, like
• Use of goto statement.

Loop in VBA

• For next loop
• For each loop
• Do loop/ do while/ do until
• Nested loop
• Exit from loop

Error in VBA.

• All types of error in VBA (application-defined or object-defined error, object doesn’t support this property or method)
• All types of error handler using in VBA ex. On error resume next, on error goto 0 and label.

Event

• Worksheet event (activate, change, selection, deactivate and other event)
• Workbook event (activate, open, close and other event)

Array

• Static array.
• Dynamic array.
• Single and multi-dimension array.
• Live array VBA projects.

Working with Dictionary

• Creating Dictionary object by Late Binding and Early Binding.
• Adding Key and Item in Dictionary.
• Accessing Key and Item from Dictionary.
• Looping on keys or items.

Working with Function Procedure

• Create Function Procedure in VBA.
• Passing parameter in Function.
• Using ByRef and ByVal in Function and Sub.

Udf (user defined function)

• Create many types of udf function for calculation.
• Create volatile & non-volatile function.
• Use select case in function.
• Use VBA function – Replace, Instr, StrConv, Len, Left, Mid, Right
• Use excel function in VBA.

User form

• Create user form by using the VBA code.
• Creating custom dialogue box in userform
• Using all types of control in user form(combobox, listbox, checkbox, optionbutton).

Pivot table & pivot chart

• Create a pivot table from VBA.
• Create chart from VBA.

File folder management.

• Using late binding & early binding methods in VBA projects.
• Using Microsoft scripting runtime for manage folder and file.
• Rename folder, create folder and delete folder.
• Rename file, create file, delete file, and move file to one folder to another folder.
• Looping over folder and file.
• Using file dialog box.
• Using file & folder pickers.
• Extract file name from folder with using dir function

Excel Based Automation

• Create Master data in excel from multiple sheet.
• Create Master date in excel from multiple workbook.
• Split data in multiple sheet/workbook.
• Live excel based projects.
• Data manipulation in excel.

Creating a word document

• Referencing the word object library.
• Using late binding and early binding method for creating a new instance of word.
• Save word document in different format (.docx, .docm, doc, and pdf)
• Copy data and chart into word
• Writing and formatting text.

Creating outlook emails

• Referencing the outlook object library.
• Using late binding and early binding method for creating a new instance of outlook.
• Opening an outlook application and creating a new mail.
• Send email from outlook with a signature from VBA
• Send emails from outlook from VBA with attachment.
• Looping over sending emails.
• Creating outlook event.

Creating a PowerPoint presentation

• Referencing the PowerPoint object library.
• Using late binding and early binding method for creating a new instance of PowerPoint.
• Creating presentation & slides.
• Copying tables & charts into PowerPoint.
• Adding and formatting textboxes.

Working with MS access and excel

• Referencing the MS access object library.
• Using late binding and early binding method for creating a new instance of MS access.
• Create table.
• Import and export data in MS access to excel.
• Using SQL query in VBA
• Using some objects for MS access in excel.
• Create for MS from wizard.
• Using query in for MS.
• Using VBA code in for MS.

ADODB method (Microsoft Activex data object 6.1)

• Create a connection between SQL server and excel by using ADODB.
• Using all types of SQL cursor in VBA.
• Create table, drop table in SQL server using VBA.
• Import & export data from SQL server.
• Modifying data, inserting data.
• Using SQL stored procedure in VBA.

Web crawling

• Web crawling with browser internet explorer.
• Creating html document.
• Understanding the html tags and use them for crawling
• Fetch the data from html document
• Using get and post request in crawling
• Using all types of web elements.
• Use Chrome/Firefox for crawling by Selenium Library.

Add-ins and custom ribbons

• Understanding of xml code
• Creating add-ins and custom ribbon for excel.
• Creating add-ins and custom ribbon for word.
• Creating add-ins and custom ribbon for powerpoint.

VBA protection & security

• Workbook, worksheet, range, and object protection
• VBA project protection, and password breaker