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:
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.