Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    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

  2. #2
    Plutonium Lounger
    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?

  3. #3
    4 Star Lounger
    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.

  4. #4
    Plutonium Lounger
    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?

  5. #5
    Uranium Lounger
    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

  6. #6
    4 Star Lounger
    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.

  7. #7
    Plutonium Lounger
    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)

  8. #8
    4 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •