How to Automate a Peer Review Process in Excel Using VBA for Algorithm Testing?
I'm collaborating on a project where I'm relatively new to this, so bear with me. I've been banging my head against this for hours. Building a research tool to facilitate peer reviews of algorithm performance within Excel, where multiple researchers need to provide feedback on various test results. The goal is to automate the collection of comments and ratings from different reviewers, consolidating them in a structured format. I've implemented a user form using VBA to collect feedback, but I'm struggling with how to effectively save this data back into the main worksheet. So far, I have created the form with fields for reviewer name, comments, and a rating out of 5. Here’s a snippet of the code I've used to handle the submission: ```vba Private Sub btnSubmit_Click() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Feedback") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 ws.Cells(lastRow, 1).Value = txtReviewerName.Value ws.Cells(lastRow, 2).Value = txtComments.Value ws.Cells(lastRow, 3).Value = txtRating.Value MsgBox "Feedback submitted!" End Sub ``` This code works for capturing data when the form is submitted. However, issues arise when multiple reviewers submit their feedback simultaneously, which leads to data being overwritten or misplaced. I’m considering implementing a way to lock the form until the data is saved, but I need some guidance on how to manage that effectively. Also, I want each piece of feedback to be timestamped automatically. Here’s what I thought: ```vba ws.Cells(lastRow, 4).Value = Now() ``` Unfortunately, with the current structure, I’m unsure about error handling, especially if a reviewer leaves out required fields. What would be the best practice for validating user input before allowing the submission? Furthermore, do you have any suggestions on how to make this process more robust against simultaneous submissions? Thanks for any insights you could provide on these aspects! This is part of a larger web app I'm building. What's the best practice here? This is part of a larger web app I'm building. The project is a CLI tool built with Vba. Thanks for taking the time to read this! For reference, this is a production CLI tool.