vbaexceludf

Excel VBA UDF autocorrects with wrong case

I have an Excel VBA addin that adds user defined functions (UDFs). These functions all work fine in terms of calculating their results.

I enter them into the Function Wizard using MacroOptions.

When using them in a worksheet, they autocorrect the function names for case. For example, if the function is named MyFunction, and the user enters it as myfunction, then when the formula is entered it autocorrects the function name's case to MyFunction. Just like with Excel's built-in worksheet functions, where if you enter e.g. median it will autocorrect it to MEDIAN.

However, there is one UDF for which that autocorrect is incorrect. I want it to be e.g. MyFunction, but even if the user enters it like that, it autocorrects to all lowercase, myfunction.

I have looked at every relevent line of code and can find no reason for that. The code is the same for all the other UDFs except of course for function name, function description, and the code and parameters in the function itself.

Any idea why that one UDF autocorrects the function name differently?

Here's an example of the MacroOptions line:

Application.MacroOptions "MyAddin.xla!MyFunction", "Description of MyFunction for the Excel's Function Wizard.", , , , , "Custom category in Function Wizard"

And the UDF itself looks like this:

Public Function MyFunction(param1 As Long, param2 As Long) As Long
   'MyFunction's code here...
End Function

Again, the UDF calculates correctly. The issue is with the autocorrect on the function name.

I tried closing the addin, then creating a new UDF with the same function name as the affected function in the new, default Excel workbook. It worked correctly -- it autocorrected to the correct case. But when I closed it and went back to the addin, the problem returned.

UPDATE:

Here are the steps that worked for me to fix the problem (thanks, Charles Williams!):

  1. Set the add-in workbook's IsAddin property to false. That turns it into a workbook, so you'll get a worksheet.
  2. File > Save As, save as new xlsm workbook. Important: make sure there are no formulas in the worksheet with the affected function name; otherwise you'll be coming back to this step.
  3. File > Options > Add-ins > Go, Uncheck the add-in so it doesn't load when Excel is started.
  4. Exit and restart Excel.
  5. Open the new xlsm workbook.
  6. Enter a formula with the function name in correct case. It should NOT autocorrect to incorrect case.
  7. Delete the formula.
  8. Save the xlsm workbook.
  9. Make a backup of the original add-in, as you're about to overwrite it.
  10. Save the xlsm as a new xla add-in, overwriting the old one (I assume this works with xlam too but didn't try that).
  11. File > Options > Add-ins > Go, recheck the add-in so it loads when Excel is started.
  12. Restart Excel. Test for the problem; the problem should be fixed.
Solution

  • Excel insists on remembering the capitalization that was used the very first time the UDF is entered into a formula.

    I have not found a sensible way of resetting this, apart from renaming the UDF.

    If the XLAM's worksheets contain a formula referring to the UDF then that reference will lock in the capitalization. If the XLAM's worksheets do NOT contain a formula referring to the UDF then it will be set from the first workbook that uses it in an Excel session. If you have 2 saved workbooks with different capitalizations and they both get opened then the capitalization in the second gets overwritten with the capitalization from the first.