Aim of this course

The course deals with programming in software packages Microsoft Office. The features of the programming language Visual Basic for Application (VBA) in spreadsheets on the basis of Excel are studied thoroughly. The course has an applied trend.

Books

Estonian

• Luczkowski, T. Excel ja VBA: http://tallinn.ester.ee/search~S1*est?/XVBA&searchscope=1&SORT=D/XVBA&searchscope=1&SORT=D&SUBKEY=VBA/1%2C23%2C23%2CB/frameset&FF=XVBA&searchscope=1&SORT=D&1%2C1%2C
• Mägi, A. Microsoft Excel 97/VBA http://tallinn.ester.ee/search~S1*est?/XVBA&searchscope=1&SORT=D/XVBA&searchscope=1&SORT=D&SUBKEY=VBA/1%2C23%2C23%2CB/frameset&FF=XVBA&searchscope=1&SORT=D&20%2C20%2C
• Linntam, A. Programmeerimine Microsoft Office Exceli keskkonnas : kohandatud Microsoft Excel 2007 jaoks: http://tallinn.ester.ee/search~S1*est/?searchtype=X&searcharg=Programmeerimine+Microsoft+Office+Exceli+keskkonnas&searchscope=9&SORT=DZ&extended=0&SUBMIT=OTSI+&searchlimits=&searchorigarg=XProgrammeerimine+Microsoft+Office+Exceli+keskkonnas%26SORT%3DDZ

English

• Shepherd, R. Excel VBA macro programming: http://tallinn.ester.ee/search~S9*est?/X(VBA)&searchscope=9&l=eng&SORT=D/X(VBA)&searchscope=9&l=eng&SORT=D&SUBKEY=(VBA)/1%2C7%2C7%2CB/frameset&FF=X(VBA)&searchscope=9&l=eng&SORT=D&3%2C3%2C
• Jelen, B. VBA and macros for Microsoft Excel: http://tallinn.ester.ee/search~S9*est?/X(VBA)&searchscope=9&l=eng&SORT=D/X(VBA)&searchscope=9&l=eng&SORT=D&SUBKEY=(VBA)/1%2C7%2C7%2CB/frameset&FF=X(VBA)&searchscope=9&l=eng&SORT=D&4%2C4%2C
• Walkenbach, J. Microsoft Excel 2000 power programming with VBA: http://tallinn.ester.ee/search~S9*est?/X(VBA)&searchscope=9&l=eng&SORT=D/X(VBA)&searchscope=9&l=eng&SORT=D&SUBKEY=(VBA)/1%2C7%2C7%2CB/frameset&FF=X(VBA)&searchscope=9&l=eng&SORT=D&6%2C6%2C

What we do?

Introduction

• Create a Macro http://www.excel-vba-easy.com/vba-how-to-create-macro-excel.html

Recording and executing macros

• Excel Macro Recorder http://www.excel-vba-easy.com/vba-how-to-excel-macro-recorder.html
• Macro Security http://www.excel-vba-easy.com/vba-basic-macro-security.html
• Practice1: http://www.wiseowl.co.uk/training/exercises/ex-1733.htm
• Practice2: http://www.wiseowl.co.uk/training/exercises/ex-1731.htm
  • More: http://www.wiseowl.co.uk/training/exercise-list/t-1426.htm

VBA language

• Getting Started with VBA in Office 2010 http://msdn.microsoft.com/en-us/library/ee814735.aspx
• Visual Basic for Applications (by wikipedia.org) http://en.wikipedia.org/wiki/Visual_Basic_for_Applications
• Visual Basic Editor http://www.excel-vba-easy.com/vba-basic-visual-basic-editor.html

Simple VBA programming

• Developing Macros in Excel http://www.excel-vba.com/vba-prog-1-5-new-macro.htm
• Testing Macros in the Visual Basic Editor for Excel http://www.excel-vba.com/vba-prog-1-6-testing-macros.htm
• Practice1: http://www.wiseowl.co.uk/training/exercises/ex-1741.htm
• Practice2: http://www.wiseowl.co.uk/training/exercises/ex-1742.htm

Variables in VBA

• Variables http://www.excel-vba-easy.com/vba-programming-excel-vba-variable.html
• MsgBox http://www.excel-vba-easy.com/vba-basic-msgbox.html
• Objects, Properties and Methods http://www.excel-vba-easy.com/vba-basic-objects-properties-methods.html
• Workbook and Worksheet http://www.excel-vba-easy.com/vba-basic-workbook-worksheet.html

Pratice

• Practice1: http://www.wiseowl.co.uk/training/exercises/ex-1746.htm
• Practice2: Social Security number check
  • In Estonian http://minitorn.tlu.ee/~jaagup/kool/java/kursused/08/vbrak/isikukood.txt
  • In English http://en.wikipedia.org/wiki/National_identification_number#Estonia

Conditional branching in VBA

• If Then Statement http://www.excel-vba-easy.com/vba-programming-if-then-statement.html

Looping through code

Loop http://www.excel-vba-easy.com/vba-programming-excel-vba-loop.html

• Practice1: http://www.wiseowl.co.uk/training/exercises/ex-1750.htm
• Practice2: http://www.wiseowl.co.uk/training/exercises/ex-1751.htm
• Practice3: http://www.wiseowl.co.uk/training/exercises/ex-1752.htm

Building procedures and functions

Building User Interfaces

Homework

Homework2

• Ex 1 http://www.wiseowl.co.uk/training/exercises/ex-1743.htm kadri
• Ex 2 http://www.wiseowl.co.uk/training/exercises/ex-1742.htm Nino
• Ex 3 http://www.wiseowl.co.uk/training/exercises/ex-1732.htm Riina
• Ex 4 http://www.wiseowl.co.uk/training/exercises/ex-1853.htm Priit
• Ex 5 http://www.wiseowl.co.uk/training/exercises/ex-1736.htm Mirjam
• Ex 6 http://www.wiseowl.co.uk/training/exercises/ex-1735.htm mariliis
• Ex 7 http://www.wiseowl.co.uk/training/exercises/ex-1761.htm
• Ex 8 http://www.wiseowl.co.uk/training/exercises/ex-1853.htm kerli
• Ex 9 http://www.wiseowl.co.uk/training/exercises/ex-1742.htm

Homework3

Please do all of them!

• http://www.wiseowl.co.uk/training/exercises/ex-1750.htm
• http://www.wiseowl.co.uk/training/exercises/ex-1751.htm
• http://www.wiseowl.co.uk/training/exercises/ex-1752.htm

Homework4

• Practice2: Social Security number check
  • Please do function what takes one Estonian Social Security Number value and returns "Correct" or "Not Correct".
    • In Estonian http://minitorn.tlu.ee/~jaagup/kool/java/kursused/08/vbrak/isikukood.txt
    • In English http://en.wikipedia.org/wiki/National_identification_number#Estonia

Homework 5

• Download a file: Osp_hw5.xls
• Write a macro that would put all the Advanced level courses to a new sheet, which is named "Courses for IT Pros", and all Essential level courses to a new sheet, which is named "Courses for Information Workers". (If in the cell "Level" (A4, A5, A6, ...) is the value "Essential", then copy the row to "Courses for Information Workers" sheet and if there is a value "Advanced" then to "Courses for IT Pros" sheet). Finally, the advanced level courses should be all on "Courses for IT Pros" sheet and a essential level courses in "Courses for Information Workers" sheet.