making excel functions *resolved*

[seec77]

in excel vba, how can i make functions, so i can use the later like: "=Function()"...
i tried doing a simple "Public Function" but it doesn't work... please help me!

[writelearner]

maybe i misunderstood, but if you want to put a fonction in a cell just type

range("A1").value = "=your fonction"
example with the now fonction
Range("AI6:").Value = "=NOW()"
your automatic editor can capture the fonction like this you don't have to type it

[writelearner]

ah and if you want to save it for later you can use a string like

string = "=now()"
and later
range("A1").value = string

[RAEsquivelC]

Here's one way:

Start a macro by hitting Tools, Macro, Record New Macro. Enter the shortcut, . Then, stop it, by hitting Tools, Macro, Stop Recording.

Now, hit View, Toolbars, Visual Basic
On the Visual Basic toolbar, hit Visual Basic Editor, and double click on the module of the macro you just created. Below the last End Sub, paste your function code. Here is an example that works for me:

=================================
Function SumNum(MyCell As Range) As Integer

' This function adds the digits in a number.
' Its syntax is: =SumNum(B2), or any other cell reference

Dim MyLen, i As Integer
MyLen = Len(MyCell)
For i = 1 To MyLen
SumNum = SumNum + Mid(MyCell, i, 1)
Next i
End Function
================================

The new function is now stored in the VB portion, and it is now available as any other function. As an example:

Any place on your spreadsheet, enter: =SumNum(C23) If cell C23 has the number 2469 in it, then your function will show the sum of the digits 2+4+6+9, or 21.

[seec77]

thanks all!