+49 (0) 30 5201 4923
info@orb-experts.com
A practical introduction to automating Microsoft Excel with Visual Basic for Applications (VBA). Participants learn how to record, understand and develop macros, work with functions and Sub procedures, control programme flow with decisions and loops and use the key objects, methods, properties and events of the Excel object model.
You will learn how to automate repetitive Excel activities and create straightforward VBA programs independently. You will work with the Visual Basic Editor, organise code into Sub procedures and functions and use variables, conditions and loops.
You will also learn how to access and modify workbooks, worksheets, cell ranges and other Excel elements through the object model. By the end of the course, you will be able to develop, test and integrate small automation solutions into existing Excel workbooks.
• Record, run, edit and manage Excel macros
• Navigate and use the Visual Basic Editor confidently
• Create Sub procedures and user-defined functions
• Work with variables, constants and fundamental data types
• Control programme flow using decisions and loops
• Access Excel objects through methods, properties and events
• Identify and resolve errors in VBA code systematically
• Automate recurring Excel tasks with custom macros
• Knowledge equivalent to the Microsoft Excel Fundamentals and Intermediate courses
• Confidence working with workbooks, worksheets, cell ranges, formulas and functions
• Basic experience linking and analysing data in Excel
• No previous programming or VBA experience required
*We customize the course outline and content to your specific needs and relevant use cases.
Module 1: Introduction to macros and VBA
• Common uses of VBA automation in Excel
• Understanding the difference between recorded macros and written code
• Recording, running and deleting macros
• Creating and saving macro-enabled workbooks
• Understanding macro security and trusted documents
• Distinguishing between workbook macros and the Personal Macro Workbook
Module 2: The Visual Basic Editor
• Navigating the main areas of the Visual Basic Editor
• Understanding projects, modules and procedures
• Entering, formatting and commenting code
• Running macros from Excel and the editor
• Executing code one statement at a time
• Using help, the Object Browser and automatic code completion
Module 3: Sub procedures and functions
• Understanding the structure of a Sub procedure
• Creating, naming and calling procedures
• Passing arguments to procedures
• Distinguishing between Sub procedures and functions
• Creating user-defined worksheet functions
• Dividing code into smaller, reusable procedures
Module 4: Variables, constants and operators
• Declaring and naming variables clearly
• Using fundamental data types for text, numbers, dates and Boolean values
• Defining constants for fixed values
• Assigning values and storing calculation results
• Applying mathematical, comparison and logical operators
• Understanding variable scope and lifetime
Module 5: Controlling flow with decisions
• Making decisions with If...Then...Else
• Handling multiple conditions with ElseIf
• Combining logical tests with And, Or and Not
• Using Select Case for clear multiple-choice logic
• Structuring nested decision statements
• Testing conditions and avoiding common logic errors
Module 6: Controlling flow with loops
• Creating counted loops with For...Next
• Processing ranges and collections with For Each
• Controlling repetition with Do While and Do Until
• Exiting loops safely when a condition is met
• Using counters, conditions and intermediate results
• Avoiding infinite loops and unnecessary repetition
Module 7: The Excel object model
• Understanding objects, collections and object hierarchies
• Working with Application, Workbook, Worksheet and Range objects
• Reading and changing object properties
• Using methods to open, save, copy and delete items
• Referencing cells and ranges with addresses, Cells and Offset
• Distinguishing between active objects and explicit object references
Module 8: Automating workbooks and worksheets
• Opening, saving and closing workbooks
• Creating, renaming, copying and deleting worksheets
• Writing values, formulas and formats to cell ranges
• Identifying dynamic ranges and the last populated row
• Reading and processing worksheet data
• Writing results to structured output areas
Module 9: Events and user interaction
• Introduction to event-driven programming
• Using workbook and worksheet events
• Running actions when a workbook opens or closes
• Responding to worksheet changes and selection events
• Displaying information with message boxes
• Collecting simple user input with input boxes
Module 10: Error handling and debugging
• Distinguishing between syntax, runtime and logic errors
• Using breakpoints and step-by-step execution
• Inspecting variables and expressions while code is running
• Using the Immediate, Locals and Watch windows
• Implementing basic error handling with On Error
• Creating clear error messages and safe exit procedures
Module 11: Good programming practice
• Choosing meaningful names for variables and procedures
• Structuring readable code with indentation and comments
• Using direct object references rather than unnecessary selections
• Moving repeated code into reusable helper procedures
• Validating assumptions, inputs and results
• Documenting automation solutions for future maintenance
Module 12: Practical final project
• Analysing a recurring Excel workflow
• Validating and processing input data automatically
• Applying decisions and loops to data processing
• Controlling worksheets and cell ranges through the object model
• Formatting results and generating a report
• Testing, documenting and deploying the completed macro in a workbook
Hands-on learning with expert instructors at your location for organizations.
Master new skills guided by experienced instructors from anywhere.
