How to use complex formulas in data validation via VBA without Excel adding the @ symbol? [duplicate]

I'm facing two related problems while working with VBA in Excel. Both involve complex formulas, but they behave differently when set through VBA compared to when entered manually in the Excel UI.

1. Problem with Data Validation Formula: I need to use VBA to set data validation for Excel cells, and some of these validations need to contain complex formulas like INDIRECT, SUMIF, and dynamic references. When I set the formula manually via the Excel UI, it works fine, but when I try to use VBA to set it, I get an error or the formula does not behave as expected.

Here’s the VBA code I’ve been using to set the data validation formula:

vba Kopiëren Bewerken

Sub SetDataValidation(sheetName As String, cellAddress As String, listOrFormula As String)
Dim ws As Worksheet
Dim rng As Range

    ' Set the worksheet and cell
    Set ws = ThisWorkbook.Sheets(sheetName)
    Set rng = ws.Range(cellAddress)
    
    ' Delete existing validation
    rng.Validation.Delete
    
    ' Check if the parameter is a formula (starts with "=")
    If Left(listOrFormula, 1) = "=" Then
        ' Remove the underscore and pass the formula as text
        With rng.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:=listOrFormula
            .IgnoreBlank = True
            .InCellDropdown = True ' Ensure the dropdown is visible
            .ShowInput = True
            .ShowError = True
        End With
    ElseIf InStr(1, listOrFormula, ",") > 0 Then
        ' Add list validation (e.g., "option1, option2")
        With rng.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:="{" & listOrFormula & "}"
            .IgnoreBlank = True
            .InCellDropdown = True ' Ensure the dropdown is visible
            .ShowInput = True
            .ShowError = True
        End With
    Else
        ' Error message if no valid list or formula is provided
        MsgBox "The specified list or formula is not valid.", vbExclamation
        Exit Sub
    End If
    
    MsgBox "Data validation set for cell " & cellAddress, vbInformation

End Sub

When I try to use a complex formula like:

excel Kopiëren Bewerken

=INDIRECT("SheetName!"&INDIRECT("Range!A"&SUMIF(Range!B:B,Condition,Range!C:C))&"1")

It works fine when entered manually but fails or doesn’t work correctly when set using VBA.

2. Problem with Formula Insertion (Dynamic Array @ Symbol): I’m also trying to insert complex formulas directly into Excel cells via VBA. However, when I do this, Excel automatically inserts an @ symbol before the formula (e.g., @INDIRECT("SheetName!"&INDIRECT("Range!A"&SUMIF(Range!B:B,Condition,Range!C:C))&"1")).

This behavior happens with formulas that return dynamic arrays, like IF, SUMIF, and INDIRECT. The formula works when manually entered, but when inserted through VBA, the @ symbol appears, indicating Excel is treating it as a dynamic array formula. This is causing problems because I need the formula to be inserted exactly as typed without the @.

Here’s the VBA code I’ve been using to insert the formula:

vba Kopiëren Bewerken

Sub InsertComplexFormula()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

    ' The complex formula as a string
    Dim formula As String
    formula = "=INDIRECT(""SheetName!""&INDIRECT(""Range!A""&SUMIF(Range!B:B,Condition,Range!C:C))&""1"")"
    
    ' Inserting the formula into cell A1
    ws.Range("A1").Formula = formula

End Sub

Questions: Why does Excel automatically insert the @ symbol in dynamic array formulas when inserting them via VBA?

Is there a way to prevent Excel from adding the @ symbol when I insert complex formulas through VBA?

How can I set a complex formula as data validation (e.g., using INDIRECT, SUMIF) via VBA without causing errors or losing the formula's functionality?

How can I insert a formula into a cell via VBA exactly as typed, without Excel automatically converting it into a dynamic array and appending the @ symbol?

I’ve tested this in both Excel 365 and Excel 2021, and the issue persists. It seems to be related to Excel’s handling of dynamic arrays in these versions. Any suggestions or solutions would be greatly appreciated!