Numerical Methods For Chemical Engineers Using Excel Vba And Matlab

A Practical Guide to Using Numerical Methods For Chemical Engineers Using Excel Vba And Matlab

Most chemical engineering textbooks treat numerical methods as abstract math exercises. In practice, you are rarely solving a textbook example. You are usually dealing with messy material balance equations, reactor models that refuse to converge, or equipment sizing problems where the numbers don't behave. The tools most people actually use are Excel with VBA for simple automation and prototyping, and MATLAB for anything that requires heavier computation or more robust solvers. This guide walks through what that actually looks like on a real desk. Excel VBA and MATLAB serve different purposes. VBA lives inside spreadsheets, which means it is easy to wire up with data entry forms, reports, and visual outputs. MATLAB has proper matrix operations, optimized solvers, and a debugger that doesn't require you to click through twenty message boxes. The combination works because VBA can call MATLAB as a backend while keeping the frontend in Excel where most engineers prefer to work. To link Excel VBA with MATLAB, you install the MATLAB Builder for Excel add-in or use the MATLAB Engine API for .NET. The simpler approach for most people is the MATLAB Engine API, which you can activate inside VBA with a COM reference. Once the connection is established, you can execute MATLAB functions directly from Excel cells or macros. A typical setup involves creating a VBA userform for input parameters, running the MATLAB solver in the background, and writing results back to the worksheet. This process usually takes about 20 to 30 minutes to get running correctly on the first try, mostly because the COM registration step is finicky depending on your MATLAB version and Windows architecture.

Start with a function file in MATLAB. For a chemical engineering example, consider solving the steady-state energy and mole balances for a non-isothermal CSTR. The system is nonlinear and sometimes stiff. Here is what the MATLAB function looks like: function F = solve_cstr(T, CA, params)
F(1) = v0/L * (CA0 - CA) - k0 * exp(-E/R/T) * CA
F(2) = v0/L * (T0 - T) + (-dH)/rhoCp * k0 * exp(-E/R/T) * CA
F = F(:);
end You call this from Excel using VBA. The VBA code would look like this:

Dim matlab As Object
Set matlab = CreateObject("Matlab.Application")
matlab.Execute "cd 'C:\myproject'"
result = matlab.Run("fsolve", "solve_cstr", initial_guess)
Range("A1").Value = result(1)
Range("B1").Value = result(2) This is the basic pattern. It is not elegant, but it works for most undergraduate and early-career design projects. I have used this exact pattern for distillation column tray calculations, heat exchanger network optimization, and reaction kinetics fitting.

A Real Problem I Encountered

Once, I was modeling an adiabatic flash drum with a three-component mixture using an isFlash function written in MATLAB. The issue was that the Rachford-Rice equation, which I solved using Newton-Raphson in MATLAB, failed to converge when the feed composition was close to the critical point of one component. The flashes would oscillate between two values and never settle. I spent two days trying to tune the initial guesses. Eventually I found that switching to bisection after the first Newton step stabilized the solver. The bisection bracket was automatically determined by checking the sign change of the Rachford-Rice function across the feasible interval. That single workaround cut my computation time from over an hour per case to about four minutes. I learned to never trust Newton-Raphson alone for phase equilibrium calculations. Newton-Raphson converges faster than bisection when it works, but it is less reliable. Many beginners write their own Newton-Raphson solver in VBA and are surprised when it diverges. The reason is that VBA has no built-in numerical differentiation protection. MATLAB's fsolve uses modified Newton methods with line search and trust regions, which handle rough Jacobians much better. Another thing people overlook is that MATLAB handles vectorized operations natively while VBA does not. If you are iterating over 1000 parameter combinations, doing it in VBA loops will be painfully slow. Move the loop to MATLAB and pass arrays at once. The difference is usually hours versus minutes. There is also the issue of variable scaling. Chemical engineering equations often mix quantities spanning many orders of magnitude, like pressure in pascals and concentration in moles per liter. MATLAB solvers can struggle with unscaled variables. Rescaling your equations so that all variables are roughly in the range of 0.1 to 10 before passing them to fsolve or ode15s makes a significant difference in convergence behavior.

When This Approach Fails Completely

The MATLAB-VBA integration breaks down when you need to run simulations in parallel across multiple cores or clusters. The COM interface is single-threaded, so it becomes a bottleneck. For large-scale optimization problems involving thousands of variables, this setup is simply not viable. In those cases, you should move entirely to MATLAB, Python with SciPy, or a dedicated process simulator like Aspen Plus. Also, if your equations involve discontinuities or non-differentiable functions, most standard solvers will fail regardless of the platform. This happens frequently in separation processes with sharp phase boundaries. You do not need expensive software licenses to begin. MATLAB offers a student version and MathWorks provides free tutorials on the Engine API. The MATLAB documentation for fsolve and ode15s is thorough and includes chemical engineering examples. For VBA, the built-in macro recorder is adequate for learning the syntax, but you will eventually need to write code manually. A useful reference is the book Numerical Methods For Chemical Engineers Using Excel Vba And Matlab, which covers both platforms in the context of unit operations and reactor design problems. It is not a comprehensive theoretical text, but it provides runnable code that you can adapt to your own projects. Start with a simple mass balance before attempting reactor models. Get the VBA-MATLAB communication working with a trivial function like squaring a number. Once that works, move to a two-variable system. Each step adds a layer of complexity that can fail in different ways, and isolating the failure point becomes much easier. Debugging a broken VBA-MATLAB link while also debugging a flawed chemical engineering model simultaneously is frustrating and unnecessary.

Use the Parameter Space tool in MATLAB to explore how your solution changes with different input values. This is far more efficient than writing VBA loops to vary inputs manually. Plotting the results directly in MATLAB and embedding the figure in Excel via ActiveX controls keeps your report automated. The time saved on manual figure creation alone justifies the initial setup effort for most ongoing projects.

Summary of What Works in Practice

Numerical Methods For Chemical Engineers Using Excel Vba And Matlab remains relevant because it bridges the gap between spreadsheet-based workflows and robust numerical computation. VBA handles the user interface and data management. MATLAB handles the heavy lifting. The combination is not the fastest possible setup, but it is accessible and sufficient for the vast majority of homework assignments, senior design projects, and many industry tasks. When you hit its limits, you know which limitation you are facing and which tool to move to next. Download links for MATLAB Builder and the Engine API are available on MathWorks official website. Sample VBA code for the CSTR example above can be adapted from the MATLAB documentation examples. The key takeaway is to treat VBA and MATLAB as separate tools with a clear boundary, not as interchangeable components. That mental model prevents most of the headaches people encounter when starting out.