Description

Save time with our course "Excel: Macros and VBA programming"

Excel is a powerful application. But before long you'll find yourself re-entering the same data or repeating the same actions. So you'd love to know how to avoid that double work.

But there's more: developing a user-friendly form? Setting the same filter across countless sheets? Automatically generating workbooks and/or worksheets? You'll learn all of that in our training "Excel: Macros and VBA programming".

In this (technical) training you'll learn how to automate your work in Excel using Office's programming language (VBA, or Visual Basic for Applications). That way you'll stop wasting time on repetitive actions and have more time for other tasks. It doesn't get much more productive than that.

Objectives

After this training you'll be able to develop your own applications in Excel Visual Basic for Applications (VBA).

Target audience

Anyone who regularly performs the same action in Excel and would benefit from automating it.

Prerequisites

Prior knowledge of VBA isn't required, but you do need a good knowledge of Excel. By a good knowledge we mean being familiar with the following terms: PivotTables, conditional formatting, data validation, tables, charts, slicers, and so on. Good analytical insight is also an asset.

Contents

Part 1: Macros

• Recording a macro

• Linking a macro to a button

• Understanding macro limitations

Part 2: VBA and programming basics

• Visual Basic Editor (Project, Code and Properties windows)

• Modules

• Variables (declaration, scope, data types)

• Routines (subs vs. functions; writing custom functions)

• Decision structures (If...Then...Else, Select Case)

• Loops (For...Next, Do While, Do Until, etc.)

• Message boxes for user interaction

• Error handling

Part 3: Using Excel objects in your code

• What is an object?

• Object properties, methods and events

• Application object (changing Excel settings via code)

• Workbook object (opening, creating, saving, closing workbooks)

• Worksheet object (adding, removing, copying sheets)

• Range object (copying cells, reading/writing data, clearing contents)

• ListObjects (creating and looping through tables)

• Other objects (pivot tables, charts, etc.)

Part 4: Developing forms

• Working with form elements (buttons, list boxes, checkboxes, etc.)

• Creating custom forms (adding and programming controls)