Introduction to Object-Oriented Programming Using Excel and Visual Basic

Overview

Excel is Microsoft’s powerful, figure-based spreadsheet which incorporates the programming language Visual Basic for Applications (VBA). Although manipulating figures is lists of Excel’s primary purpose, its row-and-column format lends itself to creating and storing data. Frequent Excel users are usually comfortable with this format and easily enter list data directly into spreadsheet cells and use Excel’s built-in formulae and other tools to analyse and manipulate this data. This analysis and manipulation can, however, be cumbersome, error-prone, and unsuitable for less experienced users. One solution is to create customised Excel forms to manage spreadsheet data.

Excel’s form controls include objects - such as labels, text boxes and command buttons – which can be placed onto a form to enhance its use and user-friendliness. VBA commands provide the functionality behind these objects, and in this context the VBA code is triggered by an event happening to an object (object-oriented and event-driven). For these event-driven procedures the necessary programming structures, functions, and methods will be taught from first principles. This process of programming form objects is referred to as Object Oriented Programming (OOP). Efficiency of design for both user interface (form + objects) and VBA code will be considered throughout as we design user-friendly, information-gathering, data-analysing, interfaces for user interaction.

This is a practical and theoretical day school based on the creation and development of Excel user-forms and associated objects with underlying VBA code. The aim of the day is to introduce, and to develop levels of understanding of, form design and object-event driven VBA coding, and how these may be used to provide a user-friendly interface to an Excel sheet. This day school is suitable for anyone wishing to learn about, or improve their knowledge of, Excel’s Visual Basic programming structures and techniques.

No previous experience of Excel forms, form objects, (object oriented) programming, or VBA commands is assumed or required as these will be taught from first principles. As practical work will use Microsoft Excel participants are expected to be familiar with at least the basic features of Excel such as: creating and editing spreadsheets; entering different types of data (text / numbers / dates / formulae) into spreadsheet cells; writing simple formulae (add / subtract / multiply / divide).

Programme details

Timetable

9.45am: Registration

10.00am: Session 1

11.15am: Coffee/tea

11.45am: Session 2

1.00pm: Lunch

2.00pm: Session 3

3.15pm: Coffee/tea

3.45pm: Session 4

5.00/5.30pm: Course disperses

Topics

This day school will introduce a few objects from the Excel/VBA toolbox, the properties which describe them, and the events which may happen to them. For these event-driven procedures the necessary programming structures, functions, and methods will be taught. Efficiency of design for both user interface and code will be considered throughout. Below is a list of topics which this day school will aim to cover.

FOR EXCEL FORMS AND VISUAL BASIC:

  • Toolbox tools / objects: Creating user forms with text boxes, labels, command buttons.
  • Object properties: Caption, color, text, value, enabled, visible, height, width, tab order.
  • Object events: Click, change, enter, exit, mouse up/down, keypress.
  • Programming concepts: Displaying user forms; Working with objects; Object-event driven code; Defining and using memory variables; Scope of memory variables; Manipulating numbers and text; Calling object-independent blocks of code.
  • Functions and methods: Message boxes; IsNumeric; UCase; LCase; Val.
  • Miscellaneous: Moving through data; Manipulating data; Calculating and counting; Setting, testing, and identifying cell values; Gathering information from the user; Presenting information to the user; Adding comments

MACRO AND VBA TECHNIQUES:

Using Excel's VBA explorer and code editor; creating new code modules; creating, storing, running, and editing automatic macro recordings; mixing automatic code with written code; adding macro buttons to Excel’s Quick Access Toolbar

PROGRAMING STRUCTURES:

  • For VBA module boundaries: Sub … End Sub; Private Sub … End Sub
  • For testing: If … End If
  • For repetition: For … Next;
  • For referencing the same object several times: With …End With.

SPREADSHEET OBJECTS:

Activecell; Selection; Cells collection; Rows; Columns

SPREADSHEET OBJECT PROPERTIES:

Font, Color, Value

Teaching/learning methods

The day will be a mix of approximately 50% formal group teaching and approximately 50% individual practical work. Students are encouraged to actively participate and question at all stages. Students are also encouraged to regard the practical periods as an opportunity for one-to-one individual tuition from the course tutor.

Fees

Description Costs
Tuition Fee (includes Tea/Coffee) £100.00
Baguette £5.50
Hot lunch (three courses) £16.00

Funding

If you are in receipt of a UK state benefit you may be eligible for a reduction of 50% of tuition fees.

If you do not qualify for the concessionary fee but are experiencing financial hardship, you may still be eligible for financial assistance.

Concessionary fees for short courses

Tutor

Ms Judith A Harley

Tutor

Judith Harley, MA, is a physics graduate and freelance computer consultant who advises on, and designs, commercial and private database, spreadsheet, and Visual Basic applications. She has taught computing courses at Oxford University for over 20 years.

Application

Please use the 'Book' or 'Apply' button on this page. Alternatively, please contact us to obtain an application form.

Accommodation

Accommodation is not included in the price, but if you wish to stay with us the night before the course, then plesae contact our Residential Centre.

Accommodation in Rewley House - all bedrooms are modern, comfortably furnished and each room has tea and coffee making facilities, Freeview television, and Free WiFi and private bath or shower rooms.  Please contact our Residential Centre on +44 (0) 1865 270362 or email res-ctr@conted.ox.ac.uk for details of availability and discounted prices.