How to Improve your Productivity using Excel Visual BASIC

This 2 day course has been specially designed for Excel Users who want to make use of the more powerful features of MS Excel, made available by VBA. This course explores the VBA language and participants will be encouraged to create Modules, Functions and Forms to ensure that future editing, amending and updating can be carried out with the minimum of effort.

This course has been designed to give an all-round understanding of working in the VBA environment, providing the foundation needed to develop interactive programs to take advantage of the amazing time and money saving potential offered by Excel VBA.

Who should attend?

Existing Excel Users who wish to utilise the Visual BASIC facility within the package to develop more sophisticated applications. What are the course prerequisites? Excel Users who are confident in their use of the commands, functions and simple Macros gained by prior attendance on an "Excel Intermediate" course or work experience.

Benefits

Participants will learn how to be more Productive by using Excel VBA to Automate their existing Excel Workflow.

Course Outline

• Introduction
  
• - What is Macro Code
  - Recording & Running a Macro
  - Relative V Absolute Recording References
  
• Assigning Macros to Buttons and Objects
  
• - Assigning a Macro to a Drawn Object on a Sheet
  - Assigning a Macro to a Button on a Toolbar
  - Creating a Toolbar
  - Customising a Toolbar
  - Button Image Editor
  - Changing a Buttons Screen Tip
  
  
• FUNCTIONS
  
• - Logical Functions
  - Nested Functions
  - Lookup and Reference Functions
  - The insert function option
  - Text functions
  
  
• WORKING WITH LISTS
  
• - List layout
  - Data forms
  - Sorting
  - Filtering
  - Sub totals
  
  
• Understanding the VBA screen
  
• - Visual Basic Editor
  - Project Explorer Window
  - Code Window
  - Properties Window
  - Printing a Visual Basic Module
  
  
• The Structure of VBA Environment
  
• - Modules
  - Sub Procedures, Private and Public
  - Functions
  - Forms
  
  
• Documentation
  
• - Inserting Comments
  
  
• Objects/Methods/Properties
  
• - Range and Offset
  - Flexible Ranges
  - Virtual Worksheets
  - Inserting Comments
  - Controlling Objects with their Properties
  - Performing Actions with Methods
  
  
• Workbooks and Sheets
  
• - Activating a Workbook and Worksheet
  - This Workbook
  
  
• Control Structures
  
• - Conditional and Looping Control Structures
  - Non-Structured Statements
  - Passing Data between Procedures
  
  
• User Interfaces
  
• - User Forms
  - InputBox
  - MsgBox
  
  
• Controls Dialogue Boxes and Forms
  
• - Built-In and Predefined Dialogue Boxes> - Custom, Worksheet and User Defined Forms
  - Adding Form Controls
  - Form Control Properties and Code
  - Control and Dialogue Box Events
  - Displaying and Dismissing a User Form
  
  
• Storing Information
  
• - Variable Types, Integer, String, Variant etc
  - Constants
  
  
• Debugging & Error trapping
  
• - Tracing Errors Step By Step
  - Break Point
  - Error Handling
  
  
• Worksheet & Workbook Events
  
• - Worksheet Object Events
  - Workbook Object Events
  
  
• Function Procedures
  
• - User Defined Functions
  
  
• Appendix A - Naming Conventions
  
• - User Form Controls
  - Memory Variables
  - MsgBox variables
  - ASCII codes