BMGT 302 Exam 2

What is the syntax that sums up all the formatting stuff?

(f'number or "string":~spacefill~fieldwidth~d or s or something~,~.2f}')

The operator can be used to extend a list with copies of itself.

*=

How do you group digits with a comma?

, after field width

How do you concatenate and repeat strings?

- 'joe' + ' ' + 'smith' = 'joe smith' - 'hi' * 3 = 'hihihi'

What are the specifiers for alignment?

- < left - > right - ^ center

What is a project?

- A group of all modules, forms, and other programming elements - There is only one project per workbook

What is a dictionary?

- A mutable collection of key-value pairs - indecies can be any immutable type but usually ints and strings - DICTIONARIES MUST BE CREATED BEFORE POPULATED - no order

What is a text file?

- A text file containing lines of plain text - as opposed to binary file - file extension: .txt, .csv, .dat, etc.

What is aliasing? What are its side effects and how can they be prevented?

- Aliasing is when "first" and "second" are names for the same (list) object - you change one you change the other - to prevent you can copy the list object (3 ways) 1. Manually copy (.append) 2. Use shorthand (third = first[:]) 3. use copy function (third = first.copy())

What are the 3 common errors for files?

- FileNotFoundError - PermissionsError - ValueError

What is the proper list comprehension syntax?

- List1 = [x, y, z] - List2 = [Desired action (ex: item + 3 or item.upper) for item in list (ex: range(x,y) or list1) if condition (ex: item % 2 = = 0)]

What is the difference between VB and VBA?

- VBA is only a subset of functionality available in VB - VB is a standalone programming language; Excel is not needed - VBA is primarily used with application software packages; Excel or another application is needed

What are typical VBA syntax conventions?

- VBA keywords are blue - red is error - green is comment - black is normal - keywords start with a cap letter - variables start lowercase - indentation is impt. - press f1 for help

What is a list?

- a list is a kind of collection - collections allow you to put many values in a single variable

How does the append() function work?

- adds elements to list - list.append(element) - MODIFES LIST

What are the parameters of a list indices? What happens if an invalid index is used?

- any single element in a list can be accessed using the index and square brackets - list indices start at 0 - index must be an integer - if invalid index is used you get an IndexError

How do you unpack a tuple?

- assign values to variables in tuple - if # of values != of variables, runtime error occurs Ex: food = ('fruit', ['chips', 'soda', 'burgers']) healthy, not_healthy = food healthy returns 'fruit' unhealthy returns ['chips', 'soda', 'burgers']

What does the count() method do for strings?

- counts the number of occurrences of a substring in string - Ex: string1 = 'hihihi' string1.count('h') returns 3

What are the three common presentation types? (f'string)

- d: formats integer values as a string (so that it's left alligned) - c: formats integer character code as asciitable - s: formats value as a string (default)

How do you loop with definitive lists?

- define list - for x in list: - print(f'Hello, {x}') - print('done')

How does the slice deletion function work (lists)?

- del(list[x]) deletes element with index x - del(list[x:y]) deletes x to y-1 - del(list[x:]) deletes x to end - del(list[:x]) deletes start to x-1 - CAUTION!!! if you try to delete nonexisting element there will be an IndexError (runtime) - MODIFES LIST

How do you map values to new values in dictionary (syntax)?

- dict1 = {...} - dict2 = {Desired action (ex: key sum(values) / len(values)) for key, values in dict1.items}

How do you swap keys and values in a dictionary?

- dict1 = {...} - dict2 = {Desired action (just key for swap) for key, values in dict1.items}

What does the find() method do for strings?

- finds first position of a substring in string - Ex: string1 = 'hihihi' string1.find('h') returns 0 - rfind does the same thing but reads string right to left

What are the reasons to use macro recording?

- helpful for beginners - helpful for experiences ppl (alt to google search and gives context clues)

What do the in/not in operators do?

- in is a boolean that tests membership - not in is also a boolean that returns the opposite answer - these operators DO NOT modify the list

What python expressions work for strings in addition to tuples?

- len - concatenation (+) - repetition (*) - membership (in) - iteration (for loop)

What do the len() function and if statement do in the context of dictionaries?

- len determines number of key-value pairs - if can determine if a dictionary is populated. ex: if dict1: print('hi')

What is proper list syntax?

- list values are surrounded by square brackets, and elements in the list are delimited by commas - a list can be any python object including another list - lists can be empty

How do you convert keys, values, and key-pairs to lists?

- list(name.keys()) - list(name.values()) - list(name.items())

How do list ranges work?

- list(range(inclusive, exclusive, step by [which can be -]) - list(range(x)) returns a list of numbers from zero to x-1 - list(range(x, y)) returns a list of numbers in the range from x to y - 1 - if x > y an EMPTY LIST returns, not error

What does the index() function do?

- list.index(value) - find out where in a list a value is by index - if item not in list you will get a ValueError (runtime)

How do you concatenate lists?

- list1 = [x] - list2 = [y] - list3 = list1 + list2 - list3 is [x, y]

How do you slice a list?

- lists slice with a : - list[x:y] returns x to y-1 - list[x:] returns x to end - list[:x] returns everything until x-1 - list[x:y:x] returns x to y (including y) with step of z. CAN STEP BACKWARDS

What is the difference between a list and a dictionary?

- lists use indices to look up values and dictionaries use keys - lists are ordered; dictionaries are not thus can't use .append or .upper etc.

What can list comprehensions do?

- map values such as after a calculation - filter elements to match a condition like only even #'s - process another list like using .upper

What are the 4 dictionary methods? Does it have its own copy of the data?

- name.keys() returns a list of the keys that appear - name.values() returns a list of the values in the dictionary - name.items() returns a list of tuples of all keys and values, one for each key-value pair - name.get(key) returns the value if the key appears in the dictionary - does not have its own copy of the data, only view

What are matrices?

- nested lists can be used - 2D data structure with lists and rows

What is the default alignment for numbers and other values?

- numbers are default right-aligned - strings are left-aligned

What does the open function do?

- opens a file at a specific path - if no path, file is created in same location as .py

What are some examples of dictionaries?

- phone books - address books - encyclopedia

What happens if you reference a key that's not in the dictionary? How can we avoid this?

- runtime error - instead, use .get ex: dict1.get('hi', 'no hi')

How do you swap values with tuples?

- set the actual tuples equal to each other if both have same # of elements - slicing: t1 = (1, 7) a = t1[0] a --> 1

How do you compare tuples?

- set the variables equal to each other or > or < - compares first item, then second, until there is a difference or no more elements - results in boolean true/false

Can you modify a tuple? How would one do something like this?

- slicing follows similar rules to lists - can't modify but can replace through slicing and concatenating... values = ('a', 'b', 'c') values = ('A',) + values[1:] values --> ('A', 'b', 'c')

How do the sort() and sorted() functions work? How about *reverse?

- sorts lists lexicographically - list.sort() - *list.sort(reverse = True) - MODIFES LIST - newlist = sorted(values) - allows you to keep OG list - *newlist = sorted(values, reverse = True)

How do you slice a string (syntax)?

- string[start:end] remember end is not inclusive

What is a tuple?

- tuple is like a list except it's IMMUTABLE - values separated by comma, parenthesis recommended - ex: tuple1 = 'a', 'b', 'c'

How do you convert lists and tuples to each other?

- tuple1 = tuple(list1) - list2 = list(tuple2)

Why use tuples?

- tuples are more efficient (simpler in terms of memory and performance than lists bc immutable) - tuples > lists when values in tuple won't change during program

How do you modify the contents of a dictionary?

- using the key, if a key doesn't exist a new entry will be created - can't change keys only values - entries can be deleted with the del()

What are the 4 different modes for opening a file?

- w: create or replace a file and write to file - a: append or create a file and write to file - r: read from file but not write - r+: open the file for both reads and writes

How do you fill an empty space with 0's?

0 can be placed before the field width (and after a + if there is one)

How do you execute a macro (2 ways)?

1) F5 2) Play>macro>run

How do you insert a module (two ways)?

1) Insert > Module 2) Right clck folder > insert > module

How do you view the object browser (2 ways)?

1) View> object browser 2) press F2

How do you write to a text file?

1) acquire a file with the "with" statement 2) open with the "open" function 3) write with the "write" function 4) close the file (happens at end of with statement block)

How do you read from a text file?

1) acquire file with the with statement 2) open or create file with open function 3) read data from the file one line at a time 4) close the file (get out of the with block)

How do you view Macro code (2 ways)?

1) click macro + F7 2) click module > view code

How do you begin recording a macro (2 ways)?

1) developer tab>record macro 2) bottom left of excel screen

How do you do the Counting Car Sales problem?

1) download file 2) save 3) new mod 4) create sub 5) run sub

How do you delete a module (2 ways)?

1) right clk > remove mod 2) select mod > edit > remove mod

What are the macro storage options (3 ways)?

1) this workbook 2) new workbook 3) personal macro workbook

What is meant by "recording a macro"?

Using the macro recorder, you can record the actions you take in using the Excel user interface

What is the security issue with VBA? How does Microsoft try to hedge against this and what should you do?

VBA code can be dangerous with viruses! • Never open a .xlsm file unless you know the source • By default, Microsoft prevents any VBA code from running

What is Intellisense?

VBE gives hints and tries to complete certain words

(T/F): Tuples can contain lists and other mutable objects. Those mutable objects can be modified when a tuple is passed to a function.

True

How do you comment in VBA? What's the point?

comments used to explain code. Just starts with '

How do you create a button?

develop tab>insert>button icon (top left)> select cells>assign macro>ok

What are subs? How do you use them?

develop tab>insert>button icon (top left)> select cells>assign macro>ok

What is the proper dictionary syntax?

dict1 = {'key':'value', 'key1':'value1'} dict2 = { } dict2['key2'] = 'value2'

How do you compare dictionaries?

dict1 == dict2 returns boolean

How do dictionaries organize information?

dictionaries organize information by ASSOCIATION

What do placeholder values use in an f-string?

format specifier

What types of functions are not permitted for tuples?

functions that would modify the tuple like sort are not permitted bc tuples are immutable

What is parsing a file?

go through each line and use delimiters to separate fields

What is a workbook module?

Contains code maintained for handling workbook-level (i.e. file) events

What is a worksheet module?

Contains code tied to actions on a worksheet (called events) (e.g. selecting a range or entering a value in a cell)

What is a UserForm module?

Dialogs which allow an application to get input from the user through user controls (e.g. buttons, list boxes, checkboxes, etc.)

What file formats exist for excel files?

Excel 2007 on: w/ VBA - .xlsm w/o VBA - .xlsm Excel 2003 & earlier: - .xls

(T/F) ('-' * 10) will cause an error

False

(T/F) A += augmented assignment statement may not be used with strings and tuples, because they're immutable.

False. A += augmented assignment statement also may be used with strings and tuples, even though they're immutable. The result is a new string or tuple, respectively.

(T/F) Tuples can contain only immutable objects.

False. Even though a tuple is immutable, its elements can be mutable objects, such as lists.

(True/False) All sequences provide a sort method.

False. Immutable sequences like tuples and strings do not provide a sort method. However, you can sort any sequence without modifying it by using built-in function sorted, which returns a new list containing the sorted elements of its argument sequence.

(T/F) Slice operations that modify a sequence work identically for lists, tuples, and strings

False. Slice operations that do not modify a sequence work identically for lists, tuples, and strings

(T/F) The + operator's sequence operands may be of any sequence type.

False. The + operator's operand sequences must have the same type; otherwise, a TypeError occurs.

(T/F): You cannot modify a list's contents when you pass it to a function.

False. When you pass a list (a mutable object) to a function, the function receives a reference to the original list object and can use that reference to modify the original list's contents.

What is a good summary of macros?

Usage of the macro recorder serves two primary purposes - Allows beginning programmers to learn how common Excel operations translate to VBA code - Allows more advanced programmers to discover hard to determine details about how an Excel function can be translated into VBA code • The usage of the macro recorder has some drawbacks and limits - Recorded code is often bloated with unnecessary lines and requires cleaning - The recorder cannot capture programming logic, such as conditional statements or repetition structures

How do you rename a module? What are the naming rules?

Select the module and adjust the Name property in the properties window Rules (in general): - Must use a letter as the first character and cannot exceed 255 characters - Can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name. - Can't use any reserved (special) words

What does the len() function do for lists?

Takes the list as a parameter and returns the number of elements in the list

How do you create replicates of a string in a list?

The * operator: numbers = [1, 2, 3] * 3

What does the pop() method do? Does it modify?

Removes an element from a list - if not given index, pop removes and returns last element - list.pop() - MODIFES LIST

What is VBA?

Microsoft's programmatic way to automate operations in its applications (i.e. Excel, Access, Word, PowerPoint, and Outlook)

How do you add development to your ribbon?

Path: - File > - Options > - Customize Ribbon > - Customize Main Tabs Ribbon > - Select Developer • Only required once

Given a list numbers containing 1 through 10, del numbers[-2] removes the value ________ from the list.

9

What is a module?

A container to organize code

What is a Macro language?

A language capable of automating the sequence of commands in spreadsheet or word-processing applications

How do you format a positive number?

Add a + before the field width

What are class modules?

Allows a programmer to create customized objects, or "things" that have attributes and behaviors

What is a trusted location?

Any .xlsm files in this folder (or its subfolders) will open without warning about enabling macros

What is a warning message? What do you need to enable?

Any .xlsm files in this folder (or its subfolders) will open without warning about enabling macros

What is a ZeroDivisionError?

Attempting to divide by zero

What is a standard module?

Holds macros the programmer creates or ones generated from the Macro Recorder

Python's string and tuple sequences are _____ they cannot be modified.

Immutable

What happens if an invalid index is used while slicing? Does slicing modify?

Invalid indexes do not raise exceptions. The following will happen to protect from runtime error. - if start > end: empty list - if end beyond list: length of list will be used - DOES NOT MODIFY LIST unless you make it

What are lines of code?

Logic statements that perform a portion of a task

What is a macro?

Logic statements that perform a portion of a task

What is a string?

a string is a sequence of characters enclosed within ' or "

What does the join() method do for strings?

concatenates list of strings into one large string ex: '-'.join(['hi', 'hey', 'hello']) returns: 'hi-hey-hello'

What does the lower() method do for strings?

copy of string in all lowercase

What does the upper() method do for strings?

copy of string in all uppercase

What does the title() method do for strings?

copy of string with first character of each word capitalized

What does the capitalize() method do for strings?

copy of string with first letter capitalized and rest lowercase

What does the lstrip() method do for strings?

copy of string with the whitespace on left of words removed

What does the rstrip() method do for strings?

copy of string with the whitespace on right of words removed

What does a negative list index mean?

if index is negative then you start counting backwards at the end of the list

Operators and determine whether a sequence contains or does not contain a value, respectively.

in, not in

What is a raw string?

like f string but instead of f it's r and r ignores all the backslashes

What is the immediate window?

like python shell

What type of data can lists store?

lists typically store homogenous data, but can store heterogeneous data

How do you protect your VBA code?

lock it: Tools>Proj Prop>Protection>Lock>Password

How can you loop through a dictionary? What is the proper syntax?

name = {...} - way 1: for key in name: print(f'{key}: {name[key]}') remember name[key] = value!!! - way 2: for key, value in name: print(f'{key}: {value}')

How do you add or subtract counts in a dictionary? (syntax)

name = {} name[fun] = 1 name[fun] += 1

What slice expression would create a new list with the elements of the og list (names) in reverse order?

names[::-1]

Does order matter in dictionaries?

no!

Why do we use dictionary comprehensions?

quickly create dictionaries

What does the replace() method do for strings?

replaces occurrences of old substring with new substring ex: string1 = 'hi hello howdy' string1.replace('hi', 'hey') result: 'hey hello howdy'

To sort a list in descending order, call this method sort with the optional keyword argument set to True.

reverse

How does indexing work with strings (syntax)?

same as lists. start with index 0

What does the split() method do for strings?

splits string into substrings ex: string1 = 'hi hey hello' string1.split() returns: ['hi', 'hey', 'hello']

How do you override exceptions?

try/except

What is the syntax for override exceptions?

try: <indented code block> except <ExceptionType>: <exception handler block> <non-indented statement>

What is the proper syntax when using a tuple with a single element?

tuple1 = ('a',) COMMA IS IMPORTANT

A sequence's elements can be ______ by assigning the sequence to a comma-separated list of variables.

unpacked.

What is the syntax for opening a file?

with open('filename.txt', mode = 'w') as values

What is the visual basic and how do you access it (2 ways)?

• A user-friendly integrated development environment (IDE) where programmed instructions are maintained - IDE: A software application suite that consolidates tools required to write and test software • To access the VBE, press Alt + F11 or click on Visual Basic under the Developer tab

What can you do with VBA?

• Automate recurring tasks • Automate repetitive tasks • Run code (macros) based on an event happening - A macro is an action or set of actions that can run as many times as needed • Create custom worksheet functions • Change the look of a workbook for an end user • Simplify the use of a workbook for an end user • Control other Office applications from Excel

What are the limitations of macro recording?

• Cannot record control logic (i.e. selecting which actions are completed based on criteria) • Cannot record repetition of action or repeating the same set of steps a number of times • Generated code is very "ugly"/verbose - Excessive, unnecessary code is often added

How do you Bypass Security Settings?

• Path: - File > - Options > - Trust Center > - Trust Center Settings > - Disable all macros with notification • Only required once • Will still be asked before opening each file containing VBA