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.
- Print excel sheet.
- 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.
- Worksheet event (activate, change, selection, deactivate and other event)
- Workbook event (activate, open, close and other event)
- 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.
- 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 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