rExcel getarraytovba size limit

Question

I've got some R code that returns a matrix that is 12 columns wide with over 1M rows. When I try to use getarraytovba to return that matrix into a VBA variant, it fails. When I say it fails I mean that it runs the code without generating any errors but the VBA variable will be empty. If I shrink the R matrix to below 5000 rows then VBA will capture the variable. If it is between 5000 and 20000 (ball park) then sometimes it will work and sometimes it won't. My system has 16GBs of ram and is only 40% utilized when I'm attempting to move the data to VBA. The memory usage doesn't seem to change as I have task manager open as I'm running the code.

I've googled the subject and the only answer I've found is that it is limited by physical memory but since I have nearly 10GB of free memory I think there is more to it than just that. Can anyone help me shed light on why getarraytovba is so limiting?

What is it you want to do with that huge data in VBA? Have you considered doing all your heavy-duty, large-data analysis on the R side and only transferring back results (small summary statistics, reports) to Excel/VBA? This is how I have always used RExcel. — flodel, Jan 09 '13 at 23:30
@flodel That's exactly what I'm doing but I want to be able to spit the results (which can be 10s of thousands, or even millions of rows) back into Excel. I ended up making a function to split up the R matrix and move it to VBA 3000 rows at a time. That fixed it...not ideal but it works. — Dean MacGregor, Jan 13 '13 at 05:15
IMHO, millions of rows in Excel is bad design. Excel should be your dashboard, only for providing user input and reporting small summary outputs. The big data should be elsewhere: on the R side, a database connection, etc. What use do you have for those millions of rows in Excel? — flodel, Jan 13 '13 at 12:27
Millions of rows is an extreme example. It's really for other users that don't have time (or want) to learn even the basics of R. What I do with R is to go through hundreds(or more) of data series and take the difference between each pair and compute various summary statistics for each. The users don't like the idea of having the results pre-filtered so that's why I need to take so much from R into Excel. I'm not doing anything with the data in VBA except to put it in a worksheet. — Dean MacGregor, Jan 14 '13 at 12:25

score 0 · Accepted Answer · answered Jan 21 '13 at 16:27

I wrote the following in VBA to address the shortcoming...

Public Function returnresults()
Dim lResultsize As Long
Dim sBigblock As Variant
Dim lLow As Long
Dim lHigh As Long
Dim vTemp As Variant
Dim i As Long
Dim j As Long
Dim lBigrow As Long
Dim lFullresults As Long
rinterface.RRun "abc<-length(vbaget[,1])"
lFullresults = rinterface.GetRExpressionValueToVBA("abc")
lResultsize = lFullresults
If lResultsize > 1048575 Then
MsgBox "Results exceed 1,048,575 rows.  Excess will be dropped."
lResultsize = 1048575
End If
sBigblock = ThisWorkbook.Sheets("results").Range("a2:m" & lResultsize + 1)
lHigh = lResultsize
lLow = 1
If lResultsize > 3000 Then lHigh = 3000

lBigrow = 1
Do While lHigh <= lResultsize And lLow < lHigh
    rinterface.RRun "temp<-vbaget[" & lLow & ":" & lHigh & ",]"
    vTemp = rinterface.GetArrayToVBA("temp")
        For i = 0 To UBound(vTemp, 1)
            For j = 1 To 13 'This is number of columns in array it could be dynamic
                sBigblock(lBigrow, j) = vTemp(i, j - 1)
            Next j
            lBigrow = lBigrow + 1
        Next i
    lLow = lHigh + 1
    lHigh = lLow + 2999
    If lHigh > lResultsize Then lHigh = lResultsize
Loop

ThisWorkbook.Sheets("results").Range("a2:m" & lResultsize + 1) = sBigblock
End Function

There doesn't appear to be a definitive point at which returning big arrays succeeds or fails but rather the bigger the array the less likely it is to succeed. For my needs doing 3000 rows at a time always works but if someone else should need this they may need to reduce the rows if it doesn't work. — Dean MacGregor, Jan 21 '13 at 16:31

rExcel getarraytovba size limit

1 Answers1