creating new functions

[Guest]

hi,
how do I create new functions in Excell? a simple example is I'd like a
MEAN() function that does the same as the AVERAGE() function. I'd like it
available to all work book (i.e. save it with Excell, not a particular
workbook).

many thanks

Pete

 

[Bob Phillips]

Generally, you can create UDFs that can be saved as add-ins or in
Personal.xls workbooks. If the latter, they have to be prefixed with the
workbook when using in another workbook.

But I am confused, if you want a MEAN that foes the same as AVERAGE, isn't
that AVERAGE <vbg>

 

[Guest]

I just used that as a simple example because i often try to type
mean(a1:a123) and it's obviously not recognised. but there are others that
I'd like e.g. I often have date times in the format yyyymmddhhmmss and the
only way to convert it I've found it something like:
=DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2)) +
TIME(MID(B2,9,2),MID(B2,11,2),RIGHT(B2,2))
but I'd like to add this ass a new function such as MyDateConvert(). I
tried entering it in VBA like this:
Public Function FUMSDATE(ref)
FUMSDATE=DATE(LEFT(ref,4),MID(ref,5,2),MID(ref,7,2)) +
TIME(MID(ref,9,2),MID(ref,11,2),RIGHT(ref,2))
End Function
but can't seem to get that to work even in one sheet - let alone for all
workbooks. I'm not very familiar with VBA

many thanks

Pete

 

[Bob Phillips]

You function should look like

Public Function FUMSDATE(ref)
FUMSDATE = DateSerial(Left(ref, 4), Mid(ref, 5, 2), Mid(ref, 7, 2)) + _
TimeSerial(Mid(ref, 9, 2), Mid(ref, 11, 2), Right(ref, 2))
End Function