Key terms 2
Terms
undefined, object
copy deck
- Fill series dialog box
- used to create a series of numbers, dates, or text based on a patter
- Database Criteria
- conditions that limit the number of records and results
- MsgBox
- displays info to the user while the macro is executing
- Excel Tables
- a range of data you can treat as a distinct object in a worksheet
- Mixed Reference
- cell reference that cointains both relative and absolute references (B$4)
- Status Bar (Excel and Word)
- a) located at the bottom of the program window b)provides info about open file and current task c) info provided specific to program
- URL
- webstite address
- NPER
- the financial function to calculate the number of payment periods in an investment or loan
- Functions
- a predefined formula that accepts one or more arguments as input, performs the indicated calculation and returns another value as ouput
- Variable Name
- a unique name that identifies a variable
- ascending order
- sorts text alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest
- =InputBox("enter your username","Log in")
- part of a macro
- Compare Value
- a category for a lookup table that is located in the table's first column or row
- Count A
- counts the non-empty cells in a range
- Goal Seek
- an Excel analysis tool that automates the trial and error process by specifying a value for a calculated item and then returning the input value needed to reach that goal
- Hyperlink
- a link in a file, such as a workbook, to info within that file or another file that, when clicked, switches to the file or portion of the file referenced by the hyperlink
- DB function
- returns the declining balance depreciation in which the asset declines by a constant percentage each year
- VBA
- a) VBA is a subset of Visual Basic b) also called subroutines; thus the word Sub at the beginning of the macro and End Sub at the end of the macro c) VBA allows you to modify macros you record with the Macro Recorder d) an "object-oriented program language", in which tasks are performed by manipulation objects
- PV function
- the financial function to calculate the present value of an invesment or loan
- Filename Extensions
- a multi-character code that Office appends to a filename to identify the program in which that file was created (.docx = Word, .xlsx = Excel, .pptx = Powerpoint)
- InputBox
- accepts info from the user while the macro is executing info is stored in a cell for use later in the procedure
- Data Validation
- set of rules that determine what users can enter in a specific cell or range
- DAVERAGE function
- returns the average of the values that meet specified criteria
- PMT
- the financial function to calculate the amount paid into an investment or loan during each payment period
- Visual Basic Editor
- a separate program that works with Excel and all of the Office products to edit and manage VBA code
- Excel Macro Recorder
- a) stores excel commands b) commands are written in Visual Basic for Applications
- Link to list
- a connection between the files that allows data to be transferred from one file to another
- how to put hyperlink on spreadsheet
- a) click cell you want hyperlink in b) Go to Insert tab then click Hyperlink c) type what you want it to say in the "Text to Display" box and then select the hyperlink you want in the file
- Cell Reference
- column and row designation, for example: A9 = column A row 9
- Absolute reference
- cell refernence that points to a specific cell and does not change when copied ($B$4)
- Structured reference (qualified and unqualified)
- the excel table name or table column header that you can use in a formula in place of its cell or range reference
- HLOOKUP
- a function that searches horizontally across a lookup table to retrieve a value from the table; used when the compare values are stored in the first row of the lookup table
- Tablestyle Formatting
- an excel setting that applies styles to four table elements (header row, first column, last column, and totals row)
- If...Then...Else statement
- gives you the ability to make decisions within a procedure and then branch to alternative sets of statements. The Else clause is optional
- Database Query
- a question you ask about the data in a database
- PivotTable/Chart
- a) Pivot Tables/Charts are linked to the underlying data (spreadsheet), but you must refresh the Table/Chart when the spreadsheet data changes b) to refresh the Pivot Table/Chart click the PivotTable Tools options tab on the ribbon, and then in the Data group, click the Refresh button
- Go dialog box
- a window from which you enter or choose settings for how you want to perform a task
- Count
- counts how many cells in a range has a number or date in it
- Syntax
- the rules governing the formation of statements in a programming language
- Lookup Function
- the value you are trying to find or retrieve from a lookup table
- Database Table Field
- an attribute or characteristic of a person, place, or thing in Excel; each column represents a field (last name, address)
- Record
- a collection of related fields that are grouped together; in Excel each row represents a record
- VLOOKUP
- a function that searches vertically down a lookup table to retrieve a value from that table; used when the compare values are stored in the first column of the lookup table
- Right Click an Object
- right clicking brings up a shortcut menu, contents sensitive menu or context sensitive menu
- Run Time Errors
- program will bomb
- descending order
- sorts text in reverse alphabetical order from Z to A, numbers from largest to smallest, and dates from newest to oldest
- freeze rows and columns
- the process of keeping rows and/or columns you select visible in the workbook window as you scroll the worksheet
- F:\FM\Tutorial\Holiday.bmp
- a) this is a file path b)F: is the drive name c) FM is the top-level folder on drive A d) Tutorial is a subfolder in the FM folder e) Holiday.bmp is the full filename with the file extension