User Defined function error?

Question

Hello,

We are getting the following message when opening on of our spread sheets.  "The following user-defined functions, created in an older version of Excel, have the same name as built-in Excel functions.  When these function names are used in a formula, the new built-in function will be used, which may cause different results.  Then it list a function named "Function" and one named "Sort".  I searched through the entire set of VBA project modules and there is no such functions defined in our file.

Answer 1

I think I've found the problem where Excel thinks that a UDF Sort is called (see text in bold):

<v:textbox style='mso-direction-alt:auto' o:singleclick="f">

  <div style='text-align:center'><font face="Arial" size="200" color="auto">Sort Standards</font></div>

  </v:textbox>

  <x:ClientData ObjectType="Button">

    <x:Anchor>

      1, 7, 4, 24, 2, 34, 4, 45</x:Anchor>

      <x:PrintObject>False</x:PrintObject>

        <x:AutoFill>False</x:AutoFill>

<x:FmlaMacro>[0]!_xludf.Sort</x:FmlaMacro>

            <x:TextHAlign>Center</x:TextHAlign>

              <x:TextVAlign>Center</x:TextVAlign>

              </x:ClientData>

Please follow this steps:

Make a backup copy of the file

Open Excel

File \ Open \ Browse \ Select the file \ Press the SHIFT key and hold it down \ Click Open

Wait till the file is opened \ Release the SHIFT key

Press Alt-F11 to open the VBA Editor

Go into module PI_Stds and search for Sort (it's the second sub from the bottom)

Rename Sub Sort() to Sub MySort()

Close the VBA Editor

Go into sheet "Standard Sheet Index"

Right-click the "Sort Standards" button (within cell B5) and choose "Assign macro"

Select "MySort" and click Ok

Select cell A1

Save the file

Close and Reopen Excel

Does this fix the issue?

Andreas.

Answer 2

Checked 'Sort' with formula =A1+A2 and 'Sort' as a named range. No warning in both cases 

Yes, but IMHO the problem goes deeper. A UDF with the same name is also not suitable to fathom the problem.

As you know I've written an AddIn to support SORT (and many other functions that exists in 365) in older versions of Excel.

When you use 365 and call a SORT function, 365 stores it in a special way (inside the XML data). So when you open such a file in a previous version you get a _xlfn. in front of the formula.

We can not reproduce the issue for sure, because we all use the OpenXML file format introduced with Excel 2007.

I guess the issue is initiated before 2007, maybe a old XLS file was converted to OpenXML (XLSX / XLSM) but before Excel has the SORT function. Now the OP opens the file and the (20 years old information) which "function" should be called creates a name conflict.

Of course that's all, more or less, just a guess, but that's how I imagine the problem.

IMHO we need to see the file to be able to give advice on what to do.

Andreas.

Answer 3

Bridge Peliminary Information Sheet

This link should allow you to download the Workbook that has the issue.  It seemed some of the reply were requesting to see the workbook.  ;-) cadd

Answer 4

Running Excel 365 64Bits v1912 b12325.20344. Just opened the old XLS:

EDIT: VBA project is password protected...

Answer 5

Running Excel 365 64Bits v1912 b12325.20344. Just opened the old XLS:

EDIT: VBA project is password protected...

The pass word is "cadd" with out the "