Typemismatch when duplicating graph


P

PBradac

Long time ago I have created a VBA code in which I create several
graphs from data in worksheets. It was developed and is still working
in Excel 2003.

Now with Excel 2007 and Excel 2010 Beta I get

Run-time error '13':
Type mismatch

when program control reaches the line:

Set graf = list.ChartObjects(1).Duplicate

The variables are explicitly declared with:

Dim list As Worksheet, graf As ChartObject

Any ideas how to solve the problem?

TIA, Primoz
 
Ad

Advertisements

A

Andy Pope

Hi,

Looks like a bug. Try referencing the latest chart object.

Dim list As Worksheet, graf As ChartObject

list.ChartObjects(1).Duplicate
Set graf = list.ChartObjects(list.ChartObjects)

Cheers
Andy
 
P

PBradac

Thank you Andy for your prompt answer. I was not so quick though...

I tried your suggestion but it didn't work. I got the error:

Run-time Error '1004':
Method 'ChartObjects' of object '_Worksheet' failed

I admit I don't understand the setting of the graf variable quite
well. Doesn't list.ChartObject() require an index and
list.ChartObjects is a set, isn't it?

In any case, what is to be done? I can't believe I'm the first one in
the world who encountered this bug. Can the people at Microsoft
somehow be alerted of the problem?

Cheers,
Primoz
 
A

Andy Pope

My bad, I left of the Count property.

Set graf = list.ChartObjects(list.ChartObjects.Count)

Cheers
Andy
 
P

PBradac

Whooooaa, Andy you solved the problem! I get the duplicated chart on
the workshhet.

But my joy is only partial. Later on in the code I crush in another
wall:

When executing:
ActiveChart.TextBoxes(1).Formula = wk
' BTW I construct wk giving something like "Sheet1!R2C3"

Excel says:
Run-time error '1004':
Unable to set the Formula property of the TextBox class.

As far as I can see Excel is even right :) as I can't find in its
Help that ActiveSheet would have TextBox property and this in turn
Format property. Googling didn't enlighten me very much. I only saw
examples... As I'm just a casual Excel VBA programmer I can't for the
life of me remember how I got to this line (more than 7 years ago). I
presumably recorded a macro and transferred it to my code. These
properties are not even to be found in Excel 2003 VBA object model
either (I tried Object Browser). If it's not too much to ask, could
you give me a hint how to proceed. I'm sure it'll be no brainer for
you!

Thank you very much in advance.

Regards,
Primoz
 
A

Andy Pope

Worked for me using A1 notation.

ActiveChart.TextBoxes(1).Formula = "Sheet1!C2"

You can use Application.ReferenceStyle to check which style to use.

Cheers
Andy
 
Ad

Advertisements

P

PBradac

Andy, I hardwired into the formula the contents in the form you
suggested to verify if "I can get through". And it worked!

I think I'll be able to change the whole code to be generally
executable again.

Thank you again for your *first class* support.

Cheers,
Primoz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top