| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Function basic
Hello all..
I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Guys,
I got one method from some other posting.. i think I can use WorksheetFunction.round(x,y) Thx any way.. Joe_Germany wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Yes ....in English ....
MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Thax Toppers..
I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
....looks like the Excel WORKDAY function!
"Joe_Germany" wrote: Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
It also counts too many if you input a sunday date.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Thanks Toppers.. But where can I find that function.. Is that an
Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
ya.. I got it in add-in.s..
thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
One more qn ..
the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. Joe_Germany wrote: ya.. I got it in add-in.s.. thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Hi
You need Analysis Toolpack Add-In for this. A lot of other useful functions are added too. Arvi Laanemets "Joe_Germany" wrote in message oups.com... Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
=WORKDAY(A1,7)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. Joe_Germany wrote: ya.. I got it in add-in.s.. thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Thanks especially to Toppers & Bob..
Thats true.. What I wanted was the WORKDAY fn.. Since I started making this I thought I would finish this.. So I did.. Adv from WORKDAY ----------------------------- It takes weekend input better.. DisAdv frm WORKDAY ----------------------------- It doesnt add -ve values.. So here it is.. ------------------------ Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) If (WeekDay_St = 7) Then WeekDay_St = 6 End If n_Weekends = WorksheetFunction.Ceiling((WeekDay_St + n - 1) / 5, 1) - 1 n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------------------------------ Now I have to see how I can make it in such a way that it can be added as ADD-INS.. Thx again guys.. Joe Bob Phillips wrote: =WORKDAY(A1,7) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. Joe_Germany wrote: ya.. I got it in add-in.s.. thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Function basic
Just like your proposed AddWorkDays !! <bg
Pete Bob Phillips wrote: =WORKDAY(A1,7) "Joe_Germany" wrote: One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numerical integration | Excel Discussion (Misc queries) | |||
| Custom function | Excel Worksheet Functions | |||
| Custom Function VLookup | Excel Discussion (Misc queries) | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| VBA Custom function for lookup | Excel Worksheet Functions | |||
Linear Mode
