MS Excel VBA
VBA is an integral aspect of any analytics related course. This is because VBA finds multiple applications in Excel related tasks such as automation, interactions between Office applications and implementation of business logic in Excel. VBA significantly improves workbook performance and enables tasks which might have been impossible using simple MS – Excel Formulas.
After successful completion of this training, participants should be able to:
- Create/update/robust macros using VBA coding to create custom solutions, automate repetitive tasks, and increase performance;
- Handle/Create Spreadsheets, PowerPoint, Outlook Emails, File and database objects using VBA code;
- Access VBA development environment and write generic, documented, easy-to-understand code;
- Learn about the different data types in VBA and their specific uses;
- Understand the concepts of conditional statements and loops and integrate them into your code;
- Learn how to apply formatting via VBA;
- Implement basic data handling and storage through the use of arrays, collections, and dictionaries;
- Write reusable code for repetitive tasks and call them during normal coding usage;
- Learn the various methods to access charts, shapes, form controls like dropdowns, list boxes, etc. ;
- Learn the basics of error handling in VBA;
- Learn about user forms and their applications in VBA;
- Implement classes and Object-oriented programming in VBA.
Who should attend?
- Data Analyst / Business Analyst / Assistant Manager;
- Financial Analyst;
- Advanced Excel User;
- Fresher seeking jobs in Analytics/Consulting job streams.
Note: A thorough understanding of Microsoft Excel will be extremely helpful while going through this course. At the very least, the candidate should have basic knowledge of formulas in MS - Excel.
Topics we will cover:
- VBA introduction - Overview;
- Variable definition and scoping;
- Understand the object model of VBA;
- Protection and event handling;
- Different Loops in VBA - FOR, DO WHILE, WHILE;
- Conditional Statements - IF, IF-ELSE, SELECT CASE;
- Data handling methods - ARRAYS, COLLECTIONS, DICTIONARIES;
- Charts – Data manipulation, setting source data, properties modification;
- Form controls – Understand the uses of form controls;
- Handling shapes and their properties in VBA;
- File handling through VBA;
- Data manipulation between different workbooks;
- Consolidation of data into a single workbook;
- Export data from the workbook in PDF, PPT, and Excel formats;
- User form design and properties;
- User form events;
- Data manipulation using the form.
- Upon completion of the training, the participants will be awarded with a certificate by Baker Tilly Academy.
- Course Language: Azerbaijan
- Course Duration: 24 hours