BYU Student Author: @Donovon
Reviewers: @Christian, @Klayton
Estimated Time to Solve: 45 Minutes
We provide the solution to this challenge using:
- Excel
- VBA
Need a program? Click here.
Overview
Over winter break you were introduced to the game of disc golf and ever since you’ve been hooked. The sound of a disc rattling the chains fills you with a sense of satisfaction that’s hard to replicate. Despite being busy with college classes and a part time job, you find a way to hit the course at least twice a week. At first you were just playing for fun, but now that you’ve started scoring more pars than bogeys, you figured it was time to start tracking your process. After all, you know that tracking performance metrics will help you to see your progress and motivate you to work even harder.
You started by creating a basic spreadsheet with spaces for each score during your round and some aggregating functions to help you keep track of how you’re performing on each hole of the course. After a round, you insert a new row in the table and type in each hole’s score. It works well, but sometimes you type the wrong value into a cell or insert the row in the wrong spot. You think there just must be a way to automate the data entry and save yourself the headache!
Luckily, you’re in an Information Systems class, and you were just introduced to basic VBA in Excel and how to use it to automate repetitive tasks on spreadsheets. You decided to add a button and VBA to your spreadsheet that will add the scores and date from your round into the table for you. You also plan to use data validation on the values you input to ensure they’re the right type for each cell. Once you’re done, you’ll be able to spend more time on the course and less time tracking the results! Good luck with the exercise, and don’t forget to have fun!
Instructions
- Download the spreadsheet, which contains your current disc golf tracking spreadsheet. All the Excel aggregation and conditional formatting is already present.
- Notice the green input cells and insert button on the right side of the sheet. Your goal is to write a VBA function that reads the input placed in X3:X21 and inserts a new row containing those values into the table.
- Create a VBA function and link it to the button.
- Start by dynamically inserting a new row to the end of the table.
- Insert the date and each hole’s score from the input range into the appropriate place in the table. Don’t forget to erase the data from the input cells when you’re done.
- Make sure that only correctly formatted data is accepted when the button is pressed. If a cell is empty, the date cell doesn’t contain a date, or any of the score cells don’t contain an integer, return an error in a message box, and do not create a new row in the table. Select the cell with the error so the user can see and fix it.
Data Files
Solution
Challenge85_Solution.xlsm
Solution Video: Challenge 85|EXCEL&VBA – Digital Disc Golf
