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

May I know which tool you used?

I have some self-written tools to analyze Excel files, but they aren't necessary to solve this puzzle.

Save the file as XLSM, unzip the file and search all files for "sort". I've used Agent Ransack for that, because I can view the data of the found places directly.

But then you have to understand a little bit of XML and Excel to recognize that it is a button called "Sort Standards".

Andreas.

Answer 3

Thanks much again Andreas, really appreciate. It's been a while I haven't "read" XML - I did UI customizations years ago with the Office UI editor so hopefully I won't be totally lost

All the best