Custom Function basic

[Joe_Germany]

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

 

[Joe_Germany]

Guys,
I got one method from some other posting..

i think I can use WorksheetFunction.round(x,y)

Thx any way..

 

[Guest]

Yes ....in English ....


MsgBox Application.Round(123.467, 2)

Look in VBA help for all functions available.

HTH

 

[Joe_Germany]

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

 

[Guest]

....looks like the Excel WORKDAY function!

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

 

[Bob Phillips]

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]

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

 

[Joe_Germany]

ya.. I got it in add-in.s..
thx..

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

 

[Joe_Germany]

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

 

[Arvi Laanemets]

Hi

You need Analysis Toolpack Add-In for this. A lot of other useful functions
are added too.


Arvi Laanemets

 

[Bob Phillips]

=WORKDAY(A1,7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Joe_Germany]

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

 

[Pete_UK]

Just like your proposed AddWorkDays !! <bg>

Pete