Evaluating (Splitting out) a formula

[meldrum_scotland]

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

 

[Joel]

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

 

[Gary''s Student]

What you need to do is parse the equation. This is something done by
compilers or interpreters.

The coding of a compiler can be a bit tricky. I suggest you start by geting
a good book on compiler construction.

 

[Bob Phillips]

offset(C1,1,1) has two commas, the OP wants this to count once!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

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

 

[meldrum_scotland]

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)

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 donot
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- Hide quoted text -

- Show quoted text -

Thank you very much for your replies.

 

[mikerickson]

How many elements are in

= (A1 + B1) * C1 ? (2 or 3?)

= SUM(A1:A10)? (1,2 or 10?)

= SUM(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)+0 ?