Notes 1,2,3,4 CIS 150
Popular in Business Analytics (Excel)
Popular in Computer Information Systems
This 15 page Bundle was uploaded by an elite notetaker on Friday August 28, 2015. The Bundle belongs to CIS 150 at University of Miami taught by Mary McKenry in Summer 2015. Since its upload, it has received 54 views. For similar materials see Business Analytics (Excel) in Computer Information Systems at University of Miami.
Reviews for Notes 1,2,3,4
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 08/28/15
CIS 150 Tutorial 8 Developing an Excel Application 1 Visua I Qverview Excel Application and De ned Names 0 A de ned name range name is a word or string of characters associated with a single cell or a range They make entering formulas faster and make the formula easier to understand 0 The Name box displays the cell reference or the de ned name of the selected cell click to display a list of all the de ned names in the workbook 0 An Excel Application is a spreadsheet written or tailored to meet speci c needs It typically includes reports and charts a data entry area a custom interface as well as instructions and documentation 0 An application includes an input area to enter and edit data You can set what types of values can be entered and where the user can enter data Also includes internal documentation as well as comments to explain cell contents and provide instructions 0 An application often includes an area with formulas labels and so forth To generate output such as a report or chart that is based on the input data Users cannot enter data into the output area 0 The De ned Names group on the Formula tab contains buttons to create edit delete and manage de ned names Click the ExpandCollapse Formula Bar button to expand or collapse the formula bar You can expand the formula bar so that entire formula is visible in the formula bar 2 Planning an Excel Application Excel Application includes designing how the worksheets will be organized You can include different sections for each functions depending on the complexity of the project An application includes separate sections to Enter and edit data Store data after it has been entered Use formulas to manipulate and perform calculations on data Displays outputs such as reports and charts The output section contains formulas and labels used to generate the invoice based on the data in the input section 3 Naming Cells and Ranges De ned Names used to assign a meaningful descriptive name to a cell or range A de ned name enable you to quickly navigate within a workbook to the cell with the de ned name You can used them to create more descriptive formulas gt Creating De ned Names There are rules when you de ne a name for a cell or range The name must begin with a letter or an underscore The name can include letters and numbers as well as periods and underscores but no other symbols or spaces oz To distinguish multiword names use an underscore between the words or capitalize the rst letter of each word The name cannot be a valid cell address function name or reserved word The name can include as many as 255 characters although short meaningful names of 5 to 15 characters are more practical The name is not case sensitive 0 Creating a De ned Name for a Cell Range Select the cell or range to which you want to assign a name Click in the Name box on the formula bar type the name and then press Enter OR In the De ned Name group on the Formula tab click the De ne Name button type a name in the Name box and then click the OK button OR In De ned Names group on the Formula tab click the Create from Selection button Specify whether to create the ranges based on the top row bottom row left column or right column in the list click the OK button You can quickly de ne names without typing them if the data is organized in a table format with labels in the rst or last column or top or bottom row The names are based on the row or column labels To Edit and Delete de ned name with the Name Manager Dialog In the De ned Names group on the Formulas tab click the Name Manager New Edit and Delete will help do what need to be done to the de ned names gt Using the Paste Names Command When there is many de ne names it is helpful to list all of them and their corresponding cell addressed in the workbooks documentation using the Paste Names Command Create a list of De ned Names in the Documentation Worksheet Switch to Documentation worksheet click a cell type De ned Names click Enter In the De ned Names group on the Formula tab click the Use in Formula button Click Paste Names The dialog box will open you can paste any selected name or you can paste the entire list x Click the Paste List button the de ned names and their cell references will be pasted If you edit a de ned name or add a new one the list of de ned names and their addresses in the Documentation worksheet is not updated You must paste the list again gt Using De ned Names in Formulas You can create more descriptive formulas by using de ned names instead of cell or range references in formulas If you enter a formula using a range reference rather than de ned name the de ned names DO NOT automatically replace the range reference in the formula As you type a de ned name in a formula the Formula AutoComplete box lists functions and de ned names that begin with the letters you types then you double click the one You can also use the pointandclick method to create a formula with de ned names gt Adding De ned Names to Existing Formulas De ned names are not automatically substituted for the cell addresses in a formula you can replace cell addresses in existing formulas in the worksheet with their de ned names to make the formulas more under understandable 0 Adding De ned Names to Existing Formulas In the De ned Names group on the formulas tab click the De ne Name button arrow and then click Apply Names if the cell reference and de ned name are in the same worksheet In the Apply Names dialog box select the names you want to apply click OK button OR Edit the formula by selecting the cell reference and typing the de ned name or clicking the appropriate cell 4 Visual Overview Data Validation and Protection You can use Data Validation to create a set of rules that determine what users can enter in a speci c cell or range An Input Message appears when the cell becomes active and can be used to specify the type of data the user should enter in that cell An Error Alert appears if a user tries to enter a value in the cell that does not meet the validation rule Workbook Protection limits user s ability to make changes to the workbook s structure and windows Worksheet Protection limits user s ability to modify the worksheet s contents structure or formatting A comment is a text box that is attached to a speci c cell in a worksheet in which you can enter notes A red triangle indicates that the cell contains a comment point to the cell to display the comment box Cells with formulas are usually locked before the worksheet is protected so that users cannot accidentally overwrite the formula by entering new data in those cells 5 Validating Data Entry Validation Rules de ne criteria for the data that can be stored in a cell or range 0 Validation Data In the Data Tools group on the Data tab click the Data Validation button Click the Setting tab x Click the Allow arrow click the type of data allowed in the cell and then enter the validation criteria for that data x Click the Input Message tab then enter a title and text for the input message Click the Error Alert tab and then if necessary click the Show error alert after invalid data is entered check box to insert a check mark Select an alert style and then enter the title and text for the error alert message Click the OK button gt Specifying Validation Criteria 0 When you create a validation rule you specify the type of data allowed as well as a list or range of acceptable values called Validation Criteria Acceptable Values Any Value Any number text or data removes any existing data validation Whole Integers only you can specify the range of acceptable integers Number Any type of number you can specify the range of acceptable numbers List Any value in a range or entered in the Data Validation dialog box separated by commas Dates only you can specify the range of acceptable dates Times only you can specify the range of acceptable times Text limited to a speci ed number of characters Values based on the results of the logical formula gt Creating an Input Message 0 An Input Message provides additional information about the type of data allowed for that cell It is one way to reduce the chance of a dataentry when a user makes a cell active They appear as ScreenTips next to the cell when it is selected You can add an input message even if you don t have a rule to validate the data in it o Create an Input Message In the Data Validation dialog box click the Input Message tab Enter the input message title and text on this tab Verify that the Show input message when cell is selected check box has a mark Enter text in Title and Input Message press OK button gt Creating an Error Alert Style and Message An Error Alert determines what happens after a user attempts to make an invalid entry in a cell that has a validation rule de ne There are three Error Alert Styles Stop Warning and Information The Stop alert prevents the entry from being stored in the cell The Warning alert prevents the entry from being stored in the cell unless the user overrides the rejection and decides to continue using the data The Information alert accepts the data value entered but allows the user to choose to cancel the data entry Create an Error Alert In the Data Validation dialog box click the Error Alert tab Make sure the Show error alert after invalid data is entered check box is marked Click Style arrow select which alert you want and ll out Title and Error Message Click OK button gt Creating a List Validation Rule Data Validation Feature used to restrict a cell to accept only entries that are on a list you create You can create the list of valid entries in the Data Validation dialog box or you can use a list of valid entries in a single column or row You should test the validation rules This is done by entering incorrect values that violate the validation rules 6 Protecting a Worksheet and a Workbook Worksheet protection prevents users from changing cell contents such as editing formulas in worksheets Workbook protection prevents users from changing the workbooks organization such as inserting or deleting worksheets in the workbook This is another way to reduce dataentry errors gt Locking and Unlocking Cells Locked Property determines whether changes can be made to that cell It has no impact as long as the worksheet is unprotected To unlock a cell you turn off the locked property by default it is turned on for each one Unless you unlock each cells in a worksheet before protecting the worksheet all of the cells in the worksheet will be locked and changes can t be made 0 To Unlock Cells Select the worksheet amp the range you want to unlock before protecting worksheet Click the Home tab on the ribbon then click the Dialog Box Launcher in the Font group The Format Cells dialog box opens with the Font tab active x Click the Protection tab and then click the Locked check box to remove the mark Click OK button gt Protecting a Worksheet When you set up worksheet protection you specify which actions are still available to users in the protected worksheet A protected worksheet can always be unprotected you can also add a password to the protected worksheet that users must enter in order to turn off the protection Protecting a Worksheet Select the cells and ranges to unlock so that users can enter data in them In the Font group on the Home tab click the Dialog Box Launcher In the Format Cells dialog box click the Protection tab click the Locked check box to remove the check mark then click the OK button In the Changes group on the Review tab click the Protect Sheet button Enter a password optional Select all of the actions you want to allow users to take when the worksheet is protected Click OK button gt Protecting a Workbook Worksheet protection applies only to the contents of a worksheet not the worksheet itself To keep a worksheet from being modi ed you need to protect the workbook Protecting the structure prohibits users from renaming deleting hiding or inserting windows of a workbook Protecting the windows prohibits users from moving resizing closing or hiding parts of the Excel window 0 Protecting a Workbook In the Changes group on the Review tab click the Protect Workbook button x Click the check boxes to indicate whether you want to protect the workbooks structure windows or both Enter a password optional Click the OK button gt Unprotecting a Worksheet or Workbook Unprotecting the worksheet can be done any time you need to unprotect a worksheet to edit its contents This applies to workbooks also if you need to insert a new worksheet or rename an existing worksheet you can unprotect the workbook to make changes 0 Unprotect Worksheet In the Changes group on the Review tab click the Unprotect Sheet button 7 Inserting Comments Comments are used to explain the contents of a particular cell such as a complex formula Comments are used to provide instructions to users Comments are used to share ideas and notes from several users collaborating project Inserting a Comment Click the cell to which you want to attach a comment Rightclick the cell and then click Insert Comment on the shortcut menu OR In the Comments group on the Review tab click the New Comment button Type the Comment into the box Visual Qverview Working with Macros The Record Macro button opens the Record Macro dialog box which you use to start recording a macro The Macros button opens the Macro dialog box which you use to run or edit existing macros in the open workbook The Macro Security Settings control what Excel will do about macros in a workbook when you open that workbook You can set the level of macro security In the Record Macro dialog box you specify a name shortcut location and description of the macro You can automate any task you perform repeatedly with a macro The macro recorded records keystrokes and mouse actions as you perform them You can customize the Ribbon by showing or hiding tabs You need to show the Developer tab to create macros You can minimize the Ribbon to make more space for a worksheet When the Ribbon is minimized only the Quick Access Toolbar and the tab names are displayed A macro button runs the assigned macro when clicked Placing a macro button on the worksheet makes it easier for a user to run the macro To view the code of a macro you need to open the Visual Basic Editor VBE which is a separate application that works with Excel and all of the Office programs to view debug edit and manage VBA code A Macro is a series of stored commands that can be run whenever you need to perform a task Commands can be viewed andor edited in the Visual Basic Editor When you run a macro Excel performs each of the recorded actions in the same order as when it was recorded Macros perform repetitive tasks faster than you can 9 Automating Tasks with Macros You create a Macro to print a worksheet insert a set of dates and values or import data from a text le and store it in Excel Using a macro you can automate any task you perform repeatedly To display Developer Tab on the Ribbon Click File then at the bottom click Options and after select Customize Ribbon In the right pane click the Developer check box to insert click OK 1O Protecting Against Mam Viruses A Virus is a computer program designed to copy itself into other programs with the intention of causing mischief or harm Macro Viruses are a type of virus that uses a program s own macro programming language to distribute the virus gt Macro Security Settings The Macro Security Setting control what Excel will do about macros in a workbook when you open that workbook The Trusted Center is a central location for all of the security settings in Office 2010 when you set macro security o By default all potentially dangerous content such a macros and workbooks with external links is blacked without warning o If content is blocked the Message Bar Trust Bar located under the Ribbon appears notifying you that some content was disabled m Disable all All macros in all workbooks are disabled and no security alerts macros without about macros are displayed Use this setting if you don t want noti cation macros to run Disable all All macros in all workbooks are disabled but security alerts macros with appear when the workbook contains a macro Use this default noti cation setting to choose on a casebycase basis whether to run a macro Disable all The same as the Disable all macros with noti cation setting macros except except any macros signed by a trusted publisher runs if you digitally signed have already trusted the publisher Otherwise security alerts macros appear when a workbook contains a macro Enable all macros All macros in the workbooks run Use this setting temporarily such cases as when developing an application that contains macros This setting is not recommended for regular use 0 Setting Macro Security in Excel In the Code group on the Developer tab click the Macro Security button Click the option button for the macro setting you want click OK OR Click the File tab and then click the Options button in the navigation bar Click the Trust Center category and then click the Trust Center setting button Click the Macro Settings category then click the option button for a macro setting Click the OK button 1Recording a Macro You can create a macro by using the macro recorder to record keystrokes and mouse actions as you perform them You can record only actions you perform with keyboard or mouse A good choice for creating a simple macro You can create a macro by entering a series of commands in the Visual Basic for Applications VBA programming language 0 A macro must include the following A unique name that begins with a letter Can contain up to 225 characters including letters numbers and underscore Cannot include spaces or special characters Use a descriptive name that describes the purpose 0 A macro needs to be store somewhere the current workbook a new workbook must be opened to use the macro or store it in the Personal Macro workbook The Personal Macro workbook is a hidden workbook names Personalxlsb that opens whenever you start Excel making the macro available any time you use Excel Stores commonly used macros that apply to many workbooks It is most convenient for uses on standalone computers 0 Recording a Macro In the Code group on the Developer tab click the Record Macro button Enter a name for the macro and then specify the location to store the macro You can chose to specify a shortcut You can chose to enter a description of the macro Click the OK button to start the macro recording Perform the tasks you want to automate Click the Stop Recording button 12 Running a Macro Running a Macro means Excel performs each of the steps in the same order as when it was recorded To run you can use to shortcut you created or you can select the macro in the Macro dialog box 0 Running a Macro Press the shortcut key assigned to the macro OR In the Code group on the Developer tab click the Macros button Select the macro from the list of macros click the Run button gt Creating the TransferData Macro 0 Example from the book to create the transferdata macro will make you do the following steps 1 Switch to the Ticket Data worksheet 2 Turn off worksheet protection in the Ticket Data worksheet 3 Switch to the In voice worksheet 4 Select and copy the Transfer Area to the Clipboard 5 Switch to the Ticket Data worksheet 6 Go to cell A1 and then go to the last row in the ticket data table 7 Turn on Relative References The Relative References button controls how Excel records the act of selecting a range in the worksheet By default the macro will select the same cells regardless of which cell is rst selected because the macro records a selection using absolute cell references If you want a macro to select cells regardless of the position of the active cell when you run the macro set the macro recorded to recorded relative cell references 8 Move down one row 9 Turn off Relative References 10 Paste values to the Ticket Data worksheet 11 Go to celAl 12 Turn on worksheet protection 13 Switch to the ln voice worksheet and then make cell B3 the active cell gt Fixing Macro Errors If a macro does not work correctly you have the following options to x it Rerecord the macro using the same macro name Delete the recorded macro and then record to macro again Run the macro one step at a time to locate the problem and then use one of the previous methods to correct the problem You can delete or edit a macro by opening the Macro dialog box selecting the macro from the list and then clicking the appropriate button 13Working with the Visual Basis Editor The Visual Basic Editor VBE use to view the code of a macro is a separate application that works with Excel and all of the Of ce programs to view debug edit and manage VBA code The VBE consists of several components including the Code window that contains the VBA code the Project Explorer window that displays a treelike diagram consisting of a every open workbook and a menu car with menus of commands you use to edit debug and run VBA statements Editing a Macro In the Code group on the Developer tab click the Macros button select the macro in the Macro name list and then click the Edit button OR In the Code group on the Developer tab click the Visual Basic button Use the Visual Basic Editor to edit the macro code x Click File on the menu bar and then click Close and Return to Microsoft Excel gt Understanding the Structure of Macros In VBA macros are called sub procedures each of them begins with the keyword Sub followed by the name of the sub procedure and a set of parentheses Sub PDFln voice PDFlnvoice is the name you gave the macro the parentheses are used to include any arguments in the procedure These arguments pass information to the sub procedure and have roughly the same purpose as the arguments in an Excel Function After Sub PDFlnvoice there are comment about the macro taken from the macro name shortcut key and description you entered in the Record New Macro dialog box Each line appears in green and is preceded by an apostrophe H the apostrophe indicates that the line is a comment and does not include any actions that need to be performed After the comment is the body of the macro a listing of all the commands performed by the PDFlnvoice macro as written in the language of VBA At the bottom of the macro is the following statement which indicated the end of the PDFlnvoice sub procedure End Sub A code window can contains several sub procedures with each procedure separated from the others by the SubProcedureName statements at the beginning and then End Sub statement at the end Sub procedures are organized into modules gt Editing a Macro Using the Visual Basic Editor The Visual Basic Editor provides tools to assist you in writing error free code As you type a command the editor will provide popup windows and text to help you insert the correct code M Creating Macro Buttons Clicking a button with a descriptive label is often more intuitive and simpler for users than trying to remember different combinations of keystrokes Creating a Macro Button In the Controls group on the Developer tab click the Insert button In the Form Controls section click the Button Form Control button x Click the worksheet where you want the macro button to be located drag the pointer untol the button is the size and shape you want and then release the mouse button In the Assign Macro dialog box select the macro you want to assign to the button With the button still selected type a new label Saving WorkIMks with Maw When you save a workbook that contains macros a dialog box opens indicating that the workbook you are trying to save contains features that cannot be saved in a macrofree workbook The default Excel workbook does not allow macros to be stored as part of the le If you want to save the workbook without the macros click the Yes button 0 The default Excel Workbook format which is a macroOfree workbook has the xlsx le extension You need to change this to a macroended workbook which has the xlsm le extension 0 Save a Workbook with Macros Click Save As in the File Name box type what you want to save it as x Click the Save as type button and then click Excel MacroEnabled Workbook Click Save button 1609ening a Workbook with Macros When a workbook with macros opens a Security Warning appears in the Message Bar providing the option to enable the macros so they can be run or to disable them o If you do not click the Enable Content button the macros remain disabled and unavailable during the current session but the other features are still available u Customizing the Ribbon You can customize the ribbon minimize the ribbon create new tabs and groups hide tabs or commands and rename tabs or commands gt Minimize the Ribbon When the Ribbon is minimized only the Quick Access Toolbar and the tab names are displayed o Minimize the Ribbon Near the upperright corn of the Excel Window click the Minimize the Ribbon button If you want to access a tab click the speci c one gt Removing a Tab from the Ribbon You can decided to remove tabs that you do not need on the Ribbon 0 Remove a Tab from the Ribbon Click the File tab on the Ribbon and then click the Options button on navigation bar Click the Customize Ribbon in the left pane and then select the tab you want to remove check box click OK gt Expanding the Ribbon You can expand the Ribbon to show the tabs will all of the options displayed so that they are all visible 0 Expand the Ribbon Near the upperright corner of the Excel Window click the Expand the Ribbon button
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'