IT Business Applications

1
IT Business Applications

• Week 20System Security
• The Document Object Model Revisited

This Presentation is in Self-Study Form. Press F5
to Begin the Presentation
2
Session Objectives

• The purpose of this presentation is to
• Discuss the options available in the Excel
  Environment which contribute to System Security
• Enhance understanding of the Excel Document
  Object Model
• Examine the ways in which the Excel Document
  Object Model can be used to incorporate security
  features into applications

3
Menu

• Excels Built-in Security Options
• The Excel Document Object Model
• Using Macros to provide Security Features
• What Happens Now?

4
Excels Built-in Security Options
5
A Note about Security

• By Security we mean the maintenance of the
  integrity of the
• Information System, its features and its data,
  protecting it from
• Human error  e.g. entering incorrect
  transactions failing to spot and correct errors
  processing the wrong information accidentally
  deleting data
• Technical errors  e.g. hardware that fails or
  software that crashes during transaction
  processing
• Data Protection Violations - incidents
  (deliberate or accidental) where sensitive data
  is accessed. possibly contrary to the DPA.
• Fraud - deliberate attempts to corrupt or amend
  previously legitimate data and information
• Commercial espionage e.g. competitors
  deliberately gaining access to commercially-sensit
  ive data (e.g. customer details
  pricing)
• Malicious damage where an employee or other
  person deliberately sets out to destroy or
  damage data and systems

6
Security in this Session

• In this session, we will only look at protecting
  the information system from
• Human Error
• Data Protection Violations
• Fraud

7
Features within Excel

• Excel has several features which can be used to
  ensure that data is more secure
• Data Validation
• Cell Protection
• Password Protection

8
Data Validation 1

• Data Validation is useful for ensuring that users
  do not enter incorrect or invalid data into
  tables.
• It allows for
• Specification of what is an allowable input
• A User message when to cell is clicked
• An error message if incorrect data is entered.

9
Data Validation 2

• When the cell is selected, a message appears,
  directing the user to inputs particular values.
• In addition, if the user fails to input correct
  values, these are rejected, and the user is
  directed to attempt another input.
• This can ensure that correct data is entered.

10
Cell Protection

• The text and formulae in individual cells may be
  locked from view or even hidden from view.
• This feature only becomes enabled when the Sheet
  is Password Protected.

11
Password Protection

• Password Protection can be afforded to Individual
  Sheets, Workbooks, and Macro Code
• The protection afforded is quite good, although
  there are a number of tools available via the
  internet which will allow hackers to access
  applications protected in this manner.

12
Other Protection Measures

• Security of Data can be enhanced by making the
  Visible property of the sheets sheets Hidden (not
  immediately visible in Excel) or VeryHidden (does
  not appear except in the Project Explorer)
• In addition, the Scroll Access Area of a
  Worksheet can be restricted.
• This can be done by changing the ScrollArea
  Property of the worksheet, either directly in the
  Worksheet Properties , or through a macro code.

13
The Usefulness of in-built Security measures

• Most of these in-built security measures are
  helpful in avoiding accidental user errors in
  input, or in preventing the casual user from
  accessing sensitive data.
• They are not guaranteed to prevent the serious
  hacker from accessing data if he/she is so
  determined.

14
A Further Word of Caution

• If you decide password protect any part of your
  application, you need to be very careful that you
  do not lock yourself out of the system, losing
  your work.
• Using a blank password is the safest method
  only when the system is complete should you be
  including actual password protection.
• Even then, you should consider using the same
  password throughout the system, so that you do
  not accidentally lock yourself out of part of it.
  
• In a real system, with multiple passwords, you
  would need to devise a secure method of storing
  these, which you as developer could access, so
  that if users forgot their passwords, the system
  could still be accessed, and these passwords
  changed.

15
The Excel Document Object Model
16
Object-Oriented Programming

• As you have seen, Excel VBA deals with objects,
  is event-driven and works primarily by changing
  the properties of objects. To some extent,
  therefore we could regard VBA as
  object-oriented.
• However, VBA is not a true Object-Oriented
  language. This can be seen in the fact that
  although we have spent about 4 weeks programming,
  we have never once
• Defined a class
• Declared variables
• This would be unheard of in a true
  Object-Oriented Environment

17
Classes Collections

• However, although we have never defined a class
  of objects, we have been working with classes of
  objects within Excel they are called
  collections.
• For example Sheets is a collection of objects
  within Excel, whose members are the different
  pages in a workbook. Individual objects within
  the collection can be identified in different
  ways
• Sheets(Sheet1).Select
• This selects the particular worksheet from the
  Sheets collection with the name Sheet1

18
Defining Classes and Variables

• In fact, it is possible to define your own
  collections within VBA, and to force all
  variables to be declared.
• The latter is good programming practice, as it
  avoids confusion, and allows other programmers to
  interpret and amend your code more easily.
• We have not done this so far, as our way in to
  the VBA environment has primarily been through
  recorded macros, and these recorded macros do not
  declare variables.

19
A Summary of the DOM

• The Excel DOM (Document Object Model) consists
  of the following
• Objects (These are the Nouns in VBA)
• What is an object?
• Collections (These are the Collective Nouns in
  VBA)
• What is a collection?
• Properties (These are the Adjectives in VBA)
• What is a Property?
• Methods (These are the Verbs in VBA)
• What is a Method?
• Events (These are the Triggers in VBA)
• What is an Event?

20
What is an Object?

• An object in VBA is something which can be
  changed, manipulated or interrogated in some way
  by software. Some examples of objects are
• A Workbook Object
• Workbooks(Booking)
• A Chart Object
• Charts(Chart1)
• A Range Object
• Range(A1B5)
• There are over two hundred different types of
  objects within the Excel DOM.

21
Objects in the VBA Environment

• Switching to the VBA Environment, we can
  immediately see a number of objects, and we can
  see the code that is associated with them.

• In Project Explorer we can see the following
• A workbook Object called Book1
• Three worksheet objects called Sheet1, Sheet2 and
  Sheet3
• A User Form Object

22
More Objects

• A User Form is an Object, and so are the ActiveX
  Controls on its surface. Some examples of these
  objects are
• A Text Box Object
• TextBox1
• A Label Object
• Label3
• A SpinButton Object
• SpinButton5
• It is possible to rename these objects so that
  when we refer to them their purpose is made
  clear.
• e.g Rename UserForm1 to be fmLogin

23
Referring to Objects (1)

• An object in VBA can be invoked or manipulated
  in different ways.
• For example a Range Object may be referred to as
  a group of cells
• Range(A1B4)
• On the other hand, if the Range A1B4 has been
  named dataTable, we could refer to it as
• Range(dataTable)

dataTable is its name in the Excel Environment
Range is the particular type of Object
24
Referring to Objects (2)

• Many currently selected objects may be referred
  to by prefixing Active to their names
• ActiveWorkbook, ActiveSheet, ActiveCell etc..
• For example
• fileName ActiveWorkbook.Name
• ActiveSheet.Range(A1) Name of workbook
  is
• ActiveCell.Value fileName
• In addition, Selection is used to refer to the
  currently selected Range
• Selection.Delete

NB There are one or two anomalies, for example
ActiveWorksheet is not an allowable object.
25
Referring to Objects (3)

• ActiveX Objects on Worksheets or User Forms may
  need to be given their Full Name, comprising the
  form on which they are located, followed by their
  name.
• For example, when assigning a piece of text to a
  textbox on a user form
• Userform3.TextBox2.Text Hello World
• or, transferring a value from a pull-down menu
  to a cell in a Worksheet
• Sheets(Cars).Cells(1,3)
• Userform1.ComboBox3.Value

26
The Application Object

• The Excel Environment is regarded as an Object in
  its own right, and can be manipulated and
  changed.
• Application.Caption Tonys Version of Excel
• This changes the title bar of Excel at the Top
  of the screen.
• Application.CellDragAndDrop False
• This prevents the user from using drag drop
  as an allowable mouse manoeuvre.

27
What is a Collection?

• Many Objects belong to Collections
• For example, when you open a new Workbook, it
  looks like this
• The Workbook Object contains a Worksheets
  Collection. This collection is itself an object.
• This Worksheets Collection contains three
  objects. We can address each of these by, for
  example
• Worksheets(Sheet1)

28
What Collections are there?

• Here are some examples of Collections
• The Workbooks object is a Collection consisting
  of all the workbooks currently open.
• The Worksheets object is a Collection containing
  all the worksheet objects in the current
  Workbook.
• The Charts object is a Collection of all the
  chart objects in the workbook.
• Workbook(Book1.xls).Charts(Chart3)
• The Range object contains actual cells on a
  Worksheet under consideration.
• Workbooks(Cars.xls).Worksheets(Data).Range(A2
  B5)

29
Addressing Objects within a Collection (1)

• As we have seen, you can address objects by name
  or by their position in the collection
• In this Workbook, we could access the
• Cars Worksheet by
• Sheets(Cars)
• Or
• Sheets(2)
• An Objects position in the collection is
  initially specified by Excel it may not be
  completely obvious what that position is a
  worksheet called Sheet3 may not in fact be the
  3rd worksheet in the collection.

30
Addressing Objects within a Collection (2)

• Each objects full name will be given by tracing
  its line of descent up through the parent
  object to which it belongs.
• Each object will be separated by a full stop,
  e.g
• Workbooks(Cars.xls).Worksheets(Login).Range(
  A2B5)
• If you omit references
• to the parents,
• VBA will default to the currently Active
  Worksheet
• e.g. Range(A2B5)
• Will refer to whatever worksheet of the current
  workbook which is presently selected.

31
Addressing Objects within a collection (3)

• One way of addressing all objects in a
  Collection is to use a variant of the For Next
  structure.
• For each sh in Worksheets
• Msgbox sh.Name
• Next sh
• This For each structure will run through all
  of the sheets, with the variable taking on the
  identity of the different objects on each
  occasion. The message box will show the names of
  each of the worksheets in turn.

32
What is the difference between Sheets and
Worksheets?

• The Sheets collection is all the pages in a
  Workbook, whatever they might be (Charts,
  Drawings, Excel Worksheets)
• The Worksheets Collection is just the set of all
  Excel Worksheets (i.e. those which have cells on
  which you can do calculations!)
• One object can in fact belong to either
  collection. If you refer to the object by name,
  this will avoid confusion.
• Worksheets(Cars) and Sheets(Cars) will always
  give the same object.
• Worksheets(1) and Sheets(1) may yield two
  different objects.

33
Relationships
Note The objects in the Sheets Collection are
all of the Worksheet Objects TOGETHER WITH the
Chart Objects.
34
What is a Property?

• Properties are the physical characteristics of
  objects.
• Some of these can be changed directly for
  example a Worksheet Range Object has a RowHeight
  property and a ColumnWidth property, which can be
  used to modify the dimensions of cells within the
  Range.
• On the other hand, some properties are read-only
  a Workbook has a Name Property, but this property
  can only be read, not changed.
• However, the Name property of a Worksheet can be
  both read and changed
• Worksheets(Sheet1).Name Cars

Object to be acted upon
Property to be changed
New value of the Property
35
Reading Writing Properties (1)
As with other references, the Object part of the
reference is separated from the Property by a
full stop.

• In the following example
• H ActiveCell.RowHeight
• W ActiveCell.ColumnWidth
• This reads the row column sizes of the
  currently selected cell
• ActiveCell.RowHeight 2 H
• ActiveCell.ColumnWidth 2 W
• This then doubles the current size of the
  selected cell.

36
Reading Writing Properties (2)

• We can use properties to transfer information
  from one cell to another
• Range(A1).Value Sheets(Cars).Range(B3).Val
  ue
• This takes the value from cell B3 of the Cars
  Worksheet and copies it into cell A1 of the
  currently active worksheet.
• Range("A1").Interior.ColorIndex
• Sheets(Cars).Range(B3).Interior.ColorIndex
• This copies the cell colour from cell B3 of the
  Cars worksheet to cell A1 of the currently active
  worksheet.

37
Properties in the VBA Environment

• Properties of objects can be viewed in the VBA
  Environment by clicking on View-Properties
  Window
• Click on each object and the properties will
  appear.
• With User Forms, clicking on each object on the
  form will allow you to inspect and alter the
  properties.

38
Application Properties

• Excel itself (The Application) has a number of
  properties which can be changed. Some of these
  can be very useful
• Application.ScreenUpdating False
• This stops the changes being displayed when
  macros are being executed. It is can stop
  annoying flicks of the screen backwards
  forwards.
• Application.DisplayAlerts False
• This prevents warning dialogue boxes from being
  displayed to the user when something important is
  about to occur, such as printing or deletion of
  pages. Use this with care.
• These Properties should be returned back to True
  just before the macro is concluded.

39
A Note on Changing Properties using VBA

• In changing the properties of cells, ranges and
  worksheets, VBA can do what is impossible for us
  to do manually.
• It can enter data on worksheets which are not
  visible on the screen, or even hidden from view.
• It can copy move data around without having to
  make the sheets active.
• Using VBA, you do not need to activate a specific
  workbook, worksheet or range in fact if you do
  not activate, your code will run much faster.
• If you simply use the Macro Recorder, then your
  macros will only do what you can do in the Excel
  Environment, and you will lose a lot of the power
  of VBA in addition your code will be very
  inefficient.

40
Changing Multiple properties at the same time

• Very often it is necessary to change a lot of
  different properties of an object in one block.
• This can be very laborious in terms of code
• Sheets(Cars).Range(A1).Select
• Sheets(Cars).Range(A1).Value X
• Sheets(Cars).Range(A1).Font.Bold True
• Sheets(Cars).Range(A1).Interior.ColorIndex
  3
• This code can be considerably shortened by making
  use of the With End With construction
• With Sheets(Cars).Range(A1)
• .Select
• .Value X
• .Font.Bold True
• .Interior.ColorIndex 3
• End With

The Object is specified after the With The
Properties are all listed on separate lines,
starting with full stops.
41
What is a Method?

• Methods are the actions that can be performed by
  an Object or on an Object.
• For example these methods act on the objects
• Range(A23).Select
• Sheets(Cars).Delete
• In this example, the method NewWindow causes the
  Excel environment to create a new window and
  display the Workbook as the active workbook.
• Workbooks(Book1).NewWindow

Select and Delete are the Methods
42
Some Useful Methods

• Methods used on WorkBooks
• WorkBooks(Cars).Close
• ActiveWorkBook.Save
• ActiveWorkBook.Protect
• Methods used on Worksheets
• Sheets(Cars).Select
• ActiveSheet.Delete
• Methods used on Ranges
• Range(A1A4).ClearContents
• ActiveCell.Copy

Different Methods are required for different
objects.
43
Application Methods

• Excel itself (The Application) has a number of
  Methods which can be invoked. Some of these are
• Application.GetSaveAsFileName
• This puts up a Save File As dialogue box up on
  the screen, allowing a user to save the current
  file under a different name.
• Application.CheckSpelling
• This method returns a value (True or False)
  which allows you to check the spelling of a word
  entered by a user
• SpellingCheck Application.CheckSpelling(TextBo
  x1.Text)
• If SpellingCheck False then
• Msgbox You have mis-spelt the word. Try again
• End if

44
Parameters

• Some Methods require parameters to be passed so
  that the Method can act with precision.
• Parameters are separated from the method by a
  space
• For example
• ActiveSheet.Paste Destination Range(G4)
• Sheets(Cars).Protect PasswordTony

Parameter Note that the value of a parameter is
specified by
Object
Method
45
What is an Event?

• An Event is an incident which occurs in the VBA
  environment during the course of a normal
  session.
• These can be things like mouse-clicks, switching
  between Worksheets, selecting a particular cell,
  the opening or closing of Workbooks or saving
  work.
• All of the ActiveX controls can respond to
  Events.
• When an event is detected, code assigned to that
  event can be executed. This can considerably
  enhance functionality.
• This example uses the CommandButton Click Event
• Private Sub CommandButton1_Click()
• UserForm1.Show
• End Sub

The code will be executed when the user clicks on
CommandButton1
46
Event Parameters

• Very often the events involve objects other than
  the one which is generating the event
• For example a SelectionChange event to a
  Worksheet will involve a Range being selected.
• Private Sub Worksheet_SelectionChange (ByVal
  Target as Range)
• Rows.Interior.ColorIndex 0
• Target.EntireColumn.ColorIndex 36
• Target.EntireRow.ColorIndex 36
• End Sub

When the user changes a selection on the
worksheet, the new selection is stored as an
object called Target

• This code does the following
• Clear all previous colour.
• Changes the colour of all the cells in the same
  row column as the Target cell

47
Events in the VBA Environment
If you double-click click on an object in Project
Explorer, the events for that object are made
available
On the right, is a pull-down menu consisting of
all the events that are associated with the
object under consideration
48
Text Number Variables

• There are 15 types of variables that can be
  defined in VBA.
• Here are some of the most commonly used ones
• Data Type Description
• Boolean True or False
• Integer Whole number from -32768 to 32768
• Decimal 28-place decimal number
• Date a date in the range Jan 1, 100 Dec 31,
  9999
• String Up to 65,400 characters
• Variant Type not defined until it is used
• Variable names cannot use spaces, although the
  underscore character can be used

49
Declaring Variables

• Variables are normally declared at the start of a
  macro by use of the Dim statement.
• For example
• Dim testType as Boolean
• Dim Z as Integer
• Dim reply as String
• Dim today as date
• If you do not declare the variables type, VBA
  assumes that it is Variant that is, the type is
  left undefined until it is used. This takes up
  more memory AND processing power. If you wish to
  speed up applications, you should always declare
  your variable types.

The Statement Option Explicit Before any
procedures in a module will require explicit
declarations of all variables
50
Object Variables

• In addition to using variables to store text and
  numbers, variables can be used to store objects
  and collections.
• In this case you use the Set statement to assign
  the object to the variable
• Dim myWkBk as Workbook
• Set myWkBk Workbooks(Book1)
• MsgBox myWkBk.name
• If you are unsure of the object type, you can
  declare the variable as Object

51
Standard vb Variables

• Internal to VBA are standard variables which can
  be used in some circumstances, both to store
  values and to control particular elements in the
  environment e.g.
• Reply MsgBox(OK to Delete?, vbYesNo)
• vbYesNo signals to the message box that both
  Yes and No buttons should appear on its
  surface. When a user clicks on one of these,
  Reply will be either vbYes or vbNo
• Other similar variables are
• vbOKCancel, vbOKOnly, vbAbortRetryIgnore
• These control the appearance of the Message Box
• vbCritical, vbInformation, vbQuestion,

52
The Object Browser
If you press F2 in the VBA Environment, you will
see the Object Browser, and you can view all the
objects that are currently available. Scrolling
down and selecting an object will allow you to
see the Methods and Properties associated with
that Object. For example the method Worksheets.Ad
d
53
Obtaining Help

• In the object Browser, if you right-click on the
  method or property and select Help, you will be
  switched to the Excel Help file, giving you
  examples of how that property or method is
  applied
• Here is an example of the Help File for
  Sheets.Visible.

54
Further Help
The VBA Help File is extensive, and has a full
exposition on the DOM as well as a fully index
reference guide to each of the Objects,
Collections, Properties Methods and Events
55
Providing Help for the User
This piece of code can help to provide users with
clues as to what they should be doing. It turns
on the Office Assistant, and puts text into the
speech balloon.

• Sub helpTips()
• With Assistant
• .Animation msoAnimationGetAttentionMajor
• .On True
• .Visible True
• End With
• With Assistant.NewBalloon
• .BalloonType msoBalloonTypeBullets
• .Icon msoIconTip
• .Button msoButtonSetOK
• .Heading "Logging In."
• .Labels(1).Text "First of all, you should
  use the Enable System at the foot of the page.
  The Password is 1234"
• .Labels(2).Text "Secondly you should log in
  as one of the users. The List of users and
  passwords is given on the right."
• .CheckBoxes(1).Text "Click here to remove
  the Wizard."
• .CheckBoxes(1).Checked True
• .Show
• End With
• If Not Assistant.NewBalloon.CheckBoxes(1).Checked
  Then
• Assistant.Visible False

56
Using Macros to provide Security Features
57
Example of a Security System Feature 1

• You should download Security System Workbook from
  the website.
• The First Page is a LOGIN page
• If you try accessing any of the other Worksheets
  you will find that you are locked out.
• You can obtain access by entering the PIN number
  1234 here

58
Security Feature 1

• This now enables the system, and allows access.

Sheets(7).Range("L27") pin letIn
Sheets(7).Range("L32") If letIn Then
TextBox2.Value ""
Range("C30") "SYSTEM ENABLED"
ToggleButton1.Caption "Click to disable"
ToggleButton1.Value True Else
Range("C30") "SYSTEM DISABLED"
ToggleButton1.Caption "Submit PIN"
ToggleButton1.Value False End If

• The code attached to this button simply reads
  Range L32 from Sheet 7
• There is no clue in this code as to what the PIN
  Number is, or how it works.

59
Security Feature 1How it works

• This number is read directly from the Textbox in
  the LOGIN page.

• On The Programming Tools Page, this is the
  calculation carried out.
• A Pass Code is calculated from the original PIN
  Number input
• This Pass Code is checked against a number in
  cell M2
• If it is the same, entry is allowed
• The formulae are

60
Security Feature 1Why is this Secure?
Both the multiplier (2179) and the addend (5162)
can be altered to provide different coding

• Unless you have access to the system you cannot
  read the Programming Tools sheet.
• In any case we can arrange for Cell M2 (Access
  code) to be hidden.
• The calculation from the PIN number to get the
  access code is one way.
• Even if you know the multiplier and the addend,
  it is extremely difficult (but not impossible) to
  work out the PIN number from the access
  code

PIN CALCULATION Input 1234 1234 x 2179
2688886 2688886 5162 2694048 Divide 2694048
by 8999 299 remainder 3347 3347 1000
4347 This is the Pass Code
61
Security Feature 1Improvements

• Clearly on a working system, we would not display
  any of the code, nor give a clue to where it is
  it would certainly be the case that the Access
  code would not be visible from the front page.
• The actual code would be on a protected page, off
  the screen and the scroll area would be such that
  even if the protection were broken a user would
  not be able to scroll to inspect the code.
• In addition, we would not store the access code
  number in cell M2, only the reference to where it
  was kept, so that the code in cell L32 reads
• If (L32 INDIRECT(M2), TRUE, FALSE)
• Finally, the multiplier and the divisor could be
  much larger this would make the data more secure.

62
Example of a Security System Access Rights

• This System has seven different worksheets (these
  contain no data, just descriptions)
• Different worksheets will allow access to read
  databases, to write to databases, to amend tables
  etc.
• Each worksheet has a clearance code, so that
  different levels of access can be arranged.
• For example the LOGIN page has clearance code 6
  (lowest), which means that everyone can gain
  access to it.
• Programming Tools has clearance code 1 (highest),
  which means only the systems programmer can
  access it.

63
Security Feature 2

• Return to The LOGIN PAGE and click on Hide All
  Worksheets
• The workbook is now in the state that a user
  would find when it was opened.
• Log In as Tim (Password x1113) note that
  passwords are case-sensitive.

64
Security Feature 2LOGIN as Tim

• This User Form is displayed, and four separate
  worksheets are made available
• Tim has clearance Code 3, and this means that he
  has access to those worksheets and tools where he
  can modify data through forms, but not the live
  database.

65
Security Feature 2LOGIN as Tony

• Log In as Tony (Password x1111)
• This is the highest level of access. All parts of
  the system are available.

66
Security System 2Other Users

• If you log on as other users, you will find that
  you are given access to different parts of the
  system.
• Some are given more, some less access rights.

67
Security System 2 Access Clearance Codes

• On the Programming Tools page, you will find a
  full account of which pages are available to
  which users.
• The principle used is that each page has a
  clearance code. If the users access code is less
  than the clearance code, then the user may view
  that page.
• The Table may be edited by hand.

68
Security System 2 Allowing Access to Pages
For x 2 To Sheets.Count access
Sheets(7).Cells(13 user, 14 x) If access
True Then With Sheets(x)
.Visible True .Unprotect
PasswordsysPass End With txt txt
" " x ". " Sheets(x).Name Chr(10)
Else With Sheets(x) .Protect
PasswordsysPass, DrawingObjectsTrue,
ContentsTrue, ScenariosTrue
.Visible xlSheetVeryHidden End With
End If Next x

• This is the section of the hideSomePages Macro
  which allows or denies access to different pages
  in the Workbook.
• It uses a variety of the DOM tools introduced
  earlier in this presentation

69
Security System 2 Exploring The System

• There are many features of the system that you
  could adapt for use with the application that you
  have been asked to create.
• You should look at these features carefully and
  use these ideas to enhance your own system.

70
Encryption 1

• From the website, download Encryption Example 1
  this is the first Sheet

Click on the Encrypt Button, and the data is
encrypted
71
Encryption 1Explanation

• Sheet2 gives the details of how the encryption
  takes place.
• It uses the same methodology as the PIN Code
  described earlier.

72
Encryption 1Coding
Function encrypt(letters) eM Sheets(2).Range("C6
") ' encryption Multiplier eA
Sheets(2).Range("C7") ' encryption Addend encrypt
" " For i 1 To Len(letters) character
Mid(letters, i, 1) x Asc(character) y
(x eM eA) Mod 256 Z Chr(y) encrypt
encrypt Z Next i End Function

• This is the VBA function which encrypts text.
• It takes one letter at a time and uses the coding
  scheme on the previous sheet to encrypt the text.
  
• A different coding scheme can be obtained by
  modifying the values in cells C6 C7 of Sheet2

73
Encryption 2
From the website, download Encryption Example 2
When this opens, you are presented with the
following
If you enter the correct PIN Code (1234), you
will be allowed access to the unencrypted data
otherwise you will be denied access.
74
Encryption 2 The Code

• The Encryption occurs when the workbook is closed
• When the workbook is opened a password is
  required.
• If this is incorrect, access is allowed to the
  encrypted version only
• If a bypass is attempted (disable macros) then an
  encrypted version only can be viewed.

Private Sub Workbook_BeforeClose(Cancel As
Boolean) If Range("A1") "XXX" Then Exit
Sub Modify ActiveWorkbook.Save End Sub Private
Sub Workbook_Open() x InputBox("Code is 1234",
"Please enter the 4-digit code") If x ltgt 1234
Then MsgBox "Wrong Code Access to encrypted
version only" Range("A1") "XXX" Else
Modify End If End Sub
75
What Should I do Now?
76
What should I do now?

• This is the final Lecture in this Module.
• From this point on, you should concentrate
  entirely on the Project.
• If you have not already done so, you should
  download Team Working 4 from the website and
  ensure that you are on track for the rest of the
  module.

77
Do I need anything else?

• Team Working Stage 5 will be available from the
  website at a later point.
• You will not need information from this until
  your project development is fairly well advanced.

78
What happens now?

• You should continue meeting as a team, and
  consulting with your tutor until the end of this
  semester.
• By now, you have learnt all the skills that you
  need to produce a fairly substantial business
  application.
• The only thing to say is Good Luck!