ACCT 218 Exam 3
how many arguments can a function contain
255
Number of characters displayed in the formula bar
32,767
how many IF functions can be nested within
64
how many characters can a formula contain
8,192
F5
GOTO
Logical IF
IF(AND), IF(OR) - Purpose to control the answer
Alt F8
Macro Menu
F8
Moves through lines of Macro code when debugging a Macro - when in Visual Basic, it will start the debugger (Ctr;l Shift F8 to stop)
Ctrl + F3
Name Manager
F3
Paste Name / Paste List / Start with = and use F3 to pick name to paste link
Conditional IF
SUMIF, COUNTIF, AVERAGEIF - used to find records matching some rules and some math - all columns must be same size - Requires input cell or string values (">5")
Number of functions in excel
around 460
APPLICATION.screenupdating = True/False
causes the screen to refresh(stutter) or not when using a macro
Relative code
code that uses the active cell based on when the macro is ran ActiveCell.Offset(0, -1).Columns("A:B").EntireColumn.Select ActiveCell.Offset(3, 0).Range("A1").Select ActiveCell.Range("A1:D1").Select
Fill Across Worksheet
command to the Quick Access Toolbar
=HLOOKUP( )
(lookup value, table array, row index num, range lookup) =(150, $B$6:$D$12, 2, TRUE) - True lookup searches range of values
=VLOOKUP( )
(lookup value, table range, column index num) =(150, $B$6:$D$12, 3, False) - False lookup gives exact value, if not then #N/A
What is the advantage of putting them in the Personal workbook?
- It loads automatically when Excel is loaded so macros in that file are always available on that computer - The macros in the Personal workbook are then available to be used in any open Excel workbook. - The Personal.xlsx file is loaded when Excel loads but it is a hidden file so unless you go to View, and choose to Unhide it, you will not be able to edit the macros it contains.
Where can macros be stored (there are 3 different places)
- Normal to save the macro with the workbook they are built in - Can be saved to a new workbook (perhaps start a new workbook called macros.xlsm) and save all your macros in it and carry that file with you to load when you want to use your macros - Store them in the Personal workbook
What are the five different ways macros can be run from within an Excel workbook sheet?
-View Ribbon tab, Macros, View Macros, Run -Use Alt F8 (Macro) and the run button (same as above but fewer steps) -Use Alt F8 (Macro) and the Step into button -Use the keyboard shortcut keys -Use a macro button (or attach the macro to any object) Use a macro button on the quick access toolbar
number of characters displayed in a cell
1,024
F4
Absolute($)
Choices for controlling how your computer works
Advanced Excel options: - "When calculating this workbook" - "General" Trust center: - "Trust Center Settings for External Content"
Sheet names in formulas
Apostrophe is used when there is a space within a sheet name - 'Sheet 1'!B12 Exclamation mark is used when different sheet name (3-D Reference) is used in a formula - Section256!B12:D36
Absolute code
Code which runs at a specific place, no matter what the active cell is. It will always go back to the same cell of a certain worksheet. Columns("A:B").Select Range("A1").Select
Ctrl + Shift + F3
Create names from selection
Date Function
DATEDDIF(older date, new date, "D,M,Y")
Alt F11
Visual Basic editor
Name Manager
must start with backslash, or underscore, or a letter - also listed in this order