The Hong Kong University of Science and Technology
COMP 1022Q

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
Using Mod for Patterns
David Rossiter and Gibson Lam
An Example Using MOD
In this presentation we will look at an example
which combines the use of lookup functions
and MOD()
The MOD() function, as you
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
More on Variables
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use the variant data type to store different
types of value in VBA
2. Creat
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Using the Set Keyword
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Explain the situations when you need to use
the Set keyword
2. Demonstr
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
VBA Functions and Subroutines
David Rossiter, Oz Lam and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Group reusable code in a VBA function or
subroutine
2.
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Making Decisions with VBA
David Rossiter, Eddie Chan and Oz Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Write VBA code to make decisions using the
If statement
COM
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q Summer 2015 Solutions to the Midsemester Review Questions
Q1)
One answer is =B5/C5.
Perhaps a better answer is =$B5/$C5 which ensures the references to the two columns dont change.
Q2)
=IF(MOD(ROW()2, $E$1+1)>0, "Attend", "Skip")
or
=IF(MOD(RO
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Introduction to Looping
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Write while loops and do loops to run code
repeatedly in VBA
COMP1022
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Different Types of Variable in VBA
David Rossiter, Gibson Lam and Oz Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Understand the use of five common types of
variabl
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
VBA Basics
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use the MsgBox command to show a
message box using VBA code
2. Understand the idea
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Using For Loops
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Write for loops to run code repeatedly in
VBA
COMP1022Q
Using For Loops
Page
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Referring to Cells in VBA
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use the Range command and the Cells
command to read and write a cel
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q Midsemester Review Questions
Basic Cell Formula
Q1)
Here is a worksheet.
Write
your
answer
here
You dont
need to
write
anything
in these
cells
Your
answer
will be
copied
and
pasted
into
these
cells
Column D in the table contains the percentage
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
More on Do Loops
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Write more do loops (using Until) in VBA
2. Use VBA to generate random numbe
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Working with
the Currently Selected Cells
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use the Selection object to get
information of the
The Hong Kong University of Science and Technology
COMP 1022Q

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
Lookup Techniques
Gibson Lam and David Rossiter
This Presentation
It is very common to use Excel to store lots of
information, and then later search through that
information to find something
In this pr
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Objects
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Describe the basic concept of objectoriented
programming
2. Write simple objectorie
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Using Colour in VBA
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Write VBA code to change colours in Excel
COMP1022Q
Using Colour in VBA
P
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Arrays
David Rossiter and Oz Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use arrays in VBA
2. Tell the lower and upper bounds of arrays in
different situations
3.
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Recursion
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Explain how recursion works
2. Describe the result of running any given
recursive f
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Basic GUI Controls
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Understand how to use GUI controls in Excel
2. Write VBA code to handle GU
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Using Cell Functions in VBA
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use cell functions in VBA code
COMP1022Q
Using Cell Functions in
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Custom Functions
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use userdefined VBA functions in cell
formulas
COMP1022Q
Custom Functions
P
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
More on For Loops
Gibson Lam and David Rossiter
Outcomes
After completing this presentation, you are
expected to be able to:
1. Use for loops with a step value
2. Write nested loops in VBA
COMP1022Q
More
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
VBA Macros
David Rossiter and Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Create a macro by recording one
2. Understand the VBA code of an existing
macro an
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Getting Started with VBA
Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Start writing VBA code using a macro
2. Start writing VBA code using a workbook/
worksh
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Introduction to
Spreadsheets
David Rossiter
Microsoft Office
Before we learn about VBA, we have to know
about Microsoft Office, which is the main
productivity software used around the world
The two main
The Hong Kong University of Science and Technology
COMP 1022Q

Summer 2015
COMP1022Q
Introduction to Computing with Excel VBA
Course Details
Summer 2015
David Rossiter and Gibson Lam
COMP1022Q
Introduction to Computing with Excel VBA
Welcome to COMP1022Q !
This PowerPoint goes through all the basic
information about the course
The Hong Kong University of Science and Technology
COMP 1022Q

Spring 2015
COMP1022Q
Introduction to Computing with Excel VBA
Filtering in Excel
David Rossiter and Gibson Lam
What is Filtering?
Filtering helps you see what you want to see, and
hide the rest
For example, you have some candy chips with many
different colours
Yo
The Hong Kong University of Science and Technology
COMP 1022Q

Spring 2015
COMP1022Q
Introduction to Computing with Excel VBA
Sorting and Filtering Using VBA
Gibson Lam and David Rossiter
What You Will Learn
In this presentation we will look at these:
A reminder of
Sorting using VBA
sorting and filtering
Basic sorting
(witho
The Hong Kong University of Science and Technology
COMP 1022Q

Fall 2014
COMP1022Q Introduction to Computing with Excel VBA
Review Questions 2
Thursday 29 September 2016
A. We will look at these textbook review questions:
o Question 2.8 (page 47)
o Question 2.5 (page 45)
B. We will look at the last 3 questions from the previo
The Hong Kong University of Science and Technology
COMP 1022Q

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Getting Started with VBA
Gibson Lam
Outcomes
After completing this presentation, you are
expected to be able to:
1. Start writing VBA code using a macro
2. Start writing VBA code using a workbook/
worksh