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

Apart from a Sub Sort I did not find anything. Just in case I renamed it > Saved > Reopened => Still got the warning. Then saved the file as .XLSM => No more warning. That's all I can say I'm afraid

@Andreas

Thanks for your previous reply (I'm aware of you add-in even if I don't use it)

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.

I was aware of the _xlfn prefix and what it means but did not know - and not sure I understand - the "storage" in the XML (that doesn't matter for now)

Anyway, thanks again and I'll keep an eye on that thread in case you (or somewhere else) find the root cause of the issue

Answer 3

Mr Killer :)

I thought about that after your reply re. XML but was lazy enough - and TBH relying on you - to reinstall some tools I haven't used for a while. May I know which tool you used?