Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Custom Function (2002)

    Hi all,

    I would like to know if any one has come across returning two values from a function.

    Option Explicit
    Public Function whatshift(strname As String) As String<-------- is it possible to return two values such as the string and the icount variable from within this function?
    Dim Remployee As Range
    Dim icount As Integer
    Set Remployee = Cells.Find(strname, , , , , xlNext, True)
    With Remployee
    icount = .Row
    End With
    Select Case icount
    Case 4 To 76 'Press days
    With Remployee
    whatshift = "Days"
    End With
    Case 79 To 117 'Press Lobster
    With Remployee
    whatshift = "Lobster"
    End With
    Case 214 To 264 'Priority
    With Remployee
    whatshift = "Priority"
    End With
    Case 131 To 204 'Pressroom nights
    With Remployee
    whatshift = "Nights"
    End With
    Case 121 To 128
    With Remployee
    whatshift = "Traditionals"
    End With
    Case 207 To 218 'press room apprentices
    With Remployee
    whatshift = "Apprentice"
    End With
    Case 284 To 320 'Press Cleaners
    With Remployee
    whatshift = "Cleaners"
    End With
    Case 339 To 359 ' Plate
    With Remployee
    whatshift = "Plate"
    End With
    Case Else
    End Select
    End Function

    Thanks,
    Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Function (2002)

    You can make a function return an array. In the version below, I have removed all the With Remployee and End With lines from the Select ... End Select statement since they don't serve any purpose whatsoever.

    Public Function WhatShift(strName As String)
    Dim Remployee As Range
    Dim strRet As String
    Dim iCount As Integer
    Set Remployee = Cells.Find(strName, , , , , xlNext, True)
    iCount = Remployee.Row
    Select Case iCount
    Case 4 To 76 'Press days
    strRet = "Days"
    Case 79 To 117 'Press Lobster
    strRet = "Lobster"
    Case 214 To 264 'Priority
    strRet = "Priority"
    Case 131 To 204 'Pressroom nights
    strRet = "Nights"
    Case 121 To 128
    strRet = "Traditionals"
    Case 207 To 218 'press room apprentices
    strRet = "Apprentice"
    Case 284 To 320 'Press Cleaners
    strRet = "Cleaners"
    Case 339 To 359 ' Plate
    strRet = "Plate"
    End Select
    WhatShift = Array(strRet, iCount)
    End Function

    You can use this in a worksheet by selecting two cells next to each other, for example A1 and B1, entering a formula such as

    =WhatShift("Jones")

    and pressing Ctrl+Shift+Enter to make it an array formula. The string will be returned in the first cell and the number in the second cell.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Function (2002)

    Other options other than the ARRAY is to use a 2nd parameter to call it again to get the 2nd item

    Option Explicit
    Public Function whatshift(strname As String<font color=red>, iReturn as integer</font color=red>)
    <font color=red>if iReturn <>1 or iReturn <>2 then
    whatshift = cverr(xlErrnum)
    exit function</font color=red>
    endif
    Dim Remployee As Range
    Dim icount As Integer
    Set Remployee = Cells.Find(strname, , , , , xlNext, True)
    With Remployee
    icount = .Row
    End With
    Select Case icount
    Case 4 To 76 'Press days
    With Remployee
    whatshift = "Days"
    End With
    Case 79 To 117 'Press Lobster
    With Remployee
    whatshift = "Lobster"
    End With
    Case 214 To 264 'Priority
    With Remployee
    whatshift = "Priority"
    End With
    Case 131 To 204 'Pressroom nights
    With Remployee
    whatshift = "Nights"
    End With
    Case 121 To 128
    With Remployee
    whatshift = "Traditionals"
    End With
    Case 207 To 218 'press room apprentices
    With Remployee
    whatshift = "Apprentice"
    End With
    Case 284 To 320 'Press Cleaners
    With Remployee
    whatshift = "Cleaners"
    End With
    Case 339 To 359 ' Plate
    With Remployee
    whatshift = "Plate"
    End With
    Case Else
    End Select
    <font color=red>if iReturn =2 then
    whatshift = icount
    endif</font color=red>
    End Function

    so a call with the 2nd parameter = 1 would yield the string and with the 2nd parameter = 2 the icount. Any other value for the 2nd parameter would yield an error

    You could also keep your current code and combine them string and icount together as a result (for example)
    Option Explicit
    Public Function whatshift(strname As String)
    ....[Your code here]
    End Select
    <font color=red>whatshift = whatshift & "("& icount &")"</font color=red>
    End Function

    But if you wanted them separate you would have to parse the string in excel

    Steve

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Custom Function (2002)

    You guys are the best!

    Thanks for the lesson.

Posting Permissions

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