Square bracketed expression is evaluated differently in M365 vs Excel 2016-CodePudding

The company I work for has recently moved from Office 2016 to M365. Some of our macro-enabled spreadsheets use bracketed expressions as a shortcut to the Application.Evaluate method. For example:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim msg As String
    
    On Error GoTo ErrHandler:
    
    If Target.Columns.Count > 100 Then
        Dim row As Range
        For Each row In Target.Rows
            RemoveRowBorders row:=row.row, firstColumn:=[colSourceDevice], lastColumn:=[colItemNumber]
        Next
        
        UpdateItemNumbers startingRow:=[rowStart], maxRange:=[maxRange]
        GoTo My_Exit
    End If
    
    If Target.Column >= [colSourceDevice] And Target.Column <= [colItemNumber] Then
        SetCalculations cellRange:=Target
    End If
    
My_Exit:
    Exit Sub

ErrHandler:
    msg = "An error occurred on cell auto-update: " & vbNewLine & Err.Description
    MsgBox msg
    Resume My_Exit
End Sub

"colSourceDevice" is a named range/object that refers to a formula that returns the column number and the bracketed expression is a shortcut to call the Application.Evaluate method. The formula referred to in "colSourceDevice" is:

=VALUE(COLUMN('HS - Main'!$A$2))

In Excel 2016, the bracketed expression returns the column number (Variant/Double).

In M365 Excel the bracketed expression returns an array (Variant/Variant) with one item (Variant/Double).

In M365 Excel, this results in a Type mismatch error.

I can use the object model approach instead of the bracketed expression and get the intended result:

Application.Evaluate("colSourceDevice")(1) 'returns column number (Variant/Double)

Why does the square bracketed expression return a different data type in M365 vs Excel 2016?

EDIT:

As chris neilsen alluded to, there was a major change to the Excel calculation engine in 2018.

See here:

This exhibits the behaviour you describe

What it's actually doing is returning a Dynamic Array of 1 element.

To return that value as a Variant, modify the Named range by wrapping it in INDEX

colSourceDevice2 Refers to = =INDEX(COLUMN(),1,1)

The result is now what you want

Disclaimer:

All that said, this is a band aid fix for some ill-conceived code. My advise: refactor your code to eliminate this and other bad practices.