There are many tasks that cannot be normally accomplished in Excel: a popular example is merging several (or several dozen) tables situated in different workbooks into one big table. Other tasks can be performed manually, but they are tedious and repetitive. VBA macros both open before the user a whole new world of possibilities and allow automation of monotonous tasks and complicated calculations.
Programming in VBA is accasible to everyone – you don’t have to have majored in Computer Studies or spent years in learning programming to write your own macros and adapt macros written by others.
During this training course, participants learn how to use VBA in practical applications. The material covered includes writing macros, creating own worksheet functions and modifying and adapting macros written by others.
Duration
2 days
Agenda
- Recording and running macros.
- Assigning macros to forms, toolbars and shortcut keys.
- Editing a macro.
- Getting familiar with Visual Basic Editor.
- Object browser.
- Modules.
- Archiving and deploying code.
- Introducing Visual Basic for Applications.
- Variables. Data Types. Arrays.
- Operators.
- Conditional statements: if, select.
- Loops: for, for..each, do..while, do..until.
- Procedures and Functions.
- Build functions.
- User functions.
- Passing values to procedures and functions. Required and optional arguments.
- Introduction to object programming. Class, Object, Collection, Properties, Methods, Events.
- Communication with user: MsgBox object and InputBox object.
- Creating forms.
- Handling events.
- Excel programming using Visual Basic for Applications (VBA).
- Using user functions in a worksheet.
- Navigating the Excel Object Hierarchy.
- Application object.
- Working with Workbook objects and Worksheet objects.
- Accessing worksheet data using Range, Selection, and Cell objects.
- Working with Chart objects.
- Working with PivotTable objects.
- Working with the PivotFields Collection.
- Using Debugging Tools.
- Stepping through code.
- Watch – determining the value of expressions.
- Setting Breakpoints.
- General rules of good coding: readability, comments, naming of objects, variables, procedures, functions, arguments, etc..
- Creating Add-Ins.
For open training, the course materials are prepared for MS Office 2010. Other versions (2013, 2007, 2003) are available on request. Contact us for details.
Audience and prerequisites
This course is intended for experienced Excel users who are interested in creating code to automate tasks in Excel, and to whom standard Excel features are not enough.
Before attending this course, students must have:
- An intermediate level of Microsoft Excel experience.
No prior programming experience is necessary.
Certificates
Course participants receive completion certificates signed by ALX.