Study Guides (248,610)
United States (123,460)
Business (15)
BUS 150 (12)

CIS 150 Exam 3.pdf

2 Pages

Course Code
BUS 150
Maryann Barber

This preview shows 80% of the first page. Sign up to view the full 2 pages of the document.
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 Recording ' • 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 Absolute Recording: • Range (“A1”) Select Relative Recording: ActiveCell.FormulaR1C1 = "Maryann Barber" ActiveCell.Offset(1, 0).Range("A1").Select • 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 Commands • Changes in format can be changed directly in command lines • Changes made to macro affect other commands, i.e.check ranges Comments • Do not affect a macro/are ignored • Asingle quote begins a comment • Comments are in green Sub…End Sub • 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
More Less
Unlock Document

Only 80% of the first page are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.