UDF returning an array and array formula
Results 1 to 5 of 5

Thread: UDF returning an array and array formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2013
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default UDF returning an array and array formula

    I have written a UDF which returns an array and want to display all of those values using an array formula on my sheet.

    The UDF is:

    Code:
    Function GetDutyDetails(Rng As Range) As Variant
    
    
    Dim ArraySize As Integer
    ArraySize = Worksheets("ATCO").Range("atco_non_operational_duties").Rows.count
    
    
    Dim DirArray() As Variant
    ReDim DirArray(ArraySize)
    DirArray = Worksheets("ATCO").Range("atco_non_operational_duties").Value
    
    
    Dim Duties() As Variant
    Dim i As Integer: i = 0
    ReDim Duties(i)
    
    
    For Each cell In Rng
        'Debug.Print cell.Value
        If IsInArray(cell.Value, DirArray) Then
            'Debug.Print ("Cell value - " & cell.Value)
        Else
            'Debug.Print ("== Operational Duty (" & cell.Value & ") == ")
            If IdentifyDutyType(cell.Value) = "Half AL" Then
                Debug.Print ("** Half AL **")
                Duties(i) = ("** Half AL **")
                i = i + 1
                ReDim Preserve Duties(i)
            ElseIf IdentifyDutyType(cell.Value) = "Medical" Then
                Debug.Print ("** Medical **")
                Duties(i) = ("** Medical **")
                i = i + 1
                ReDim Preserve Duties(i)
            ElseIf IdentifyDutyType(cell.Value) = "Early Arrival" Then
                Debug.Print ("** Early Arrival **")
                Duties(i) = ("** Early Arrival **")
                i = i + 1
                ReDim Preserve Duties(i)
            ElseIf IdentifyDutyType(cell.Value) = "Duty Extension" Then
                Debug.Print ("** Duty Extension **")
                Duties(i) = ("** Duty Extension **")
                i = i + 1
                ReDim Preserve Duties(i)
            ElseIf IdentifyDutyType(cell.Value) = "Ad Hoc Duty" Then
                Debug.Print ("** Ad Hoc Duty **")
                Duties(i) = ("** Ad Hoc Duty **")
                i = i + 1
                ReDim Preserve Duties(i)
            End If
          
        End If
    
    
    Next cell
    
    
    ReDim Preserve Duties(i - 1)
    
    
    GetDutyDetails = Duties()
    
    
    End Function
    My immediate window for the supplied range is below which is as expected and correct

    Code:
    ** Early Arrival **
    ** Ad Hoc Duty **

    The formula I am using is an array formula and I have also tried it as a normal formula but am not getting the results as expected

    Code:
    =GetDutyDetails(ATCO!$K$16:ATCO!$K$20)
    The cells where the formula is are just showing the same value (i.e. first in the array) over and over again.

    Code:
    ** Early Arrival **
    ** Early Arrival **
    ** Early Arrival **
    ** Early Arrival **
    ** Early Arrival **
    ** Early Arrival **
    ** Early Arrival **
    Does anyone know where might be going wrong or how to get the expected output on the sheet from the formula I have written.

  2. #2
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,268
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: UDF returning an array and array formula

    Code:
    ArraySize = Worksheets("ATCO").Range("atco_non_operational_duties").Rows.count -1
    Dim i As Integer: i = 1
    Trial fixing these parts first. HTH. Dave

  3. #3
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,268
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: UDF returning an array and array formula

    Whoops that's not right on 2nd read. This part here seems wrong though...
    Code:
    Dim i As Integer: i = 0
    ReDim Duties(i)
    For Each cell In Rng
        'Debug.Print cell.Value
        If IsInArray(cell.Value, DirArray) Then
            'Debug.Print ("Cell value - " & cell.Value)
        Else
            'Debug.Print ("== Operational Duty (" & cell.Value & ") == ")
            If IdentifyDutyType(cell.Value) = "Half AL" Then
                Debug.Print ("** Half AL **")
                Duties(i) = ("** Half AL **")
    This part here...
    Code:
    Dim i As Integer: i = 0
    ReDim Duties(i)
    gives no positions to the array which U then assign a value to it..
    Code:
     Duties(i) = ("** Half AL **")
    So...
    Code:
    Dim i As Integer: i = 1
    Or...
    Code:
       i = i + 1
                ReDim Preserve Duties(i)
                Duties(i) = ("** Half AL **")
    Same change for all. HTH. Dave

  4. #4
    New Member
    Join Date
    Apr 2013
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UDF returning an array and array formula

    So I changed the following line
    Code:
    Dim i As Integer: i = 1
    and my cell with the formula shows 0 now for all the cells. If I show you my Immediate and locals window after your change it looks like this



    I see that the first value is empty and presume that's why it's returning zero.
    If I revert back to my original code and look at the same windows again I see what I'm expecting to see in the values of the Duties() array.



    My problem is still that when I execute the formula in a cell I get the first value from the array repeat down the cells whether I use a formula array (I presume I should use a formula array?) or not.

  5. #5
    New Member
    Join Date
    Apr 2013
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UDF returning an array and array formula

    I'm just getting to trying to find a solution for this one.
    Has anyone as suggestions as to how to display what the function is returning to the Immediate window?

    As in I want the Duties Array values to be returned to the cells in the spreadsheet, if that is possible.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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