CODEWITHSUNDEEP
arraysvbafunctionexcel
phillipsK
phillipsK

Reputation: 1516

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

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

Upvotes: 0

Views: 68

Answers (1)

Demetri
Demetri

Reputation: 859

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

Upvotes: 2

Related Questions