Unsupported VBA in 2007? (En/Excel/2007)

Topic

New Reply

I just got my new machine with Vista and Office 2007. I’m slowly finding my way around and I’ve discovered a problem with some of my VBA code. I really have two questions – one specific and one more general.

First, the following code worked fine in Excel 2003 but does not in 2007.

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
“Line – Column on 2 Axes”

The error is

Run-time error ‘-2147467259 (80004005)’:
Method ‘ApplyCustomType’ of object’_Chart’ failed

What should this code look like in Excel 2007? I would re-record the macro but I don’t see custom chart formats as an option.

My second question, does anyone have any resources they can recommend for getting up to speed on Office 2007, VBA, and especially migrating older VBA code into 2007?

Thanks so much in advance,

Kevin

Reply | Quote

Replies

I don’t have Office 2007 yet, so I cannot really help, but the Excel 2007 Developer Reference mentions that many “old” objects, properties and method for charts have been hidden, and new ones have been added. Hopefully someone who has Office 2007 can help.

Reply | Quote

Thanks Hans. What do they mean by “hidden”?

Kevin

Reply | Quote

Hidden means that the functionality is still present, but it is not exposed to the programmer.
If you look at the Object Browser (press F2 to see it), you can right-click and select ‘Show Hidden Members’ from the popup menu to see properties etc. that have been hidden from normal view.

Reply | Quote

Have you checked whether Excel actually has that custom chart type available?

Reply | Quote

I cannot find any custom chart types in Excel 2007. In 2003, you would go to Insert>Chart and there would be a Custom Types tab. On that tab would be a list of various custom chart types, such as “Line – Column on 2 Axes”. In 2007 I can’t find custom chart types anywhere.

Kevin

Reply | Quote

Maybe you have to create one yourself first? I haven’t played with that part of 2007 yet.

Reply | Quote

>I’ve discovered a problem with some of my VBA code.
I ran extensive tests using Word/VBA on Office2007 and did not find any problems. My tests include the generous use of a 400+ function library of procedures shared across some 20 applications. I made reports in a blog (Office 2007 for the application developer) especially in the earlier entries.
I did not test much in the way of Excel/VBA, and I know that previous versions of Office had different interpretations. That said, the office2007/VBA ought to have been only about implementing an (internal) interface to versions of VBA, rather than rewriting VBA, so i expect everything to work. (Writing an interface to a complex machine is often simpler than building the original machine).
I would expect all VBA featuresto be supported, and will suspect that any differences are related to the setup. I note a suggestion that perhaps a virgin installation may have, as yet,,no cum chart types.

Reply | Quote

So the custom charts are an option that has to be selected during the install of Excel?

Reply | Quote

> has to be selected during the install of Excel?
I’m not sure.
I understood the earlier post to be suggesting that until you had created a custom chart, custom charts were, by definition, not available to you.

Reply | Quote

This might be of interest…
http://blogs.msdn.com/excel/archive/2006/08/28/724641.aspx%5B/url%5D
“Deprecated Features For Charting”
(Excel 2007 – Eric Patterson – Microsoft)
—
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware%5B/url%5D

Reply | Quote

Googled for a bit and found this:

http://groups.google.com/group/microsoft.p…f4b993ee4b5d4d9%5B/url%5D

Reply | Quote

Re your second question, I’ve just got this book, and it seems quite comprehensive. There’s a whole chapter on charts I haven’t got to yet.
http://www.wrox.com/WileyCDA/WroxTitle/pro…0470046430.html%5B/url%5D
Peter

Reply | Quote