Writing my own Excel functions 3

[Binary]

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

 

[GregLocock]

You'll kick yourself when you see how easy it is. This is very powerful, but there are some restrictions on what you can do inside a function, as usual it seems to be a case of suck it and see.

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 = &quot;Dolly&quot
Dim RetVal
' The function can be invoked as follows:
RetVal = MyFunc(&quot;Hello&quot;, 2, &quot;World&quot ' All 3 arguments supplied.
RetVal = MyFunc(&quot;Test&quot;, , 5) ' Second argument omitted.
' Arguments one and three using named-arguments.
RetVal = MyFunc(MyStr:=&quot;Hello &quot;, MyArg1:=7)



Cheers

Greg Locock

 

[Binary]

I'll be gob smacked! That as too simple. I feel like a bit of a wally for even needing to ask.

 

[GregLocock]

I only found out a few months back - I'd always been meaning to learn VBA ever since it was introduced, but this forum made me take the plunge.



Cheers

Greg Locock

 

[Mutt]

Gobsmacked - haven't heard that for a while!

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!!

 

[Denial]

There is a small typo in Mutt's example. The braces (aka curly brackets) are not typed in as part of the formula, but are automatically inserted by Excel after you have done the ctrl-shift-enter bit. As Mutt says, it is often extremely useful to be able to return multiple answers from a single function execution.

 

[lasercat]

Mutt-
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 &quot;MuttFunction&quot; 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.

 

[Denial]

Try the following VBA code:

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 &quot;Private Function ...&quot; 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

 

[Denial]

Oops!
Forget the &quot;Dim I as Long&quot; line.
Brain in neutral.

 

[lasercat]

Thanks for your prompt reply!

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.

 

[Denial]

If desperate to do such an illogical thing, you can always resort to individual invocations such as
=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.

 

[joerd]

I agree with Denial, and I am pretty sure that Excel doesn't allow you to use a non-contiguous range in an array function.
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.

 

[lasercat]

Denial and Jeord,
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