UDF returning an array and array formula

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
87
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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
 
Upvote 0
So I changed the following line
Code:
[COLOR=#333333]Dim i As Integer: i = 1[/COLOR]

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

9A8StKj.png


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.

tNwkvn0.png


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

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top