Purpose Selecting a Cell/Range
• To save time, executes faster than manual work • Find and Select → select → current region
• Macro can eliminate need for human intervention • Can use existing criteria range or prompt user to input criteria
• Macro can automate a task you seldom do Entering Data/Formatting Font/Widening a Column
• Allows giving associates, not proficient in excel, hard tasks Sub NameAndCourse()
Security: Macros can get viruses '
Enabling Macros: prompts when opening workbooks with macros ' NameAndCourse Macro
• Do you trust source of macro? '
o Opening Macro-Enabled Workbook brings up security ' Keyboard Shortcut: Ctrl+n
content & asks if you want to enable macros 'Range("A1").Select
• Trust Center → Trusted Locations (you can add new ones) ActiveCell.FormulaR1C1 = "Ana Gil"
o If a workbook with macros is placed in a “Trusted Range("A2").Select
Location” macros are automatically enabled ActiveCell.FormulaR1C1 = "CIS150"
o i.e Template folder, XLSTART folder Range("A1:A2").Select
Saving Macro-Enabled Workbooks: save workbook as macro enabled Selection.Font.Bold = True
workbook with extension .xlsm Selection.Font.Italic = True
• Macro Enabled Workbook → Default: Macros come disabled With Selection.Font
.Name = "Arial"
Naming .Size = 11
• No spaces, must start with a letter .Strikethrough = False
o Allowed: underscore, letters, numbers .Superscript = False
Shortcut Keystrokes .Subscript = False
• Shortcut Key (Optional): 52 options [any one letter, uppercase .OutlineFont = False
(shift) or lowercase] .Shadow = False
• If you create shortcut key that is same as shortcut key you use as.Underline = xlUnderlineStyleNone
something else, then when macro is in memory its shortcut .ThemeColor = xlThemeColorLight1
will take precedence over default use of it (Ctrl + C) .ThemeFont = xlThemeFontNone
• Do not use as a shortcut key, a letter you use for another shortEnd With
Storing in “This Workbook” Columns("A:A").EntireColumn.AutoFit
• Macro is in memory only when the current workbook is open Range("A3").Select
• Specific macros (only used for 1 workbook) End Sub
Storing in “Personal Macro Workbook”:Always in memory & hidden Refining/Editing Recorded Macros
• First time use, creates Personal.xlsb workbook in XLSTART Sub NameAndCourse()
• Workbooks in XLSTART are open every time you run excel '
• Generic macros, available to any workbook at any time ' NameAndCourse Macro
• Do not record what you type until you complete entry ' Keyboard Shortcut: Ctrl+n
• Do not record commands until you execute the command, i.e. it
does not record opening a dialog box or switching tabs Range("A1").Select
• ActiveCell.FormulaR1C1 = "Maryann Barber"
Developer tab on the left: how much time left Range("A2").Select
• Bottom left button that if you click it, it will stop recordActiveCell.FormulaR1C1 = "CIS150"
• Do not forget to stop recording Range("A1:A2").Select
• Each macro recorded will go one beneath the other Selection.Font.Bold = True
New module sheet vs existing module sheet Selection.Font.Italic = True
• If macro is recorded in same session → same module sheet With Selection.Font
• If you close the workbook and open it again (started a new End With= "Arial"
session), macros you record will go in new module sheetColumns("A:A").EntireColumn.AutoFit
o If you want it on same module sheet, copy and pasteRange("A3").Select
and delete module2 sheet End Sub
• Range (“A1”) Select
ActiveCell.FormulaR1C1 = "Maryann Barber"
• Range (“A1”) means select cell that looks likeA1 (single cell)
• Offset: Relative Reference, how many rows and/or columns you
need to move from previous cell
o Coordinates: i.e. (3,2) move 3 down and 2 right;
(-1,-2) move 1 up and 2 left
• Click relative references for it to use relative references,
otherwise it will default to absolute
o Put in personal macro workbook
o Whatever cell is active before macro is the cell you
start to type the macro in
o Select cell beneath when done and stop recording
• Changes in format can be changed directly in command lines
• Changes made to macro affect other commands, i.e.check ranges
• Do not affect a macro/are ignored
• Asingle quote begins a comment
• Comments are in green
• Need sub-statement
• Every sub needs and N sub, n sub ends macro
• If you change the name, change the sub-statement Debugging Changing Cells
• Looking for a logical error • Picks up initial entries in these cells & you can change them
• Debug Menu → Debugging program to edit codes • Put in defined names for cells, so that you can easily identify
o F8 → executes another line of code what you are changing
• Use debug command to step through macro • You can change more than one group of cells at a time
• Logic of macro isn’t executing correctly, debug to find error o You can prevent changes by checking off the box
• If it is syntactically incorrect, then it cannot execute the code o Every scenario needs to be edited
--------------------------------------------------------------------------------- o Summary Button: create summary report or pivot table
Data Validation: Validated as entered, validate calculations, FormulaAuditing
• i.e. zip codes, phone numbers