On this 3-day course, participants will learn how to
- Develop customized solutions using Visual Basic for Applications (VBA)
- Apply the object models to program effectively in the Microsoft Office System
- Take full advantage of extensive built-in VBA functionality
- Exploit ActiveX controls to create functional and familiar user interfaces
- Retrieve data from PC and server databases
- Troubleshoot code using VBA's debugging tools
- Secure your code and protect your users from macro viruses
- Use Visual Basic to control Excel, and to automate processes to speed up and simplify their work with Excel data.
Course benefits
They will also learn how to create interactive procedures using message boxes, and custom dialog boxes using Visual Basic controls. By the end of the course participants will have a good understanding of the Visual Basic language and the main elements of Excel that are referenced when using VBA.
Introduction
The advantages of VBA
- An easy-to-use and versatile language
- Integration into MS Office suite
- Reduction of development time and costs
What mean VBA, macro and template
Macros: the first step
- Recording and running simple macros
- Limitations
- Structuring complex macros
Getting to Know the VBA Development Environment
Visual Basic Editor
- Project Explorer
- Code Window
- IntelliSense
- Help system
- Properties Window
- Object Browser
Debugging
- Code stepping
- Breakpoints
- Immediate Window
- Quick Watch
- Locals Window
Writing VBA code
Object-oriented programming
- Objects
- Methods
- Properties
- Parameters
- With ... End With statement
Excel Object Model
- Hierarchy
- Collections
- Referencing specific objects
- VBA Excel methods and properties
Variables and constants
- Declaration and assignment
- Data types
- Option explicit
- Scope and lifetime of variables
- Fixed and dynamic arrays
Conditional statements
- If...Then...Else
- Select...Case
Loop statements
- Do ... While and Do … Until loops
- For ... Next and For Each ... Next
Procedures
- Sub and Function procedures
- Call procedures
Event-driven programming
- What are events?
- Event procedures
- Events linked to workbook and worksheets
Building Intuitive user interfaces
Using intrinsic dialogs
Creating customized dialogs with UserForm objects
- Command buttons
- Labels
- Text boxes
- List and Combo boxes
- Option buttons
- Check boxes
- Frames
- Advanced ActiveX controls
Working from Excel with other MS applications
- Word
- Outlook
- Access with ActiveX Data Objects (ADO)
Providing a Safe and Secure Environment
Handling runtime errors
- The On Error GoTo structure
- Err object
- Resume, Resume Next or Resume labels
Implementing security
- The dangers of macro viruses
- Macro security levels in MS Office
- Trust Center
- Password protection
Distributing procedures
- Exporting and importing modules
- Creating add-ins
This course is valuable for anyone who wants to automate tasks through VBA programming. Some experience using the products in the Microsoft Office System is assumed. No previous programming experience is required.
Excel Advanced, or equivalent knowledge.
