10.00am: Session 1
11.45am: Session 2
2.00pm: Session 3
3.45pm: Session 4
5.00/5.30pm: Course disperses
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
- 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.
Activecell; Selection; Cells collection; Rows; Columns
SPREADSHEET OBJECT PROPERTIES:
Font, Color, Value
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.