Capstone Assignment 2 (VBA and Alteryx) Instructions PDF

Preview

Summary

This is the file that contains a large portion of the project materials. You can find out how to do all the work from this document....

Description

INMT 242 – VBA and Alteryx Capstone Assignment Fall 2021 Possible Points: 100 Due date: IN CANVAS (Assignment Tab) BEFORE 11:59pm Eastern Tuesday, November 30, 2021 NOTE: This is an INDIVIDUAL ASSIGNMENT. Do not share work with other students. If you have questions about this policy, please contact Professor Smith. Violations to the Academic Integrity policy will result in consequences noted in the syllabus. Submission Details: Students must submit a zipped folder containing the following workbooks:   

Excel file named Lastname_Firstname_VBA Alteryx zipped folder Containing 3 Capstone Exercises – SAVE DIRECTLY BACK TO THIS FILE once you have unzipped it!!! 3 pictures of completed Alteryx workflows named as follows o AlteryxCapstoneWorkflow1 o AlteryxCapstoneWorkflow2 o AlteryxCapstoneWorkflow3

Students should CLOSE ALL FILES BEFORE ZIPPING their files into a folder before submitting and DOUBLE CHECK that the correct file is uploaded. No late work will be accepted, and no exceptions will be made for corrupt or blank files due to the timing of the assignment deadline. DOUBLE CHECK BEFORE TIME EXPIRES. A student’s last submission will be graded.

Part 1 VBA (50 points) The excel workbook named Capstone 2 Assignment (VBA) contains several worksheets. Please complete the following tasks on the related worksheets. NOTE: The recording or coding of Macros should enable them to be run on ANY ACTIVE WORKSHEET. Userform Worksheet: Create a VBA Userform with 2 buttons, one label and one textbox. Ensure that the buttons have the following text displayed OK (CommandButton1) and CANCEL (CommandButton2) Copy and paste the following code into the userform object within VBA. Note, if your objects such as USERFORM, COMMANDBUTTONS, LABELS, TEXTBOXES are not named the same thing as referenced in the code, then your code will not run. Ensure the NAME properties of your userform match the code below.

INMT 242 – VBA and Alteryx Capstone Assignment Fall 2021 After pasting the code below, place a button on the Userform worksheet that when clicked opens (runs) the userform. Ensure that the form closes when user clicks Cancel and the Data Validation messages are displayed when the user clicks OK

Private Sub UserForm_Initialize() Me.Caption = Date Label1.Caption = "Please enter a " & vbNewLine & "whole number " & _ vbNewLine & ">0 and < 100" End Sub Private Sub CommandButton1_Click() Dim myoutcome As String myoutcome = checkit(TextBox1.Text) messages (myoutcome) End Sub Function checkit(ByVal valueA As String) If Not IsNumeric(valueA) Then checkit = ("You did not enter a number.") ElseIf valueA - Int(valueA) 0 Then checkit = ("You did not enter an integer.") ElseIf valueA = 100 Then checkit = ("You did not enter a number that is" & vbNewLine & "greater than 0 and less than 100.") Else checkit = ("Thank you for entering valid input.") End If End Function Sub messages(ByVal mymessage As String) MsgBox (mymessage) End Sub Private Sub CommandButton2_Click() Unload Me End Sub Record a macro: Every day at your new job you work with Excel workbooks. As a standard at your company, each excel workbook must have the following data in cells A1:A4 (with the specific noted fonts). At first it doesn't seem like a big deal to do this, but then after a while you get tired of doing it and just want to automate the process.

INMT 242 – VBA and Alteryx Capstone Assignment Fall 2021 Remember that not all files you work with have blank cells in rows 1 through 4 so make sure that in your macro, blank rows are inserted so that you do not overwrite any data. Also, place a blank row after the last row of your header, just to be sure. Record a macro to accomplish this IN THIS WORKBOOK. Name the macro Work_Header. Your header should be formatted as follows: (All font set to Arial Black, Company and Department should be italicized. You may change the color if you wish to match your company)

YOUR NAME Best Place to Work (name of a company you want to work for) __________________ Department (use the department you want to work for)

Today’s Date and current time (using a function that shows date and time)

Misspelled Words: The following code was taken from the website: https://trumpexcel.com/excel-macro-examples/#Highlight-Cells-with-Misspelled-Words This exercise is to teach you that you can find code on the internet, place it in a module and manipulate it to use as you would like. The code will highlight the cells that have misspelled words that are on the active worksheet. After placing this code in a module, run the code to see its effect. Then edit the macro to highlight the words in yellow instead of red. Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub Place a button on the worksheet that will run the macro when pressed. Formulas and Constants: Record 2 macros in this workbook. One macro (called “Highlight_Constants”) should highlight all cells that contain hard keyed (constant) numbers. Please highlight these constants in a light green fill. For the second macro (called “Highlight_Formulas”), create a macro that will highlight

INMT 242 – VBA and Alteryx Capstone Assignment Fall 2021 all formulas on an active worksheet. Please highlight these in light yellow fill and make them underlined. Budget 2017: Use this worksheet to create a macro that will apply a double accounting underline to any group of selected cells if the cells do not already contain a double accounting underline. Your macro should also remove the double accounting underline if it is already present.

Part 2 Alteryx: 50 Points Download the zipped folder named Alteryx Capstone 2. Unzip the folder and save the extraction to your desktop. Close all files and open the folder from your desktop. Save all work back to this folder. You will need to ZIP IT SHUT to upload it when completed. Instructions for each assignment are located below (and inside Alteryx .yxmd files). In addition to uploading your zipped workbook, for credit you must also upload 3 PICTURES of your completed workflows that show your “Alteryx About” information with your name in the picture (see red oval below). Your pictures MUST MATCH YOUR WORKFLOW OR YOU WILL RECEIVE A ZERO FOR THE ASSIGNMENT. NO EXCEPTIONS. See below for an example screenshot.

Capstone Exercise 1 (.yxmd file)

INMT 242 – VBA and Alteryx Capstone Assignment Fall 2021 An Input Data tool contains 100 records, each representing a unique customer transaction information. The Customer ID (a unique identifier for each customer) and Customer Segment provide basic information about the customer. The Weekly Sales column shows the average weekly sales for each customer.

In this exercise, drop the columns "Regions", "Store Volume" and "TransInYear". Then find the 3 customers with the highest average weekly sales in the "Consumer" and "Corporate" segments. Rename the Customer_Segment field to Customer Type. Capstone Exercise 2 (.yxmd file) Combine the Customer Transaction data in "Start 2.1" and "Start 2.2" and ensure there are no duplicate users. Then combine those results with the "User Details". The "User Details" information is delimited by pipes, |. (ctrl + \).

Show the final output ordered by Customer_Segment with the Customer numbers in descending order. Be sure to rename fields as appropriate after parsing out the User Details data. Capstone Exercise 3 (.yxmd file) Combine the customer information in "Start 3.1" with the "Order Details". Ensure both the "Order Date" and "Ship Date" columns are formatted as "date" data type.

Find the number of days to ship after the order was placed for each line item. Also in a new column called Total calculate the subtotal for each line item (price multiplied by quantity). Finally, show columns for the Total cost for each order,the total Shipping Cost for each order, the "Total Saved" for each order, and the maximum days to ship any line item within each order.

In your final output show details about Customer_ID (Renamed ID), Name, Address, State, City, Zip Code as well as all requested calculated fields. Group your final output by Store number....