Study Guides (238,166)
United States (119,694)
Business (15)
BUS 150 (12)


2 Pages
Unlock Document

University of Miami
BUS 150
Maryann Barber

Vy Vo CIS 150 P MACROS Purpose: Automate things for us Make advanced tasks easy for novices. Take away the needs for human intervention (things we always do or rarely do) Make things go faster, more efficiently without mistakes Macro Security: Macro is a program  Easily attacked by viruses enabling macros: Unless it is saved in a trusted location (Microsoft Start, templates), macro comes disabled by default and you have to enable it in order to use it. saving macro enabled workbooks: Save any files containing macros as .xlsm (macro-enabled wb) Naming the Macro: Start with a letter + can only have letters, numbers, underscores, no spaces Shortcut Keystrokes: 1 letter, Capital (Ctrl + Shift) and normal letters (Ctrl) are different  Can conflict with a default shortcut key  Macro shortcut key takes precedent (only if the macro is in memory  wb is opened) Storing the macro: Personal Macro Workbook  Stores in the XLStart folder. Has extension .xlsb. Generic macro that is always available (opened) when working with any workbook This Workbook  Works specifically for that workbook (Advanced Filter, etc.). Can be applied to any other workbook when it is in memory (wb is opened). When this workbook is not open (or in memory), Macro will not be effective Recording the macro: Record the completion of the command NOT the execution (E.g. Does not record backspace)  When finished, Stop recording  Goes on a module sheet New module sheet: Close the wb then comeback & reopen it vs existing module sheet: Macros in the same session. Can copy & paste macros from different module sheets Relative recording - offsets vs absolute recording - cell addresses Absolute: Range ‘A1’  means A1 and only A1 is selected Relative: Offset (1,0). Range ‘A1’  means address is relative and will be selected based on grid (r, c) r (+/-) = (+) Row below (-) Row above c (+/-) = (+) Column right\\ (-) Column Left Understanding the macro code Commands vs comments: Green with ‘ in front, Ignored by Excel Sub…End Sub: Start - End Selecting a cell/range: Cell: Absolute: Range("B1").Select Relative: ActiveCell.Offset(1, 0).Range("A1").Select Range: Absolute: Range("B1:B3").Select Relative:ActiveCell.Offset(2,0).Range("A1:A3").Select Entering data: ActiveCell.FormulaR1C1 = "Maryann Barber" Formatting the font: Selection.Font.Bold = True, With Selection.Font\.Name = "Arial"\End With Widening a column: Columns("B:B").EntireColumn.AutoFit Refining/editing recorded macros: View Code or F11  VBA Project with modules Renaming macros - Change name in Sub line in the module Reassigning shortcut keys - Developer Tab Go to Macros  Macro Options  Change shortcut Copying/moving macros – Add / Copy & Paste / Delete / Edit InputBox statement: When run, dialogue box open asking for the input  Generic Info  Personal wb E.g. ActiveCell.Formula.R1C1=InputBox (“Enter Your Name”)  When run, Dialogue box opens “Enter Your Name”  Type then move on Assigning macros to a Button - Button can perform any macros (normal or custom) Debugging macros: If the syntax is wrong, it won’t execute it until it is corrected  Debug\Step Into to helps fix your logic (not syntax) DATA VALIDATION Types of data that can be validated: Constant (whole numbers, decimal, list, text length (ID number, Phone number), Date, Time..) NOT Formula Validation rules & specifying acceptable data: Custom  Put in formula (E.g. If (E4=40, true, false))  Can’t enter Overtime hours if regular hours < 40 Input Message: Title, Input message  Optional Error Alerts (Stop: Default, Can’t enter that, Warning: You are not supposed to do this but do you still want to proceed  Flexibility, Information: Message) & messages Validating existing
More Less

Related notes for BUS 150

Log In


Don't have an account?

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.