Not directly. You could use the "split" function and then compare
elements of the array. First, I get the formula and split it to be
assigned to the variable "sArguements". This is an array variable
that would look like this:
sArguements(0)="=sum(a1"
sArguements(0)="a2"
sArguements(0)="a3"
sArguements(0)="a4"
sArguements(0)="a4)"
Then I strip out the portion of the formula up to the parenthesis and
the last parenthesis.
Finally, I compare each cell that's referenced by the formula to the
other cells referenced by the formula. It could be done more
efficiently than this, but I don't know if it would be worthwhile
(e.g., if you have a lot of cells in each sum function).
You'd have to take another track if you have references that cover
more than one cell, (i.e. a1:a3)
Public Function test1() As Boolean
Dim sFormula As String
Dim sArguements
Dim i As Integer, x As Integer
Dim bDuplicate As Boolean
bDuplicate = False
sFormula = ActiveSheet.Cells(5, 5).Formula
sArguements = Split(sFormula, ",")
For i = 0 To UBound(sArguements)
If InStr(1, sArguements(i), "(") <> 0 Then
sArguements(i) = Mid(sArguements(i), InStr(1, sArguements
(i), "(") + 1, 999)
Else
If InStr(1, sArguements(i), ")") <> 0 Then
sArguements(i) = Left(sArguements(i), InStr(1,
sArguements(i), ")") - 1)
End If
End If
Next i
For i = 0 To UBound(sArguements)
For x = 0 To UBound(sArguements)
If Not i = x Then
If sArguements(i) = sArguements(x) Then
bDuplicate = True
End If
End If
Next x
Next i
test1 = bDuplicate
End Function
