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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com