These are called userdefined functions.

The live in a general module (just like your general subroutines).

But you pass them arguments and return a value.

Option Explicit
Function myFunction(rng1 As Range, rng2 As Range) As Variant

If rng1.Cells.Count 1 _
Or rng2.Cells.Count 1 Then
myFunction = "Error--Single cells only"
Exit Function
End If

If IsNumeric(rng1.Value) _
And IsNumeric(rng2.Value) Then
myFunction = rng1 * rng2 + (rng1 - rng2)
Else
myFunction = "Non-numeric Data"
End If

End Function

One really important thing to remember is to pass it everything that it needs.
If you do something like:

Option Explicit
Function myFunction2(rng1 As Range) As Variant
myFunction2 = rng1.value * worksheets("sheet1").range("a1").value
End Function

then excel doesn't know that your function should recalculate when A1 on sheet1
is changed.

Better would be:

Option Explicit
Function myFunction3(rng1 As Range,rng2 as range) As Variant
myFunction3 = rng1.value * rng2.value
End Function

Then use it like:
=myfunction(b9,sheet1!a1)

=======
the other thing that you'll to do is validate everything you can think of.
Single cell ranges should be single cell ranges.

If you expect numbers, then check for numbers.



Random wrote:

I am sure the answer is somewhere, but I can't seem to find it. I am
trying to add a user function to an Excel sheet so that anyone in the
sheet can use the function in a cell. Is this possible?

I am fairly competent with VB, so the inner code is not an issue. Can
someone point me in the right direction or post a sample code that
does something simple like adding 1 to a cell.

Thanks in advance.

--

Dave Peterson