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: File:Osp hw5.xls Osp hw5.xls