Microsoft Excel Level 4 - Macros & VBA (All versions)

In Reading

Price on request

Description

  • Typology

    Course

  • Location

    Reading

  • Duration

    2 Days

Description

To provide delegates with the confidence, knowledge and practical experience to be able to automate tasks in Excel for rapid numerical analysis & presentation of information. Delegates will learn how by using macros, they can create flexible solutions that can be used by colleagues with limited knowledge of Excel.

Facilities

Location

Starts

Reading (Berkshire)
16 Summerfield Rise, RG8 0DS

Starts

On request

To take into account

This course is aimed at delegates who wish to learn how to automate tasks in Microsoft Excel. Delegates should have a good working knowledge of Excel, by either attending the Intermediate course, or from personal experience.

Questions & Answers

Ask a question and other users will answer you

Who do you want to respond to your question/enquiry?

We will only publish your name and question

Course programme

Microsoft Excel Level 4 - Macros & VBA (All versions)

Please remember a customised outline can also be put together of subjects you wish to cover. Our trainers will always quantify what delegates wish to learn on each course as well and try to include this in the content.

Completion of this course will map delegates towards the Microsoft Excel MOS Exam - Variation between different versions, e.g. 2003 and 2007 will be covered within the relevant course when delivered.

Overview

Delegates learn how to record and develop automated procedures in Excel using the Visual basic for Applications language. It emphasises hands-on experience through use of sample course files and, if appropriate, delegates' own worksheets.

Recording Macros:

  • Naming the procedure
  • Allocating keyboard shortcuts
  • Where to store the recorded code
  • Relative vs. absolute recording
  • Stopping recording.

Running your Procedure:

  • Keyboard shortcut
  • From the menu
  • Button on worksheet
  • Toolbar Buttons
  • Customising the menu.

The Visual Basic Editor:

  • Identifying key screen components
  • Finding your procedure
  • Describing your procedure.

Objects, Properties and Methods:

  • Introducing Objects, Properties and Methods
  • Using the Object Browser
  • Editing your VBA code.

Writing Procedures:

  • Inserting a module
  • Writing your code
  • Looping
  • Techniques to speed up performance
  • Keeping the user informed of progress
  • Using the VBA Help facility.

Using Variables:

  • Creating Variables
  • Types of Variable
  • Declaring Variables
  • Scope of Variable
  • Forcing Declaration of Variables.

Creating an Interface:

  • Using Message Boxes, Input Boxes and User Forms
  • Linking Controls to and From Worksheets
  • On Action Procedures.

Controlling the Worksheet:

  • Placing your data correctly
  • Using Named ranges
  • Adjusting Column widths
  • Protecting the Worksheet
  • Protecting your procedure.

Handling Errors:

  • Syntax Errors
  • Run Time Errors
  • Logical Errors
  • Stepping through your procedure
  • Using Break points
  • The Immediate and Watch windows
  • Using OnError.

Creating Function Procedures:

  • Describing a Function procedure
  • Writing the Function
  • Calling the function
  • Saving the Function as an Add-In.

Handling Arrays:

  • Describing and Declaring Arrays
  • Populating your array with data
  • Extracting data from the Array to the worksheet.

Consolidation Exercises

There will usually be time at the end of the course for delegates to consolidate their new skills by creating procedures from scratch. It is strongly recommended that delegates use this time to develop procedures that will automate tasks in their own files. If this is not convenient, further exercises will be set by the trainer.

Microsoft Excel Level 4 - Macros & VBA (All versions)

Price on request