Remove curly brackets from a formula with VBA?

[Glasgowsmile]

Some background... I wrote a python program that queries a database, exports the data into an xlsx spreadsheet and then adds that data into a template with existing formulas.

The issue I'm having is after the data is added to the template and the file saved; when I access the template most of the formulas are now array formulas but they aren't meant to be. Example below

{=IF($D$1="","",FILTER(Sheet1!I3:I400000,(Sheet1!E3:E400000=$D$1)*(Sheet1!F3:F400000=1)))}

If I highlight the cell with the formula, click the formula bar, and hit enter, the brackets remove, and the formula works again. Otherwise, it's only pulling a single line of data. I don't really want to select 10 cells after the template is saved and do this process, I'd rather have it automatically do it when the workbook is opened using VBA but I'm not sure where to begin?

 

[Micron]

I always have believed the solution to the problem is best applies to where the problem originates from rather than doing remedial work. Do you know why this happens and can you not prevent it from happening? Failing that, a workbook or worksheet event could loop through a range of 10 cells and perform the vba Replace function on them. Note that I said vba Replace, not worksheet replace formula function. I know little about how well the latter performs.

 

[smozgur]

I agree with @Micron as it will be the best solution to solve the problem at the origin.

However, if you want that all cells wrapped in curly braces containing a formula (starting with = sign) should be cleaned, then a simple VBA routine like the one below could do that.

Sub cleanFormulas()
Dim rng As Range
Dim cll As Range

    ' You need to define your own range here
    ' Possibly dynamically due to unknown number of rows
    ' Please provide more info about the data range if you have difficulty here
    Set rng = Range("A1:A18")
    
    ' Loop through cells in the range
    ' to clean the curly braces
    For Each cll In rng.Cells
        ' Check if the cell contains a formula wrapped in curly braces
        If Left(cll.Value, 2) = "{=" And Right(cll.Value, 1) = "}" Then
            ' Trim the curly braces
            cll.Value = Mid(cll.Value, 2, Len(cll.Value) - 2)
        End If
    Next cll
End Sub

 

[Glasgowsmile]

I always have believed the solution to the problem is best applies to where the problem originates from rather than doing remedial work. Do you know why this happens and can you not prevent it from happening? Failing that, a workbook or worksheet event could loop through a range of 10 cells and perform the vba Replace function on them. Note that I said vba Replace, not worksheet replace formula function. I know little about how well the latter performs.

I'm not exactly sure why it's occurring. I suspect it might have something to do with the fact that the original file is CSV and then converted to XLSX before being added to the template. I've re-written the python code to save it as XLSX to remove that converting step but I haven't tested it yet.

Beyond that, I'm not entirely sure why it's happening.

 

[Glasgowsmile]

I agree with @Micron as it will be the best solution to solve the problem at the origin.

However, if you want that all cells wrapped in curly braces containing a formula (starting with = sign) should be cleaned, then a simple VBA routine like the one below could do that.

Sub cleanFormulas()
Dim rng As Range
Dim cll As Range

    ' You need to define your own range here
    ' Possibly dynamically due to unknown number of rows
    ' Please provide more info about the data range if you have difficulty here
    Set rng = Range("A1:A18")
   
    ' Loop through cells in the range
    ' to clean the curly braces
    For Each cll In rng.Cells
        ' Check if the cell contains a formula wrapped in curly braces
        If Left(cll.Value, 2) = "{=" And Right(cll.Value, 1) = "}" Then
            ' Trim the curly braces
            cll.Value = Mid(cll.Value, 2, Len(cll.Value) - 2)
        End If
    Next cll
End Sub

Thanks! Should I find myself unable to resolve this at the source, at least I have a way to potentially solve it with VBA.

 

[smozgur]

Thanks! Should I find myself unable to resolve this at the source, at least I have a way to potentially solve it with VBA.

 

[Micron]

While not wrong, it's not necessary to check for either brace first.

For Each cll In rng.Cells
  Replace(Replace(cll.Value,"{",""),"}","")
Next cll

should work whether either or no brace is found.

 

[smozgur]

While not wrong, it's not necessary to check for either brace first.

For Each cll In rng.Cells
  Replace(Replace(cll.Value,"{",""),"}","")
Next cll

should work whether either or no brace is found.

What if the cell actually contains real data (not function) starting with { but not ending with }, or vice versa, or even contains curly braces somewhere in the text or string in a formula?

 

[Micron]

That actually might be the case? Then I guess your way is safer.
I was guided by what the OP said that the formula bar would contain.