Selecting a chart in a worksheet with VBA trouble
 

Hi there,

I'm experiencing trouble while selecting a chart object in a worksheet. With
the code I included below, I generate an array of chart objects and put them
on a sheet named "Charts". Those chart objects are named "Die(x,y)" where x
and y are numerical values. If I want to select a single chart object in a
sheet named "Charts", the macro recorder records:
ActiveSheet.ChartObjects("Die(3,1)").Activate. But if I use this simple
code, I get an errormessage 1004: Unable to get the ChartObjects property of
the Worksheet class. Who can tell me what the problem can be? I also tried
ActiveSheet.Shapes.Range("Die(3,1)").select but this seems not to work...
I searched over the internet, but could not find a solution.

Thanks in advance,
Sebastiaan Maas.

Sub TestCharts1()
Dim myChart As ChartObject, ChartName As String, DataX() As Double,
DataIb() As Double, DataIc() As Double
Dim aNewSeries As Series, Temporary As String

Sheets("Charts").Select

For x = 0 To 7
For y = 0 To 7
OffsetX = x * 75 + 1
OffsetY = y * 75 + 1
ChartName = "Die(" & CStr(x + 2) & "," & CStr(8 - y) & ")"

Set myChart = ActiveSheet.ChartObjects.Add(Left:=OffsetX,
Width:=75, Top:=OffsetY, Height:=75)

myChart.Name = ChartName
myChart.Chart.ChartType = xlXYScatterLines

If (x + y) Mod 2 = 0 Then 'Create checkerboard
pattern
myChart.Chart.ChartArea.Interior.ColorIndex = 19
End If 'Light yellow color
Debug.Print x, y, myChart.Chart.Name, myChart.Name
Temporary = myChart.Chart.Name
Next y
Next x

ActiveSheet.Shapes.Range(Array("Die(2,8)", "Die(3,8)", "Die(4,8)", _
"Die(7,8)", "Die(8,8)", "Die(9,8)", "Die(2,7)", "Die(9,7)", _
"Die(2,1)", "Die(9,1)")).Select 'Delete obsolete graphs
(no existing dies on the wafer)
Selection.Delete

With Wafer.Dies("3,1").DIETESTs(1).Modules("W140
(7335,775)").SCRIPTs("scripts\BJT1A.scr").Devices( 1).Algorithms(1)
'Data is stored in this dot structure
ReDim DataX(.DATA(1).NumValues - 1) As Double, _
DataIb(.DATA(1).NumValues - 1) As Double, _
DataIc(.DATA(1).NumValues - 1) As Double
'Store all necessary data in local variables (Arrays)
For i = 0 To .DATA(1).NumValues - 1
DataX(i) = .DATA(3).Value(i)
DataIb(i) = .DATA(1).Value(i)
DataIc(i) = .DATA(2).Value(i)
Next i
End With

'ActiveSheet.ChartObjects("Die(3,1)").Activate 'This
generates Error 1004: Unable to get the ChartObjects property of the
Worksheet class
Sheets("Charts").ChartObjects("Die(3,1)").Activate 'This
generates Error 1004: Unable to get the ChartObjects property of the
Worksheet class

'And now select x-axis values = DataX and Y-axis value are DataIb and
DataIc etc. etc.

Selecting a chart in a worksheet with VBA trouble
 

Objects with names that include most types of punctuation are not accessible
at the Drawingobject level, or in your case as ChartObject. I would suggest
don't include the brackets or the comma in "Die(2,8)", try instead something
like "Die_2_8".

But if you really must, or to get you out of trouble, go via Shapes. Eg

Set myChartObject = mySheet.Shapes("Die(2,8)").DrawingObject
or
Set myChart = mySheet.Shapes("Die(2,8)").DrawingObject.Chart

Probably better to start by attempting to set a Shape reference first, then
the Chartobject or Chart.

I haven't looked at the rest of your code but normally not necessary to
Activate or Select the chart for whatever it is you want to do with it.

Regards,
Peter T

"SebasFM" wrote in message
...
Hi there,

I'm experiencing trouble while selecting a chart object in a worksheet.
With
the code I included below, I generate an array of chart objects and put
them
on a sheet named "Charts". Those chart objects are named "Die(x,y)" where
x
and y are numerical values. If I want to select a single chart object in a
sheet named "Charts", the macro recorder records:
ActiveSheet.ChartObjects("Die(3,1)").Activate. But if I use this simple
code, I get an errormessage 1004: Unable to get the ChartObjects property
of
the Worksheet class. Who can tell me what the problem can be? I also tried
ActiveSheet.Shapes.Range("Die(3,1)").select but this seems not to work...
I searched over the internet, but could not find a solution.

Thanks in advance,
Sebastiaan Maas.

Sub TestCharts1()
Dim myChart As ChartObject, ChartName As String, DataX() As Double,
DataIb() As Double, DataIc() As Double
Dim aNewSeries As Series, Temporary As String

Sheets("Charts").Select

For x = 0 To 7
For y = 0 To 7
OffsetX = x * 75 + 1
OffsetY = y * 75 + 1
ChartName = "Die(" & CStr(x + 2) & "," & CStr(8 - y) & ")"

Set myChart = ActiveSheet.ChartObjects.Add(Left:=OffsetX,
Width:=75, Top:=OffsetY, Height:=75)

myChart.Name = ChartName
myChart.Chart.ChartType = xlXYScatterLines

If (x + y) Mod 2 = 0 Then 'Create checkerboard
pattern
myChart.Chart.ChartArea.Interior.ColorIndex = 19
End If 'Light yellow color
Debug.Print x, y, myChart.Chart.Name, myChart.Name
Temporary = myChart.Chart.Name
Next y
Next x

ActiveSheet.Shapes.Range(Array("Die(2,8)", "Die(3,8)", "Die(4,8)", _
"Die(7,8)", "Die(8,8)", "Die(9,8)", "Die(2,7)", "Die(9,7)", _
"Die(2,1)", "Die(9,1)")).Select 'Delete obsolete
graphs
(no existing dies on the wafer)
Selection.Delete

With Wafer.Dies("3,1").DIETESTs(1).Modules("W140
(7335,775)").SCRIPTs("scripts\BJT1A.scr").Devices( 1).Algorithms(1)
'Data is stored in this dot structure
ReDim DataX(.DATA(1).NumValues - 1) As Double, _
DataIb(.DATA(1).NumValues - 1) As Double, _
DataIc(.DATA(1).NumValues - 1) As Double
'Store all necessary data in local variables (Arrays)
For i = 0 To .DATA(1).NumValues - 1
DataX(i) = .DATA(3).Value(i)
DataIb(i) = .DATA(1).Value(i)
DataIc(i) = .DATA(2).Value(i)
Next i
End With

'ActiveSheet.ChartObjects("Die(3,1)").Activate 'This
generates Error 1004: Unable to get the ChartObjects property of the
Worksheet class
Sheets("Charts").ChartObjects("Die(3,1)").Activate 'This
generates Error 1004: Unable to get the ChartObjects property of the
Worksheet class

'And now select x-axis values = DataX and Y-axis value are DataIb and
DataIc etc. etc.

Selecting a chart in a worksheet with VBA trouble
 

Thank you for your help. It works now!

Kind regards,
Sebastiaan.

"Peter T" <peter_t@discussions wrote in message
...
Objects with names that include most types of punctuation are not
accessible
at the Drawingobject level, or in your case as ChartObject. I would
suggest
don't include the brackets or the comma in "Die(2,8)", try instead
something
like "Die_2_8".

But if you really must, or to get you out of trouble, go via Shapes. Eg

Set myChartObject = mySheet.Shapes("Die(2,8)").DrawingObject
or
Set myChart = mySheet.Shapes("Die(2,8)").DrawingObject.Chart

Probably better to start by attempting to set a Shape reference first,
then
the Chartobject or Chart.

I haven't looked at the rest of your code but normally not necessary to
Activate or Select the chart for whatever it is you want to do with it.

Regards,
Peter T