Reverse compatibility problem of the old ATP functions in non English versions of Excel

Ron de Bruin (last update 21-June-2009)
Go back to the Excel tips page

In Excel 2007 the Analysis ToolPak add-in with the extra worksheet functions not exist anymore.
The old ATP functions are now standard worksheet functions in Excel 2007.

This a great change because:
1) No problem if a user not have the add-in installed
2) The formulas will be translated if you open the workbook in another language Excel version of Excel 2007.

But there is a problem when you use a non English version of Excel 2007 and save the workbook in the
97-2003 format so that other users can use the workbook in the Excel versions 97-2003.

***This problem is Fixed in Office 2007 SP2***

Example

Note: I use the Dutch version in this example but the same problem exists in all other non English versions.
Note: All old functions of the ATP add-in have this problem

1) Open a new file in the Dutch version of Excel 2007.
2) Enter a date in cell A1
3) Add the Dutch WEEKNUM function in cell B1
=WEEKNUMMER(A1)
4) Save the workbook as 97-2003 workbook and close it

5) Open this workbook in a Dutch Excel 2003

Look at the formula now and you see that it is changed to =WEEKNUM(A1)
It will translate the formula to English and it will not work in your non English version.

Conclusion

If you have a non English version of Excel 2007 and you must save your workbooks in the 97-2003
format to share your workbooks with users with older non English versions of Excel you can not use
the old ATP worksheet functions.

Workaround

You can use this basic macro to change the formulas from English to local.
Add the functions you want in the array in the macro.

The example macro change WEEKNUM and EOMONTH to the Dutch name of the function

Sub Change_ATP_Functions_To_Local_On_ActiveSheet()
    Dim English_ATP As Variant
    Dim Local_ATP As Variant
    Dim N As Integer

    'Add the functions you want in the array
    English_ATP = Array("=WEEKNUM", "=EOMONTH")
    Local_ATP = Array("=WEEKNUMMER", "=LAATSTE.DAG")


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With


    For N = LBound(English_ATP) To UBound(English_ATP)

        With ActiveSheet
            .Cells.Replace What:=English_ATP(N), Replacement:=Local_ATP(N), _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
        End With

    Next N


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Nice Add-ins for formula translations

See this add-in: TranslateIT from KeepItCool (Jurgen Volkerink)
http://members.chello.nl/jvolk/keepitcool/download.html

Or the Analysis ToolPak Translator add-in from Eric Desart
http://www.rondebruin.nl/atptranslator.htm

Or not use the ATP functions at all, see Dicks blog for formulas that not use the ATP
This are all default Excel functions that always translate correct.

http://www.dicks-blog.com/archives/2004/12/18/replacing-the-analysis-toolpak-addin-part-1/

http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

http://www.dicks-blog.com/archives/2004/12/20/replacing-the-analysis-toolpak-addin-part-3/

http://www.dicks-blog.com/archives/2004/12/22/replacing-the-analysis-toolpak-addin-part-4/