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.

Random,

Open the VBA Editor and go to the Insert menu, and choose Module.
This will add a new code module to the VBProject of the workbook.
In that module, enter your code, something like

Public Function TimesTwo(V As Double) As Double
TimesTwo = V * 2
End Function

Then, you can call this function directly from the worksheet with
a formula like

=TimesTwo(5)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Random" <Random@nwhere wrote in message
...
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.

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