CODEWITHSUNDEEP
vbaexcel
Matthias Pospiech
Matthias Pospiech

Reputation: 3488

adding chart to worksheet in excel (vba) fails

I have the following very simple code which however fails

Dim ws As Worksheet 
Dim ShapeRef As Shape
Set ws = Sheets("DatenFilledChart") 
ShapeRef = ws.Shapes.AddChart()

with error 91: variable not defined.

I fail to understand why this fails.

Upvotes: 0

Views: 1111

Answers (2)

David Zemens
David Zemens

Reputation: 53623

Is there a reason you need to add this chart as a member of shapes collection?

If not, if you need to work with the ChartObject, do this:

''Create a chart object (size/dimensions may be overridden or changed later in your code)
Set ShapeRef = ws.ChartObjects.Add(Left:=chtLeft, Top:=chtTop, Width:=740, Height:=300)

If you need to work with the Chart, add another variable:

Dim cht as Chart
Set cht = ShapeRef.Chart

Upvotes: 0

CuberChase
CuberChase

Reputation: 4518

A Shape is an object so you need to use Set like you have done for the Sheets object. Although this is a Run-time error '91' the error description should be 'Object variable or With block variable not set'.

Anyway, try setting the object like so: Set ShapeRef = ws.Shapes.AddChart().

Upvotes: 1

Related Questions