Office 2003 to 2010 explanation needed!

[johnnyjoe1076]

I have been working with excel and visual basic for the past two years. Creating many various documents for the company I work for. We recently upgraded to office 2010. Now about 90% of my files that get resaved as an xlsm are failing in some form. I could post random issues and random questions everytime I came across them but I thought I would ask is there a general website or article or anything that deals specifically with all the issues related to macros in excel files when switching from 03 to 2010? What do I need to do and what is the mot efficient way of doing it to fix all of my files. Thank you so much in advance!

John L

 

[Smitty]

VBA code going from 2003 up to 2010 should work fine unless you're trying to do things like hide command bars, which won't work with the Ribbon.

Other than that you'll need to post some specifics.

 

[Trevor G]

If you are using FileSearch in 2003 that will work but in 2010 it has been removed so you have to work another method. If you program to use the Office Assistance that wouldn't work as they removed it in 2007.

As Smitty has stated you would have to post some example when something doesn't work.

 

[shg]

The only thing that comes to mind that went away in Excel 2007 is Application.FileSearch. Functions that were part of the ATP in 2003 became native in 2007, so the invocation in code changed.

New methods were added (like for sorting), but backward compatibility has been maintained with prior methods -- no exceptions come to mind. Similarly, several new worksheet functions were added in 2010, but the old functions still work as they did.

I don't have a general reference to point you to, but we can help you work through specific issues.

 

[Gary Drumm]

http://office.microsoft.com/asstvid.aspx?&type=flash&assetid=XT010149329&vwidth=1044&vheight=788

Here is a Microsoft Link to the best transition info I've found so far.

HTH,
Gary

 

[Trevor G]

Gary he is asking about VBA rather than the user interface. There is also versions of this to 2010 as well.

 

[johnnyjoe1076]

First of all thank you for the varied responses, it is most appreciated. What I am actually realizing is that there are issues that are mainly coming from the refreshing of the imported data from other excel files. I typically perform the refreshes in a code (private sub when opening or an individual module). For some reason I am not getting consistent performance on these refreshes, especially with the formulas filling down adjacently to the imported data. I will have to pull up some examples probably. I will repost them shortly, thanks.

 

[johnnyjoe1076]

I may have to repost this as the majority of my issues are currently 2010 to 2003. As there is a lag in getting everyone in our company upgraded to 2010 (frustrating!). They provided me with Code Compatibility Checker but it doesn't seem to catch everything. I have created some complex files with codes that will not work in 2003. Here are a just few codes that I will have to change because of the lack of compatibility.

ActiveWorkbook.Worksheets("Project Request Database").Sort.SortFields.Add Key _
        :=Range("J4:J65536"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder _
        :="Pending,Approved,Not at this time", DataOption:=xlSortNormal

and

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
#

In the one above apparently 2003 doesn't like the "TintAndShade" line.

Obviously the solution to everything would be our entire company upgraded to 2010. Sigh... I think I need to give Bill Gates a call about all of this, anyone have his number???