UDF returning an array and array formula

crookesa

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

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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
 

crookesa

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



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.
 

crookesa

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

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,828
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top