Excel VBA: Array to Range (for SetSourceData)

[Alba Ra]

Hello,
as anyone reading this might have guessed I need help. And please forgive me for making it long. You may skip right to Found Half of a Solution if you don't want/need the background of my problem.

Why and What

I am developping for a client an Excel worksheet to create reports on its production.

Changing the software seems out of question, until I might address the (very unlikely) possibility to switch I have to work with what's installed.

The Task at Hand

I have a worksheet containing multiple data. There's a daily data entry from which monthly and the annual figures are calculated; the data is composed of about two dozen rows that are re-grouped in three categories. My client wants me to create a Clustered Column chart with the data in descending order (without changing the order in the datasheet itself) per category but all three categories in the same chart. (I hope I'm making sense to you!?)

The Problem

As Excel does not let me to sort the data in the chart, even less if I want to sort the data in three groups, I need to do the sorting in another way. My first idea was using MS Query (where I could have used T-SQL to do the grouping and sorting) but apparently MS Office is not completely installed.

So by now I'm trying the VBA approach by saving the data in an array, sort the data in the array (using a function) and then create a chart using a macro.

I don't want to put the data into an range on a datasheet as the workbook will have about 14 sheets of data (not counting the sheets that will contain the various sharts) and this number is potentially growing (I was told). And we'll have almost 30 Excel files (and likely to grow in numbers too) so I'd really like to limit the amount of new datasheets.

Found Half of a Solution

Jon Peltier has proposed a way to add a chart using VBA:

Sub AddChartObject()
Dim myChtObj As ChartObject

    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)
    myChtObj.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
    myChtObj.Chart.ChartType = xlXYScatterLines
End Sub

And Shasur has posted on the VBA Tips & Tricks blog a way of transferring an array to an Excel range:

Sub Sheet_Fill_Array()
    Dim myarray As Variant
    myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    Range("A1:A10").Select
    Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub

I though of combing the two ideas to create something like that:

Sub AddChartObject()

Dim myChtObj As ChartObject

    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)
    myChtObj.Chart.SetSourceData Source:=Application.WorksheetFunction.Transpose(TestArray)
    myChtObj.Chart.ChartType = xlColumnClustered
End Sub

But I get the runtime error 424: object required. That was likely to be expected. I can see that Shasur has filled the Range.Value with the Transpose function while SetSourceData requires just the Range but I don't see how that can be accomplished.

So, has anyone the right idea to make it work? It does create an empty chart...

Software Configuration
Microsoft Windows XP Professional SP3
Microsoft Excel 2003 SP3
Part of Microsoft Office Standard Edition 2003 (of which neither Access, Publisher, nor FrontPage are avaible, likely not installed)
with VBA based on Microsoft Visual Basic 6.3

[Alba Ra]

A minor success in my efforts: I tested adding a range collection as source and it works alright.

Dim range1 as Range
Dim range2 as Range
Dim range3 as Range
Set range1 = Range("B2:C10")
Set range2 = Range("X2:X10")
Set range3 = Union(range1, range2)

Dim myChtObj As ChartObject
      Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)
      myChtObj.Chart.SetSourceData Source:=range3
      myChtObj.Chart.ChartType = xlColumnClustered

It does create a chart with B2:C10;X2:X10 as data source.

Now, do how can I process the data within the range, seeing that its data are of different data types. Easiest way for sorting would be to use the Sort Method on the range collection ( range3 in our example above): see my thead (as of yet unsolved).

[Alba Ra]

To get back to this problem and summarize it:

I need to get an array back to a range but not onto the worksheet.

Dim myarray As Variant
      myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Dim myrange as Range

How to I get the data from myarray to myrange when myrange does not represent actual cells on the worksheet?

Or the other way around: How can I partially sort the data from my (successfully tested) code from above, i.e. I want to sort, say, range B2:C6;X2:X6 and B7:C10;X7:X10 independently from each other!?

Further read on the subject:
Referring to Excel Ranges by Bill Jelen and Tracy Syrstad, July 2, 2004
Cells and Ranges on MSDN Library
Sample Visual Basic macros for working with arrays in Excel in Microsoft Support