Refresh Cells Based on Macros
 

Hello,

I've adapted a VBA script that I found online so that it takes in a
range of cells, and concatenates the values of all of those strings,
ignoring cells that are blank. The problem that I'm running into is
that those concatenated strings aren't updating as they should.

Here's a bit more info:

I'm using the value of once cell (let's call it the control cell) to
alter a range of cells. So, when I change the value of the control
cell, the values of the cells in that range change. I then use my
custom VBA method to concatenate all of the non-blank cells in that
range and surface that result into another cell. The problem that I'm
running into is that when I change the value of the control cell, the
values of my contactenated cells aren't updated to reflect the new
range.

Is there something obvious that I'm doing wrong here? If anybody has
any questions, here's the StringConcat method that I'm using:

Function StringConcat(Sep As String, ParamArray Args()) As String
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
' StringConcat
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If


For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If R.Text < "" Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
End If
Next N

'''''''''''''''''''''''''''''''''''
' Remove the trailing Sep character
'''''''''''''''''''''''''''''''''''
If Len(Sep) 0 Then
S = Left(S, Len(S) - Len(Sep))
End If

StringConcat = S

End Function