Excel VBA Level 2
Course Outline

DURATION	1 Day
PREREQUISITES	Must least have had sufficient exposure to the concepts covered in Level 1 to be able to move onto new concepts.
DESCRIPTION	Anyone wishing to automate common worksheet operations, convert worksheets into forms and understand Excel VBA code.
COURSE DATES	Click here to request an alternate date Or call us on 020 313 73958 from 8:00 to 22:00 Monday to Sunday. Weekend Sat, 18-Sep-10 10:00 - 16:30 £200 Weekday Tue, 21-Sep-10 09:30 - 16:30 £200 Weekend Sat, 9-Oct-10 10:00 - 16:30 £200 Weekday Tue, 12-Oct-10 09:30 - 16:30 £150 Weekday Tue, 2-Nov-10 09:30 - 16:30 £200 Evening Mon, 8-Nov-10 and Tue, 9-Nov-10 18:30 - 21:30 £200 Weekday Thu, 2-Dec-10 09:30 - 16:30 £200 Evening Wed, 15-Dec-10 and Thu, 16-Dec-10 18:30 - 21:30 £200 Weekday Wed, 5-Jan-11 09:30 - 16:30 £200 Evening Mon, 10-Jan-11 and Tue, 11-Jan-11 18:30 - 21:30 £200 Any One-to-One Tuition Any Date - Fully Customisable 09:30 - 16:30 £400 Levels 1-4 Specials: Click here to request an alternate date Or call us on 020 313 73958 from 8:00 to 22:00 Monday to Sunday. Weekday Mon, 11-Oct-10 - Thu, 14-Oct-10 09:30 - 16:30 £540

Revision of Level 1

• VBE Components

• If Statements
  

• With…
  

Continuing Sub Procedures

• Revisiting the scope of Variables
  

  • Dim

  • Public dim

  • Const

  • Public const

   

• Understanding Events
  

• User Input
  

  • A simple form

  • Inputbox and Msgbox

  • Using Worksheet Data

  • Providing a browse for a file box

  • Providing a GetSaveAsFileName box to save a file

   

• Using Select Case vs using If statement
  

• Expanding on Conditional Formatting limitations
  

Looping

• For Each cell in range… etc
  

• Do While… Loop
  

• Do Until… Loop

• For a = 1 to 50 etc

Error Handling

• Setting and going to Bookmarks

• Catching Errors
  

• On error goto….
  

• On Error Resume Next
  

• On Error goto 0
  

Functions

• Using Built in VBA Functions
  

  • Working out differences in dates (Datediff, etc)

  • Extracting info from a long string (trim, left, right, mid, instr)

  • Working out what kind of value is residing in a cell (isnumeric, isstring, isdate… etc)

   

• Creating your own Functions
  

  • A value with VAT added to it

  • Returning a country based on a provided city

  • Returning the last cell address in a range

  • Returning the last row in a range

  • Returning the last column in a range