XL2007 vs XL2003

[Guest]

Hi,

I've yet to update to XL2007. Does anyone know of anything in particular
that's different with regard to programming in VBA?

 

[teepee]

I've yet to update to XL2007. Does anyone know of anything in particular
that's different with regard to programming in VBA?

The main one I've noticed is that objects seem to behave differently.

 

[Bob Phillips]

The main one I've noticed is that objects seem to behave differently.

In what way?

 

[Bob Phillips]

FileSearch is gone.

There is a CountLarge property to allow for the 1M rows.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

teepee,

Thanks -- in addition to new properties (like the countlarge that you
mention) have you run into any new problems?

 

[Bob Phillips]

As I said FileSearch is gone, so that may be a big problem.

Also, the ribbon is obviously different to commandars, and have to be
created differently, unless you are happy that they end up on the Addins
tab.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[teepee]

teepee,

Thanks -- in addition to new properties (like the countlarge that you
mention) have you run into any new problems?

Can't protect sheets and then allow users to edit those objects via VBA.

Can't alter certain characteristics of certain objects - eg with a graph you
can change the colour but not the border.

 

[Guest]

teepee,

Thanks very much. It will be a few months before I upgrade -- I guess I'll
look forward to it.

 

[Guest]

Bob,

Thanks for the info.

As I said FileSearch is gone, so that may be a big problem.

Also, the ribbon is obviously different to commandars, and have to be
created differently, unless you are happy that they end up on the Addins
tab.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Flanagan]

Art, I've noticed that the macro recorder in Excel 2007 records very little
with regard to shapes and charts. So one often doesn't get even a good
starting point.

I also have noticed that macros run noticably slower in Excel 2007.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

I agree: recorded macro while creating/working with x-y scatterchart was
absolutely useless; could not have been used to recreate the chart using the
recorded macro at all, and gave no useful information to the novice charter
(me).

Numerous people, including myself, have reported extreme performance
degradation after putting graphs into worksheets.

I've had mixed results in timing tests of macros: I've seen them run faster
on slower machines under earlier versions of Excel, and I've seen them run
faster. I'm thinking it's related more to what's being done rather than that
it's being done in code in general. But creating x-y scattercharts (can't
speak to other types - didn't go there) is more than 10x slower under 2007
than it was in 2003.

There are a couple of new parameters added to some operations (.Find?) when
recorded into VBA that will cause some headaches if the file is saved for use
in an earlier version and then used in an earlier version. Kind of like the
DataOption1, DataOption2 and DataOption3 parameters that were added to the
..Sort operation at one point.

 

[Guest]

Bob,

Well, that's not good news. Thanks for the link -- I haven't spent much
time looking at it yet, but it seems interesting.

On a related topic... I've been trying somewhat succesfully to use VSTO --
Visual Studio is very nice and the functionality of a full version of VB.net
is really nice. BUT... it is really a pain to use sometimes and definitely
harder to deploy than a file with VBA in it. The Object Model is also not
nearly as easy as running directly in VBA in Excel. So I was sort of hoping
(without any real expectation of this happening) that perhaps the VBA
implementation might move a little in that direction in order to get the best
of both worlds.

Anyway, sorry about the semi-rant, but thanks again for the info.

 

[Guest]

JLatham,

I appreciate the warning. I've never used VBA to put an x-y chart in a
spreadsheet, but I guess that's just a matter of time. It is nice to know,
up front, that I better pay attention to performance should I have to do it.

 

[Guest]

All:

I need to upgrade from XL2000 (VBA 6.3). Question I have is whether it's
worth going straight to XL2007 even thought most of my clients are using
XL2003. My primary concern is the apparent superior security that 2007
provides - I've been wary of the limited, 40-bit encryption that earlier
versions offer.

TIA,

Jim