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!?)
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
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
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...
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