title: How can I programmatically modify an embedded bar/column chart with VBA?
body: <p>I'm creating a ProcessBook display that populates an embedded Microsoft Office 11.0 Spreadsheet object (Office 2003) with a set of entries. I'm then calculating aggregate data about them; this aggregate data should not be visible in spreadsheet form onscreen, but does need to be used to generate a bar chart. The data is currently being used to populate a separate Microsoft Office 11.0 Spreadsheet object. It's organized such that the title of each bar chart is in column A and the corresponding value is in column B.</p>
  
  <p>Since this is ProcessBook, I've had some difficulty even gaining access to embedded objects, but I've managed to embed and gain access to a ChartSpace object, as well as a child <a href="http://msdn.microsoft.com/en-us/library/aa205365(v=office.11).aspx" rel="nofollow">ChChart</a> object. Unfortunately, I can't figure out how to either manually set the values of the bars or how to use the <code>.SetData</code> or <code>.SetSpreadsheetData</code> methods to point it to an object that I've populated.</p>
  
  <p>Accessing the ChartSpace object is pretty straightforward: <code>ThisDisplay.ChartSpace1</code><br>
  I can then add a Chart and access it fairly easily:<br>
  <code>
  Dim objChart as ChChart<br>
  Set objChart = ThisDisplay.ChartSpace1.Charts.Add(0)
  </code>  </p>
  
  <p>I can access my spreadsheet values pretty easily as well:<br>
  <code>
  strBarName = ThisDisplay.sstChartData.Range("A2").Value<br>
  intBarVal = ThisDisplay.sstChartData.Range("B2").Value
  </code></p>
  
  <p>How do I actually set the data source or manually set the values of the bars in the ChChart object? Alternatively, how do I use a different object to accomplish the same goal?</p>