Excel: Programming with VBA

Seminarziel

This Excel seminar teaches you the basics of VBA programming. You will learn the programming environment and master important language elements. You will also learn programming techniques, and how and when to use them. The focus is on applying this knowledge to programming simple solutions in Excel.

Inhalt

• The Development Environment 
  • Components of the Development Environment
  • Project Explorer
  • Properties Window
  • Module Window
  • Working in the Module Window
• Basics of VBA Programming 
  • Applications of macros and VBA
  • Macro recording
  • A simple program
  • Structure of procedures
  • Comments and line wrapping
  • VBA syntax
  • Input and message boxes
• Use of macros in Excel
  • Triggering macros 
  • Form design 
  • Control elements 
• Basic language elements 
  • Objects?
  • Properties, methods and events
  • Objects on forms
  • Naming conventions 
  • Working with the object catalogue
  • Linking events and procedures
• Functions and variables 
• Predefined functions
  • Declaring variables
  • Data types
  • Declaration of constants
  • Principles of event-oriented programming 
  • Overview of events
  • Arguments in event procedures
  • Event procedures in practical use
• Controlling the programme flow
  • Programme flow charts
  • Instructions sequences
  • Two-way and multi-level selection
  • Case selection
  • Loops
  • For loop
  • While loop
  • Until loop
  • Do loop
  • Further control structures (Exit, GoTo, Recursion)
• Extended language elements 
  • Object variables
  • Listings
  • Named arguments
• Troubleshooting and Error Handling 
  • Basics of error handling
  • Testing procedures with step in
  • Checking and monitoring variables
  • Catching and handling run-time errors
• Objects in Excel
  • Properties and methods of Excel objects
  • With statements
  • The object catalogue
  • The Excel object model
  • References to libraries
• Working with Excel data
  • Searching directories
  • Saving Excel files
  • Checking if files are open
  • Accessing sheets and cells
  • Checking whether a sheet exists in a folder
  • Transfer values to other sheets
  • Working with columns and rows