Excel - vba

course description
this two-day course is designed to give participants a sound working knowledge of vba, to develop and automate spreadsheet solutions. the modular design includes practical examples and case studies. participants will acquire a robust vocabulary to devise real solutions and promote effective use of microsoft excel.
the uses that excel vba may be put to include:

1. automate a repetitive task - speeds it up, makes the task more reliable because the macro/routine will always work.
2. automate awkward tasks - you no longer need to remember how to do the task afresh every time, just run the macro/routine.
3. simplify tasks for non-experts - create the macro/routine that will allow a user with little excel skill to perform complex tasks in excel
4. perform a task that is not possible in standard excel e.g. new calculating functions for commissions or discounts, importing or exporting data at the click of a button

target audience:are you ready?
no previous programming skills are necessary, although a good working knowledge of excel is an advantage including using some functions.
in tuition use objective-based training. each exercise is clearly described first, run through and then reviewed. this means that by the end of the course you will be able to:

• record macros and use relative and absolute references to avoid problems
• use the visual basic editor to write, edit, organise, debug and run procedures
• use loops and conditions to automate repetitive or awkward tasks
• create and use custom functions to enable and to simplify calculations and formatting
• use conditions to determine what action must be taken depending upon the spreadsheet data
• create user-interactive procedures to ask the user what to do next or what data should be used
• create forms to simplify a procedure like data entry
• transfer the data from a macro/routine to a spreadsheet
• transfer the data from a spreadsheet to a macro/routine
• debug the code if it goes wrong
• trap errors and give users more friendly error messages than the default messages

After completing this course, participants will be able to:

macros

• record
• absolute and relative reference
• where macros can be stored
• running macros using buttons and keyboard shortcuts

visual basic editor window

• screen components and their uses
• modifying it to make it easier to use
• edit the code to improve its functionality and speed
• create new modules for storing code

conditions

• if condition to make the code perform a task if the conditions (the data) is right
• select case condition to improve your understanding

loops

• to loop the code so that a macro works all the way down a column
• add a condition to a loop to stop it at the right point
• understand the different types of loops

variables

• store data in a variable
• use variables in calculations
• use variables to store user input data
• declare variables to stop using so much memory
• understand the scope of variables to make the work in other worksheets
• understand the use of static and constant variables

testing & debugging

• use the immediate window to see code results
• run code one line at a time to see the effect of each line
• set break points to speed up debugging

user forms

• create a form to simplify data entry
• use the different types of controls to input the data into e.g. text box, drop down list box
• add code to command buttons to perform specified actions
• transfer the data from a form to a spreadsheet

custom functions

• create calculating functions especially for your operation
• include conditions within a function to use with e.g. commission or discount calculations

objects, properties, methods

• understand the excel object model to prepare you for more advanced code writing

related courses


excel - formulas and functions

excel - intermediate

excel - introduction
tailored training is available