On Sun, 31 Aug 2008 02:55:35 -0700 (PDT), 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

I'm not sure if this algorithm will work for you. If not, you will need to get
into real equation parsing, and define more precisely what you mean by an
"element" and a "part"

But perhaps the number of "parts" would be equal to the number of operators,
and the number of "elements" would be equal to the number of operators + the
number of commas.

If we define an operator as anything in the set of [-+/*^=], then we do not
have to "add 1" as that would effectively be done by counting the initial "="
in the formula text.

You will need VBA to obtain the formula text, so we might as well do the whole
thing with a UDF:

=AnalyzeFormula(cell_ref, [elements])

The elements entry is optional; if false or blank, the formula will return the
number of "Parts" (3 in your example)

If True, it will return the number of "Elements" (5 in your example).

=================================================
Option Explicit

Function AnalyzeFormula(cell_ref As Range, Optional Elements As Boolean =
False) As Variant
Const sParts As String = "[-+/*^=]"
Const sElements As String = "[-+/*^=,]"
Dim sFormulaText As String
Dim sStr As String

Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
If Elements = True Then
re.Pattern = sElements
Else
re.Pattern = sParts
End If

If cell_ref.Count < 1 Then
AnalyzeFormula = CVErr(xlErrRef)
Exit Function
End If

sFormulaText = cell_ref.Formula
sStr = re.Replace(sFormulaText, "")

AnalyzeFormula = Len(sFormulaText) - Len(sStr)
End Function
=====================================
--ron