=INDEX(PeriodList,MonthNo) creating _xlfn.SINGLE name in workbook

I hope someone will be able to help with a problem that has surfaced with a recent Excel update.

If I define two range names in an Excel worksheet and then enter =Index(PeriodList,MonthNo) in cell A13 via the UI, I find that Thisworkbook.Names.Count in VBA reports 2 - as expected. However, if I execute Activesheet.Range("A13").Formula = "=Index(PeriodList,MonthNo)" in VBA, Thisworkbook.Names.Count reports 3 and I find that a new name that cannot be deleted is created. The name is _xlfn.SINGLE. The existence of this name is a problem for a significant number of widely distributed Excel templates that fail when attempting to delete this name. This only became a problem after a recent Excel update.

The problem seems to be with the MonthNo element in that "=Index(PeriodList,1)" does not generate the _xlfn.SINGLE name.

Can anyone identify how to enter something that is the equivalent of "=Index(PeriodList,MonthNo)" in VBA without creating the unwelcome _xlfn.SINGLE name?

2 Answers

If the function has worked for you in the past and has only recently become an issue, you should check with your IT department as it points to a problem with the version of Excel being used.

_xlfn.SINGLE is displayed for dynamic array functions when they are not supported by the version of Excel that is running.

There are many dynamic array VBA solutions available such as here.

Use this code to display all defined names in the UI Names Manager. Then you can delete the named ranges of your choice, including those that were previously hidden.

Sub ShowAllDefinedNames()
'Source: Dim tempName As Variant 'Unhide all names in the currently open Excel file For Each tempName In ActiveWorkbook.Names tempName.Visible = True Next
End Sub

After much further investigation I came upon the note at which indicates that the formula now needs to be array entered with CTRL+SHIFT+ENTER. Now the _xlfn.SINGLE name is no longer created. Problem solved.