Project Overview
Developed a high-performance Excel-VBA tool for a clinical trial company to replace a formula-heavy planning sheet, improving load time to under 10 seconds. The solution supported complex multi-country trial planning, dashboards, scenario comparisons, and smooth data flow without formulas. Advanced automation and user-friendly forms enhanced usability and scalability.
The Challenge
Our client had an Excel Formula based tool for New drug planning. Because of the formula-based nature of the tool, it had limitations in scaling. Also, the user experience was not optimum as the tool faced delays while opening and saving. This also limited the features which could be handled in a single file.
Requirements: Client came with a strict requirement to update/improve the tool and make the planning sheet load in 10 sec max. There were additional requests to update the cohort modules, add dashboard, handle Booster Inhibitor flow, expand the planning sheet, scenario comparison, compatibility with downstream tools and more. The tool overall allows a user to plan out clinical trial of a new drug in multiple countries, select hospitals, decide the trial style – one shot or cohort based etc.
Our Solution
Formula based approach had already reached its threshold and hence we took a VBA based approach to solve the problem with triggers b/w sheets to ensure that latest data gets transmitted b/w the sheets without use of formulas. We retained many of the values in global cache as soon as the file is opened to improve integration speed. Forms were used to provide easy interfaces. We had graphs on most of the module pages and dashboard were created to guide users on the progress. In addition, there were easy means to take exports from the system in excel format and upload it back into the template to get the tool loaded back with the same data. This facilitated easily saving data pertaining to different stages of planning and restoring back to any point in history easily.
Automation tools were created to randomly generate multiple cases and pick location from different regions.