Excel Programming for Business: An Introduction to Excel Macros and Visual Basic

Overview

Excel is the powerful, figure-based spreadsheet component of Microsoft Office used by many businesses for its calculating and data manipulation capacity. Your business might use many of Excel’s built-in features and functions, but are you making the most of Excel’s capacity to analyse data quickly; customise calculations; and obtain information from, and supply output to, a general user without their needing to have direct access to the spreadsheet grid and any underlying calculations? All of this can be achieved with Excel’s programming language Visual Basic.

Are your spreadsheets inefficient or poorly designed? Do they require considerable maintenance to update? Are they difficult to understand? Do you need to manipulate list data? If any answer is ‘Yes’ then this course could help you!

In addition to an extensive range of built-in functions, used for sophisticated calculations and data manipulation, Excel contains part of the Visual Basic programming language Visual Basic for Applications (VBA) as an aid to creating stored, reusable instructions called macros. Visual Basic macros allow you to automate repetitive and complex tasks, analyse data quickly, and customise calculations. Macros and VBA work with and beyond the built-in functions to enhance Excel’s functionality, especially when combined with user-friendly interface forms for data input and output.

Although manipulating figures is Excel’s primary purpose, its row-and-column format lends itself to creating and storing lists of data. Frequent Excel users are usually comfortable with this format and easily enter list data directly into spreadsheet cells. This can, however, be cumbersome, error-prone, and unsuitable for less experienced users. One solution is to create customised user forms with objects - such as command buttons, lists, and check boxes – which use Visual Basic programming code to manage users’ requests for data gathering and manipulation.

Enhance your business use of Microsoft Excel by learning to program in Visual Basic, the structures and commands of which are common to many programming languages. This course introduces and develops Excel macros, Visual Basic programming, and the creation and use of user forms. Specific references are made to Excel’s spreadsheet and cell objects and their properties.

Programme details

Course begins: 20th Sept 

Week 0:  An Introduction to Teams – Course orientation

This is a practical and theoretical course based on the creation and development of Excel macros using Visual Basic for Applications (VBA), first as stand-alone modules of code, then attached to user form objects. The aim of this course is to assist business users to improve their use of Excel by introducing, and developing levels of understanding of, program structures, coding, and design, and how these may be used to automate repetitive tasks of varying degrees of sophistication, especially when analysing data.

The course begins by introducing Excel's automatic macro recording facilities, then examines the VBA programming structures and code created by the macro recorder. As the automatic recording is limited to menu and toolbar commands, additional VBA programming structures and code are introduced to develop macros beyond this stage. Program efficiency and good design will be considered throughout, and many of the structures and techniques taught will apply to other software applications. During the course we will progress from an elementary introduction to simple macros to coded programs of reasonable complexity.

Businesses often use Excel for storing and analysing data. To simplify data gathering and manipulation Excel users can create customised forms and objects which use Visual Basic programming to manage data requests. An introduction to Excel’s forms will follow the introduction to Excel’s macros and VBA. Excel’s form controls include objects – such as labels, text boxes, buttons, lists, option groups, and check boxes – which can be placed onto a form to enhance its 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. Efficiency of design for both user interface and code will be considered throughout as we design user-friendly, data-gathering, data-analysing, interfaces for user interaction.

This course 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 programming, macros, VBA, or Excel user forms and objects is assumed or required. 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).

The following list gives some of the topics which this course aims to cover:

FOR MACROS AND VISUAL BASIC:

  • TECHNIQUES:

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

  • SPREADSHEET OBJECTS:

Activecell; Selection; Cells collection; Rows; Columns.

  • SPREADSHEET OBJECT PROPERTIES:

Font (Bold, Style, Name, Size), Color, Value, Formula, NumberFormat.

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

Defining and using memory variables; Manipulating numbers, dates and text; Setting, testing, and identifying cell values; Calculating values; Counting; Gathering information from the user; Presenting information to the user; Checking for and trapping programming errors; Adding comments.

  • FUNCTIONS AND METHODS:

Message and Input boxes; IsNumeric; Val.

FOR EXCEL FORMS AND VISUAL BASIC:

  • Toolbox tools:                            Creating user forms with text boxes, labels, command buttons, option buttons, check boxes, frames, lists.
  • Object properties:                     Caption, color, text, value, enabled, visible, height, width, tab order.
  • Object events:                            Click, activate, load, change, give/lose focus, 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, dates, and text; Formatting dates.
  • Functions and methods:           Message & Input boxes; Setfocus; IsDate; UCase; LCase; Adding items to lists.
  • Miscellaneous:                           Moving through data; Manipulating data; Working with lists; Calling object-independent blocks of code.

Certification

Students who register for CATS points will receive a Record of CATS points on successful completion of their course assessment.

To earn credit (CATS points) you will need to register and pay an additional £10 fee per course. You can do this by ticking the relevant box at the bottom of the enrolment form or when enrolling online.

Coursework is an integral part of all weekly classes and everyone enrolled will be expected to do coursework in order to benefit fully from the course. Only those who have registered for credit will be awarded CATS points for completing work at the required standard.

Students who do not register for CATS points during the enrolment process can either register for CATS points prior to the start of their course or retrospectively from the January 1st after the current full academic year has been completed. If you are enrolled on the Certificate of Higher Education you need to indicate this on the enrolment form but there is no additional registration fee.

Fees

Description Costs
Course Fee £250.00
Take this course for CATS points £10.00

Tutor

Ms Judith A Harley

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.

Course aims

This is a practical and theoretical course based on the creation and development of first VBA macro programming, then second of Excel user-forms and associated objects with underlying VBA code. The aim of this course is to introduce, and to develop levels of understanding of, spreadsheet use and efficiency; the analysis, manipulation, and checking of data with VBA; and form design and object-event driven VBA coding, and how these may be used to provide a user-friendly interface to enhance business use of an Excel sheet.

Course Objectives:

  1. To introduce macros and Visual Basic for Applications (VBA) for automating repetitive tasks within the Microsoft Excel environment.
  2. To introduce Excel forms, tools, and programmable form objects for designing a user-friendly, data-gathering, data-analysing, interface.
  3. To develop programming skills in Visual Basic for Applications (VBA) to enhance business use of Excel, particularly in programming form objects; analysing and manipulating sets of data; and working with spreadsheet and cell objects.

Teaching methods

The course will be a mix of approximately 50% formal group teaching online and approximately 50% individual practical work following pre-recorded video topics. During the online teaching sessions students will have the opportunity to ask questions about the pre-recorded session content. Students are encouraged to actively participate and question during the online teaching and to regard this as an opportunity to interact with other participants and the course tutor.

Learning outcomes

By the end of this course students will be expected to see improvements in the following areas of knowledge and skills:

1. Development of knowledge and understanding of:

  • Planning, creating, and editing Excel VBA macros and code;
  • Excel VBA programming concepts, structures, and design;
  • Using objects to design, create, and programme an effective interactive Excel user form;
  • Event-driven, object-orientated program design and coding;
  • Spreadsheet, cell, and form objects and their properties.

 

2. Development of various skills involved in business spreadsheet design and VBA programming:

  • To analyse -                          developing the logic to analyse a business or programming situation and to analyse data entry requirements, data manipulation, and suitable user interface tools;
  • To organise -                          organising and ordering the information given and information required;
  • To develop and design -   using suitable programming tools to design and build efficient instruction sets and designing and building a suitable user interface;
  • To communicate -                communicating with the user during design, development and maintenance;
  • To evaluate -                         evaluating requirements, evaluating and using suitable code structures and form objects, and evaluating the suitability of the resulting project.

Assessment methods

Assessment will take the form of a single worked practical spreadsheet project based on material taught during the course, accompanied by a descriptive written account. Each student is encouraged to complete this.

Students must submit a completed Declaration of Authorship form at the end of term when submitting your final piece of work. CATS points cannot be awarded without the aforementioned form.

Application

We will close for enrolments 7 days prior to the start date to allow us to complete the course set up. We will email you at that time (7 days before the course begins) with further information and joining instructions. As always, students will want to check spam and junk folders during this period to ensure that these emails are received.

To earn credit (CATS points) for your course you will need to register and pay an additional £10 fee per course. You can do this by ticking the relevant box at the bottom of the enrolment form or when enrolling online.

Please use the 'Book' or 'Apply' button on this page. Alternatively, please complete an application form.

Level and demands

This course 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 programming, macros, VBA, or Excel user forms and objects is assumed or required. 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).

Most of the Department's weekly classes have 10 or 20 CATS points assigned to them. 10 CATS points at FHEQ Level 4 usually consist of ten 2-hour sessions. 20 CATS points at FHEQ Level 4 usually consist of twenty 2-hour sessions. It is expected that, for every 2 hours of tuition you are given, you will engage in eight hours of private study.

Credit Accumulation and Transfer Scheme (CATS)