UDF takes <30 seconds on initial creation, but later takes > hour(s)? UDF concatenates matching cells into one string

[insaneoctane]

I've written the UDF (see below) and I'm using in my spreadsheet in one column across 3,000 rows with this call:

=ConcatRangeIF(BB6,$BB$6:$BB$3000,$BG$6:$BG$3000," >")

Now, when I first use it, Excel takes about 20-30 seconds to calculate it. But, later as I manipulate data on the page (pasting, filtering, etc), it gets "confused" and it takes hours. Honestly, after an hour it was only at like 17%, I aborted. My fix is to delete all 2,999 cells with my formula (leaving the original top line) and re-paste the formula into the original spot. Then, again, it only takes 20-30 seconds. This is happening all the time. Why? Any suggestions on how to do this better/faster? The code simply looks at an array, finds rows that match a target value and appends the contents of a diff column from those matching row's. In my example above BB6 is the target to match, BB6:BB3000 is what it's comparing the target to and if matching, col BG is appended to the result. The ">" are just appended to the end of each matched line.
FWIW, I read that I shouldn't use variants, so I'll make that change soon...but I'm looking for something bigger, I think.

Function ConcatRangeIF(ByVal Target As Variant, ByVal if_range As Range, ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value
ifArray = if_range.Value

'Confirm two ranges are same size
if_rangex = UBound(ifArray, 1)
if_rangey = UBound(ifArray, 2)
cell_rangex = UBound(cellArray, 1)
cell_rangey = UBound(cellArray, 2)
If (if_rangex <> cell_rangex Or if_rangey <> cell_rangey) Then
    ConcatRangeIF = CVErr(xlErrNA)
    Exit Function
End If


For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        'test = ifArray(i, j)
        If (Len(cellArray(i, j)) <> 0 And ifArray(i, j) = Target) Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatRangeIF = newString

End Function

Excel 2007 on Windows 7

 

[diddi]

try disabling autocalculation and screenupdating

 

[insaneoctane]

Thanks for the reply. I will need autocalc on. I can turn off screen update, but since this udf gets called 3,000 times, I am not sure turning screen update off then back on in the udf will help?

 

[snb_]

You shouldn't use a UDF to perform this; since the values in BG do not change the loop will be endless.
Use a macro instead that runs only once if necessary.

 

[insaneoctane]

...since the values in BG do not change the loop will be endless...

Why do the values in BG not changing make this endless?

A macro is likely the best solution here, but I wish I knew (for future projects) why this isn't working well.

 

[diddi]

you can disable within macro, which is what I meant...

Application.calculation=xlManual
Application.screenupdating=false

' do code

Application.screenupdating=true
Application.calculation=xlAutomatic

also try stepping your code after the first run that works properly to see what it is doing. use F8 in the coding window

and yes a Better macro would prolly help out...