Microsoft Excel – Developing Applications I

• Basics of Visual Basic for Application (VBA), which will be used for writing macros and automating work in Microsoft Excel.
• Basic work with the Excel object model, especially objects Range, Worksheet, Workbook and Application.

• Knowledge of working in Microsoft Excel on user level.
• The advantage is a basic knowledge of programming in any language.

• Professional explanation with practical samples and examples.

• Powerpoint handouts and module printouts.

Introduction to Macros

• Basic concepts
• Recording macros
• Absolute, relative references
• Starting macros using keyboard shortcuts
• Personal Macro Workbook
• Editing Macros

Working with program code

• Modules
• Using the editor window
• Comments
• Setting Up the Environment
• Automatic code completion
• Configuration dialogs

Variables and procedures

• Declaring variables
• Data Types
• Data Types conversions
• Constants
• Arrays

Procedures and Functions

• Procedures and Functions
• Parameters of functions and procedures
• Optional parameters
• Passing parameters by value and by reference
• Selected built-in functions

Program Flow Control

• Logical and comparison operators
• If … Then
• Select … Case
• Do … Loop
• For … Next
• Exiting Cycle

Debugging

• Types of errors
• Watch window
• Immediate window
• Call Stack
• Locals window
• Break mode & Program stepping

Catching errors

• Run-time error
• Err object
• Concatenation of errors
• Inline error handling

Excel object model

• Basic characteristics of objects
• Properties, methods and events
• Object Browser
• Collection

Object Range

• Basic work with the Range object
• Definition of contiguous and non-contiguous areas
• Passage cells area
• Collection of rows, columns, and cells
• Working with the currently selected area
• Inserting values
• Inserting formulas

Worksheet object

• Sheets Worksheets and Charts objects
• Inserting and removing sheets
• Protection
• Visibility
• Print Settings
• Sheet events

Workbook object

• Creating a new workbook
• Opening and saving workbooks
• Data consolidation
• Workbook closing
• Workbook protection
• Workbook events

Object Application

• Application object and MS Office
• Selected properties and methods of Excel.Application object

Previous courses

Microsoft Excel – Advanced Methods and Functions [MSEX3]

Programming and Algorithmization Basics [PRG]

Next courses

Microsoft Excel – Developing Applications II [MSEX6]

Related courses

Microsoft Access – Application Development [MSAC2a]

Introduction to Microsoft Visual Basic .NET Programming [GOC301]