Excel issue “excel 4.0 function stored in defined

问题:

I am trying to isolate the issue causing

"excel 4.0 function stored in defined names"

What I am unable to figure out is the reason behind this.

This issue started coming recently and unable to figure out why.

回答1:

Not to bump an old thread, but I had this same issue as well and wanted to post my resolution in case anyone else experiences the same thing as myself.

My problem ended up being caused by a MySQL Excel COM add-on that was a component included in the MySQL Workbench package. Disabling that add-on caused the macro prompt to disappear when saving spreadsheets.

回答2:

For me this message was caused by the MySQL For Excel COM Add-In. The message stopped appearing when I unloaded the COM Add-In.

回答3:

The solution already pointed out is correct: disable the MySQL for Excel COM Add-in.

The reason for the message is that this add-in adds hidden names to the workbook. These names are not visible in the Excel Names Manager. But you can see them in the VBA Direct Window if you add this code to a workbook module:

Public Sub DEV_CheckNames()

Dim n As name
For Each n In ActiveWorkbook.Names
    If Not n.Visible Then
        Debug.Print n.NameLocal, n.RefersToLocal
        'If you want to delete the name, uncomment this line:
        'n.Delete  
    End If
Next
End Sub

Result (for a German instance of Excel 2013):

LOCAL_DATE_SEPARATOR        =INDEX(ARBEITSBEREICH.ZUORDNEN(37);17)
LOCAL_DAY_FORMAT            =INDEX(ARBEITSBEREICH.ZUORDNEN(37);21)
LOCAL_HOUR_FORMAT           =INDEX(ARBEITSBEREICH.ZUORDNEN(37);22)
LOCAL_MINUTE_FORMAT         =INDEX(ARBEITSBEREICH.ZUORDNEN(37);23)
LOCAL_MONTH_FORMAT          =INDEX(ARBEITSBEREICH.ZUORDNEN(37);20)
LOCAL_MYSQL_DATE_FORMAT     =WIEDERHOLEN(LOCAL_YEAR_FORMAT;4)&LOCAL_DATE_SEPARATOR&WIEDERHOLEN(LOCAL_MONTH_FORMAT;2)&LOCAL_DATE_SEPARATOR&WIEDERHOLEN(LOCAL_DAY_FORMAT;2)&" "&WIEDERHOLEN(LOCAL_HOUR_FORMAT;2)&LOCAL_TIME_SEPARATOR&WIEDERHOLEN(LOCAL_MINUTE_FORMAT;2)&LOCAL_TIME_SEPARATOR&WIEDERHOLEN(LOCAL_SECOND_FORMAT;2)
LOCAL_SECOND_FORMAT         =INDEX(ARBEITSBEREICH.ZUORDNEN(37);24)
LOCAL_TIME_SEPARATOR        =INDEX(ARBEITSBEREICH.ZUORDNEN(37);18)
LOCAL_YEAR_FORMAT           =INDEX(ARBEITSBEREICH.ZUORDNEN(37);19)

These names are added to the active workbook in the moment you click on the button of the add-in (which opens the taskpane). Unfortunately, I haven't found out yet why the MySQL add-in sometimes adds these names by itself without being activated.

Added: This is a known bug in the MySQL for Excel add-in as of http://bugs.mysql.com/bug.php?id=73467

回答4:

I have the same problem in Excel 2013 and was solved by unloading the MySQL Add in - I did this by going to FILE - OPTIONS - Add Ins

回答5:

As in ChipsLetten's answer, same solution here on my computer: On saving some (actually macro free) workbook, I get an alert "Excel 4.0 function stored in defined names", and an option to save the workbook as "macro-enabled". On de-activating the "MySQL for Excel" COM-Add-in, the alert does not appear any more.

In order to disable the "MySQL for Excel" add-in (if you ever have installed it): Tab "Developper", then button "COM Add-Ins", and you get a list of add-ins that you can enable or disable per tick-mark.

回答6:

have you installed some connector for excel?, maybe that's causing the issue, just uninstall it and the error will be gone.

回答7:

If you have worksheets with hidden ranges as described in domke consulting's answer, you can use the following code to remove them:

Public Sub Remove_Hidden_MySQL_Names()

Dim n As Name
For Each n In ActiveWorkbook.Names
    If Not n.Visible Then
        'Delete Names added by MySQL for Excel add-in
        If (InStr(n.NameLocal, "LOCAL_") <> 0 And (InStr(n.NameLocal, "_FORMAT") <> 0 Or _
                                                    InStr(n.NameLocal, "_SEPARATOR") <> 0)) Then
            Debug.Print "Would delete", n.NameLocal, n.RefersToLocal
            'If you want to delete the name, uncomment this line:
            'n.Delete
        Else
                Debug.Print "Keeping", n.NameLocal, n.RefersToLocal
        End If
    End If
Next
End Sub