IT Business Applications

1
IT Business Applications

• Presentation 15
• VBA Programming

This presentation is in Self-Study Form Press F5
to begin
2
Objectives

• After studying this weeks material you will
  enhance your ability to
• Use the Visual Basic Editor to examine and
  interpret VBA Code
• Modify Macros in order to extend their role
• Communicate with the user via input and message
  boxes
• Use if conditions within VBA Macros to select
  courses of actions depending on conditions.
• Use programming structures (If - EndIf
  While-Wend For-Next) to allow repetition of
  tasks.

3
Using this Presentation

• The presentation is broken down into an
  Introduction and four separate sections.
• First of all, you should work through section 1
  (Introduction), which takes you through the full
  activity, and explains what needs to be done.
• The other sections are broken down into various
  levels of difficulty. As a minimum, you will need
  to complete the first part of each section. This
  will give you a functioning workbook at the end.
• Two spreadsheets accompany this presentation. The
  first is called Appointments Book Solution. This
  is needed for the Introduction. Please note that
  the Macro Code is locked to prevent copying
  pasting of code.
• When you have completed your work on all the
  sections in this presentation, you should go on
  to Formative Activity 10.

4
Menu
When doing Tasks 1-4, if you do not complete
all the parts of the previous section, you should
download the solution for the previous Task, e.g.
Appointment Book (Task 2) Solution and start with
that.

• Introduction
• Page Switching
• Automating Booking
• Searching using While Wend
• Searching using For next
• What Next?

5
Introduction

• Example of a Working Appointments Book System

6
Good Hairdays Salon

• This presentation takes you through the creation
  of an appointments booking system for a
  Hairdresser.
• The appointments structure has already been
  created and is shown here. The hairdressers is
  open 6 days a week, 9am-7pm, and has 4 stylists
  working.
• Each stylist is available for 40 hours per week
  .These times are shown as blank cells.
• In order to fully understand what is required,
  you should now download the completed solution
  and examine it in detail.

7
The Completed Solution

• Download the workbook called Appointment Book
  Full Solution.
• We will now look at its functionality, and begin
  to take it apart to see how the functions are
  made to work.
• Please Note
• The Macro Code has been locked to prevent you
  examining it, or cutting pasting from it in
  the rest of this presentation!

8
Page Switching

• First of all, you will note that there is only
  one page in the workbook.
• Page switching is in fact an illusion, achieved
  through the use of freeze pane and sliding
  particular panels into view.
• There are two methods used to achieve this
  switching. One is a set of option buttons, one
  for each day. If you examine these (right-click,
  and assign macro), you will find that each button
  runs a different macro, named according to the
  day of the week..
• The pull down menu (bottom) achieves the same
  effect if you select a particular day, the
  appointments for that day come into view. In this
  case, it is achieved through the use of a single
  macro called selectDay.

9
Making a Booking

• To make a booking, select a stylist, a time and a
  day as follows
• Select Ann, at 1500 on Tuesday.
• Write in the name of a Customer in the space.
• Click on the button.

10
Making a Booking

• The customer name is written in the appointment
  book at the correct time
• A message appears to say that the booking was
  successful

11
Making a Booking

• If a time is selected when the stylist is not
  free (day off, lunch, tea break or already
  booked), then a message appears to say that the
  stylist is not available.

12
Searching for a Free Stylist

• Select a time and day on which the appointment is
  to be made.
• Click on the Search for Free Stylist button.
• The first free stylist is offered-in this case
  Clare.
• If you click No, a different stylist is
  offered.
• This will continue, until all available stylists
  have been exhausted, or you have made a booking.

13
Search for Free Times

• Select a stylist and a day on which the
  appointment is to be made. (The time will be
  ignored)
• Click on the Search for Free Times button.
• This will provide a list of the times that the
  stylist is free.
• This option does not make a booking.

14
Comments on the System

• You may have noticed that it is entirely possible
  to use the system without clicking any of the
  buttons. The user could simply write into the
  cells of the address book.
• However, it must be remembered that what is
  presented here is a much simplified system - it
  is an appointment book for one week, with
  one-hour appointments, starting on the hour.
• In reality the appointments might be anywhere
  between 15mins and 4 hours depending upon the
  type of task. The full appointment book would
  cover 52 weeks.
• In such a system, allowing busy operators to
  write directly into cells might be susceptible to
  operator errors.
• In addition, many of the features shown have been
  included simply to demonstrate different elements
  of programming.

15
Task 1

• Page Switching

16
Appointment Book 1

• First, close all current Excel Workbooks. This is
  so that you do not become confused as to which
  version of the workbook you are working on.
• Next, open up Appointment Book (Task 1).
• At first viewing, this looks like the Appointment
  Book you have been examining however, this is a
  non-functioning version with two macros only.

17
Adding the VB Toolbar

• Click on View-Toolbars and select Visual Basic
  Toolbar.
• The VB Toolbar Appears.
• Drag Drop the toolbar, merging it with toolbars
  at the top of the screen.

18
Examining the Appointments Book

• You should notice that
• The pull-down menus do not work.
• Only the top two options buttons work.
• None of the command buttons work

19
Pull Down Menus

• Scroll down the screen to rows 24-44.
• Here you will find the lists for the pull down
  menus.
• The first part of this task is to make these
  three work correctly.
• Right click on each of these in turn, and in
  turn, using Format Control, make them write to
  the cells
• B33 (Stylist),
• E33 (Times)
• H33 (Days)

HINT The Stylists menu take its list from cells
C3438, and writes to cell B33
Solution
20
Solution to Part 1
STYLISTS Input Range C34C38 Cell Link
B33 (solution shown here) TIME Input Range
F34F43 Cell Link E33 DAY Input Range
I34I39 Cell Link H33
21
Page Switching using the Options Buttons

• Currently only the top two buttons work.
• If you right-click on any of the buttons, you
  should notice that they all write to cell H33.
  This is the same cell that the Days Pull Down
  menu writes to.
• Check that these two different methods talk to
  one another, i.e. when Tuesday is clicked on the
  option button list, the selected day appears in
  the pull down menu, and vice-versa.

22
Switching Panels in and out of View 1

• Use the slider bar to slide the appointments
  panel along.
• You will notice that the workbook has been
  designed so that each day occupies exactly the
  right sized portion of the view pane.

The days appointments are constructed as
follows Monday Start at cell U1, occupies 10
cols Tuesday Start at cell AE1, occupies 10
cols Wednesday Start at cell AO1, occupies 10
cols, etc.
23
Switching Panels in and Out of View 2

• Now examine the two working Macros in Module 1 of
  the VB Editor Window these are called Monday and
  Tuesday respectively.
• You will notice that the macros use the Cells
  property when addressing cells.

Sub Monday() Cells(1, 21).Select End Sub Sub
Tuesday() Cells(1, 21).Select ActiveWindow.SmallSc
roll ToRight10 Cells(1, 31).Select End Sub

• The Tuesday macro
• Select cell (1,21)
• (row 1, column 21)
• This is Cell U1
• Next, scroll 10 columns to the right
• Select cell (1,31)
• (row 1, column 31)
• This is Cell AE1

Notes on Using Cell Ranges in Macros
24
Using Cells Ranges in Macros (1)

• There are two very important (and equivalent)
  ways in which
• cell ranges can be referred to within macros
• Range
• Range(U21).Select
• This uses the normal Excel Cell Reference
  approach.
• Note that the brackets inverted commas are
  necessary.
• Cells
• Cells(1, 21).Select
• This refers to cells in terms of their position
  from the top left
• hand corner of the current object (In this case
  Sheet1).
• The first value is the row, the second is the
  column.

25
Using Cells Ranges in Macros (2)

• These can be used to create alternative ways of
  addressing cells
• Sheets(Sheet2).Range(X3).Select
• Sheets(Sheet2).Cells(3,24).Select
• This will change the current Excel Worksheet to
  Sheet 2,
• then highlight cell X3
• Range(A5) 20
• Cells(5,1) 20
• This puts the value of 20 into cell A5 of the
  current worksheet.
• Range(B7) Sheets(Sheet3).Range(T2)
• Cells(7,2) Sheets(Sheet3).Cells(2,20)
• This puts the value from cell T2 of Sheet 3 into
  cell B7
• of the current worksheet.

26
Using Cells Ranges in Macros (3)

• Which method you use is dependent upon what you
  are trying to achieve.
• However, it is important to note that if you opt
  to use Range, then you must specify the cell
  reference explicitly, and you cannot change it.
• On the other hand, if you use the cells method,
  you can use variables within the code to refer to
  different cells on the worksheet, depending upon
  conditions.
• Example
• Row Range(A1)
• Cells(Row,2)X
• This reads a number from cell A1 of the current
  worksheet
• The Letter X is then inserted at that row in
  column 2.

27
Creating the firstnew macro

• We will create a new macro by cloning the Tuesday
  Macro.
• Copy the Tuesday Macro paste it underneath.
• Make the changes suggested.
• These changes simply ensure that the correct
  number of columns are slid along, and the correct
  top left hand corner of the panel is selected

Change Tuesday to Wednesday
Sub Tuesday() Cells(1, 21).Select ActiveWindow.Sma
llScroll ToRight10 Cells(1, 31).Select End Sub
Change 31 to 41
Change 10 to 20
28
Creating the secondnew macro

• Create another new macro by cloning the Tuesday
  Macro again.
• Copy the Tuesday Macro paste it underneath the
  Wednesday Macro.
• Make the changes suggested.
• These changes simply ensure that the correct
  number of columns are slid along, and the correct
  top left hand corner of the panel is selected

Change Tuesday to Thursday
Sub Tuesday() Cells(1, 21).Select ActiveWindow.Sma
llScroll ToRight10 Cells(1, 31).Select End Sub
Change 31 to 51
Change 10 to 30
29
Creating the Third Fourthnew macros

• Create two more new macros by cloning the Tuesday
  Macro again.
• Copy the Tuesday Macro paste it twice underneath
  the Thursday Macro.
• Make the changes suggested.
• These changes simply ensure that the correct
  number of columns are slid along, and the correct
  top left hand corner of the panel is selected

Change Tuesday to ???
Sub Tuesday() Cells(1, 21).Select ActiveWindow.Sma
llScroll ToRight10 Cells(1, 31).Select End Sub
Change 31 to ???
Change 10 to ???
30
FullSolution
Sub Wednesday() Cells(1, 21).Select ActiveWindow.
SmallScroll ToRight20 Cells(1, 41).Select End
Sub Sub Thursday() Cells(1, 21).Select ActiveWind
ow.SmallScroll ToRight30 Cells(1,
51).Select End Sub Sub Friday() Cells(1,
21).Select ActiveWindow.SmallScroll
ToRight40 Cells(1, 61).Select End Sub Sub
Saturday() Cells(1, 21).Select ActiveWindow.SmallS
croll ToRight50 Cells(1, 71).Select End Sub

• This the full set of macros.
• If you had difficulty writing these, the macros
  in the second panel can be copied pasted into
  Module 1.

Sub Monday() Cells(1, 21).Select End Sub Sub
Tuesday() Cells(1, 21).Select ActiveWindow.SmallSc
roll ToRight10 Cells(1, 31).Select End Sub
31
Assigning the new macros

• Right-Click on the Wednesday option button, and
  assign the Wednesday macro,
• Repeat this for the Thursday, Friday and Saturday
  option buttons
• Check that these work.

32
Page Switching usingthe Pull Down Menu

• The idea is that when we select the day using the
  pull-down menu, the page switches to that days
  sheet.
• This represents an entirely different kind of
  problem we currently have 6 different macros,
  and we are only allowed to assign one macro to
  this button.
• The secret is this this menu writes a value to
  cell H33 depending upon which item has been
  selected.

33
Writing a New Macro

• This time we will write the macro from the
  beginning.
• Underneath the last macro, type in Sub
  selectDay
• You will notice that the VB Editor recognises
  this as a new macro, and ends the macro with an
  End Sub line automatically.
• The first command is to read the value from cell
  H33, and store the value in a variable named d.
  Type in this line.
• Now further type in these two lines. These lines
  will select the correct macro, depending upon the
  value stored in the variable d.
• Clearly the macro is incomplete. Add another four
  lines for the 4 other days.

Sub selectDay() d Cells(33, 8) If d 1 Then
Monday If d 2 Then Tuesday End Sub
34
Assigning the Macro

• Finally assign the Macro to the pull down menu
  Right-Click, Assign Macro, then select
  selectDay.
• This should now work.
• If does not, check that you have completed the
  Macro Code correctly.

35
The selectDay Macro

• Sub selectDay()
• d Cells(33, 8)
• If d 1 Then Monday
• If d 2 Then Tuesday
• If d 3 Then Wednesday
• If d 4 Then Thursday
• If d 5 Then Friday
• If d 6 Then Saturday
• End Sub

• Here is the solution to the selectDay Macro.
  Please note the following
• The macro reads a value stores in the cell at row
  33, column 8 (cell H33)
• This value is used to select one of six macros to
  run. For example if the value is 1, then the
  Macro called Monday is run.
• Each of the six macros (Monday through Saturday)
  have already been defined and are written in the
  Workbook.

36
Supplementary work (1)

• In this presentation there are a number of points
  where we look at additional (an more complex)
  programming issues.
• If you are finding the presentation challenging
  so far, you may find these sections quite
  difficult, and you may be better off skipping
  them at first reading, and possibly returning to
  them later on.
• If you are the programming guru in your team,
  then you will definitely need to study each of
  these sections at some point.
• The first of these sections looks for a single
  formulaic approach to screen selection.

Return to Menu
Carry on to next section
37
A more sophisticated approach

• You may have noticed that there is a lot of
  repetition in the macros for each day (Monday,
  Tuesday etc.)
• With a bit more thought we might have saved some
  effort in writing these macros.
• For example, compare the macros for Tuesday
• With the Macro for Wednesday

Cells(1, 21).Select ActiveWindow.SmallScroll
ToRight10 Cells(1, 31).Select
This is run when d 2
Cells(1, 21).Select ActiveWindow.SmallScroll
ToRight20 Cells(1, 41).Select
This is run when d 3
38
Looking for Connections
Try to find the connection between the value of
d, The amounts scrolled the column
selected When d 2, We scroll 10 select
column 31 When d 3, We scroll 20 select
column 41 When d 4, We scroll 30 select
column 51 When d 5, We scroll 40 select
column 61
Hint Try reducing or increasing the value of d
by 1
39
Manipulating Variables
The secret here is to note that if we calculate
(d - 1) We can scroll a value equal to 10 (d
- 1) And if we calculate (d 1) We can select
column 10 (d 1) 1

• d (d-1) 10(d-1) (d1) 10(d1)1
• 0 0 2 21
• 1 10 3 31
• 2 20 4 41
• Etc.

40
Writing the Macro

• We will now consider writing a new macro called
  selectDay2
• This will do the following
• Read the value of d from H33
• Select cell U1
• Scroll to the right the number of cells equal to
  10 (d - 1)
• Select the cell at row 1, column equal to 10 (d
  1) 1
• Write this macro and assign it to the pull down
  menu.

41
The selectDay2 Macro

• Here is the solution to the Macro
• The solution can be examined in Appointment Book
  (Task 1) Solution

Sub selectDay2() d Cells(33, 8).Value Cells(1,
21).Select ActiveWindow.SmallScroll ToRight(d
- 1) 10 Cells(1, 10 (d 1) 1).Select End
Sub
42
Task 2

• Automating Booking

43
The Booking Macro Understanding the Problem
When the full booking macro is complete, it will
do the following 1. Read the values output by
each of the pull-down menus for Stylist, Time
and Day 2. Read the Customer Name 3. Write the
customer Name in the appropriate cell on the
appropriate appointment sheet.
44
Small beginnings
If you did not succeed in completing the
previous section, you need to download
Appointment Book (Task 1) Solution and start with
that.

• To begin with, we will only consider bookings on
  Mondays. Ensure that the days menu reads
  Monday.
• The first free stylist is Bernie, free at 9am.
  Set the menus to read this as above.
• Write in the name of a customer. Any name will
  do.
• Now scroll down to the tables below the menus and
  examine where the values are stored.

45
The Pointer Cells
Cell E33
Cells F34 F43
Cell B33
Cell H33
Cells C34 C38
Cells I34 I39
46
Beginning the Macro

• First of all, create the shell of a new macro
  called makeBooking
• Secondly, we will define three variables
• appStylist, which is the index number of the
  stylist required
• appTime, the index number of the time required
• Customer, the name of the customer to be written.

Sub makeBooking() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") End Sub
47
Understanding the Appointment Sheet

• The first cell (Ann at 9am) in the Monday Sheet
  is cell X9,
• Row 9
• Column 24
• (appTime 1, appStylist 1)
• The cell for Bernie at 11am would be Y11,
• Row 11
• Column 25
• (appTime 3, appStylist 2)
• We will therefore access the correct cell by
  using the following formula
• Row 8 apptime
• Column 23 appStylist

48
Completing the makeBooking Macro
Sub makeBooking() appStylist Range("B33") appTim
e Range("E33") customer Range("K30") appRow
8 appTime appCol 23 appStylist Cells(appR
ow, appCol) customer End Sub

• Within the makeBooking Macro, create three new
  lines
• appRow calculates the row to be written to
• appCol calculates the column to be written to
• The final line actually writes the name of the
  customer into the appropriate cell of the
  appointment book.
• Check that this macro works before moving on.
  You will need to assign the macro by
  right-clicking on the Make Booking button .

49
Improving the makeBooking Macro
Sub makeBooking() appStylist Range("B33") appTim
e Range("E33") customer Range("K30") appRow
8 appTime appCol 23 appStylist Cells(appRo
w, appCol) customer End Sub

• The makeBooking Macro should work, and will write
  to the cells required.
• Unfortunately it will also overwrite
  appointments, and will schedule appointments on
  days off.
• To improve, before we write to the cell, we need
  to check whether there is anything written there
  already.
• An If structure will do this for us.

Notes on IF Statements
50
Types of If Statements

• There are three different versions of an if
  Statement available in Visual Basic
• A Single Line Version
• A Multiple Line Version
• An alternative actions version
• The version that you require will be dependent
  upon circumstances.

51
Using If Statements(Single Line Version)

• This is the simplest type of statement, and has
  the form
• If condition Then action
• For example
• If x 1 Then Range(A1) Hello World
• If Cells(2,5) Then Cells(2,5)Occupied

If the value of x is 1, then the message Hello
World is written into cell A1
If cell E2 is empty then the message Occupied
is now written into it
52
Using If Statements(Multiple Line Version)

• This is an extension of the previous version,
  which allows for more than one action to be
  carried out
• If condition Then
• action(s)
• EndIf
• For example
• If Cells(2,5) Then
• Msgbox Cell E2 was previously empty
• Cells(2,5)Occupied
• Msgbox However, it is now occupied
• endIf

If cell E2 is empty then the following occurs 1.
A message is sent to the user to say the cell is
empty 2. Occupied is now written into I the
cell 3. A further message is sent to the user to
say the cell is now occupied.
53
Using If Statements(Alternative Actions Version)

• This is a more sophisticated version, which
  allows for alternative courses of actions
  depending upon whether the condition is true or
  false
• If condition Then
• action(s) 1
• Else
• action(s) 2
• EndIf
• For example
• If Range(A1) Then
• Msgbox Cell A1 is empty
• Else
• x Range(A1)
• Msgbox The value in Cell A1 is x
• EndIf

If cell A1 is empty then a message is sent to the
user to alert them to the fact. If the cell is
not empty, then the message tells the user what
the value in cell A1 actually is.
54
Amending the makeBooking Macro

• First of all, clone the makeBooking Macro, and
  rename it makeBooking2
• In order to construct an appropriate if
  statement, we need to add a line which read the
  value of the cell which we are about to write to,
  and put it in an appropriate variable.
• We will call this variable entry
• In the space, write a line which does this.
• Hint the cell you need to be checking is on the
  line below!

Sub makeBooking2() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") appRow 8 appTime appCol 23
appStylist Cells(appRow, appCol)
customer End Sub
55
Further Amendments to the makeBooking Macro
Sub makeBooking2() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") appRow 8 appTime appCol 23
appStylist Entry Cells(appRow,
appCol) Cells(appRow, appCol) customer End
Sub

• We will now insert an If statement.
• This if statement will test whether the variable
  entry is equal to a blank string (i.e. entry
  )
• If it is blank, write the customer name in the
  cell, and send a message to the user to say that
  the appointment has been made.

56
Final Amendment to the makeBooking Macro
Sub makeBooking2() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") appRow 8 appTime appCol 23
appStylist Entry Cells(appRow, appCol) If
cells(appRow,appCol) Then Cells(appRow,
appCol) customer Msgbox Appointment
made End If End Sub

• We will now insert an alternative course of
  action, if the condition is not true, i.e. there
  is something already written in the cell.
• This should be a statement to the effect that the
  stylist is not available at that time, and should
  be prefaced with an Else
• You will need to re-assign this macro to the Make
  Booking button.

57
The Completed makeBooking2 Macro
Sub makeBooking2() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") appRow 8 appTime appCol 23
appStylist Entry Cells(appRow, appCol) If
cells(appRow,appCol) Then Cells(appRow,
appCol) customer Msgbox Appointment
made Else Msgbox Stylist not available at
that time End If End Sub

• This effectively completes the Monday Bookings
  Macro, and your version should work effectively.
• Please check this carefully. If it does not,
  please check line by line against the code here,
  and make any changes.
• You may now continue to Supplementary Work 2
  where we amend the macro to make it work for all
  days of the week.
• Alternatively, you can or carry on to the next
  section which looks at Searching

Carry on to next section
Return to Menu
58
Towards a Fuller makeBooking Macro
Sub makeBooking3() appStylist
Range("B33") appTime Range("E33") customer
Range("K30") appRow 8 appTime appCol 23
appStylist Entry Cells(appRow, appCol) If
cells(appRow,appCol) Then Cells(appRow,
appCol) customer Msgbox Appointment
made Else Msgbox Stylist not available at
that time End If End Sub

• Here we will make the code work for all days in
  the appointment book.
• First of all, clone the macro, and change the
  name to Makebooking3
• The only changes necessary are
• Use the variable appDay to store the index number
  of the day selected (this is in cell H33)
• Amend appCol to incorporate that value, so that
  appCol now refers to the correct stylist on the
  correct day.
• HINT Each appointment book page is 10
  columns.

59
makeBooking3The full Macro
Sub makeBooking3() appStylist
Range("B33") appTime Range("E33") appDay
Range(H33) customer Range("K30") appRow 8
appTime appCol 23 appStylist 10 (appDay
1) Entry Cells(appRow, appCol) If
cells(appRow,appCol) Then Cells(appRow,
appCol) customer Msgbox Appointment
made Else Msgbox Stylist not available at
that time End If End Sub

• Here is the finished macro
• Note how we needed to reduce appDay by 1 in order
  to add on the correct number of 10-column pages.
• You should assign this macro to the button and
  check it thoroughly.

60
Task 3

• Searching Using While Wend

61
Searching for a StylistUnderstanding the Task

• When booking an appointment, many people are only
  concerned to get an appointment at a particular
  time, so we need to know which stylists are
  currently free at that time.
• Again here, we will begin by only considering
  bookings for Monday, and extend this later.
• The strategy will be to search through a
  particular row until we come to a free box.

If you did not complete all the parts of the
previous section, you should download Appointment
Book (Task 2) Solution and start with that.
62
Beginning the searchForStylist Macro

• First of all, create the shell of a new macro
  called searchForStylist
• Secondly, we will use four variables
• appTime, the index number of the time required
• appRow, row number corresponding to the time
  required. (These values have been worked out in
  previous macros)
• appStylist which is the index number of the
  stylist required. In this case we will start at
  stylist 1.
• appCol, the column number of the stylist.

Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist End Sub
63
Searching Strategies

• There are two basic strategies to use when
  searching
• Search through the items until you find the items
  that you are looking for and then stop. This can
  be programmed using the While Wend programming
  structure.
• Search through all the items, and compile a list
  of all those which match your requirements. This
  can be programmed using the For Next structure.
• In this case, our strategy will be to search
  through all the stylists at a particular time,
  until we find one that is free, in which case we
  book an appointment.
• To do this we shall use the While Wend
  structure.

Notes on While - Wend
64
Using While - Wend

• A condition is tested at the start of the While
  loop. If this condition is true, the commands
  inside the loop are executed. When Wend is
  reached, processing jumps back to the While
  condition, and tests it again. When the condition
  is false, then processing jumps to the command
  after the Wend statement.
• While condition
• action(s)
• Wend
• For example
• col 1
• While Cells(1, col)
• Cells (1, col).Select
• Msgbox This Cell is empty
• col col 1
• Wend
• Msgbox First non-empty cell at column col

Firstly, we start at row 1.
While the cell in the first row of the current
column is empty, carry out the actions below
Select that particular cell Put up a message box
to say that the cell is empty Increment the
column number by 1
Finally, when a non-empty cell has been reached,
we put up a message saying what column it is in.
65
Amending the searchForStylist Macro
Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While
Wend End Sub

• We will now insert a While Wend structure to do
  the searching.
• While the cell under scrutiny is NOT empty, the
  following actions should be carried out
• Increment the stylist by 1
• Recalculate appCol.
• When an empty cell is found, send a message to
  the user stating the number of the stylist that
  is free.
• NB The search MUST end, as the column after the
  final stylist 29 is empty.

66
Hints for the searchForStylist Macro
Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While
Wend End Sub

• Here are some hints which might help you without
  telling you exactly what to write in the four
  boxes.
• You should make every effort to get these right
  before giving up and looking at the solution.

Use Cells (appRow, AppCol), and the ltgt sign.
appSylist should be made equal to 1 more than
itself
appCol should be calculated again exactly as in
the line above While
The message box should combine "Stylist no. ,
the value of appStylist and the words " is
free. They should be glued together with an
sign.
67
The searchForStylist MacroThe first solution -
nearly
Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While Cells(appRow,
appCol) ltgt "" appStylist appStylist 1
appCol 23 appStylist Wend MsgBox "Stylist no.
" appStylist " is free" End Sub

• This is the first almost working version of the
  search macro.
• It will actually find a free stylist, but that
  stylist might be at column 29 (off the end of the
  appointment sheet), and will be stylist number 6
  which we do not have.
• We will now amend this so that if the stylist is
  6, we do not book, but send a message to say
  there are no free stylists.

68
Completing the searchForStylist Macro
Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While Cells(appRow,
appCol) ltgt "" appStylist appStylist 1
appCol 23 appStylist Wend MsgBox "Stylist
no. " appStylist " is free" End Sub

• In the space below we will insert an If structure
  to say the following
• If stylist number is 6, then
• Put up a message to say there are no free
  stylists.
• Else
• Put the stylist number in cell B33
• Send a message to say that the stylist is free.

69
Completing searchForStylist
Sub searchForStylist() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While Cells(appRow,
appCol) ltgt "" appStylist appStylist 1
appCol 23 appStylist Wend If appStylist 6
Then MsgBox "No stylist available at that
time" Else Range("B33") appStylist
MsgBox "Stylist no. " appStylist " is
free" End If End Sub

• Although this effectively sets up the macro to
  book, it does not actually book.
• This can be affected simply by now clicking on
  the Make Booking macro.
• Alternatively, the command makeBooking2 can be
  inserted either after, or instead of the message
  saying that the stylist is free.
• The supplementary work which follows improves
  this rather crude device, and extends bookings to
  other days
• If you do not wish to do this, carry on to the
  next section.

Return to Menu
Carry on to next section
70
Supplementary Work 3

• There are two separate improvements we can are
  going to make to the macro
• Firstly we will extend the the bookings to days
  other than Monday. This is fairly straightforward
  and follows the method used in previous
  supplementary work
• Secondly, we will to allow the user to reject a
  particular stylist as we go through the list of
  free stylists, going on to the next one, and
  booking an appointment with the particular
  stylist of their choice,

71
Extending searchForStylist to other days
Sub searchForStylist2() appTime
Range("E33") appRow 8 appTime appStylist
1 appCol 23 appStylist While Cells(appRow,
appCol) ltgt "" appStylist appStylist 1
appCol 23 appStylist Wend If appStylist
6 Then MsgBox "No stylist available at that
time" Else Range("B33") appStylist
MsgBox "Stylist no. " appStylist " is
free" End If End Sub

• This can be done by adding Firstly clone the
  macro, and rename it searchForStylist2. Now add
  code in three separate places
• Read appDay from cell H33
• In the two places where appCol is calculated, put
  in an additional term which shift it 10 columns
  to the right depending upon the value of appDay
  (Remember you will need to use one less than the
  current value of appDay to do this) carry on to
  the next section.
• (This is the same as in Supplementary Work 2)

Return to Menu
Carry on to next section
72
The amended searchForStylist2 Macro
Sub searchForStylist2() appTime
Range("E33") appDay Range("H33") appRow 8
appTime appStylist 1 appCol 23 appStylist
10 (appDay - 1) While Cells(appRow, appCol) ltgt
"" appStylist appStylist 1 appCol
23 appStylist 10 (appDay - 1) Wend If
appStylist 6 Then MsgBox "No stylist
available at that time" Else Range("B33")
appStylist MsgBox "Stylist no. " appStylist
" is free" End If End Sub

• This is the completed version of the macro.
• It still only searches for a free stylist on the
  day and time specified, changing the value in the
  pull-down menu box.
• A further improvement now follows.

73
A New Structure

• Set up a New Macro and called searchforStylist3
• Set up the variables as before
• 
  
• Change the Use a While Wend loop to go through
  the stylists 1 to 5 (ie less than 6)
• The final line will be invoked if the stylists
  index number reaches the value of 6

appTime Range("E33") appDay
Range("H33") appRow 8 appTime appStylist 1
While appStylist lt 6 appStylist appStylist
1 Wend
Some Coding will be written in here.
If appStylist 6 Then MsgBox "No suitable
stylists free at that time"
74
Inside the While Loop
While appStylist lt 6 appStylist
appStylist 1 Wend

• Calculate the column to be accessed.
• If that cell is empty then
• Determine the Stylists name
• Change the current setting of the pull down menu
  box
• carry out a Query Booking task

appCol 23 appStylist 10 (appDay - 1) If
Cells(appRow, appCol) "" Then Stylist
Cells(33 appStylist, 3) Range("B33")
appStylist End If.
Query Booking Task
75
The Query Bookings Task

• This is the structure to be put inside the If
  Statement, embedded within the While Loop.

Reply MsgBox(Stylist " is free. Do you wish
to book?", vbYesNo) If Reply vbYes Then
makebooking3 appStylist 6 End If
This is a different kind of Message box
statement, which asks for a button click (Yes or
No) response from the user.
If reply is that the Yes button has been
clicked, then the following actions are carried
out
Notes on Message boxes
A booking is made.
The number of the stylist is now set beyond the
range 1- 5.
76
A note on Message Boxes 1

• There are two different versions of message boxes
  in Visual Basic
• Version 1
• MsgBox Hello World
• Version 2
• Reply MsgBox(Hello World,VBYesNo)

This is the simplest version of the statement,
used for giving information to the user.
This is a different form of the statement, which
changes the nature of the message box into a
dialogue box. The user is required to provide a
response in the form of a button click. The
brackets are essential, as is the variable name.
77
A note on Message Boxes 2

• Version 2 comes in many different forms
• Reply MsgBox(Hello World,VBYesNoCancel)
• Reply MsgBox(Hello World,VBCritical)
• Reply MsgBox(Hello World,VBRetryCancel)

This is just a selection of the different forms
of the statement. The response from the user is
contained in the variable Reply, which can then
be tested to see whether it is equal to VBYes,
VBNo, VBRetry, VBCancel, VBOK etc.
78
The full code inside the While Loop
While appStylist lt 6 appStylist
appStylist 1 Wend
appCol 23 appStylist 10 (appDay - 1)
If Cells(appRow, appCol) "" Then Stylist
Cells(33 appStylist, 3) Range("B33")
appStylist Reply MsgBox(Stylist "
is free. Do you wish to book?", vbYesNo)
If Reply vbYes Then
makebooking3 appStylist 6
End If End If.

• Two important questions are
• What stops more than one booking being made on
  each query?
• If a booking is made, what prevents the no free
  stylists message from occurring at the end?

79
The full searchForStylist3 Macro
Sub searchForStylist3() appTime
Range("E33") appDay Range("H33") appRow 8
appTime appStylist 1 While appStylist lt 6
appCol 23 appStylist 10 (appDay - 1)
If Cells(appRow, appCol) "" Then Stylist
Cells(33 appStylist, 3) Range("B33")
appStylist Reply MsgBox(Stylist "
is free. Do you wish to book?", vbYesNo)
If Reply vbYes Then makebooking3
appStylist 6 End If
End If appStylist appStylist
1 Wend If appStylist 6 Then MsgBox "No suitable
stylists free at that time" End Sub
80
Task 4

• Searching Using For Next

81
Search for Free Times
If you did not complete all the parts of the
previous section, you should download Appointment
Book (Task 3) Solution and start with that.

• This is the second type of search, and is
  designed for use in the situation where a
  customer wishes to have a particular stylist, and
  is willing to come at a time available.
• The strategy here will be to search every
  available time, and present the user with a
  complete list.
• Again here, we will start with Monday, and then
  extend this to other days in the week.
• The strategy will use a For Next loop.

Notes on For Next Loops
82
Using For - Next

• The For Next Structure allows the programmer
  to process a set number of repetitions of a
  particular event
• For variablename lowervalue To uppervalue
• action(s)
• Next variablename
• For example
• For row 1 to 8
• If cells(row, 1) then
• Msgbox Cell number row is empty
• End If
• Next row

Carry out the set of actions in between the
For-Next commands 8 times For the first time
row 1, For the second time, row 2 etc.
If the row is empty, put up a message box to say
so.
When this line is reached, processing will return
to the For line above, and the row variable
incremented. If row gets to 9, processing stops
83
The Search For Free Times strategy

• Read the index number of the stylist from cell
  B33
• Set the list of free times to blank.
• For each timeslot available (1 to 8)
• Work out the clock time
• Calculate the row co-ordinates of the
  cell
• Calculate the column co-ordinates of the cell
• If the cell is blank, add the time to the list of
  free times
• Display the list of free times.

appStylist Range(B33)
listOfTimes
For appTime 1 to 8 Next appTime
clockTime(33appTime,6) appRow 8
appTime appCol 23 appStylist If Cells(appRow,
appCol) "" Then listOfTimes
listOfTimes clockTime End If
MsgBox "Times available are " listOfTimes
84
The Basic searchForFree Macro
Type in this macro and assign it to the Search
for free stylist button.
Sub searchForFree() appStylist
Range("B33") listOfTimes "" For appTime 1 To
8 clockTime Cells(33 appTime, 6)
appRow 8 appTime appCol 23
appStylist If Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime
End If Next appTime MsgBox "Times available are
" listOfTimes End Sub
When you click on the button, you will find that
it works, but is not very user friendly.
We are going to amend these two lines so that the
list of times is easier to read. We will use a
Control Character to do this.
Notes on Control Characters
85
Control Characters

• In message boxes, input boxes and other elements
  where text is displayed on the screen, you may
  need to insert line-breaks and other screen
  control characters in text to make them
  readable.
• The chr() function in VB takes values form 0-255
  These are interpreted as ASCII codes, the first
  32 are control characters of various types.
• The letter A has an ASCII code of 65, i.e.
  chr(65) A
• The control character we are going to use is
  chr(10), which is called a linefeed
  basically, to move down onto the next line to
  display the next piece of information. This will
  be inserted into the text.

86
Amending the searchForFree Macro
Sub searchForFree() appStylist
Range("B33") listOfTimes "" For appTime 1 To
8 clockTime Cells(33 appTime, 6)
appRow 8 appTime appCol 23
appStylist If Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime
End If Next appTime MsgBox "Times available are
" listOfTimes End Sub
Make the following two changes to the macro
listOfTimes listOfTimes clockTime chr(10)
MsgBox "Times available are " chr(10)
listOfTimes
87
The completed searchForFree Macro
Sub searchForFree() appStylist
Range("B33") listOfTimes "" For appTime 1 To
8 clockTime Cells(33 appTime, 6)
appRow 8 appTime appCol 23
appStylist If Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime
chr(10) End If Next appTime MsgBox "Times
available are " chr(10) listOfTimes End Sub

• This macro is now completed.
• Clearly it is still very crude, and there are
  several obvious improvements we could make.
• These improvements are addressed in Supplementary
  Work 4 (optional) which follows

Return to Menu
Carry on to next section
88
Supplementary Work 4

• Clearly there are several improvements we could
  make.
• The first improvement is simply to ensure that we
  can search for free times on any day available.
• The second improvement is to ensure that if there
  are no times available, a suitable message box is
  displayed.

89
Extending searchForFree to other days
Clone the macro, call it searchforFree2, and add
two lines as follows
Sub searchForFree2() appStylist
Range("B33") listOfTimes "" For appTime 1 To
8 clockTime Cells(33 appTime, 6)
appRow 8 appTime appCol 23
appStylist If Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime
chr(10) End If Next appTime MsgBox "Times
available are " chr(10) listOfTimes End Sub
Read appDay from the correct cell
Add an additional term to this line, to calculate
the correct appCol taking account of the value in
Appday
90
Extending searchForFree to other days
Sub searchForFree2() appStylist
Range("B33") appDay Range(H33) listOfTimes
"" For appTime 1 To 8 clockTime Cells(33
appTime, 6) appRow 8 appTime appCol
23 appStylist 10 (appDay 1) If
Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime chr(10)
End If Next appTime MsgBox "Times available
are " chr(10) listOfTimes End Sub
This is the solution. For details of why these
work, refer back to previous supplementary work!
Now improve this message by producing two
versions. If the list of times is blank, print a
message to say there are no free times, or else
print this line.
91
Extending searchForFree to other days
Sub searchForFree2() appStylist
Range("B33") appDay Range(H33) listOfTimes
"" For appTime 1 To 8 clockTime Cells(33
appTime, 6) appRow 8 appTime appCol
23 appStylist 10 (appDay 1) If
Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime chr(10)
End If Next appTime MsgBox "Times available
are " chr(10) listOfTimes End Sub
This is the solution. For details of why these
work, refer back to previous supplementary work!
Now improve this message by producing two
versions. If the list of times is blank, print a
message to say there are no free times, or else
print this line.
92
The Completed SearchForFree2 Macro
Sub searchForFree2() appStylist
Range("B33") appDay Range(H33) listOfTimes
"" For appTime 1 To 8 clockTime Cells(33
appTime, 6) appRow 8 appTime appCol
23 appStylist 10 (appDay 1) If
Cells(appRow, appCol) "" Then
listOfTimes listOfTimes clockTime chr(10)
End If Next appTime If listOfTimes "" Then
MsgBox "No times available" Else MsgBox
"Times available are " Chr(10)
listOfTimes End If End Sub
This is the final version we will look at.There
are clearly other improvements that we could
make, but we will stop here.
93
What Next?
94
What Next?

• Formative Activity 10 New builds on these ideas,
  creating a database of customers and cars for a
  Car Hire Company.