Excel 2007 VBA Training

Our Excel VBA training walks you through the techniques used by professional application developers. We fortify your skills with "try-it" exercises, and provide plenty of shortcuts and hints to get you up and running with VBA in no time.

Getting Started with VBA

The VBA editing environment is like a cockpit - a comprehensive editing environment that includes the code editor, management windows, wizards, inspectors, and plug-ins - making it easy for you to manage projects and assemble code. Knowing how to use these tools efficiently will save you time and effort in building advanced features into your applications. This introductory training session will show you how to:

• Launch the VBA editing environment, and load and run an application
• Maximize the potential of the VBA toolbox
• Automate commands by recording a macro, and edit the underlying code in the Visual Basic editor
• Activate the object browser to view Excel 2007 objects (components)
• Invoke the VBA debugger to ferret out elusive bugs

Working with Expressions, Variables, Functions and Procedures

The fundamental building blocks of VBA syntax are expressions, variables, functions, and procedures. Whether you're new to Visual Basic or a veteran, our training will teach you the best practices for organizing your statements and expressions into procedures and functions, and then later into larger modules. Our Excel VBA training combines comprehensive walkthroughs with hands-on exercises as you learn to:

• Declare and set variables, define their scope, and associate them with data types
• Work with strings, numbers, Booleans, dates, and objects
• Write basic expressions and program statements
• Create procedures and pass values to them using variables
• Invoke functions that return a resulting value
• Call VBA's intrinsic functions to perform calculations and modify strings
• Execute sections of your program based on conditions you define

How to Program Excel Components as Objects

VBA is an object-oriented language, offering all the benefits of object-oriented program design. VBA represents all Excel spreadsheet components, such as forms, worksheets, charts, and PivotTables, as objects that you can call and control using their built-in properties and methods. Understanding how to use objects is not only important to learning Visual Basic, it is central to building professional-quality Excel spreadsheet applications. The walkthroughs and exercises in our Excel VBA training will make you an expert. Specifically you will learn how to:

• Categorize and store objects in classes
• Explore the specifics of objects in the Excel Object hierarchy
• Store an array of objects in a collection
• Call an object's methods and either query or set its properties
• Program an object to respond to an event
• Use the Object Browser to examine an object's methods, properties and events

Working with Forms and Controls

This critical Excel VBA training section will show you how to create professionally designed forms that meet industry-standard user-interface guidelines. You'll learn how to use VBA forms to create dialog boxes and windows to help end users interact with your application. We'll also teach you how to best use the VBA UserForms object's toolbox of controls, such as option buttons, check boxes, text-editing controls, and scrollbars. After this part of the Excel VBA training, you'll know how to:

• Create a UserForm and add it to your project
• Define events for your forms
• Match your company's "look and feel" in your forms by setting properties
• Add user-friendly controls like command buttons, text boxes, option buttons, and frames
• Let your users select items from a dropdown list to avoid data entry errors

All about PivotTables & Excel VBA

If your organization analyzes complex data from worksheets or external sources, PivotTables are a powerful tool for summarizing, analyzing and presenting this data. Our Excel VBA training demystifies the process of developing PivotTables, giving you hands-on practice using the convenient wizard to create PivotTables and to summarize your data. We show you how to program your application to invoke the PivotTable wizard so that your users can easily refine PivotTable charts and reports themselves. You'll learn how to:

• Create a PivotTable using the time-saving wizard
• Summarize complex data in an easily comprehensible chart or report
• Modify and enhance the layout of a PivotTable report
• Call the PivotTableWizard method from a VBA application
• Work with PivotFields and store them in a collection
• Compile and run PivotTable reports

Debugging Code in Excel VBA

Even well-designed programs can produce unexpected results - bugs - in specific situations. An application developer can spend as much as one-third of their development time debugging their programs, so knowing how to identify bugs quickly is essential to rapid program development. Our training will show you how to:

• Differentiate between logic, runtime, and syntax errors
• Develop strategies to minimize errors in programming
• Create comprehensive test procedures to test your application in different conditions
• Use the debugging tools in VBA to examine you program's execution
• Set a breakpoint and inspect your code at that point
• Step through your code to see how the error occurs

Handling Errors in Excel VBA

A properly designed and debugged program can still encounter unplanned error events. For example, a user may attempt to save a worksheet when the disk is full, or a procedure can receive a value that is out of range. The solution is to handle errors gracefully, so that when an error occurs, your procedure takes an action, such as giving the user an option to save the file in another location. Our training explains what error conditions are likely to occur, and then walks you through creating your own handling procedures. By the end of this session you will be able to:

• Declare an error event
• Trap an event and set basic options for dealing with it
• Get a description of what caused the error
• Define a procedure to handle an error event
• Use the error trapping mechanism to assist your debugging efforts

Prerequisites:

• Working experience with Microsoft Excel.
• Prior programming experience helpful but not required.

Excel VBA training offered at four locations::

• Excel VBA training San Francisco
  
• Excel VBA training Los Angeles
  
• Excel VBA training San Jose
• Excel VBA training Sacramento