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
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