CPSC 203: Final exam information

Course web page: Introduction to problem solving with applications James Tam

Return to the course web page

CPSC 203: Final Exam Information

Date/location:

The final exam is scheduled by Student Service, you can find information via the [university portal]

Practice review questions:

There are a number of sources of review material that are pertinent to this semester:
TopHat questions can give an idea of what some of the (easier - in most cases) multiple choices may look like.
During lecture as I cover the material I will provide suggestions of what an exam-like question may look like. You should attend class for this info and note the details as it comes up. If you missed class then you should try talking to a friend that attends class and takes thorough notes.
Data files for the in class review: [Access database] [Starting website]
Extra VBA review questions: the following are meant to provide you with some extra practice so you need to actually try them on your own to get anything out of it. For that reason solutions won't be posted. But if you still have questions after attempting a question then you can ask (just be sure to show me the work that you have done so I can see how far that you have gotten and that you're not just coming in cold without making an attempt): [Questions]

Front cover from the actual exam (so make sure you look at this beforehand...important so make sure you read them beforehand!):

(Flip side of cover - top of page showing where you write your student ID number)

Exam topics

Lecture topics covered so far	Approximate proportion of exam¹
Material covered before the midterm (computers, logic, spreadsheets)	~10%
Databases	35
VBA programming (excludes: securing a Word document with a password, accessing tables/shapes/images)	35
HTML and building a web page	6
The Internet	12
Computer security	Not applicable
Other	~1%

(Early note: the final examination may include material from the first part of the term but there's a heavy focus on material on the topics covered after the midterm, databases onwards).

Exam questions¹

Multiple choice questions: 43 marks total
	43 questions
Short answer questions: 65 marks total
	SA1: pre-midterm topic
	SA2 - SA4: Access database questions (2 questions involve queries, 1 question comes from another part of the database section)
	SA5 - SA7: VBA (2 questions involve writing programs, 1 question involves tracing a VBA program)
	SA8: Web/html
	SA9: The Internet

¹^{It's based on a near-final version of the midterm (exact proportions may vary
*slightly*)}

VBA library documentation that will get for the final exam (not all library functions may have been covered during a particular semester)
Accessing the following VBA objects:
	Application: the MS-Office program running ActiveDocument: current MS-Word document Selection: currently selected text
Useful methods and attributes of the Application object
	Application.Windows.Count: number of open Word windows Application.CapsLock: a Boolean to indicate whether caps lock is turned on or off
Useful methods and attributes of the ActiveDocument object
	ActiveDocument.CountNumberedItems: total number of bulleted and numbered items ActiveDocument.Save: saves the current document ActiveDocument.SaveAs("<filename>"): save and rename document ActiveDocument.Select: make the entire document the current selection ActiveDocument.ActiveWindow.Caption = "<Caption>": change caption title in the Word window ActiveDocument.Close (<wdPromptToSaveChanges> or <wdDoNotSaveChanges> or <wdSaveChanges>): close current Word window and apply the effect of specified constant ActiveDocument.Checkspelling: run spell checker ActiveDocument.DeleteAllComments: delete all comments from the active document ActiveDocument.Printout: prints the active document ActiveDocument.SendMail: sends email with the active document as an attachment ActiveDocument.Words.Count: number of words in the MS-Word document ActiveDocument.Words.Comments.Count: number of annotation comments in the MS-Word document ActiveDocument.Select: select all the text in the active Word document ActiveDocument.Application: the application associated with the document ActiveDocument.Name: name of the active document ActiveDocument.Path: save path of the active document ActiveDocument.FullName: name & save path of the active document ActiveDocument.HasPassword: a Boolean to indicate if the active document has been saved since last changed ActiveDocument.password = "<Password>" : sets the password for the active document to the value specified ActiveDocument.SpellingChecked: a Boolean to indicate if the active document has been spell checked since last changed ActiveDocument.Saved: a Boolean to indicate if the active document has been saved since last changed ActiveDocument.SpellingErrors.Count: the number of spelling mistakes in the active document ActiveDocument.InlineShapes.Count: number of inserted inline shapes in the document ActiveDocument.Shapes.Count: number of primitive shapes in the document ActiveDocument.Tables(i) Allows access to one table from the collection of tables. 'i' is a positive integer (i.e. >= 1) ActiveDocument.Tables.Count: number of tables in the active document ActiveDocument.Tables(i).Sort(<Boolean>): 'i' is a positive integer, sorts the table specified by 'i' in the active document, the Boolean specifies if it is true that the tables include headers ActiveDocument.InlineShapes(i).height : height of inline shape # 'i' ActiveDocument.InlineShapes(i).width : width of inline shape # 'i' ActiveDocument.Shapes(1).Delete : delete shape # 'i' ActiveDocument.Shapes(i).height : height of shape # 'i' ActiveDocument.Shapes(i).width : width of shape # 'i' ActiveDocument.Shapes(6).Fill.ForeColor = <Color: vbBlack, vbRed, vbGreen, vbYellow, vbRed, vbBlue, vbWhite>: sets Shape # 'i' to the specified color
ActiveDocument: find and replace text ActiveDocument.Content.Find.Execute FindText:="<text>", ReplaceWith:="<text>", _ Replace:= <wdReplaceAll>, MatchCase:= <Boolean>
ActiveDocument: find and replace font effects (turn on/off) With ActiveDocument.Content.Find .Font.<Font effect> = <Boolean> With .Replacement .Font.<Font effect> = <Boolean> End With .Execute Replace:=wdReplaceAll End With
ActiveDocument: find and replace text styles in a document With ActiveDocument.Content.Find .Style = "<Search style>" With .Replacement .Style = "<Replacement style>" End With .Execute Replace:=wdReplaceAll End With
Counting the number of occurrences of a particular word in a document: With ActiveDocument.Content.Find Do While .Execute(FindText:=<"searchWord">, Forward:=True, _ MatchWholeWord:=True) = True <Do something when each instance of search word is found> Loop End With
Useful methods and attributes of the Selection object
	Selection.Expand: expand selection to next grouping size of text Selection.Copy: copy selected text Selection.MoveLeft: move cursor left Selection.MoveRight: move cursor right Selection.ParagraphFormat.Alignment = <wdAlignParagraphCenter or wdAlignParagraphLeft or wdAlignParagraphRight>: to center, left or right align a paragraph (respectively) Selection.ParagraphFormat.SpaceAfter = <# points e.g. 0, 3, 6 etc.): Adds extra spacing to the end of paragraph Selection.ParagraphFormat.LeftIndent = InchesToPoints(# inches): # inches for left indenting of a paragraph Selection.ParagraphFormat.RightIndent = InchesToPoints(# inches): # inches for right indenting of a paragraph Selection.PasteAndFormat (<wdFormatOriginalFormatting or wdFormatPlainText>): copied text is pasted with the specified formatting Selection.Font.Name = <Font name> : change font type Selection.Font.Size = <Size> : change font size Selection.Font.Bold = <Boolean> : turn bolding on or off Selection.Font.Bold = wdToggle: toggle bolding Selection.Font.Italic = <Boolean> : turn italics on or off Selection.Font.Italic = wdToggle: toggle italics Selection.Font.Underline = <Boolean> : turn underlining on or off Selection.HomeKey Unit:= <wdStory>: move selection to the top of the document Selection.Type : returns information about the type of information selected (if any: <wdSelectionIP - none> <wdSelectionNormal - text selection> <wdSelectionShape - graphical shape selected> ) Selection.text = <"Selected text"> : writes the selected text to the document by setting the text attribute directly Selection.TypeText(<"Selected text">) : writes the selected text to the document through the method TypeText()
Common and useful collections
	InlineShapes(): includes all the external images inserted into a Word document Lists(): all the lists in a Word document Shapes(): all the primitive shapes in a Word document Tables(): all the tables in a Word document Documents(): the collection of documents currently opened
Useful methods and attributes of the Documents collection
	Documents.Item(i): Allows access to one document from the collection of documents. 'i' is a positive integer (i.e. >= 1) Documents.close: closes all of the open Word documents Documents.count: returns a positive integer that is equal to the number of documents currently opened. Documents(i).Save: save the document accessed through the index Documents(i).SaveAs (<"name">): save the document (accessed through the index) under another name Documents(i).PrintOut: print the document accessed through the index Documents.Open (<"location and name">): opens the document with the specified location and name and adds it to the documents collection
Useful functions/methods
	Len(<string>): this function returns the number of characters in the specified string Dir("<Path to a folder>") : returns the name of a document under the specified path Dir("<Path to a document>") : returns the name of the document under the specified path Dir : after the path to a folder has been specified with a previous call to Dir, this call returns the name of the next document in the specified folder CreateObject("<Name of MS-Office application>"): starts up the Office application specified in the brackets. <Name of an excel spreadsheet variable>.workbooks.Open("<Path and name of an Excel spreadsheet/worksheet>"): opens up the Excel spreadsheet at the specified location. <Name of an excel spreadsheet variable>.Range("<Cell in a spreadsheet>").Value: Returns as a string the contents of the cell of spreadsheet referred to by the spreadsheet variable.
Excel methods (accessible via VBA run through Word)
	<reference to an Excel window>.Visible = <Boolean> : Makes an Excel window visible or not visible <reference to an Excel window>.workbooks.open("<Path to an Excel document">) :Opens the Excel document named under the specified path and returns a reference to that document. <reference to an Excel window>.range("<Cell address>").Value : returns the contents of the specified cell

As mentioned VBA documentation WILL NOT be provided for the following (you're expected to know how to apply them without documentation)

Common and simple functions such as MsgBox and InputBox
Branching constructs (If-Then, If-Then Else, If-Then, ElseIf, Else) are not functions or methods
Logical (And, Or, Not), mathematical (+, -, *, /), string (concatentation) and relational (e.g., >=) operators
Looping constructs (For-Next, Do-While Loop)

VBA concepts that you did learn and are expected to know

(This is only a quick overview, you should look to the course notes for details)

How to create and run a VBA program (although you won't be asked questions about the VBA interface i.e. 'Where to click'
Defining a (single) subroutine
Variables
Named and pre-defined constants
Getting input via the InputBox and displaying output using a MsgBox and the status/title bar of the MS-Word window.
Naming conventions used for: variables, named constants, subroutines and the name of Word document that contains the macro
Logical, mathematical, string, and relational operators
Writing documentation (although you don't have to document your code writing solutions in the exam unless asked)
Branching
Looping
Accessing the objects and using the methods specified in the VBA library documentation (above)
What is the difference between the following file types: '.docm', '.docx' and '.doc' and the effect on computer security

You could see exam questions that involve any of the above questions in the form of VBA code writing or a program code trace.

The following HTML tags were covered in class but you won't see in the final exam:

<embed src="<video name and path>" autostart="<Boolean>">
<img src= "<image name and path>">
<href="<web address of linked document>"></a>
<href="mailto:<Email address>"></a>

Material that you should study

The final exam is cumulative but with a focus on material covered after the midterm exam (this excludes databases, it may have been covered before the midterm but you weren't examined on this topic or you only saw a few questions at most on the midterm).

(Comments from the midterm that apply to the final exam)

The lecture material: the PowerPoint slides will provide you with an outline of the topics to be covered. However studying the information just listed on the slides won't be sufficient to do well on the final exam. You will also need to take notes in class and to study and understand those notes. If you have any questions then make sure that you ask me for help and clarification.

Using software applications: General
	You won't be asked about where different features are located in a particular program i.e., what is the sequence of clicks to invoke a particular feature of Excel

MS-Excel documentation

Basic mathematical operators: assignment (=), addition (+), subtraction (-), multiplication(*), division (/), exponnent(^)
Basic statistical formulas
	sum (<start cell> : <end cell>) average (<start cell> : <end cell>) min (<start cell> : <end cell>) max (<start cell> : <end cell>)
Counting functions (an alternative to entering the start: cell range is to manually enter the data sequence as the function argument).
	count (<start cell> : <end cell>) counta (<start cell> : <end cell>) countblank (<start cell> : <end cell>)
String (and related utility) functions (an alternative to entering the cell is to manually enter a string as the function argument).
	left (<cell>, <number of characters>) right (<cell>, <number of characters>) mid (<cell>, <start position>, <number of characters>) find(<find text>, <within text>, [<start position>]) concatenate(<cell₁>, cell_2,cell_3....cell_n)
Date and time functions
	today() now()
Branching function
	=if (<condition to check>, <return value: condition true>, <return value: condition false>)
Logical comparators (relational operators): less than (<), less than equal to (<=), greater than (>), greater than equal to (>=), equal to (=), not equal to (<>)
Lookup functions
	LOOKUP(<Lookup value>, <Lookup column (vector) Start : End>, <result column (vector) Start : End>) VLOOKUP(<Lookup value>, <Lookup table Start : End>, <Lookup table Column specifying the return value>, [<Exact match required?>])
Conditional counting functions (an alternative to entering the start:cell range is to manually enter the data sequence as the function argument).
	countif (<start cell> : <end cell>, <condition>) countifs (<start cell> : <end cell>, <condition>, (<start cell> : <end cell>, <condition>) )

Reminder with many Excel functions (an alternative to entering the start : cell range is to directly enter the data sequence as the function argument).

MS-Access documentation

Characters that define input masks
	Numbers: 0, 9 (mandatory, optional) Alphabetic letters: L, ? (mandatory, optional) Alpha or digit: A, a (mandatory, optional) Digit, space, plus or minus sign: # (default is a space) Changing case: >,< (upper, lower)
Specifying validation rules (Strings): Like "<Format for String>"
	Formats: Numbers [0-9], Alphabetic [A-Z], #, Wildcard *, Single character wild card ?

Mapping of raw percentage scores to grade points

Min percent	GPA
0	0
10	0.1
20	0.3
30	0.5
35	0.7
40	1
44	1.1
46	1.2
48	1.3
50	1.4
52	1.5
54	1.6
55	1.7
56	1.8
57	1.9
58	2
59	2.1
60	2.2
61	2.3
62	2.4
63	2.5
64	2.6
65	2.7
66	2.8
67	2.9
68	3
69	3.1
70	3.2
71	3.3
72	3.4
74	3.5
76	3.6
78	3.7
80	3.8
82	3.9
84	4
90	4.1
96	4.2
98	4.3