Excel User Defined Function: Writing One With AI, Then Checking It

A user defined function, or UDF, is a formula you build once, give a name to, and then call like any built-in Excel function — =WeightedAverage(range1, range2) instead of retyping the underlying SUMPRODUCT expression every time. Excel has two separate ways to build one, they behave differently, and an AI tool will happily hand you either without telling you which you actually asked for. Knowing the difference matters more than the syntax.

This is the reusable-formula companion to the formula bar in Excel, which covers where you go to actually read what a formula does once it exists. A UDF is just a formula worth naming because you will use it more than once.

Two ways to build one, not one

LAMBDA, added to Excel as a native worksheet function, lets you write a formula once, wrap it in LAMBDA(parameters, calculation), and save it under a name through the Name Manager (Formulas tab → Define Name). From then on it behaves exactly like SUM or VLOOKUP — no macro security warning, no separate file type, and it works in Excel for the web as well as desktop.

A VBA user defined function is older: a Function procedure written in the VBA editor (Alt+F11), saved inside a macro-enabled workbook (.xlsm). It can do things LAMBDA cannot — loop over cells procedurally, talk to other applications, read and write files — but it triggers macro-security prompts, will not run in Excel for the web, and breaks if the workbook is opened somewhere macros are disabled by policy, which is common in regulated workplaces.

If a LAMBDA can do the job, use a LAMBDA. Reach for VBA only when the task genuinely needs a loop, a file operation, or something else LAMBDA cannot express — and know that choice before you ask an AI tool for help, because the two answers look nothing alike.

Where AI actually helps

Writing the LAMBDA expression itself is the part an AI tool is genuinely good at, in the same way it is good at any syntax-heavy, well-specified task — general-purpose models have got measurably better at exactly this kind of structured output, tracked year over year by the Stanford AI Index. Naming the parameters clearly, and deciding whether the function needs to handle blank cells, text, or errors gracefully, is still the part you have to think through — a model will write whatever you describe, including a UDF that quietly breaks the first time a blank cell shows up, because you did not mention blanks.

The pattern that produces something worth trusting is the same one covered for prompting generally: a specific request, with an example of the inputs and the expected output, beats a vague one. The original research on this is about language tasks rather than spreadsheets, but the mechanism transfers directly — showing the model one row of real data and the number you expect back gives it something concrete to match, rather than a description it has to guess the edge cases of.

A worked example

Ask an AI tool: "Write an Excel LAMBDA named WeightedAvg that takes a values range and a weights range and returns the weighted average, treating blank cells in either range as excluded from both sums rather than as zero. Here is a sample: values B2:B6 are 10, 20, blank, 40, 50 and weights C2:C6 are 1, 2, 3, 4, 5 — the blank row should be dropped entirely, not counted as a zero-value 40-weight pair."

A reasonable answer comes back as something like =LAMBDA(vals,wts,LET(mask,(vals<>"")*(wts<>""),SUMPRODUCT(vals*wts*mask)/SUMPRODUCT(wts*mask))) — then the instruction to open Name Manager, create a new name (WeightedAvg), paste that into the "Refers to" box, and call it on the worksheet as =WeightedAvg(B2:B6,C2:C6).

Before trusting it, run the sample you gave the model by hand: (10×1 + 20×2 + 40×4 + 50×5) ÷ (1+2+4+5) = 400 ÷ 12 = 33.33. Then check the LAMBDA returns exactly that on the same five rows, not a number that is merely close. If it matches on the case you specified, it has cleared the first bar — it still has not been tested against a fully blank weights range, which is the next thing to try before relying on it in a live sheet.

What goes wrong

• —Blank handling that silently treats a blank as zero instead of excluding the row — the single most common gap, because "ignore blanks" and "treat blanks as zero" produce identical-looking formulas until you test a row that is actually blank.
• —A LAMBDA that works when typed directly into a cell but fails once named, because a relative reference inside it was written assuming a starting cell that Name Manager does not preserve the way a normal formula would.
• —Circular naming — calling the new function something that shadows an existing name already used elsewhere in the workbook, which produces a #NAME? error that has nothing to do with the LAMBDA's own logic.
• —A VBA Function that runs fine on your machine and fails on a colleague's, because their security settings block macros by default and nothing in the cell tells them why the result is #NAME? instead of a number.

None of these are exotic. They are the ordinary shape of a fluent, plausible-looking answer turning out to be wrong on the one case you did not think to test — the general pattern behind most AI output that looks right and is not, applied here to a named formula instead of a paragraph of prose.

Checks before you trust it

1. 01Test on a case you can compute by hand first, the same routine how to check an AI answer when you are not the expert recommends whenever you cannot verify a result by expertise alone.
2. 02Test the edge case on purpose — an entirely blank range, a zero weight, a negative value — rather than only the tidy example you first described.
3. 03Read what the LAMBDA actually contains in the formula bar after naming it, not just the result it returns; Name Manager can silently retain an old version if you edit and forget to update the reference.
4. 04If you moved to VBA, open it on a second machine with default security settings before you distribute the workbook, so the first person to hit a macro warning is not a client.

A named function you have not tested against a blank row is just a formula wearing a more confident name.

Prompting for it well

The same specificity that vendor guidance recommends for any AI task applies directly here — OpenAI and Anthropic both describe a concrete example as more reliable than an abstract description, which is exactly the difference between "average these, weighted" and the fuller request with sample rows above. Writing a prompt that works on the first try covers the same discipline for tasks well beyond spreadsheets, and it is worth reading once rather than relearning by trial and error each time.

The same build-then-verify shape applies whether the reusable thing is a formula, a whole recurring report, or a Solver problem set up once and rerun with new numbers — a UDF is just the version where the reuse itself is the point, so an unchecked mistake gets called from every sheet you build afterward rather than caught once. Quick Analysis is the other end of that spectrum: built-in, no naming required, useful for the one-off calculation a UDF would be overkill for.

What to do next

Next time a calculation is worth reusing, ask for the LAMBDA version first, give the model a worked example with the edge case you actually care about named explicitly, and test that exact case before you name it and trust it elsewhere in the workbook.

Coursium teaches this kind of practical, check-it-yourself habit directly — short lessons and a quiz that tests whether the checking actually stuck, not just the syntax. Stay ahead of AI by learning the tools on your phone.