excel - Calling a function with a parameter and returning array and then accessing the array - Stack Overflow

admin管理员组

文章数量:1355596

I have a Sub which calls a function with an input value and returning an array. How do I access the returned array?

Sub RunMe()

     pathtoexceldoc = sh.Cells(iRow, "A")    
     sh2.Cells(invbookrow, "E") = gettotalsandvat(pathtoexceldoc)
     ....here...how do i extract what is in arr???....

End Sub

Private Function gettotalsandvat(Pathstr As String) As Variant

     Dim arr(1) As Variant
      .... i do stuff....

     invdate = wkBook.Sheets("Sheet1").Cells(16, "O")
     arr(0) = invdate

     Total = wkBook.Sheets("Sheet1").Cells(row, "P").Value
     arr(1) = Total
     MsgBox arr(0) & ", " & arr(1) ...does contain 2 values....
     gettotalsandvat = arr   .....i return arr... with 2 elements

End Function

I have a Sub which calls a function with an input value and returning an array. How do I access the returned array?

Sub RunMe()

     pathtoexceldoc = sh.Cells(iRow, "A")    
     sh2.Cells(invbookrow, "E") = gettotalsandvat(pathtoexceldoc)
     ....here...how do i extract what is in arr???....

End Sub

Private Function gettotalsandvat(Pathstr As String) As Variant

     Dim arr(1) As Variant
      .... i do stuff....

     invdate = wkBook.Sheets("Sheet1").Cells(16, "O")
     arr(0) = invdate

     Total = wkBook.Sheets("Sheet1").Cells(row, "P").Value
     arr(1) = Total
     MsgBox arr(0) & ", " & arr(1) ...does contain 2 values....
     gettotalsandvat = arr   .....i return arr... with 2 elements

End Function

• excel
• vba

Share Improve this question Follow edited Mar 28 at 12:01 jonrsharpe 122k3030 gold badges268268 silver badges475475 bronze badges asked Mar 28 at 11:44 Charles CatCharles Cat 744 bronze badges 3

• Do you want to write the result into the sheet (as you currently do) or do you want to do something in your code? – FunThomas Commented Mar 28 at 12:04
• yes whatever is in arr, i want to write to the current sheet please – Charles Cat Commented Apr 1 at 8:49
• res = gettotalsandvat("") i cant call it like this as i have to pass the path to my excel doc – Charles Cat Commented Apr 1 at 9:04

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Do something like this:

Sub RunMe()
    Dim res As Variant, i As Long
    res = gettotalsandvat("")
    For i = LBound(res) To UBound(res)
        Debug.Print i, res(i)
    Next
End Sub

You can write the values next to each other into the sheet to each other in one go:

sh2.Cells(invbookrow, "E").Resize(1, UBound(res) - LBound(res) + 1)) = res

Or if you are sure that you have always exact 2 values

sh2.Cells(invbookrow, "E").Resize(1, 2) = res

If you need the data above each other, you either have to loop

For i = LBound(res) To UBound(res)
    sh2.Cells(invbookrow + i, "E") = res(i)
Next

For 2 values, of course you could also simply write

sh2.Cells(invbookrow, "E") = res(0)
sh2.Cells(invbookrow + 1, "E") = res(1)

or you need to create a 2-dimensional array.

Private Function gettotalsandvat(Pathstr As String) As Variant
     Dim arr(0 to 1, 0 to 0) As Variant
     arr(0, 0) = invdate
     arr(1, 0) = Total
End Function 

sh2.Cells(invbookrow, "E").Resize(UBound(res, 1) - LBound(res, 1) + 1), UBound(res, 2) - LBound(res, 2) + 1)) = res

---

If I were you: When you plan to use an array as interface into Excel, start the array index with 1, not with 0. In any case, always specify the lower and upper limit of the array:

Dim arr(1 to 2, 1 to 1) As Variant

本文标签： excelCalling a function with a parameter and returning array and then accessing the arrayStack Overflow