BYU Student Author: @Kylie_Larsen17
Reviewers: @Keanu_Gauthier, @Michael_Barney
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
- ChatGPT
- Excel
- Python
Need a program? Click here.
Overview
As a sales analyst at Acme Retail, you’ve been tasked with modernizing an existing Excel-based revenue calculation tool. The company currently relies on a VBA script to automate the process of calculating total revenue for each product based on sales data stored in an Excel spreadsheet. However, to improve efficiency and integrate with cloud-based analytics tools, Acme Retail is transitioning to Python. Your job is to use ChatGPT to debug the VBA script, convert it to Python, and troubleshoot any errors that arise. Through this process, you’ll refine the script to ensure accuracy while leveraging AI-powered coding assistance to streamline debugging.
Instructions
-
Download and open the Excel file, ensuring that you have enabled macros in Excel if prompted. Look at the dataset and familiarize yourself with the file.
-
Open the Visual Basic for Applications Editor by pressing ALT + F11. In the left panel, under Modules, open Module1. Read through the script and try to understand what it does.
-
Copy the VBA code and paste it into ChatGPT’s Canvas function. Use Chat to debug the VBA code and explain any errors it fixes. Review the fixes Chat made.
-
Next, use Chat to convert the VBA code into Python code. Open Visual Studio Code, or any Python-compatible IDE, and copy and paste the Chat-generated Python script into a new Python file. Run the Python script and check if it correctly calculates the Total Revenue in column D.
-
If the Python script does not work as expected, copy the error message that is returned and use Chat to fix the error in the code. Apply and re-run the code after Chat provides corrections. Repeat this process until the Python script runs without errors and produces the correct results.
-
Post on this thread a screenshot of your Python code as well as one new thing you learned by completing this challenge.
Data Files
Suggestions and Hints
- It helps to include the name of the Excel file in your prompt so that Chat can incorporate that into the Python code.
- Before running the Python script, make sure you have closed the Excel file first. The code will not run if the Excel file is open.
Solution