Custom user function without using VBA

Question

Is it possible to create a custom user function in Excel without using VBA?
My problem is I have a long string of standard excel functions strung together to make one very long function. This function is used across 25 different worksheets in my workbook. If I need to make changes to it I would like to do so in only one place and have the changes propagated across all the sheets automatically.

For example, a simple and trivial case would be to add one to the result of SUM(), ie. SUM(mySeries)+1, and call it a new function MYSUM().

I am hesitant to translate the long formula into VBA due to the potential errors that might arise and the added complexity.

Answer 1

Yes, it's possible if you use Excel Named Formulas.

For instance, suppose that you need to calculate the difference between the sums of two consecutive columns (A5:Ax - B5:Bx) in different places in your workbook (x is the final row of the each column):

So you define in A11 a name called diff (any name can be used) as =Sum(A$5:A10)-Sum(B$5:B10), assuming that data start in row 5 until the previous row. It could be any cell, not just A11, but the definition changes in the same way.

Unfortunately, Excel 2010 inserts absolute prefixes ($) and worksheet prefixes, so you need to erase the prefixes, but keeping the exclamation marks and erase most $ characters.

When you move the formulas, most references are relative. So it always calculates the difference between the current column and the following, starting from the row 5 until the line before the total line.

So if you have data between C5 and D100 you put just =Diff in C101 and it calculates Sum(C5:C100) - Sum(D5:D100).

Obviously, you can use local or global names in the named formulas, like you mentioned in your question.

You can read more details in Named Formulas.

Answer 2

I know you said no VBA, but doing as follows does NOT require you actually re-write your formulas, know much about VBA, nor maintain your formulas in VBA. You can write it once and forget about it.

Create a User-Defined Function to extract the formula from a cell as a string:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Create another to evaluate a string like a formula:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

If you had your master formula in sheet1!a1, then you'd put this in every cell that needs to use it:

=eval(getformula(sheet1!a1))

Answer 3

This thread is a bit old, but I stumbled across it looking for something else and thought I'd share a workbook I created a while back trying to accomplish this exact thing: https://www.dropbox.com/s/gf5qrjj5q81tpke/Title_Case_Named_Range_Function.xlsx?dl=1

In short, you CAN create a UDF without VBA (sort of) by hijacking one of Excel's built-in formulas with a 0-length string output and coming up with a method to interpret the inserted data via Named Range string manipulation, but it is beyond inefficient to do so. YOU'VE BEEN WARNED! :)

METHOD:

1. In the example, I used both =REPT([cell reference or some text between quotes here],0) and =TEXT([cell reference or some text between quotes here],";;;") as the starting point to be able to have some form of user input into a built-in formula that would not produce any visible text result in the cell itself.

2. Then I created a Named Range that would be the equivalent of your UDF name (TCase is the primary example in the sheet, but there are also several other variations in there). This Named Range actually references a whole host of other hidden names in the workbook to extract the FormulaText from the cell it's used within and then applies some logic to it to produce a final output as a string. It's worth noting that this method could only return text results and not numbers (though it could return a number as text to be converted in a separate cell).

3. On its own, =TCase produces an error since there is no cell/string reference for it to interpret (in the example sheet, the error message is just a reminder of the format that needs to be followed). But, combining TCase with the expected 0-length string Excel formula allows it to read the input from the formula as a string and apply your logic to it. In the end, the formula/output would look something like below. Note that I inserted the text string directly in the formula below, but you'll see in the example sheet that it can also interpret a single cell reference.

   FORMULA:
   =TCase&REPT("i want to convert this mIxEd sTRing into A TITLE cAsE string!!!",0)

   OUTPUT:

   I Want to Convert This Mixed String Into a Title Case String!!!
   

In the end, I would not recommend following this method for anything other than a morbid curiosity to see what limits Excel can be pushed to. VBA is a much simpler and elegant UDF solution, but I at least had fun taking on the challenge.

NOTE: If you want to see how it works in the example file a bit easier, use the Evaluate Formula from the Formulas ribbon and step in/out of the formula in one of the cells where I inserted an example. I hid all of the underlying Named Ranges to clean it all up after I was done, so they don't show up in the Names Manager unless you unhide them all.

Answer 4

There is a "LAMBDA function" feature in Office 365 that allows you to define a function entirely within Excel as a defined name.

No longer a need for macros and VBA. Certainly no need to "hijack" anything.

The documentation says it is available for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web

Please check this out: https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

Answer 5

The following is based on the answer by @Kirk, but I wanted to provide an MRE/MCVE of the technique he uses.

Add the following names in the Name Manager:

Name            Refers to
myfn            =INDIRECT(myfn.reference)+1
myfn.debug      =myfn.reference
myfn.formula    =FORMULATEXT(myfn.self)
myfn.reference  =MID(myfn.formula;myfn.textcall+1;LEN(myfn.formula)-(myfn.textcall+1)-2)
myfn.self       =INDIRECT("RC";FALSE)
myfn.textcall   =FIND("(";myfn.formula)

Then call the "function" with a relative ref like this:

=myfn&REPT(B2;0)

or with an absolute reference (or any other type of ref):

=myfn&REPT($B$2;0)

Note: to translate to fr-fr, one only need to replace "RC" by "LC"