DCOM, R and ExcelDuncan Temple LangDepartment of StatisticsUC Davis1OutlineAn exampleThe DCOM modelRDCOM - facilities in R.Excel model.EventsReflection in R.Mention R-DCOM Servers2ExampleBootstrap example that we looked at for CGI but this time in Excel.Take inputs from the user for the statistic to be computed for each bootstrap sample, and the number of bootstrap samples to create.Outputs will be Numerical summary of the bootstrap distribution.Density plot of the distributionAll the samples and the associated statistic.Displayed on a new worksheet.3General commentsExcel is very popular tool, familiar to many, and relatively easy to use.Covenient workflow model for arranging computations in visual manner.However, computational engine is poor for statistics incomplete and poor accuracy and precision in statistical methods (See McCullagh, The American Statistician)Programming languages not very good Visual Basic - poor language C++ - too low level when we have R.Very beneficial to merge R’s functionality and language with Excel’s interface and visual programming metaphor.4R GUIExcelEvent fromcommand buttonCreate Excel instanceadd form elements.Add worksheet with bootstrap results.5DCOMR and Excel are two separate applications, yet we need to be able to control Excel from within R.And we need Excel to be able to signal to R to do something.So we need some sort of inter-application communication.Need to be able to pass data from R to Excel and to be able to call R functions from within Excel.This is bi-directional communication.Exchanging data files won’t work.6DCOM stands for Distributed Component Object Model.R and Excel act as Components of a bigger application that we are building.Both R and Excel provide Objects within this “application”.We can think of Excel as being an Object that is made up of lots of other sub-objects (e.g. workbook, worksheet, cells, buttons, etc.)We will focus on the pair R and Excel, but COM means that we can connect any number of components together. 7DCOMThe D in DCOM stands for Distributed.It allows for R and Excel to be running on different machines and still communicate.So we might have R on a high performance compute server and Excel on the user’s laptop.Distributed means security and permissions are issues.DCOM provides facilities for specifying these.DCOM is Windows-specific. It is very similar to CORBA - the Common Object Request Broker Architecture which is platform-neutral.8RDCOMClient packageFrom within R, we want to create a new instance of Excel and a worksheet with form elements.The RDCOMClient package provides functionality in R to create DCOM objects,get and set their data fields, called PROPERTIESand call their methods, like R functions but specific to a COM class and instances of that class.9RDCOM BasicsCOMCreate() creates a new instance of a COM object.ex = COMCreate(“Excel.Application”)word = COMCreate(“Word.Application”)ie = COMCreate(“InternetExplorer.Application”)or any other registered COM object.This gives us a reference or handle to an object that represents that DCOM server.It has class “COMIDispatch”.We can also connect to existing instance - getCOMInstance(“Excel.Application”)10DCOM BasicsEach DCOM server provides methods/functions and properties/data that can be accessed by a client.These can return other DCOM objects, each with their own methods and functions.We can access all of these from within Rbut we have to know which functions and methods are of interest.How do we find out what properties and methods are available ?11Discovering properties and methodsRead books and tutorials on the Web Excel 2003 VBA Programmer’s Reference MSDN website - msdn.microsoft.com/library/For Microsoft Applications (and others) use the Object Browser in the Visual Basic Editor.Tools -> Macro -> Visual Basic Editor followed by View -> Object Browser(or Alt + F11 followed by F2 keys)Use R commands to query the Type Library of the DCOM object.1213SWinTypeLibsThe SWinTypeLibs package for R allows us to explore information about DCOM classes.LoadTypeLib() and getFuncs() are the two main functions for our purposes.lib = LoadTypeLib(ex)names(lib) # to get the names of the classes in the librarylib[[“Application”]]els = getFuncs(lib[[“Worksheet”]])names(els)els[[‘Range’] # PropertyGetDescription with 2 arguments14Application BasicsThe different Microsoft Application DCOM objects (Word, Excel) share a similar object model.For example, one can make them visible by setting the Visible property to TRUE, and invisible by setting to FALSE.In R, we can get the value of a DCOM property in an object via the [[ ]] operator, obj[[“propertyName”]], e.g. ex[[“Visible”]]And we can set the value via ex[[“Visible”]] = TRUENote that values are returned as R objects and R values can be assigned to DCOM properties.15ExcelThe Excel.Application object has a hierarchical structure and the application object is at the top. We navigate through the hierarchy to get at the objects and values we want.The application provides its own functions and properties.Properties: Visible, Interactive, Height, Memory, ActiveSheet, ...Methods: Calculate, Quit, Run, ... utility functions such as CheckSpelling, InchesToPoints16General Hierarchy ElementsApplication has a collection of WorkbooksWithin each workbook, there is a collection of Worksheet objects in a Worksheets list. These are accessed via the tabs below the sheets.Within each Worksheet, we have a rectangular array of cells.Collections of cells are described by Range objects.17WorkbooksThe Application has lists of workbook objects that can be accessed via the Workbooks property. books = ex[[“Workbooks”]]or books = ex$Workbooks()books is an RDCOM object with its own methods...It is an ordered collection, like a list in R.books$Count() gives the number of elements,books$Item(i) gets the i-th element (starting at 1)18WorkbookWe can add a new workbook via book = books$Add()The result (in book) is a new Workbook object.While you may think this is the thing we want to work with, we are not there yet...A Workbook has a list of Worksheet objects.sheets = book$Worksheets()This has similar methods as Workbooks, i.e. Count(), Item()19COMListSince the concept of an ordered container of elements arises often in DCOM, the RDCOMClient package provides a type - COMList - to simplify working with these.Create a COMList in R using the constructor function books =