For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...
Figure 1: The final professional user interface for the Excel Solver tool.
Designing a Professional Engineering Tool
In the previous parts of this series, we covered the theory and the core VBA code. Now, let's focus on the User Experience (UX). A raw spreadsheet is fine for quick calculations, but if you want to distribute your tool to other engineers, it needs a clean interface.
Below is a walkthrough of how a robust Gauss Elimination Solver should behave, using screenshots from our reference application. You can use this as a blueprint to design your own interface in Excel.
Advertisement
1. The Startup Screen
When a user opens your engineering tool, they shouldn't be bombarded with raw data. A clean "Welcome Screen" sets the tone.
Design Tip: Use a simple splash screen image and a "Start" button to guide the user. Ensure your VBA macros are enabled.
Figure 2: A dedicated startup screen hides the raw data and welcomes the user.
2. Disclaimer and Safety
Professional engineering tools often include a disclaimer. This protects you as the developer. You can implement this using a simple VBA UserForm that appears on startup.
Figure 3: A UserForm disclaimer ensures the user accepts responsibility before using the tool.
3. The Main Menu
A central hub allows users to navigate between "Reviewing Existing Data" and "Creating New Calculations." This keeps the workbook organized.
Figure 4: A navigation menu separates data entry from calculation tasks.
4. Data Entry and Review
This is the core workspace. In our design, we lock all cells except for the Matrix [A] and Vector {B}. This prevents users from accidentally breaking formulas.
Note: The "Solve" button triggers the VBA macro we wrote in Part 1.
Figure 5: The main calculation workspace where users input the Matrix [A] and Vector {B}.
Advertisement
5. Robustness Testing: Division by Zero
In Part 3, we discussed the "Division by Zero" error. A good UI should handle this gracefully.
If we swap the equations (putting a zero in the pivot position), our Partial Pivoting logic kicks in behind the scenes, and the program still calculates the correct result without crashing.
Figure 6: Proof of concept: The solver correctly handles a zero in the pivot position thanks to partial pivoting.
6. Error Messages
However, if the system is mathematically impossible (Singular Matrix), the program must inform the user clearly. Instead of a generic "Runtime Error 1004," display a custom message box like the one below.
Figure 7: A user-friendly error message explains "Singular Matrix" issues in plain English.
Next Part: Scaling Up
How do we handle larger systems, like 10 equations with 10 unknowns? We will explore dynamic matrix sizing in the final part of our series.
Continue to Part 5:
Solving System of Equations using Gauss Elimination Method (Part 5: Dynamic Scaling)
Comments