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

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

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

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

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

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

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

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

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

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..

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

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

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..