Results 1 to 8 of 8
Thread: function call (Excel 2003)

20060203, 11:29 #1
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
function call (Excel 2003)
Hi all,
i'm in trying to learn VBA mode, here's my question regarding this statement, the result of this sub is for me to determine what iweek and isweek are, based on the number of weeks from a certain date. What I would like to know is, is it possible to make this a function? And secondly, can I put this into a class module and create a method?
Thanks for anyone that would like to help me on this one
Darryl.
Sub
Dim i As Integer
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value  xDate) / 7)
For i = 1 To z
iWeek = iWeek Mod 13 + 1
Isweek = Isweek Mod 2 + 1
Debug.Print i; iWeek; Isweek
Next i

20060203, 11:40 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: function call (Excel 2003)
It's not clear what you are doing here. Apparently you have a Date/Time Picker control DTPicker1, but what is xDate?

20060203, 12:21 #3
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: function call (Excel 2003)
Hi Hans
Xdate in this case is December 25, 2005 I am using it as a starting point to count the number of weeks to a based on the DTPicker, If I choose one year from that point I can determine the number of weeks since that date which gives the number of times I need to run the loop to find the vairable of iweek and Isweek. What I am trying to teach myself is the difference between a Sub and Function.
Thanks,
Darryl.

20060203, 12:52 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: function call (Excel 2003)
Perhaps you should take a much simpler problem to learn the difference between a procedure (sub) and a function. Including userform controls and trying to return multiple results complicate things and make it harder to concentrate on the basics. Why don't you get one of the beginners' books on VBA or Excel VBA and work through that?

20060203, 14:12 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: function call (Excel 2003)
As is, your Sub does not make sense:
1 What is DTPicker? It looks like it is probably some kind of control, but it is not part of Excel or VBA.
2 You subtract xDate from whatever DTPicker is, but you have never assigned a value to xDate.
3 You use iWeek and Isweek in formulas in the loop, but they have never been assigned a value. So, iWeek is going to cycle the values 0 to 12 and Isweek will cycle the values 0 to 1. Is that what you want?
4 A function returns a value where a Sub does not. If you turn this into a function, what value do you want to return?Legare Coleman

20060203, 16:19 #6
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: function call (Excel 2003)
Hans thanks for the tip,
I have been reading and reading, and still reading, but until I see it being used I work my way thru it. my original question stemmed from a procedure that I had and I wanted to make better or go about it a different way, instead of placing values on a sheet, and extracting values . I thought I could do it all inside VBA, I was right. It was actually quite simple, less inclined to produce an error. Here's what I had,
Public Sub kkk()
Dim x As String 'calender date value
Dim WeekStaff As Integer 'staff week
Dim WeekSup As Integer 'Lob and Sup staff
Sheets("Sheet1").Select
x = UserForm1.Calendar1.Value 'calendar1 control
Cells.Find(what:=x, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True).Activate
ActiveCell.Offset(0, 2).Select
WeekStaff = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
WeekSup = ActiveCell.Value
End Sub
Here's what I came up with,
Public Const xDate As Date = "12 / 31 / 05"
Private Sub CommandButton1_Click()
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value  xDate) / 7)
iWeek = 1
Isweek = 1
For i = i + 1 To z
iWeek = iWeek + 1
Isweek = Isweek + 1
If iWeek = 14 Then
iWeek = 1
End If
If Isweek = 3 Then
Isweek = 1
End If
Next i
End Sub
Here is what I have now, refined by Rory.
Public Const Xdate As Date = "12/27/05" ' another module
Private Sub CommandButton5_Click()
Dim i As Integer
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value  Xdate) / 7)
For i = 1 To z
iWeek = iWeek Mod 13 + 1
Isweek = Isweek Mod 2 + 1
Debug.Print i; iWeek; Isweek
Next i
End Sub
I learned 3 things here, the Mod operator, the use of the ";" in debug, the use of a public const, and the IN function, I also learned a bit about arrays stemming from original post. So what I am asking is can I make this into a Sub, that returns a value into a UDF? so that it returns the variable values of iweek and isweek?
You never know unless you ask.
Thanks Darryl.

20060203, 16:33 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: function call (Excel 2003)
Well, of course it is possible to convert the procedure to a function, but do you have any idea what you are going to do with it?
Function Huh(Date1 As Date, Date2 As Date) As Integer()
Dim i As Integer
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = (Date2  Date1) 7
' Define array
ReDim arr(1 To z, 1 To 2) As Integer
' Fill array
For i = 1 To z
iWeek = iWeek Mod 13 + 1
Isweek = Isweek Mod 2 + 1
arr(i, 1) = iWeek
arr(i, 2) = Isweek
Next i
' Set function result to array
Huh = arr
End Function
Call it as Huh(DTPicker1.Value, XDate)

20060203, 16:49 #8
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: function call (Excel 2003)
Thanks Hans.