×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Writing my own Excel functions
3

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

RE: Writing my own Excel functions

3
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 = "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

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

RE: Writing my own Excel functions

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

RE: Writing my own Excel functions

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

RE: Writing my own Excel functions

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.

RE: Writing my own Excel functions

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

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

Oops!
Forget the "Dim I as Long" line.
Brain in neutral.

RE: Writing my own Excel functions

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.

RE: Writing my own Excel functions

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.

RE: Writing my own Excel functions

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.

RE: Writing my own Excel functions

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources