Categories:
Excel,
VBA
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.
Download as PDF
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.
Our locations
- Online (English) – your home, office or wherever you want
- Warsaw (English) – Jasna 14/16A
- any other location (London, UK, EU) on request
Group size
Open training groups are no larger than 14 participants.
Private training can be conducted for groups of any size, according to the Customer's preference. To assure high quality and good learning environment, the group size should not exceed 12.
On-demand training costs and registration
The prices provided in the general tables cover the cost of an open (public) training for one person.
In the case of an on-demand training at the Customer's premises, you are required to provide the necessary facilities (room, computers, projector) and we will send an instructor to run the training. The price for a session is calculated for the whole group.
The cost does not change significantly along with the number of participants. There is no minimum number of participants but we recommend no more than 10-12 people because the training becomes much less effective with a bigger group..
The cost of an on-demand training consists of:
- the base training cost, plus
- travel and accommodation charges for the instructor
The base price of an on-demand training varies, but it is usually around the price of an open training for 3-5 persons (regardless of your group size).
The typical travel and accommodation price for the EU/Schengen zone is under 1250 GBP (1500 EUR) for a 5 day session. We do our best to keep it as low as possible in your location.
The payment can be in GBP or EUR, whichever you prefer. The quotation we will send you with the exact amount in your currency will be valid for 3 months, regardless of any changes in the exchange rate.
For more information, or to register to a group, please contact us at groups@alx-training.co.uk and tell us know:
- the course(s) that you are interested in,
- your location,
- your preferred dates,
- the number of people you wish to train,
And any other questions that you may have.
Customised training
We can also customise the training program of any of our trainings (or create a new one) according to your needs – whether you want to focus on particular solution used in your company, include material concerning a technology we do not usually cover, or create a tailor-made training.
Read more about customized training here or contact us at groups@alx-training.co.uk.
Individual consultation
For individual persons for whom all scheduled open training is inconvenient, we offer individual consultation. Virtually all of our training courses are available as such.
During individual consultations, while it can be a direct equivalent of a group training in terms of subject covered, we can also provide more than that – we can solve problems, help choose appropriate solutions and provide advice on the most effective use of chosen tools.
More information about individual consultations can be found on the separate Consulting page. In case of any question, we encourage you to contact us.
Price
190 EUR
The price includes:
- course materials,
- snacks, coffee, tea and soft drinks,
- course completion certificate,
- one-time consultation with the instructor after course completion.
