How To Easily Make
Excel Custom
Function
Just in 10 minutes
Naufal Hadi
Introduction
Microsoft Excel is one of the go-to tools for engineers across many
industries. With its powerful features and easy customization, Excel
makes complex calculations, data analysis, and reporting feel more
manageable. That flexibility is why many engineers rely on it to
handle everyday tasks quickly and efficiently.
Even with its flexibility and powerful built-in functions, the
engineering world often demands more. Some engineering
calculations go beyond what standard Excel formulas can handle
out of the box. That’s exactly why we’ll walk through how to create
custom Excel functions step by step—simple, practical, and
engineer-friendly.
Naufal Hadi
The Problem: Colebrook-
White Equation
𝟏 𝜺 𝟐. 𝟓𝟏
= −𝟐𝒍𝒐𝒈𝟏𝟎 +
𝒇 𝟑. 𝟕𝑫 𝑹𝒆 𝒇
Now take a look on equation above. The equation are called
Colebrook-White equation, that widely used on every engineer that
deal with fluid flow, especially fluid flow in pipe. The objective of this
equation is to calculate the friction factor (f), which is function of
pipe roughness (ε), pipe internal diameter, and Reynold number
(Re). This is very fundamental equation.
Take a look again on equation above, the equation is implicit which
means it could not be solve directly since the friction factor (f) are
appears in left and right side. The equation is also nonlinear.
Therefore, iteration procedure is required in order to solve the
equation. Sometimes, manual iteration in excel is bit tricky and
takes time to develop. Nevertheless, we can develop excel custom
function that could self-iterating to solve this equation.
Naufal Hadi
Step 1 : Activate VBA
Excel custom function will use built-in VBA in excel. So first step is
activate the VBA.
1. Open Excel, than click File tab, then click Option under
File tab, then click Customize Ribbon.
2. New window will appear.
Then thick Developer option
in Customize Ribbon
window. Then click OK
3. New tab Developer will appear on ribbon.
Naufal Hadi
Step 2 : VBA Code
Now we start to write the VBA code
1. Press Alt + F11, to open VBA console
2. Add new module. Click
Insert > Module
3. New module will appear on VBA console.
4. Start to writing VBA code to make custom function.
Naufal Hadi
Step 2 : VBA Code
At this point, we might think that we have to know hot code in VBA.
But worry not, this task could easily solved with little help of AI. We
just need to know the fundamental of the equation, to validate the
result. So let’s open our favourite LLM agents (Gemini, Claude,
Copilot, or ChatGPT). In this case I would use Copilot.
4. Prompt the copilot as follow :
Write excel VBA code to make custom function to solve friction
factor (f) based on the Colebrook-White equation iteratively. Use
Swamee-Jain as initial guess. The input is Reynold number, pipe
roughness (in meter), and pipe inside diameter (in meter). For
laminar region (Reynold number < 2100), use f = 64/Re
approximation. Name the custom function to "frictionfactor"
Prompt explanation : The prompt is ask the Copilot to write VBA
code to make new excel custom function. Recalling on
Colebrook-White, the equation is a function of Reynold number
(Re), pipe roughness (ε), and pipe internal diameter (D). For unit
consistency, we will use meter. Also note for laminar flow, we
used f = 64/Re, since Colebrook-White only valid for turbulent
flow. Finally, we name this function as “frictionfactor”.
Naufal Hadi
Step 2 : VBA Code
5. The Copilot will write the VBA code.
6. Copy the code and paste directly to VBA module. Then
close the module, and save the Excel workbook as xlsm
file.
Naufal Hadi
Step 3 : Test the Function
7. Go back to excel. Prepare the sheet as follows:.
8. Solve the yellow cell manually from pipe surface area to
Reynold criteria. Note this is basic calculation, so no tutorial
are needed. Left blank in Friction Factor because we will
calculate the friction factor using custom function. The
result as follow :
Naufal Hadi
Step 3 : Test the Function
9. Now solve the friction factor using our custom function
=frictionfactor(Reynold_number,pipe_roughness,pipe_internal_diameter)
D20 is cell for Reynold number, D8 is pipe roughness (need
convert to meter by divide 1000), and D5 is pipe diameter (also
need convert to meter). Please note that the input must set in
[Link] result as follow:
10. Finally we solved the equation using excel custom function.
And results are pretty satisfied compared to Moody Chart
0.0195
Source : [Link]
Naufal Hadi
Insight
Microsoft Excel remains a powerful and indispensable tool
that engineers must master, as it provides flexibility, transparency,
and strong analytical capability for everyday engineering work. In
today’s era, it is undeniable that artificial intelligence needs to be
integrated into our professional workflows to keep pace with
increasing complexity and speed of decision-making. By leveraging
AI appropriately, engineers can significantly simplify tasks, save
time and cost, and reduce the risk of human error.
With the presence of AI, capability gaps such as limited
coding skills are no longer major barriers, because many technical
solutions can now be generated, explained, and adapted efficiently.
Nevertheless, strong fundamentals and basic engineering
principles remain essential, as they are required to critically
assess, validate, and verify the results produced by any tool or AI
system.
Naufal Hadi