Custom Formula Functions firing for unrelated sheets

[thesimile]

Hi Gurus,
I have a bunch of sheets . I am running a macro on Sheet1. There is a column that uses a function 'Concat_With_String'. This has been used in various sheets. In sheet1, there is this piece of code that i'm writing to insert rows
based on range from another excel file. As soon as the insertrow line comes in, system is randomly executing the 'Concat_With_String' for various other sheets. This is causing row insert to shift rows unevenly. Any debug idea is appreciated.

  Rng2.AutoFilter Field:=L_Col_Src + 1, Criteria1:=""
  Range(Rng2(2, 1), Rng2(2, Rng2.Columns.Count).End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
  Set Rng3 = Rng2.SpecialCells(xlCellTypeVisible)
  Diff_Array = Rng3
  For Ars = 1 To Rng3.Areas.Count
   Set Rng4 = Rng3.Areas(Ars)
   For i = 1 To Rng4.Rows.Count
    If Not (i = 1 And Ars = 1) Then
    Application.DisplayAlerts = False
    Rng1.Rows(Rng1.Rows.Count + 1).EntireRow.Insert
    Set Rng1 = Rng1.Resize(Rng1.Rows.Count + 1)
    Rng4.Rows(i).Copy Rng1.Rows(Rng1.Rows.Count)
    Rng1(Rng1.Rows.Count - 1, 1).Copy
    Rng1(Rng1.Rows.Count, 1).PasteSpecial xlPasteFormulas
    Rng1.Cells(Rng1.Rows.Count, 1 + L_Col_Src) = ""
    Rng1.Cells(Rng1.Rows.Count, 1 + L_Col) = "X"
    End If
   Next i
  Next Ars

 

[RoryA]

Try setting calculation to manual at the start of your code and then back to automatic at the end. A UDF shouldn't interfere with what your code is actually doing though.

 

[thesimile]

Tried this

The UDF fires randomly for some other sheets still

 

[RoryA]

Is the UDF volatile, or does it depend on any of the cells that your macro is affecting? Also, you should really reset calculation right at the end of the code.

 

[thesimile]

This is the UDF
Public Function Concat_with_String(ConcatRng As Range, sDelim As String)
For Each R In ConcatRng
S_tot = S_tot & sDelim & R.Value
Next R
S_tot = WorksheetFunction.Substitute(S_tot, "|", "", 1)
Concat_with_String = S_tot
End Function