An Excel-based Automatic Data Processor using VBA

I created an Excel workbook capable of automatically processing data using VBA. The files are available on Github, as well as a guide on how to expand the spreadsheet to keep track of some variables that are not already on it. The reason this was made is because our team cannot rely on internet or data, so we needed an easier method of offline scouting.

There are two premade spreadsheets, one for the Bethesda MD competition and another for the Edgewater MD competition. Good luck to everyone attending!

So, this is a scouting database? What data does it process? What are the inputs and outputs?

1 Like

On the spreadsheet labeled “Data Input” the user inputs a team’s performance during a single match as one row. The user continues entering rows into the Data Input spreadsheet, and all the data points are averaged out for each team into the “Processed Data” spreadsheet. Right now, there are 13 variables kept track of by the workbook. These are:

  • Autonomous Mode Bottom Power Cells
  • Autonomous Mode Outer Power Cells
  • Autonomous Mode Inner Power Cells
  • Autonomous Mode Percentage Initiation Line Crossed
  • Tele-Op Bottom Power Cells
  • Tele-Op Outer Power Cells
  • Tele-Op Inner Power Cells
  • Tele-Op Percentage of times the team completed rotation control
  • Tele-Op Percentage of times the team completed position control
  • Endgame Percentage of times team completed climb
  • Endgame Percentage of times the alliance generator switch was balanced
  • Win Percentage
  • Games Played

A guide to adding custom variables and processing them through the code is available on the Github

I have several questions/comments:

  1. On the macro enabled sheet, quite literally nothing happens when I press the button. I entered some fake data which should be valid-ish (assuming I’m understanding each header correctly). What Macro is supposed to execute?

  2. On the macro enabled sheet, I also tried to manually execute the Macros (not using the button) and nothing happened.
    EDIT: I discovered that I need to manually enter corresponding team numbers on each sheet. If I were you, I’d build it in to your code to do that automatically.

  3. What is the purpose of the non-macro version? If it isn’t doing anything with the data, why would I use it? (This is not intended in a snarky way, I am actually trying to understand the use case)

  4. Is there anything being calculated using macros that cannot be calculated with simple formulas? When using formulas rather than VBA, it will generally load much faster, work on a variety of additional platforms, and be easier to debug and modify for outside users. Since I can’t really see what’s actually happening (based on questions 1 and 2 above), I can’t say for certain that it’s not doing anything that requires visual basic, though I’m skeptical.

To answer your questions:

While I am not new to programming, I am very new to VBA, which next to no one on the team has experience in. While I may integrate this feature in the future, for now I have other priorities with the team, and just needed a quick script to process data with the spreadsheets the team already had, which included team numbers already manually entered.

The macro enabled spreadsheets are of the .xlsm file type, which has the ability to mess with files on the computer. Because of this, it is possible that some computers, say school laptops, would not allow users to download .xlsm files as a whole, so I included the .xlsx files separately along with code that can be copypasted into it. Even if this is just an excuse, including the .xlsx sheets (which I already had) into the repo was easy and has no negative consequences.

In fact, someone on the team did create a spreadsheet which uses formulas to do the same thing, after we realized that a google sheets script prototype would not be viable in a competition without a reliable internet connection. My issue with it was that their version required the Data Input spreadsheet to be sorted by team number, while I wanted to be able to quickly enter data into the sheet, without having to sort by team number often.

I hope this helped clear some stuff up!

I’m mostly clear on everything except for this part. A quick =SORT(UNIQUE(RANGE)) in your team number column and a =FILTER() or =VLOOKUP() or =HLOOKUP() should be able to do exactly what you need.

Update: Three new variables have been added. These are:

  • How many power cells a team scores in the bottom port per second
  • How many power cells a team scores in the outer port per second
  • How many power cells a team scores in the inner port per second

Would it be possible to add “cells obtained” during autonomous period? In addition to just averages It would be really cool if there was a mechanism that showed trends overtime as well as highlighted outliers(bad match lost coms etc) rather than only having the numerical data. We have always used paper in the passed because of this reason, but if it could be easily implemented we would love to give your spreadsheet a try!