Create a custom function in Excel

Question

This seems like such an obvious thing that excel must have this feature, I just can't find it.

How can I create a custom function without using VBA? (VBA is too big of a hammer and causes security warnings, etc).

For example, I have a spreadsheet with several very complex formulas. Each of these formulas are replicated in several columns. Each column has hundreds of entries, so each one is replicated hundreds of times. If I tweak something then I have to manually fill-down or copy my change from one column to another.

A simple one looks like this:
=(Payment1 - F$12)*12 + ($D21-H21)
But what I'd like to do is:
=MyFunction(f$12,$D21,H21)
And have the actual formula for "MyFunction" written just once someplace.

I've found a few things that come close to giving me what I want. For example, in tables Excel will automatically replicate changes in a formula down the rest of the column saving you the step of manually selecting the range and doing a "Fill Down".

It will also allow relative references off of named cells, which seems equivalent of a user-defined parameter-less functions.

Answer 1

You can do this for the example you show if I interpret it correctly. If not you may be able to rearrange things slightly to conform

your function has three parameters:

The first comes from row 12 of the current column The second from column D of the current row The third comes from the column two to the right of the current row I assume Payment1 is a named variable already?

Set the cursor in say F21 and then define this name

MyFunction =(Payment1 - F$12)*12 + ($D21-H21)

This will set the parameters to come from the places shown

To understand this better switch to RC mode and type the formula as:

 =(Payment1 - R12C)*12 + (RC4-RC[+2])

You can now propagate down the formula through the F coloumn

=MyFunction

and it will always use the values in the corresponding F12 column Dxx and column Hxx

If you drag the formula to the next column it will use G12, Dxx and Ixx

If you want to change the formula edit it in the define name space

This is a general exception to the rule that you cannot have non-vba UDFs in Excel. Often in Excel the things you want as 'arguments' to the function are actually in fixed places (rows or columns) that can be addressed relatively.

For example you often want to perform a udf on the cell to the left

So a udf giving the cuberoot of the cell to the left would be a named formula like this:

Cuberoot =(RC[-1])^(1/3)

Or in a1 form set the cursor in B1 and type =(A1)^(1/3) And Excel will convert it internally to the RC form

For three args - use three columns

It works and does not suffer the volatility issue mentioned about evaluate()

Yes I know this is an old posting but it may help someone with the same issue.

Bob J.

Answer 2

if you can use text to create the formula, then you can define a name to evaluate the function.

In cell A2, create a name EvalAbove, and in Refers To, enter =evaluate(A1)

This way, you can construct a formula
e.g. B1 contains SUM, B2 contains =("="&B1&"(A2:A5)")
and in B3, you can then put =EvalAbove

This means that if you change the formula name in B1, then B2 will change to show the changed formula, and B3 will change to show the result.

• Note that this still counts as a macro enabled workbook, but there's no VBA code, just named ranges