©  2015  Sideris  Courseware  Corporation  831  Beacon  Street,  Suite  295  

Newton,  MA  02459  

Phone:  +1.617.965.9800  info@sideris.com  www.sideris.com  

 

 

Programming in VBA

ms803-ver2013-sg

Product Information

Using Microsoft Excel 2013

2 Days Instructor Resources SiderisTraining

v General  Description  

The  skills  and  knowledge  acquired  in  this  course  are  sufficient  to  be  able  to  create  real  life  working  VBA  applications  within  Excel.  The  learner  will  be  able  to  work  with  VBA  within  the  Excel  environment  to  program  and  automate  worksheet  operations.  

v Learning  Outcomes  

At  the  completion  of  this  course  you  should  be  able  to:  

• understand  the  Excel  object  model  and  VBA  concepts  • work  effectively  with  the  main  features  of  the  VBA  Editor  window  • create  procedures  in  VBA  • create  and  use  variables  • create  and  work  with  user-­‐defined  functions  in  VBA  • write  code  to  manipulate  Excel  objects  • use  a  range  of  common  programming  techniques  • create  a  custom  form  complete  with  an  assortment  of  controls  • create  code  to  drive  a  user  form  • create  procedures  that  start  automatically  • write  a  variety  of  error  handling  routines  

v Prerequisites   This  publication  assumes  the  learner  has  a  good  knowledge  of  working  with  Excel.  The  learner  should  be  able  to  create  and  edit  workbooks,  enter  formulas,  copy,  paste,  and  format  data.  The  learner  must  also  have  a  general  understanding  of  personal  computers  and  the  Windows  operating  system  environment  and  be  able  to  use  File  Explorer  to  locate  and  copy  files.  

v Topic  Sheets   124  topics  

v Pages   136  

 

Contents  

Understanding  Excel  VBA  Programming  in  Microsoft  Excel  VBA  Terminology  Displaying  the  DEVELOPER  Tab  The  VBA  Editor  Screen  Opening  and  Closing  the  Editor  Understanding  Objects  Viewing  the  Excel  Object  Model  Using  the  Immediate  Window  Working  With  Object  Collections  Setting  Property  Values  Working  With  Worksheets  Using  the  Object  Browser  

Programming  With  the  Object  Browser  Accessing  Help  Code  Glossary  

Starting  With  Excel  VBA  Using  the  Project  Explorer  Working  With  the  Properties  Window  Using  the  Work  Area  Viewing  Other  Panes  Working  With  Toolbars  Working  With  a  Code  Module  Running  Code  From  the  Editor  

Setting  Breakpoints  in  Code  Stepping  Through  Code  

Procedures  Understanding  Procedures  Where  to  Write  Procedures  Creating  a  New  Sub  Routine  Using  IntelliSense  Using  the  Edit  Toolbar  Commenting  Statements  Indenting  Code  Bookmarking  in  Procedures  Code  Glossary  

 

©  2015  Sideris  Courseware  Corporation  831  Beacon  Street,  Suite  295  

Newton,  MA  02459  

Phone:  +1.617.965.9800  info@sideris.com  www.sideris.com  

 

 

Programming in VBA

ms803-ver2013-sg

Product Information

Using Microsoft Excel 2013

2 Days Instructor Resources SiderisTraining

Using  Variables  Understanding  Variables  Creating  and  Using  Variables  Explicit  Declarations  The  Scope  of  Variables  Procedure  Level  Scoping  Module  Level  Scoping  Understanding  Passing  Variables  Passing  Variables  by  Reference  Passing  Variables  by  Value  Understanding  Data  Types  for  Variables  Declaring  Data  Types  Using  Arrays  Code  Glossary  

Functions  in  VBA  Understanding  Functions  Creating  User-­‐Defined  Functions  Using  a  User-­‐Defined  Function  in  a  Worksheet  Setting  Function  Data  Types  Using  Multiple  Arguments  Modifying  a  User-­‐Defined  Function  Creating  a  Function  Library  Referencing  a  Function  Library  Importing  a  VBA  Module  Using  a  Function  in  VBA  Code  Code  Glossary  

Using  Excel  Objects  The  Application  Object  The  Workbook  Objects  Program  Testing  With  the  Editor  Using  Workbook  Objects  The  Worksheets  Object  Using  the  Worksheets  Object  The  Range  Object  Using  Range  Objects  Using  Objects  in  a  Procedure  Code  Glossary  

Programming  Techniques  The  MsgBox  Function  Using  MsgBox  InputBox  Techniques  Using  the  InputBox  Function  Using  the  InputBox  Method  The  IF  Statement  Using  IF  for  Single  Conditions  Using  IF  for  Multiple  Conditions  The  Select  Case  Statement  

Using  the  Select  Case  Statement  For  Loops  Looping  With  Specified  Iterations  The  Do  Loop  Statement  Looping  With  Unknown  Iterations  Code  Glossary  

Creating  Custom  Forms  Understanding  VBA  Forms  Creating  a  Custom  Form  Adding  Text  Boxes  to  a  Form  Changing  Text  Box  Control  Properties  Adding  Label  Controls  to  a  Form  Adding  a  Combo  Box  Control  Adding  Option  Buttons  Adding  Command  Buttons  Running  a  Custom  Form  

Programming  UserForms  Handling  Form  Events  Initialising  a  Form  Closing  a  Form  Transferring  Data  From  a  Form  Running  Form  Procedures  Creating  Error  Checking  Procedures  Running  a  Form  From  a  Procedure  Running  a  Form  From  the  Toolbar  Code  Glossary  

Automatic  Startup  Programming  Automatic  Procedures  Running  Automatic  Procedures  Automatically  Starting  a  Workbook  

Error  Handling  Understanding  Error  Types  The  on  Error  Statement  Simple  Error  Trapping  Using  the  Resume  Statement  Using  Decision  Structures  in  Error  Handlers  Working  With  Err  Object  Error  Handling  in  Forms  Coding  Error  Handling  in  Forms  Defining  Custom  Errors  Code  Glossary