Writing my own Excel functions
Writing my own Excel functions
(OP)
Hi there.
I'm wondering if it can be simply explained how to write my own functions in Excel.
I know how to code in VBA but what I want is to be able have a custom function which behaves like all of the built-in Excel functions.
Specifically, I do a lot of determination of volumes and differential volumes and I'd like to be able simply type, for example, =VOL(4,3,8) and have the cell display the volume of an annular cylinder of ODxIDxL.
Kind regards,
Binary
I'm wondering if it can be simply explained how to write my own functions in Excel.
I know how to code in VBA but what I want is to be able have a custom function which behaves like all of the built-in Excel functions.
Specifically, I do a lot of determination of volumes and differential volumes and I'd like to be able simply type, for example, =VOL(4,3,8) and have the cell display the volume of an annular cylinder of ODxIDxL.
Kind regards,
Binary





RE: Writing my own Excel functions
Go into the VBA task (under Macro), insert a new module, type in your function, go back to your spreadsheet and use it!
Here's the blurb from the help file:
This example uses the Function statement to declare the name, arguments, and code that form the body of a Function procedure. The last example uses hard-typed, initialized Optional arguments.
' The following user-defined function returns the square root of the
' argument passed to it.
Function CalculateSquareRoot(NumberArg As Double) As Double
If NumberArg < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
CalculateSquareRoot = Sqr(NumberArg) ' Return square root.
End If
End Function
Using the ParamArray keyword enables a function to accept a variable number of arguments. In the following definition, FirstArg is passed by value.
Function CalcSum(ByVal FirstArg As Integer, ParamArray OtherArgs())
Dim ReturnValue
' If the function is invoked as follows:
ReturnValue = CalcSum(4, 3 ,2 ,1)
' Local variables are assigned the following values: FirstArg = 4,
' OtherArgs(1) = 3, OtherArgs(2) = 2, and so on, assuming default
' lower bound for arrays = 1.
Optional arguments can have default values and types other than Variant.
' If a function's arguments are defined as follows:
Function MyFunc(MyStr As String, Optional MyArg1 As _ Integer = 5, Optional MyArg2 = "Dolly")
Dim RetVal
' The function can be invoked as follows:
RetVal = MyFunc("Hello", 2, "World") ' All 3 arguments supplied.
RetVal = MyFunc("Test", , 5) ' Second argument omitted.
' Arguments one and three using named-arguments.
RetVal = MyFunc(MyStr:="Hello ", MyArg1:=7)
Cheers
Greg Locock
RE: Writing my own Excel functions
RE: Writing my own Excel functions
Cheers
Greg Locock
RE: Writing my own Excel functions
You may know this but just in case :
I use defined functions a lot for short cut models in chem eng and have found very useful to define a function to pass back whole load of calc results in one call - in VBasic do all the necessary calcs, then store the answers in a prescribed order in an array, say temp(i,0) (note the 0 - is required to get answrs in first column) where i is for each answer wanted - at end of function just put functionname = temp On workbook side can define range of cells with array calling the function to access all the values calced - select range A1:A7, type {=gobsmacked(input1,input2,input3)}, press ctrl-shift-enter
Hope that is clear!!
RE: Writing my own Excel functions
RE: Writing my own Excel functions
Thanks for your posting about creating a function that returns an array. I've tried it, and as usual with any Microsoft product the devil is in the details, especially in the statements that dimension the arrays and return the final values.
So, could I impose on you to provide a simple working example that I could work from?
Let's pick a trivial task: Suppose I want to take a row of 5 numbers in Excel and find their (a) sum and (b) their product, and I want to do it all with one user-defined function. I'll call that function MuttFunction. The goal here is to define a single function "MuttFunction" that returns an array of two values.
So, In Excel I select two adjacent cells (becasue I want to get the average and the product, respectively to be in those two cells), and I type
={MuttFunction(A1, A2, A3, A4, A5)},
(where the curly brackets are automatically inserted when I use the Ctrl-Shift-Enter keys.)
Now for the VBA function itself. It might look like:
Function Private MuttFunction()
'Insert correct Array Dimension statements Here!!!
MuttAverage = ??
MuttProduct = ??
End of Function
My thanks to you in advance for providing a working example.
RE: Writing my own Excel functions
Function MuttFunction(a, b, c, d, e)
Dim I As Long
Dim Answers(5) As Double
Answers(0) = a + b + c + d + e
Answers(1) = a * b * c * d * e
MuttFunction = Answers
End Function
Using "Private Function ..." will prevent your MuttFunction from appearing in the list of functions that appears when you use the Insert / Function pull-down.
If you prefer your arrays to begin with element 1 rather than element 0, then you need to include a statement
Option Base 1
at the very start of your module (ie before you begin the definition of any functions). But let's walk before we try to run.
HTH
RE: Writing my own Excel functions
Forget the "Dim I as Long" line.
Brain in neutral.
RE: Writing my own Excel functions
Now, have you ever figured out a way to make Excel use non-adjacent cells as the output elements of an array formula? Excel's Help file says that the ctrl key can select groups of non-adjacent cells, but I haven't been able to make that work when defining cells to be the output cells of an array function.
RE: Writing my own Excel functions
=index(myfunction(arg1,arg2,...),1)
in one cell, and
=index(myfunction(arg1,arg2,...),2)
in some other cell. But it is highly inefficient in that the function will be executed multiple times.
I do not know of any other way, but neither can I say I've given it much thought.
RE: Writing my own Excel functions
Another workaround would be that you call the function in some location outside your results sheet (either remotely on the same sheet or on another, possibly hidden, calculations sheet, and then refer to the appropriate result cells on the results sheet.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Writing my own Excel functions
I didn't want to calculate the array elements twice (it is a lengthy calculation), so I used Jeord's workaround and it works fine. Thanks, gentlemen. It is truly a luxury to have access to your expert advice.
Lasercat