# Solved: Problem with Excel 2003 macros in Excel 2007



## Tuckersluck (Jul 3, 2008)

I have just been upgraded to Excel 2007. I find that some of the macros that were recorded in Excel 2003 no longer work in Excel 2007. We use the Analysis Toolpak VBA add-in and I have added this in to 2007 ok, but when running a macro I get a run time 1004 error ATPVBAEN.XLA cannot be found. After some searching it would appear that some of these functions have been renamed in 2007 e.g. it is now a .XLAM file rather than .XLA which is why the macro code can't find it. I guess I could manually edit all the macros and change the fucntion names but there are other colleagues in my team that are still using 2003, so I don't want to break things for them. Is there any solution to this? Thanks


----------



## Rollin_Again (Sep 4, 2003)

So just add some error handling to the macro. You can trap for the error message or the error number and then call a different procedure to use or you can use an *IF* statement to determine the correct add-in to use. Another option is to use VBA code to determine which version of MS Office or Excel is installed on the computer and then use similar logic to tell the macro which add-in to use.

Can you post your sample workbook and/or your macro code?

Regards,
Rollin


----------



## Tuckersluck (Jul 3, 2008)

Hi, Thanks
Here is sample of some of the code
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$60:$A$75"), _
ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range("$A$79" _
), False, False, False, False, , False
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$C$60:$C$75"), _
ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range( _
"$A$100"), False, False, False, False, , False


----------



## Aj_old (Sep 24, 2007)

Try something like this:

```
If Application.Version <= 11 Then
         Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$60:$A$75"), _
               ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range("$A$79" _
               ), False, False, False, False, , False
         Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$C$60:$C$75"), _
              ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range( _
               "$A$100"), False, False, False, False, , False
    ElseIf Application.Version > 11 Then
         Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$A$60:$A$75"), _
               ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range("$A$79" _
               ), False, False, False, False, , False
         Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$C$60:$C$75"), _
              ActiveSheet.Range("$B$60:$B$75"), False, False, , ActiveSheet.Range( _
               "$A$100"), False, False, False, False, , False
    End If
```


----------



## Tuckersluck (Jul 3, 2008)

Thanks, yes, that works! Great stuff! Though we do have dozens of macros with similar stuff so they may take quite a bit of converting. Any one know if Microsoft have an automatic soultion to this incompatability, or even recognise the problem?


----------



## Rollin_Again (Sep 4, 2003)

Tuckersluck said:


> Any one know if Microsoft have an automatic soultion to this incompatability, or even recognise the problem?


Yes Bill Gates recognizes the problem and he is unable to sleep at night thinking about all the people who were affected. 

Regards,
Rollin


----------



## slurpee55 (Oct 20, 2004)

It was worries like this that led Bill to retire, didn't you know that, Rollin?

And Tucker, if you consider this solved (even if a pain), please use the Mark Solved button at the top of the page to do so.


----------

