Excel: Learning VBA

Introduction

• Extend the capabilities of Excel using VBA
• What you should know before starting

1. Introducing Visual Basic for Applications

• Introduce object-oriented programming
• Examine the Excel object model
• Work in the Visual Basic Editor
• Set VBA project properties
• Create, export, and delete code modules
• Create a subroutine
• Create a function
• Add comments to your code
• Run a VBA procedure
• Add code to a recorded macro

2. Defining Variables, Constants, and Calculations

• Introduce Excel VBA data types
• Declare variables and require declaration before use
• Manage variable scope
• Define static variables and constants
• Create a calculation using mathematical operators
• Define arrays
• Define and use object variables
• Streamline code references using With…End With statements
• Challenge: Define variables, constants, and calculations
• Solution: Define variables, constants, and calculations

3. Adding Logic to Your VBA Code

• Repeat a task using a For…Next loop
• Step through all items of a collection using a For…Each loop
• Repeat a task using a Do loop
• Execute code conditionally using an If…Then statement
• Select which action to take using a Case statement
• Challenge: Add logic to your code
• Solution: Add logic to your code

4. Debugging Your VBA Code

• Manage errors using On Error statements
• Step through a subroutine or function
• Set breakpoints in your code
• Verify output using the Immediate window
• Watch a value in a routine
• Challenge: Debug your VBA code
• Solution: Debug your VBA code

5. Manage Workbook Elements and Data in VBA

• Write a value to a cell
• Cut, copy, and paste cell data
• Find values in cells
• Refer to cells using the OFFSET property
• Concatenate text strings
• Return part of a string
• Manage worksheets with VBA
• Manage workbooks with VBA
• Challenge: Manage workbook elements and data in VBA
• Solution: Manage workbook elements and data in VBA

6. Adding Advanced Elements to Your VBA Code

• Turn off screen updating when you run a macro
• Use worksheet functions in a macro
• Acquire values using an input box or message box
• Call a subroutine from another subroutine
• Challenge: Add advanced elements to your workbook
• Solution: Add advanced elements to your workbook

7. Using Excel Events in Your VBA Code

• Run a procedure when you open, close, or save a workbook
• Run a procedure when a cell range changes
• Trigger a procedure using a specific key sequence
• Challenge: Use Excel events in your VBA code
• Solution: Use Excel events in your VBA code

Conclusion

• Further resources