You simply need to count the number of commas and add one. I made an
unviersqal UDF whiich will look for any character that is passed to the
function and returns one more than the count. I set CountString to one
rather than 0 at the beginning to give a results one more than the count.

Call with
=CountString(A1,",")


Function CountString(TargetString As String, _
FindChar As String) As Integer

'Start a one because number of strings is 1 more
'than the number of times a character appears
CountString = 1
For i = 1 To Len(TargetString)
If Mid(TargetString, i, 1) = FindChar Then
CountString = CountString + 1
End If
Next i

End Function


"meldrum_scotland" wrote:

Hi,

If there was a formula in cell A1 of the following: =offset(C1,1,1) +
G1 * sheet2!A1

I would like to (using VBA) determine the number of elements in the
formula. So for the above formula the evaluating would be:

Parts: 3 (1=offset(C1,1,1), 2 = G1, 3=sheet2!A1)
Elements: 5 (C1, 1, 1, G1, sheet2!A1)

Ref Elements: 3 (C1, G1, sheet2!A1)
Number Elements: 2 (1,1)

Any help would much be appreciate. (Apologies for posting half
complete beforehand)

Best

Meldrum