Why isn't this function returning an array with data?

Question

Why is this function not returning any data after passing through my sub procedure?

The three arrays needed are Returned_Array_CNR, Returned_Array_NR, Returned_Array_Rel As Variant.

Did I execute a return statement correctly with Returned_Array = a_AR?

I aim to return three separate arrays with this function and I wish to title them through the Returned_Array parameter defined in the function header.

I pass this variable in the function's argument - am I doing this correctly?

    Public Function a_AR(ByVal cA, cB, ByVal cC As Double, cName As String, Returned_Array As Variant, Optional cD As Double) As Variant()
        Lr = Range("A65000").End(xlUp).row
        ReDim aAR(1 To Lr, 1 To 4)
        ReDim Returned_Array(1 To Lr, 1 To 4)
              For r = 2 To Lr
             cRow = cRow + 1
             aAR(cRow, 1) = Sheets(1).Cells(r, cA) 'Fund Number
             aAR(cRow, 2) = Sheets(1).Cells(r, cB) 'class
             'Debug.Print aAR(cRow, 2) 'debugging
            If cName = "Net Assets" Then
                aAR(cRow, 3) = Sheets(1).Cells(r, cD) / Sheets(1).Cells(r, cC) 'TNA
            Else
                aAR(cRow, 3) = Sheets(1).Cells(r, cC)
            End If
         Next r

        Returned_Array = a_AR

    End Function


    Sub Refactored_Macro()

    'CNR array
    ImportCNR_w_Paramaters "B2", "Entity ID", "Share Class", "Exchange Rate", Returned_Array_NR, "Net Assets"
    'Nav rec array
    ImportCNR_w_Paramaters "B3", "ENTITY_ID", "LEDGER_ITEMS", "BALANCE_CHANGE", Returned_Array_CNR
    'Relationship array
    ImportCNR_w_Paramaters "B4", "Hedge Entity Id", "Entity ID", "Share Class", Returned_Array_Rel

    End Sub

    Sub ImportCNR_w_Paramaters(cell As String, cName1, cName2, cName3 As String, Returned_Array2 As Variant, Optional cName4 As String)

    MyPath = Range(cell)                                'Defines cell that contains path to source that have been saved down
    Workbooks.Open (MyPath)                             'Opens workbook that have been saved down
    Set tempbook = ActiveWorkbook                       'Names  workbook for future closing
    'LR = Range("A65000").End(xlUp).row                  'finds last row in edits



    'ReDim aAR(1 To LR, 1 To 4)
    cRow = 0
     cName = cName1
     cA = Cells.Find(What:=UCase(cName), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
     cName = cName2
     cB = Cells.Find(What:=UCase(cName), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
     cName = cName3
     cC = Cells.Find(What:=UCase(cName), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
     cName = cName4
     cD = Cells.Find(What:=UCase(cName), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Column

    '      For r = 2 To LR
    '         cRow = cRow + 1
    '         'a_AR(cRow,r,cA,cB,cC,cD,cName)

                a_Array = a_AR(cA, cB, cC, cName, Returned_Array2, cD)

    '
    '        aAR(cRow, 1) = Sheets(1).Cells(r, cA) 'Fund Number
    '        aAR(cRow, 2) = Sheets(1).Cells(r, cB) 'class
    '        aAR(cRow, 3) = Sheets(1).Cells(r, cD) / Sheets(1).Cells(r, cC) 'TNA
    '
    '     Next r


    tempbook.Close

    End Sub

It's honestly kind of a mess. If you are going to return more than one variable from the function then you are better off converting it to a Sub instead of a Function. The parameters to the function are strange. Why is the first one passed ByVal? What variable data type do you expect cA and cB are (because they are variants), while cC is defined as a Double. Let's start with those questions... — Excel Hero
– Excel Hero, Commented Oct 21, 2015 at 20:14
a_AR is the name of the function, so if it is to be assigned a value, a_AR must equal something. My guess is that instead of Returned_Array = a_AR you probably mean to say a_AR = aAR and maybe get rid of Returned_Array altogether. — Demetri
– Demetri, Commented Oct 21, 2015 at 20:23
You're using both aAr and a_AR - you need the latter to return the data as the result of the function. — Rory
– Rory, Commented Oct 21, 2015 at 20:23
Yes, yes. But OP wants to return three arrays and only two are possible with the code as is (I guess)... — Excel Hero
– Excel Hero, Commented Oct 21, 2015 at 20:26
@Demetri that worked but I need to pass Returned Array within the function argument as this parameter allows me to return three array, I can only return one without this declaration? — phillipsK
– phillipsK, Commented Oct 21, 2015 at 20:55

Demetri · Accepted Answer · 2015-10-21 22:01:42Z

2

Perhaps you're looking for something like this?

Public Function a_AR(ByVal cA, cB, ByVal cC As Double, cName As String, Optional cD As Double) As Variant()

Lr = Range("A65000").End(xlUp).Row
ReDim aAR(1 To Lr, 1 To 4)
For r = 2 To Lr
    cRow = cRow + 1
    aAR(cRow, 1) = Sheets(1).Cells(r, cA) 'Fund Number
    aAR(cRow, 2) = Sheets(1).Cells(r, cB) 'class
    'Debug.Print aAR(cRow, 2) 'debugging
    If cName = "Net Assets" Then
        aAR(cRow, 3) = Sheets(1).Cells(r, cD) / Sheets(1).Cells(r, cC) 'TNA
        Else
        aAR(cRow, 3) = Sheets(1).Cells(r, cC)
    End If
Next r
a_AR = aAR

End Function
Sub Refactored_Macro()
    'CNR array
    Returned_Array_NR = ImportCNR_w_Paramaters("B2", "Entity ID", "Share Class", "Exchange Rate", "Net Assets")
    'Nav rec array
    Returned_Array_CNR = ImportCNR_w_Paramaters("B3", "ENTITY_ID", "LEDGER_ITEMS", "BALANCE_CHANGE")
    'Relationship array
    Returned_Array_Rel = ImportCNR_w_Paramaters("B4", "Hedge Entity Id", "Entity ID", "Share Class")
End Sub

answered Oct 21, 2015 at 22:01

Demetri

8591 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

phillipsK Over a year ago

did you change anything other than a_AR = aAR ?

Demetri Over a year ago

Yes, I eliminated the "Returned_Array", and just set each of the three variables equal to the function one at a time. I am not sure if that is what you are trying to do, but I assumed that you wanted the value of Array 1 to go to Returned_Array_NR, Array 2 to go to Returned_Array_CNR, and so on. So for each variable, I just set it equal to the function. If this is not quite right, I hope it gives you some ideas as to what you can try.

Collectives™ on Stack Overflow

Why isn't this function returning an array with data?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related