The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Range References
Gibson Lam
Stavros Papadopoulos
Referring to Cells
An Excel formula may contain a function
For example, SUM(), MAX(), and AVERAGE()
Such functions typically perform operations on
sever
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 9
Console I/O
1. Understand how console
program executes
2. Learn methods to perform
input/output from/to the
console
Pros and Cons
of using Greenfoot
Pros
A friendly library for
games and
simulations
Cons
Lack support for
building enterprise
app
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 7
Arrays and Nested Loop
1. Understand the concept of
Arrays
2. Learn how to operate on an
array using loops
Background
Mass Transit Railway (MTR) is the railway
system in Hong Kong
You should be quite familiar with that!
Two Picobotlike characte
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 5
Expressions and Branching
Statements
1. Arithmetic expressions
2. Boolean expressions
3. Branching statements
If statements
Ifelse statements
Switch statements
Motivation
(Expressions)
When writing a program, we use
expressions as building blo
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 10
String manipulation
and File I/O using Eclipse
Trainee The skeleton code
String manipulation
public class MTRTrainee cfw_
/* 1. Instance variables for characters and strings */
/* 2. Member methods on characters and strings */
public char whatIsMy
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 11 Supplementary
Binary Search
Advanced recursion example:
Binary search
Given a sorted array, design a method to fi
nd an element quickly
Solution: Binary search
In this example, we find the value 9
in only 2 steps. When the size of an
array grows
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 11: Supplementary
Tower of Hanoi
Tower of Hanoi
A game starts with 3 pegs (A, B, C) and a
stack of discs of different sizes
All discs stacked up on the peg A with smaller
discs sitting on top of bigger discs
The goal is to move the entire stack of
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 12
Putting Programming
Concepts Together
Topics we learned
Hardware and
software
Basic data types,
variables, assignment
statements
Objects and classes
Methods and scope
rules
Arithmetic and
Boolean expressions
Branching statements
Loops
1D and
The Hong Kong University of Science and Technology
COMP 1022

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
Cell Formatting
Gibson Lam and David Rossiter
Cell Formatting
After you enter a value into a cell there are many
different ways in which the value can be displayed
Select the cell, then use the controls
The Hong Kong University of Science and Technology
COMP 1022

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
Excel Tables
David Rossiter
Common Use of Excel
We have already looked at the most obvious way
to handle data in Excel i.e. adding data into columns,
then adding formulas which refer to the data
For exa
The Hong Kong University of Science and Technology
COMP 1022

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
More on Cell References
Gibson Lam, Stavros Papadopoulos and David Rossiter
Referring to Cells
An Excel formula may contain a function
For example, SUM(), MAX(), and AVERAGE()
Such functions typically
The Hong Kong University of Science and Technology
COMP 1022

Fall 2015
COMP1022Q
Introduction to Computing with Excel VBA
Starting to Use Excel
David Rossiter
Excel Basics
You have probably used Excel before
Even so, we will start from the basics
Excel is all about numbers and formulas, and
displaying the numbers and the
The Hong Kong University of Science and Technology
COMP 1022

Fall 2013
COMP1022Q Midterm Review Questions
Cell Formula
Q1) Here is a screen dump showing our target result. The chart at the bottom is included only to give
you an overview of the data.
Page 1 of 10
To achieve this result, you need to enter a correct Excel cell
The Hong Kong University of Science and Technology
COMP 1022

Fall 2013
What is your travelling plan?
http:/www.mtr.com.hk/eng/train/system_map.html
From:
To:
Prince Edward
Choi Hung
(Please enter a MTR station name)
How many people are in a taxi?
How fast can you walk?
5
5 km/h
(From 1 to 5 people)
(Please enter a MTR statio
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 1
Introduction to Computers:
Hardware and Software
Computer Science
Computer Science studies the design and
development of algorithms to solve problems
Algorithm: Set of instructions or rules
Operations involved in designing algorithms
Software r
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
Week 6
Loops
1. Understand concepts of loops
2. Learn how to program with
different types of loops
Motivation
Very often a program would repeat the
same set of procedures several times
Multiply a number several times
Keep asking for user input to popula
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Lookup Techniques
David Rossiter and Eddie Chan
This Presentation
It is very common to use Excel to store lots of
information, and then later search through that
information to find something
Excel give
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Course Details
Fall 2014
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
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Starting to Use Excel
David Rossiter
Excel Basics
You have probably used Excel before
Even so, we will start from the basics
Excel is all about numbers and formulas, and
displaying the numbers and the
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Using Approximate Matching
in Lookups
David Rossiter
Approximate Matching
Previously we looked at finding an exact match
when we do a lookup
However, sometimes you need the closest match
For example, a
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Excel Tables
David Rossiter
Common Use of Excel
We have already looked at the most obvious way
to handle data in Excel i.e. adding data into columns,
then adding formulas which refer to the data
For exa
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Advanced Example
of Cell Referencing
David Rossiter
Writing a
Correct Formula
In this example we want to know the
best country to get profit by buying
and then selling lots of iPad 2 tablets
The profit
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Handling Cell Formula Errors
Gibson Lam and David Rossiter
This Presentation
In this presentation, we look at some common
types of cell formula errors:
1.
2.
3.
4.
Invalid reference error
Invalid value e
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Cell Formula Basics
David Rossiter and Oz Lam
In This Presentation
We will look at the following topics in this
presentation:
The basics of cell formulas
Arithmetic operators
Relational operators
Log
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Worksheets
David Rossiter
Workbook Structure
The basic structure of an Excel file is:
Workbook
Workbook basically means an Excel file
1 or more worksheets
Each worksheet
has lots of cells
You can think
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Making Decisions
with Excel Formulas
David Rossiter, Eddie Chan and Oz Lam
This Presentation
In this presentation, we will look at:
IF
Nested IF
Multiple nested IFs
COMP1022Q
Making Decisions with Exc
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
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
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
Introduction to VBA
David Rossiter
Looking at VBA
At this stage we have learned a lot of things
about using Excel (without thinking about VBA)
Now we will start looking at VBA and how it
can be used wit
The Hong Kong University of Science and Technology
Introduction to JAVA programming
COMP 1022

Fall 2014
COMP1022Q
Introduction to Computing with Excel VBA
A First Look at Excel VBA
David Rossiter
The VBA Signal
There has to be some kind of signal
for VBA code to begin
This signal is called an event
There are lots of different possible events
An example