Excel 2002 to 2007 - issues with Macro / VB?

[Don]

I will be testing the new Office 2007 to make sure things work for our
company. I have created many macro's many people are using and wonder if
anyone can tell me of anything/issues to look out for. I know that you have
to save it as a certin format to save the Macro's , but my VB code in the
files are a bit complex so not just record and play.

 

[Harald Staff]

From top of head:

Application.Filesearch is gone.

2007 files may or may not have lots of more rows and columns. 65536 is no
longer the row limit, beware of hardcoded values.

You can not put sheets from one workbook to another if they have different
smount of rows.

Toolbars and menus are gone. You may want to put macro access onto the
ribbon. See http://www.rondebruin.nl/tips.htm on how.

SaveAs may need more info on filetype and file prefix than earlier.

Almost everyting else works perfect as is.

HTH. Best wishes Harald

 

[Don]

thanks, this gives me a bit of comfort

Is Excel 2007 using VB.net or VB ? or am I misunderstanding?

 

[Chip Pearson]

Is Excel 2007 using VB.net or VB ? or am I misunderstanding?

Excel, like all the other Office programs, uses VBA, not VBNET. You
can, though, write add-ins and "code-behind" workbooks using VBNET if
you have Visual Studio and Visual Studio Tools For Office (VSTO).

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Don]

looks like some other items do not work either

- PrintQuality in VB for Excel 2007 caused an issue
- Application.EnableEvents = caused it to lock up

 

[Dave Peterson]

I would expect that it wasn't the difference between versions of excel for
..printquality that caused the trouble. I'd bet that it was the difference in
printers that was the problem.

And I've never seen .enableevents locking up any version of excel.

 

[exceluserforeman]

I f you do not use the reference type "textbox1.text" but just "textbox1"
this may cause errors.
eg:
strMyText=textbox1.text returns the text in the textbox but
strMyText=textbox1 may actually dimensionalise strMyText as textbox1. VB7 and
Excel 2003 and up (ie 2007) needs complete reference types. Yes they might
work the shortcut way but complex coding may confuse the ref type and
unexpected errors may occur. It is also ggod coding to use the correct ref.
Also Make sure all the "Dim"s are at the top not stuck half way through the
code. ReDim is the only one I know that is used anywhere in the code. Dim as
much as possible. Try to avoid variants ie if it is an integer.

Try not to Dim objects like textbxes, listboxes etc.. Many people do but it
is not necessry.