CPSC 130 Computing With Spreadsheets List Management and

CPSC 130 Computing With Spreadsheets List Management and Macros Week 7 Brenda Vander Linden and Aaron Armstrong

Outline n n n List and Data Management Database Issues In closing … 9/15/2020 Brenda Vander Linden and Aaron Armstrong 2

List and Data Management n Recall the data hierarchy: • fields • records • files n n Working with Excel lists (aka files) Converting data to information 9/15/2020 Brenda Vander Linden and Aaron Armstrong 3

A Data Hierarchy n n n Bit Character, string and number Field Record File Database 9/15/2020 Brenda Vander Linden and Aaron Armstrong 4

Information from Lists n n Manipulating lists Filtering lists • Similar to database query features n Summarizing lists with pivot tables • Similar to database report functions 9/15/2020 Brenda Vander Linden and Aaron Armstrong 5

Feature Creep n n Now we have facilities for sorting, input, etc. , as well as a means to deal with multiple worksheets. We get something approaching a database management system (DBMS), without having a one. 9/15/2020 Brenda Vander Linden and Aaron Armstrong 6

Database Issues n Database - a collection of data that is: • persistent • too large to fit into main memory n Database Management System - a system that maintains and provides multi -user access to a database, and whose operation is: • • • 9/15/2020 efficient convenient safe Brenda Vander Linden and Aaron Armstrong 7

Database Issues, cont. n n When to use databases (and not Excel): • Large data sets • Formed input and output • Arbitrary and complicated queries • Multiple users • Security CPSC-135 covers databases 9/15/2020 Brenda Vander Linden and Aaron Armstrong 8

“All our knowledge brings us nearer to our ignorance, All our ignorance brings us nearer to death, But nearness to death, no nearer to God. Where is the Life we have lost in living? Where is the wisdom we have lost in knowledge? Where is the knowledge we have lost in information? The cycles of Heaven in twenty centuries Bring us farther from God and nearer to the Dust. [98] Poems 9/15/2020 T. S. Eliot, Choruses From ‘The Rock’, Selected (New York: Harcourt, Brace & World, 1964), p. 107. Brenda Vander Linden and Aaron Armstrong 9

What is a macro? • • • A set of instructions telling Excel what commands to execute Written in Visual Basic for Applications (VBA) Recorded with the Macro Recorder 9/15/2020 Brenda Vander Linden and Aaron Armstrong 10

Recording a macro • • Name. And. Course macro Shortcut key Record macro Stop recording 9/15/2020 Brenda Vander Linden and Aaron Armstrong 11

Elements of a Macro • • • Sub/End Sub Comments Variables Properties With Statement 9/15/2020 Brenda Vander Linden and Aaron Armstrong 12

Step Into • • Executes one command of the macro Other debugging commands available 9/15/2020 Brenda Vander Linden and Aaron Armstrong 13

Personal Macro workbook • • • Opened every time Excel starts Only on your machine Always accessible 9/15/2020 Brenda Vander Linden and Aaron Armstrong 14

User interaction functions • • • Input. Box Msg. Box Example: modifying another macro 9/15/2020 Brenda Vander Linden and Aaron Armstrong 15

The If Statement Sub Pepsi() Active. Cell = _ Input. Box("Enter something interesting") If Active. Cell = "Britney" Then Msg. Box ("Try Pepsi") Else Msg. Box ("Try Britney") End If End Sub 9/15/2020 Brenda Vander Linden and Aaron Armstrong 16

The Do Statement Sub Redden() Range("A 4"). Select Do Until Active. Cell = "" Selection. Font. Color. Index = 3 Active. Cell. Offset(1, 0). Select Loop End Sub 9/15/2020 Brenda Vander Linden and Aaron Armstrong 17