Excel Programming using VBAExcel-VBA

Currently there are no scheduled dates

Available on demand, at time and place convenient to you, for groups of at least 4 participants.

4.7/5 (232)
Stars
exempt from VAT

Regular Price: £ 510

Special Price: £ 380

refreshments included

computer station included

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

  1. Recording and running macros.
  2. Assigning macros to forms, toolbars and shortcut keys.
  3. Editing a macro.
    • Getting familiar with Visual Basic Editor.
    • Object browser.
    • Modules.
    • Archiving and deploying code.
  4. 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.
  5. Communication with user: MsgBox object and InputBox object.
  6. Creating forms.
  7. Handling events.
  8. 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.
  9. Using Debugging Tools.
    • Stepping through code.
    • Watch – determining the value of expressions.
    • Setting Breakpoints.
  10. General rules of good coding: readability, comments, naming of objects, variables, procedures, functions, arguments, etc..
  11. 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.

There are currently no entries

Enter your e-mail address and we will notify you about future dates

Question?

Ask us anything about this training

* I hereby give my consent to the processing of my personal data by ALX Training Ltd. for the purpose of this request.