Home |
Search |
Today's Posts |
#1
|
|||
|
|||
creating new functions
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 |
#2
|
|||
|
|||
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 -- HTH Bob Phillips "Peter R Knight" <Peter R wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote: 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 -- HTH Bob Phillips "Peter R Knight" <Peter R wrote in message ... 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 |
#4
|
|||
|
|||
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 -- HTH Bob Phillips "Peter R Knight" wrote in message ... 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" wrote: 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 -- HTH Bob Phillips "Peter R Knight" <Peter R wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a excel spread sheet using ffinancial functions | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
Creating & Combining Macro to Functions | Excel Worksheet Functions | |||
I have had difficulty in creating user defined functions in Excel | Excel Worksheet Functions | |||
Creating a functions | Excel Worksheet Functions |