Visual Basic for Applications (VBA) in Excel - Introduction to programming Training Course

Macros

• Recording and modifying macros
• Linking macros to forms, toolbars, and keyboard shortcuts

VBA Environment

• Visual Basic Editor settings
• Management components
• Keyboard shortcuts
• Optimizing the workspace

Intro to Procedural Programming

• Procedures: Sub and Function
• Data types
• If... Then... ElseIf... Else... End If structures
• Select Case statements
• Loops using For... Loop, While, and Until
• For... Next loops
• Loop control statements like Exit For and Exit Do
• Processing arrays

String Handling

• String concatenation
• Converting between data types (implicit and explicit)
• Processing string characters

Visual Basic

• Reading and writing data to spreadsheets (Cells, Range)
• Interacting with users via InputBox and MsgBox
• Variable declarations
• Variable scope and lifetime
• Operators and precedence rules
• Module options
• Creating custom functions for use in sheets
• Objects, classes, methods, and properties
• Code security
• Protecting code from tampering and previewing

Debugging

• Step-by-step execution
• Locals window
• Immediate window
• Breakpoints and Watches
• Call Stack

Error Handling

• Common error types and prevention strategies
• Catching and managing run-time errors
• Error structures: On Error Resume Next and On Error GoTo

Excel Object Model

• The Application Object
• Workbook object and Workbooks collection
• Worksheet Object and Worksheets collection
• ThisWorkbook, ActiveWorkbook, and ActiveCell objects
• Selection Object
• Range collection
• Cells Object
• Displaying data on the status bar
• Optimization via ScreenUpdating
• Timing measurements using the Timer method

Event Handling in VBA

• Understanding events
• Responding to events by creating handlers
• Triggering event handlers
• Managing frequently occurring events

VBA Forms

• Building custom forms
• Invoking forms from code
• Integrating forms into sheets