Went from Office 2003 to 2007. Error 438 VB v.6.5.

[laura_in_abq]

This code was "fine" before I went to Excel 2007. Now I get 438 "Object
doesn't support this property or method".... It would be a lot of code to
reproduce here - and it's a different environment so I can't cut and paste -
so this is the gist of it. In particular I'm using VB to make charts (plot
from data in spreadsheets in the same workbook). The problems are around
making text boxes.

sub make_chart
....
apply_label
end sub
sub apply_label
activechart.shapes.addshape(msoShapeRectangle(300,50,180,15).select '
Line 1
selection.characters.text = "This text" ' Line 2
end sub

I'll get the 438 on Line 2. But only if I've called apply_label from
make_chart. If I end when I get the 438 dialog box, and then just execute
apply_label, no error, no problem. Just baffling.

Is there some general hints on VB when going from 2003 to 2007? Did I not
set my environment up with the install of Excel 2007? Thanks in advance.

 

[michdenis]

Hi Laura,

Try this :

Replace "Graph1" by the name of your "ActiveChart"
'---------------------------------
Sub apply_label()
With Sheets("Graph1")
With .Shapes.AddShape(msoShapeRectangle, 300, 50, 180, 15)
.OLEFormat.Object.Text = "This text"
End With
End With
End Sub
'---------------------------------



"laura_in_abq" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
This code was "fine" before I went to Excel 2007. Now I get 438 "Object
doesn't support this property or method".... It would be a lot of code to
reproduce here - and it's a different environment so I can't cut and paste -
so this is the gist of it. In particular I'm using VB to make charts (plot
from data in spreadsheets in the same workbook). The problems are around
making text boxes.

sub make_chart
....
apply_label
end sub
sub apply_label
activechart.shapes.addshape(msoShapeRectangle(300,50,180,15).select '
Line 1
selection.characters.text = "This text" ' Line 2
end sub

I'll get the 438 on Line 2. But only if I've called apply_label from
make_chart. If I end when I get the 438 dialog box, and then just execute
apply_label, no error, no problem. Just baffling.

Is there some general hints on VB when going from 2003 to 2007? Did I not
set my environment up with the install of Excel 2007? Thanks in advance.

 

[Martin Brown]

This code was "fine" before I went to Excel 2007. Now I get 438 "Object
doesn't support this property or method".... It would be a lot of code to
reproduce here - and it's a different environment so I can't cut and paste -
so this is the gist of it. In particular I'm using VB to make charts (plot
from data in spreadsheets in the same workbook). The problems are around
making text boxes.

sub make_chart
....
apply_label
end sub
sub apply_label
activechart.shapes.addshape(msoShapeRectangle(300,50,180,15).select '
Line 1
selection.characters.text = "This text" ' Line 2
end sub

I'll get the 438 on Line 2. But only if I've called apply_label from
make_chart. If I end when I get the 438 dialog box, and then just execute
apply_label, no error, no problem. Just baffling.

Is there some general hints on VB when going from 2003 to 2007? Did I not
set my environment up with the install of Excel 2007? Thanks in advance.

The short answer is *DON'T* XL2007 is fubar compared to 2003 or 2002.

Charts and graphical object are screwed up in 2007. You can fork the
code to make a 2007 compatible version but there are gratuitous and
arbitrary differences in the definition of shapes in the newest version.
They would be amusing quirks in a graduate students end of term project,
but not in a commercial product. Good luck you will need it!

Regards,
Martin Brown

 

[laura_in_abq]

Thanks, haven't been back to try that yet.

 

[laura_in_abq]

Martin, Excuse me while I freak out! Wasn't my choice to go to 2007! I have
a couple thousand lines of VB to create a quarterly report of about fourty
charts to trend/avg, over years of history, about 60 metrics. Long story why
I'm using VB/Excel (inherited it several years ago, data comes to me in
Excel, and haven't had time to change environments (IDL anyone?)). And I
sure don't have time this quarter. ok, end of freak out.

Can't be I'm the first person to ask this, but I didn't find related Q/A in
these forums, or anywhere on microsoft.com Appreciate hints, guidance to
resource/discussions on the known issues. The environment looks different
but the VB version is the same (under <Help><About>. Is this
..excel.programming where I should find such Q/A or am I in the wrong place.

 

[Martin Brown]

Martin, Excuse me while I freak out! Wasn't my choice to go to 2007! I have

Oh dear. Bad luck!!!

a couple thousand lines of VB to create a quarterly report of about fourty
charts to trend/avg, over years of history, about 60 metrics. Long story why
I'm using VB/Excel (inherited it several years ago, data comes to me in
Excel, and haven't had time to change environments (IDL anyone?)). And I

IDL is very expensive per seat if you don't qualify for academic
discount. Its data visualisation tools are impressive though.

sure don't have time this quarter. ok, end of freak out.

Your best bet this time around is to export the data you have in XL2007
as XL97 compatible XLS files and then use the old version of your code
on those. Most of the changes are pretty mechanical but tedious and a
few other things break or were gratuitously withdrawn because XL
programmers could not get them to work.

MSKB tries very hard to disguise and play down these faults but they are
in there if you use exactly the right keywords. There are bodge arounds
for most of the known problems but some are Vista specific which isn't
much help if you still have a large corporate userbase on XL.

Can't be I'm the first person to ask this, but I didn't find related Q/A in
these forums, or anywhere on microsoft.com Appreciate hints, guidance to
resource/discussions on the known issues. The environment looks different
but the VB version is the same (under <Help><About>. Is this
.excel.programming where I should find such Q/A or am I in the wrong place.

Searching MSKB in the Office developers section isn't a bad place to
start but they tend to play down the significance of the XL2007 faults
and some of the stated error codes are wrong (they were right once in
the very early days but SP1 and SP2 have changed things).

The sort of thing I mean is that some graphical objects like Connector
lines were once parameterised with coordinates
TopLeftX, TopLeftY, Width, Height
and are now
TopLeftX, TopLeftY, BottomRightX, BottomRightY.

Not surprisingly this introduces major distortions into anything drawn.

Logically I much prefer to have vector objects specified as a list of
coordinates, but this change was very painful because they kept the same
names and modified the behaviour radically in XL2007. It broke all
existing code that uses or manipulates shapes very badly.

Regards,
Martin Brown

 

[laura_in_abq]

Martin, please give more direction to "...Searching MSKB in the Office
developers section ". Is this the site? http://support.microsoft.com/

Then what to do to get to the Office developers section?
thanks much
lb

: