The problem is that I am getting data from many different sources (information providers), and everyone appears to like to use a different way to identify missing values. The funny thing with Variant/Error is that "#N/A" is represented as Error 2042, and if you do CDbl() of that you get the number 2042, which can be quite annoying ;)
The solution I would like most is to be able to specify a "desired" datatype as parameter of the PutArrayFromVBA function instead of relying on inferring the type to use from upper-left array item. Regards, Christian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Erich Neuwirth Sent: Thursday, April 24, 2008 18:23 To: R (D)COM and RExcel server related issues Subject: Re: [Rcom-l] PutArrayFromVBA: numeric vs string This is an array with mixed data types. This is difficult to handle. I do a lot of analyzing for missing values in Excel sheets, but I cannot do that in VBA easily. What you are doing is not using missing values, but string values as placeholders for missing values. The next release (which is probably at least half a year away) will take care of this more carefully. But you really need to create an Error value in VBA to transfer something as missing. Currently, PutArrayFromVBA assume that all the array elements have the same data type. So you should do some R programming after transfer to clean up missing data. When you read a cell with an NA value into a VBA variable, you do not get a string value, you get another data type! Variant/Error Christian Prinoth wrote: > Sorry, that's just one of my concoctions, just replace it with > > a = array("1", 2, 3, "NA", 5, 6, 7, "#N/A", 9, 10) > > Right now I do this: > Sub NumArr2R(VarName As String, arr) > Dim n As Integer, i As Integer > n = ismatrix(arr) > If n = 0 Then > arr = CStr(arr) > ElseIf n = 1 Then > arr(LBound(arr)) = CStr(arr(LBound(arr))) > ElseIf n = 2 Then > arr(LBound(arr), LBound(arr, 2)) = CStr(arr(LBound(arr), LBound(arr, > 2))) > Else > Stop > End If > putarrayfromvba VarName, arr > If n = 0 Or n = 1 Then > rrun VarName & "<-as.numeric(" & VarName & ")" > ElseIf n = 2 Then > rrun VarName & "<-matrix(as.numeric(" & VarName & ")," & UBound(arr) > - LBound(arr) + 1 & "," & UBound(arr, 2) - LBound(arr, 2) + 1 & ")" > End If > End Sub > > Which is not very elegant... > Btw, the function StringArray crashes if the array contains a Null value. > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Erich > Neuwirth > Sent: Thursday, April 24, 2008 17:22 > To: R (D)COM and RExcel server related issues > Subject: Re: [Rcom-l] PutArrayFromVBA: numeric vs string > > Handling missing values can be controlled when transferring data from Excel > sheets to R. > It is not implemented in detail when transferring data from VBA to R. > Where does makearr come from anyway? > It is neither a VBA function vor a function from RExcel. > > > > Christian Prinoth wrote: >> I often use the above function to trasfer arrays to R from VBA. >> Sometimes these arrays have missing values, for example, I may have >> a = makearr("1", 2, 3, "NA", 5, 6, 7, "#N/A", 9, 10) >> > >> If I do >> > >> putarrayfromvba "a", a >> in R I get: >> > a >> [1] 1 2 3 0 5 6 7 0 9 10 >> > >> What should I do to get >> > >> [1] 1 2 3 NA 5 6 7 NA 9 10 >> instead? >> > >> Thanks >> Christian Prinoth >> > >> DISCLAIMER: >> L'utilizzo non autorizzato del presente messaggio è vietato e potrebbe >> costituire reato. Le informazioni e le opinioni contenute in questo >> messaggio sono inviate dal mittente a proprio nome e non costituiscono una >> posizione ufficiale di Epsilon SGR. Questo messaggio e' confidenziale e se >> non e' a Lei indirizzato Le saremmo grati se, via e-mail, ne comunicasse >> l'errata ricezione al mittente. >> The information and opinions contained in this message do not constitute an >> official position of Epsilon SGR and are communicated by the sender in is >> own name. This message is confidential, if you are not the intended >> recipient, please notify the sender immediately. The information and >> opinions contained in this message do not constitute an official position of >> Epsilon SGR and are communicated by the sender in his own name. This message >> is confidential, if you are not the intended recipient, please notify the >> sender immediately. >> > >> _______________________________________________ >> Rcom-l mailing list >> Rcom-l@mailman.csd.univie.ac.at >> http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l >> More information (including a Wiki) at http://rcom.univie.ac.at >> > > > > -- > Erich Neuwirth, University of Vienna > Faculty of Computer Science > Computer Supported Didactics Working Group Visit our SunSITE at > http://sunsite.univie.ac.at > Phone: +43-1-4277-39464 Fax: +43-1-4277-39459 > _______________________________________________ > Rcom-l mailing list > Rcom-l@mailman.csd.univie.ac.at > http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l > More information (including a Wiki) at http://rcom.univie.ac.at > > DISCLAIMER: > L'utilizzo non autorizzato del presente messaggio è vietato e potrebbe > costituire reato. Le informazioni e le opinioni contenute in questo messaggio > sono inviate dal mittente a proprio nome e non costituiscono una posizione > ufficiale di Epsilon SGR. Questo messaggio e' confidenziale e se non e' a Lei > indirizzato Le saremmo grati se, via e-mail, ne comunicasse l'errata > ricezione al mittente. > The information and opinions contained in this message do not constitute an > official position of Epsilon SGR and are communicated by the sender in is own > name. This message is confidential, if you are not the intended recipient, > please notify the sender immediately. The information and opinions contained > in this message do not constitute an official position of Epsilon SGR and are > communicated by the sender in his own name. This message is confidential, if > you are not the intended recipient, please notify the sender immediately. > _______________________________________________ > Rcom-l mailing list > Rcom-l@mailman.csd.univie.ac.at > http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l > More information (including a Wiki) at http://rcom.univie.ac.at > > -- Erich Neuwirth, University of Vienna Faculty of Computer Science Computer Supported Didactics Working Group Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-39459 _______________________________________________ Rcom-l mailing list Rcom-l@mailman.csd.univie.ac.at http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l More information (including a Wiki) at http://rcom.univie.ac.at DISCLAIMER: L'utilizzo non autorizzato del presente messaggio è vietato e potrebbe costituire reato. Le informazioni e le opinioni contenute in questo messaggio sono inviate dal mittente a proprio nome e non costituiscono una posizione ufficiale di Epsilon SGR. Questo messaggio e' confidenziale e se non e' a Lei indirizzato Le saremmo grati se, via e-mail, ne comunicasse l'errata ricezione al mittente. The information and opinions contained in this message do not constitute an official position of Epsilon SGR and are communicated by the sender in is own name. This message is confidential, if you are not the intended recipient, please notify the sender immediately. The information and opinions contained in this message do not constitute an official position of Epsilon SGR and are communicated by the sender in his own name. This message is confidential, if you are not the intended recipient, please notify the sender immediately. _______________________________________________ Rcom-l mailing list Rcom-l@mailman.csd.univie.ac.at http://mailman.csd.univie.ac.at/mailman/listinfo/rcom-l More information (including a Wiki) at http://rcom.univie.ac.at
