4

Context: I am using R to do some data manipulation which I then export to Excel and create a barchart with.

Problem: Thus far it is relaitively easy to record an Excel VBA macro and then translate it into R code via the RDCOMClient package. However I am at a loss as how to interpret the VBA "with" function structure.

Question: I would like to translate the following Excel VBA code into R code (specifically using the RDCOMClient package):

' Activate barchart
ActiveSheet.ChartObjects("Chart 1").Activate

' Select the Male data column
ActiveChart.SeriesCollection(1).Select

' Change the colour of the Male bars in the barchart
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Solid
End With

Reproducible code: The following R code will set up the excel worksheet with a barchart

# Load package and helper functions - see http://www.omegahat.org/RDCOMClient
require(RDCOMClient)
source("http://www.omegahat.org/RDCOMClient/examples/excelUtils.R")

# Create Excel application
xls <- COMCreate("Excel.Application")

# Make Excel workbook visible to user
xls[["Visible"]] <- TRUE

# Add a worksheet to the workbook
wb = xls[["Workbooks"]]$Add(1)

# Add data.frame to worksheet (Hishest Qualification of Job Applicants by Sex)
df <- data.frame(Degree=c("BSc", "MSc", "PhD"), Male=c(322, 107, 39), Female=c(251, 128, 25))
exportDataFrame(df, at = wb$ActiveSheet()$Range("A1"))

# Add Chart
chart.display.range <- wb$ActiveSheet()$Range("E2:M20")
wb$ActiveSheet()$Range("A1:C4")$Select()
wb$ActiveSheet()$Shapes()$AddChart(Top = chart.display.range$Top(), 
                                   Left = chart.display.range$Left(), 
                                   Height = chart.display.range$Height(), 
                                   Width = chart.display.range$Width())$Select()

What I've done thus far: The first two lines of the VBA code are easy to translate into R:

# Activate chart
wb$ActiveSheet()$ChartObjects("Chart 1")$Activate()

# Select the Male data column
male <- wb$ActiveChart()$SeriesCollection(1)
male$Select()

And then for the with structure

# bar colour to be changed (this is a guess)
bar <- male$Selection()$Format()$Fill()

which results in the following error:

#Error in .COM(x, name, ...) : 
# Cannot locate 0 name(s) Selection in COM object (status = -2147352570)

Which I'm interpreting as "Selection" should not be used? I'm not sure where to go from here but I think that once I fix the error above then I'd do something like the following:

bar[["Visible"]] = 1
bar[["ForeColor"]][["ObjectThemeColor"]] = 5
bar[["ForeColor"]][["TintAndShade"]] = 0
bar[["ForeColor"]][["Brightness"]] = 0

Thanks in advance!

P.S. I know that there may be an option to export an R plot into Excel but I'm more interested in trying to figure out how to interpret the "with" function structure.

P.P.S I'm using Windows 7 x64, x86_64-w64-mingw32/x64 (64-bit), R 3.0.1, RDCOMClient_0.93-0.1

Tony Breyal
  • 5,338
  • 3
  • 29
  • 49
  • 1
    I have never worked with `R` and hence a stupid question... Does `R` even support `With Contsruct`? If not then you can use multiple lines instead of using selection... `ActiveChart.SeriesCollection(1).Format.Fill.Visible = msoTrue` and so on... – Siddharth Rout Oct 04 '13 at 10:29
  • @SiddharthRout Ahh, interesting, so that's what the "with" structure means. Yes, what you sugest would work too i.e. `x = wb$ActiveChart()$SeriesCollection(1)$Format()$Fill()` and then `x[["Visible"]] = 1`. Thanks. – Tony Breyal Oct 04 '13 at 10:37
  • 1
    As a style point, although Excel uses them alot when it autogenerates code, `ActiveWorkbook` and `ActiveWorksheet` are dangerous. Better to name a specific workbook or worksheet. For example, if you have a long-running macro, then start working on another spreadsheet, the active workbook changes to the other thing that you are working on, and breaks everything. – Richie Cotton Oct 04 '13 at 10:43
  • @RichieCotton Very good point and something I will make sure I do in the future. – Tony Breyal Oct 04 '13 at 10:51

1 Answers1

4

Do

bar <- male$Format()$Fill()

Instead of 

bar <- male$Selection()$Format()$Fill()
JoshuaCrove
  • 466
  • 1
  • 4
  • 6