Table autofilling formula even with Application.AutoCorrect.AutoFillFormulasInLists = False-CodePudding

Not sure what's going on. I have Application.AutoCorrect.AutoFillFormulasInLists = False set before assigning a range's formula to an array. The expectation is that the formula's will be uniquely set to a value in the array. Instead, they are immediately auto-filled to the first value in the array.

I've made sure that the array is formatted properly and displays the correct values so long as I don't put "=" in front of the string. Excel only changes the values in the table when the string is a valid Excel formula.

Others in the past have stated that Application.AutoCorrect.AutoFillFormulasInLists = False should fix this, but I'm thinking Office365 is different. As a side note, Excel is also adding rows automatically to the table even with that option turned off (although I do want that to happen). Maybe macros have different settings than excel itself? Any suggestions?

Edit: Adding some example code which produces the same result for me.

Public Sub Test()
    
    Dim testTable As ListObject
    
    If Sheet1.ListObjects.Count = 0 Then
        Set testTable = Sheet1.ListObjects.Add(xlSrcRange, Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 1)))
    Else
        Set testTable = Sheet1.ListObjects(1)
    End If
        
    Dim dataArray As Variant
    dataArray = Application.Transpose(Array("=""Formula 1""", "=""Formula 2""", "=""Formula 3"""))
        
    Application.AutoCorrect.AutoFillFormulasInLists = False
    testTable.DataBodyRange.Formula = dataArray
    
End Sub

CodePudding user response：

For completeness I'll update the sample code with the FormulaArray workaround. The following works for a single column:

Public Sub Test()
    
    Dim testTable As ListObject
    
    If Sheet1.ListObjects.Count = 0 Then
        Set testTable = Sheet1.ListObjects.Add(xlSrcRange, Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 1)))
    Else
        Set testTable = Sheet1.ListObjects(1)
    End If
        
    Dim dataArray As Variant
    dataArray = Application.Transpose(Array("=""Formula 1""", "=""Formula 2""", "=""Formula 3"""))
    
    Application.AutoCorrect.AutoFillFormulasInLists = False
    testTable.DataBodyRange.FormulaArray = dataArray
    Application.AutoCorrect.AutoFillFormulasInLists = True
    
End Sub

If you have a table with multiple columns though:

Public Sub Test()
        
    Dim testTable As ListObject
    
    If Sheet1.ListObjects.Count = 0 Then
        Set testTable = Sheet1.ListObjects.Add(xlSrcRange, Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 2)))
    Else
        Set testTable = Sheet1.ListObjects(1)
    End If
    
    Dim newFormulaArray As Variant
    newFormulaArray = Array("=""Formula 1""", "=""Formula 2""", "=""Formula 3""")
    
    Dim newDataArray As Variant
    newDataArray = Array("Value 1", "Value 2", "Value 3")
    
    Application.AutoCorrect.AutoFillFormulasInLists = False

    testTable.DataBodyRange.Value = Application.Transpose(Array(newFormulaArray, newDataArray))
    
    testTable.ListColumns(1).DataBodyRange.FormulaArray = Application.Transpose(newFormulaArray)

    Application.AutoCorrect.AutoFillFormulasInLists = True
        
End Sub

CodePudding user response：

To be honest, I'm not sure this was possible even before Excel 365, and would welcome proof to the contrary (I can no longer test).

Several options, each of which might have issues:

Convert the table to a range (unmake it a table), write the formulas, and reconvert. This won't work if your formulas include structured references.
Use .FormulaArray, if your formulas don't break as array formulas and you don't mind the enclosing {} in the formula bar.
Use .Formula but write the formulas in "chunks" - i.e. split your array into several smaller arrays and write them in succession.