autstat Support

icon
Avatar
benmarjot
Posts: 6
Hi Thomas,

I have a table of two columns (x and y) of random numbers in Range("Sheet2!A1:B6"). I am trying to fit a linear model and output the coefficients to Range("Sheet2!F3:F4") using

Sub Regression()
Dim rintf As RInterface
Set rintf = New RInterface
rintf.PutDataframe "mydf", Range("Sheet2!A1:B6")
rintf.GetArray "lm(y ~ x, data = mydf)$coefficients", Range("Sheet2!F3:F4")
End Sub

I get an error on the line rintf.PutDataframe "mydf", Range("Sheet2!A1:B6") which reads

Run-time error '-2146233088 (80131500)':
Exception if type 'ExcelDna.Integration.XlCallException' was thrown.

Many thanks,
Ben
2017-07-21 14:28
Avatar
Thomas Baier
Moderator
Posts: 142
Just tried it:

Public Sub test1()
Dim RInterface As New RInterface
Dim rng As Range
Set rng = Range("Sheet2!A1:B6")
RInterface.PutDataFrame "mydf2", rng
End Sub


The data is

> mydf2
1 2
1 4 3
2 5 6
3 8 7
4 9 10
5 11 12

Just noticed, that WithColumnNames is always treated as true, so we got column names here. A1:B6 contains numeric values 1..12

Could you please post a DebugView log starting with Excel startup (or more precisely: connection to R) ending when the error occurs.

Thomas
2017-07-21 14:39
Avatar
Thomas Baier
Moderator
Posts: 142
Another idea: please create the Range-object explicitly as shown in my example and have a look at the range value while single stepping in the debugger. Maybe the range passed to PutDataFrame is the problem...
2017-07-21 14:44
Avatar
benmarjot
Posts: 6
I have tried creating the Range object explicitly and am still getting the same error. Below is the DebugView log:

00000001 0.00000000 [3784] avcore\npsm\localprovider\baseprovider\lib\baseprovider.cpp(604)\NPSMDesktopProvider.dll!00007FFB38C72140: (caller: 00007FFB38C726E5) ReturnHr(9131) tid(21c) 80070490 Element not found.
00000002 0.63346505 [8268] statconnDCOM Installation path is "C:\Program Files (x86)\statconn\DCOM"
00000003 0.63625574 [8268] BDXImpl_init(licensee=SExcel4, connector=R, parameters=dm=2, objptr=007F667C, installpath=C:\Program Files (x86)\statconn\DCOM, licensepath=C:\Program Files (x86)\statconn\DCOM
00000004 0.65168172 [8268] BDX_get_vtbl2: conn=R:dm=2, licensee=SExcel4, objptr=0040B6A0, version=1, path=C:\Program Files (x86)\statconn\DCOM
00000005 0.65743661 [8268] BDX: DateTime enabled
00000006 0.65745598 [8268] BDX: Special .NET features enabled
00000007 0.65747529 [8268] BDX: x86 support (32bit) enabled
00000008 5.21231270 [8268] H&S: Closing Splash Screen
00000009 5.21501255 [8268] ~SplashImpl
00000010 5.21503782 [8268] CStatConnector::initBDXForConnector() vtbl-object@1007B000
00000011 5.21515083 [8268] connector: R
00000012 5.21517324 [8268] DLL: rscproxy.dll
00000013 5.21519947 [8268] environment: R_HOME
00000014 5.21520853 [8268] registry key: Software\R-core\R
00000015 5.21522713 [8268] registry value: InstallPath
00000016 5.21524572 [8268] version key:
00000017 5.21526623 [8268] version value:
00000018 5.21528721 [8268] binary dir: bin/i386
00000019 5.21531200 [8268] connector: RPre2.12
00000020 5.21533251 [8268] DLL: rscproxy.dll
00000021 5.21534967 [8268] environment: R_HOME
00000022 5.21536970 [8268] registry key: Software\R-core\R
00000023 5.21539021 [8268] registry value: InstallPath
00000024 5.21541214 [8268] version key:
00000025 5.21543360 [8268] version value:
00000026 5.21545744 [8268] binary dir: bin
00000027 5.21548128 [8268] connector: Rold
00000028 5.21551609 [8268] DLL: rproxy.dll
00000029 5.21554422 [8268] environment: R_HOME
00000030 5.21555376 [8268] registry key: Software\R-core\R
00000031 5.21557570 [8268] registry value: InstallPath
00000032 5.21559763 [8268] version key:
00000033 5.21561384 [8268] version value:
00000034 5.21563196 [8268] binary dir: bin
00000035 5.21565199 [8268] connector: SciLab
00000036 5.21567011 [8268] DLL: sciproxy.dll
00000037 5.21568871 [8268] environment: SCILAB_HOME
00000038 5.21572733 [8268] registry key: Software\Scilab\%s
00000039 5.21573734 [8268] registry value: SCIPATH
00000040 5.21577883 [8268] version key: Software\Scilab
00000041 5.21580219 [8268] version value: LASTINSTALL
00000042 5.21582270 [8268] binary dir: bin
00000043 5.21586657 [8268] sc_proxy_find_connector: found "R"
00000044 5.21588945 [8268] sc_proxy_find_connector: found "R"
00000045 5.21590424 [8268] sc_proxy_load log follows!
00000046 5.21592617 [8268] sc_proxy_load: sc_proxy_find_connector(,<R>) done
00000047 5.21598816 [8268] sc_proxy_load: sc_proxy_find_connector(,<R>) done
00000048 5.21604443 [8268] Look up "R" in the registry
00000049 5.21605158 [8268] sc_proxy_load: use registry key "Software\R-core\R"
00000050 5.21607304 [8268] looking up registry key at "HKCU\Software\R-core\R\InstallPath"
00000051 5.21611977 [8268] looking up registry key at "HKLM\Software\R-core\R\InstallPath"
00000052 5.21619463 [8268] adding <C:\Program Files\R\R-3.4.1\bin/i386> to path <C:\Program Files (x86)\Microsoft Office\Office16\;C:\ProgramData\Oracle\Java\javapath;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Skype\Phone\;C:\Users\marjotb\AppData\Local\Microsoft\WindowsApps;>
00000053 5.21621799 [8268] new path is C:\Program Files (x86)\Microsoft Office\Office16\;C:\ProgramData\Oracle\Java\javapath;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Skype\Phone\;C:\Users\marjotb\AppData\Local\Microsoft\WindowsApps;;C:\Program Files\R\R-3.4.1\bin/i386
00000054 5.22023964 [3784] avcore\npsm\localprovider\baseprovider\lib\baseprovider.cpp(604)\NPSMDesktopProvider.dll!00007FFB38C72140: (caller: 00007FFB38C726E5) ReturnHr(9132) tid(21c) 80070490 Element not found.
00000055 5.22169876 [8268] No Debugging of rproxy
00000056 5.22176647 [8268] BDX: data mode set to 00000002
00000057 7.52515602 [3784] avcore\npsm\localprovider\baseprovider\lib\baseprovider.cpp(604)\NPSMDesktopProvider.dll!00007FFB38C72140: (caller: 00007FFB38C726E5) ReturnHr(9133) tid(21c) 80070490 Element not found.
00000058 10.88575745 [3784] shell\explorer\taskband2\taskband2.cpp(4148)\Explorer.EXE!00007FF780CC77DA: (caller: 00007FFB4D087DE3) ReturnHr(4996) tid(17c0) 80004005 Unspecified error
00000059 17.41790199 [3784] avcore\npsm\localprovider\baseprovider\lib\baseprovider.cpp(604)\NPSMDesktopProvider.dll!00007FFB38C72140: (caller: 00007FFB38C726E5) ReturnHr(9134) tid(21c) 80070490 Element not found.
2017-07-21 14:51
Avatar
Thomas Baier
Moderator
Posts: 142
Sorry to ask again, but did you check the range object?

The reason why I'm asking is, that the exception class points to a part of the code where the range objectg is converted to some internal representation, and this conversion seems to fail.

When looking at the range object in the debugger, you will find a Value2 property and this property will contain the values (in the form of a multi-dim array).
2017-07-21 16:31
Avatar
Thomas Baier
Moderator
Posts: 142
another question: are you invoking the function from a sheet function? It would really be great if you could provide a complete example and attach it to the thread.

Please upload as a ZIP file
[Last edited by thomas, 2017-07-21 19:23]
2017-07-21 19:16
Avatar
Thomas Baier
Moderator
Posts: 142
Results of my first analysis: The code you provided runs fine for me (with the exception that cells A1:B1 have to contain column names x and y).

If putting the code in a function instead of a sub, and invoking this from the worksheet, then the exception occurs as reported by you. Now I need some more input if your code really fails or if this is not the real code and the real one is in a function called by a cell formula.
2017-07-22 12:15
Avatar
benmarjot
Posts: 6
Hi Thomas,

I tried setting up a new worksheet, copying across the exact same macro, to attach here as an example. The code ran absolutely fine in this case.

I think there must have been an issue with the original worksheet that was causing the problem, I'm trying to see what the differences are between the two that could cause the issue.

Thanks very much for your help looking into this.

Regards,
Ben
2017-07-24 10:22
Avatar
Thomas Baier
Moderator
Posts: 142
Ben,

was the same for me when trying to analyze the problem. I had both a Sub which was invoked manually and a Function invoked by a cell formula. The cell formula was causing the problem.

Could also be that the debugger hides the problem when starting the Sub manually. Did you invoke your Sub via a Toolbar button or a menu item?

Thomas
2017-07-24 10:41
Avatar
benmarjot
Posts: 6
I was invoking the sub using the toolbar button.
2017-07-24 10:56
icon