Break string to formula

[Edcronos]

Hello,
I am Brazilian, and I'm using an online translator
I'm trying to make a function to break strings of formula for use in a macro that filters values

"!setor1,@E(1,2,3,@Ou(1,4,12,31,@E(a,a,a,a)),@Ou(b,b,b,b))"

@E(1,2,3,@Ou(1,4,12,31,@E(a,a,a,a)),@Ou(b,b,b,b))
>>1,2,3

@Ou(1,4,12,31,@E(a,a,a,a))
>>1,4,12,31
@E(a,a,a,a)

@Ou(b,b,b,b)



I had a function, that of beginning work well
but for a mistake, I modified, and now I am unable to align the idea with the result.

Contfor("!setor1,@E(1,2,3,@Ou(1,4,12,31,@E(a,a,a,a)),@Ou(b,b,b,b))", 3, 0)
=@E(a,a,a,a)
Contfor("!setor1,@E(1,2,3,@Ou(1,4,12,31,@E(a,a,a,a)),@Ou(b,b,b,b))", 3, 1)
=a,a,a,a

Public Function Contfor(ByVal TextoX As String, ByVal OcorrenciaX As Long, Optional ByVal ss As Long) As String
    Dim pos As Long, Ax As Long, ltx As Long, ax2 As Long
    lent1 = Len(TextoX)
    For Ax = 1 To lent1
        lety = Mid(TextoX, Ax, 1)
        If lety = "@" Then pos = pos + 1
        If pos = OcorrenciaX Then
            nucle = 0: ax2 = Ax
            dd = 0
            GoTo tex:
          End If
    Next
    Contfor = "erro"
    Exit Function
   
tex:
    For Ax = ax2 To lent1
        lety = Mid(TextoX, Ax, 1)
        If lety = "(" Then
            nucle = nucle + 1
            If dd = 0 Then nucle0 = Ax
            dd = 1
        End If
        If lety = ")" Then nucle = nucle - 1

        If nucle = 0 And dd = 1 Then
            If ss = 1 Then
                dfg = InStr(nucle0, TextoX, ",@", 1) - 2
                dfg1 = InStr(nucle0, TextoX, ")", 1) - 1    ' --<<--
                If dfg < 1 Or dfg1 < dfg Then dfg = dfg1 - 1 'Else dfg = dfg - 2

                Contfor = Mid(TextoX, nucle0 + 1, dfg - ax2 - 1)
                Exit Function
            Else
                Contfor = Mid(TextoX, ax2, Ax - ax2 + 1)
            End If
            Exit Function
        End If
    Next
    Contfor = "erro"
End Function

 

[NARAYANK991]

Hi ,

It is not clear as to what you want , and what are getting.

You have posted two results of the function :

1. Function( input string , 3 , 0) , which gives you @E(a,a,a,a)

2. Function( input string , 3 , 1) , which gives you a,a,a,a

So what ?

Is 1 wrong or is 2 wrong or are both wrong ?

In any case , what are the results you expect ?

Narayan

 

[Edcronos]

Hi ,

1. Function( input string , 3 , 0) -->> @E(a,a,a,a) (OK)
2. Function( input string , 3 , 1) --->> a,a,a,a (OK)

2. Function( input string , 3 , 1) --->> a,a,a,a, (not)
2. Function( input string , 3 , 1) --->> a,a,a,a) (not)


Function( input string , X , y )

x=1 , y=0 -->> @And(@Or(1,4,12,31,@And(a,a,a,a)),@Or(b,b,b,b))

x=2 , y=0 -->> @Or(1,4,12,31,@And(a,a,a,a))
x=2 , y=1 -->> 1,4,12,31
x=3 , y=0 -->>@And(a,a,a,a)
x=3 , y=1-->> a,a,a,a
x=4 , y=0 -->>@Or(b,b,b,b)
x=4 , y=1-->>b,b,b,b

 

[Edcronos]

I managed to make an adjustment, which took some inconsistencies
But I think that the function is greatly exaggerated

Public Function Contfor(ByVal TextoX As String, ByVal OcorrenciaX As Long, Optional ByVal ss As Long) As String
    Dim pos As Long, Ax As Long, ltx As Long, ax2 As Long, dd As Long

    lent1 = Len(TextoX)
    For Ax = 1 To lent1
        If Mid(TextoX, Ax, 1) = "@" Then pos = pos + 1
        If pos = OcorrenciaX Then
            ax2 = Ax
            GoTo tex:
        End If
    Next
    Contfor = "erro"
    Exit Function

tex:
    dd = 0: nucle = 0:
    For Ax = ax2 To lent1
        lety = Mid(TextoX, Ax, 1)
        If lety = "(" Then
            nucle = nucle + 1
            If dd = 0 Then nucle0 = Ax
            dd = 1
        End If
        If lety = ")" Then nucle = nucle - 1

        If nucle = 0 And dd = 1 Then
            If ss = 1 Then
                If Mid(TextoX, nucle0, 2) = "(@" Then Contfor = "no": Exit Function

                dfg = InStr(nucle0, TextoX, ",@", 1) - 1
                dfg1 = InStr(nucle0, TextoX, ")", 1) - 1

                If dfg < 1 Or dfg1 < dfg Then dfg = dfg1 - 1 Else dfg = dfg - 2
                dfg1 = Mid(TextoX, nucle0 + 1, dfg - ax2 - 1)
                If Right(dfg1, 1) = ")" Or Right(dfg1, 1) = "," Then dfg1 = Left(dfg1, Len(dfg1) - 1)
                Contfor = dfg1
                Exit Function
            Else
                Contfor = Mid(TextoX, ax2, Ax - ax2 + 1)
            End If
            Exit Function
        End If
    Next
    Contfor = "erro"
End Function

and limits the syntax of the formula

thus accepts only:
@Or(1,4,12,31,@And(a,a,a,a))

but does not do so:
@Or(@And(a,a,a,a),1,4,12,31)

 

[NARAYANK991]

Hi ,

What you are looking for is a parser , which is not at all a simple piece of code.

Just enter the following in Google , and see if any of the results is helpful :

excel formula parser

Narayan

 

[Edcronos]

Hi,
Narayan
I'm developing a macro that works just like the SQL SELECT
my sheet has the shape of a database
tab = database
sector "column conjuto" = tables
it connects to a Postgres database, but I feel more comfortable in excel

'Symbols of the commands
'$ = sheet
'! = sector "column group" has 10 possible sectors for tab with dynamic number of columns
'# = column, which will be analyzed
'@ = Functions Ex. AND, OR, Lin, Col, Return, "or something"
'% Type =' Ex. If type = "day" Then Value = Day (Value)
'_ = Return

parts of the formula, will be performed by the macro I did
and return only rows the sector that matches the analysis
ex.
"!employees, $Fixed (3) @E(#6, Missing, @or(# 5, ana, john)) _!employees"

will list all the lines, staff Sector, the, tab, Fixed (3), which has the status "missing" in column 6, and (Ana Or John) in column 5
and will put in the employees sector of the active Sheet

but the q vc indicated, can give me a hand to improve the idea

 

[Edcronos]

Hi ,

What you are looking for is a parser , which is not at all a simple piece of code.

Just enter the following in Google , and see if any of the results is helpful :

excel formula parser

Narayan

What do I'm doing is a own project, to be used within a macro that will filter large amounts of data within an array

but his appointment is of great value,
It will help me when I'm adding new operators
I'm still lost with the formula of style,
I still can not do arithmetic comparisons within the macro,
I can only filter types of dates, days, months, days of the week, values and centenças.
but when I can, I think the formula of style will limit myself
for now only troubleshoot.

 

[Marc L]

Hi !

The very bad idea is to use an unique delimiter for differents things :
leads to this kind of gas factory code …

Without a crystal clear explanation with smart samples covering
all the needs, that could be a never ending story !

See also Split VBA function …​

Sub Demo()
    SP = Split("!setor1,@E(1,2,3,@Ou(1,4,12,31,@E(a,a,a,a)),@Ou(b,b,b,b))", ",@")
    MsgBox "@" & Split(SP(3), ")")(0) & ")", , "  Sample #1 :"
    MsgBox Split(Split(SP(3), "(")(1), ")")(0), , "  Sample #2 :"
End Sub

 

[Edcronos]

Hello,
beautiful approach to the split
but I could not see how to separate the internal parts of the formula with this method

@And(@Or(1,4,12,31,@And(a,a,a,a)),@Or(b,b,b,b))

"@Or(1,4,12,31,@And(a,a,a,a))" << need this integer part
@And(a,a,a,a) to know that this part is the @Or(


my problem is really a lack of experience

I did not understand about "unique delimiter."
I made the macro filtering formula for an immediate need
but it worked so well that I decided to expand the functionality

I use Split VBA function within the macro
Call SplitVALArray (core formula, "" return array)
but it is to separate the core formula in an array, and define what number

Sub SplitVALArray(ByVal StringsVal As String, ByVal Separador As String, ByRef nomeArrayRetorno)
    cotin = Split(StringsVal, Separador)
    ReDim nomeArrayRetorno(1 To UBound(cotin) + 2)
    For h = 0 To UBound(cotin)
        v = cotin(h)
        If IsNumeric(v) = True Then
            nomeArrayRetorno(h + 1) = Val(v)
        Else
            nomeArrayRetorno(h + 1) = v
        End If
    Next
End Sub

for operators I use separate functions
@And , @or, @<>, @>, @<, @>=, @<=
the # is to indicate the column that will be tested
the% is to indicate how the value will be compared
@Or (#Date,%Week, 1,3,5)
filters the lines sector, the column # date, has a %week 1 or 3 or 5

the use of symbols! @ # $% Was to facilitate the identification and separation of functions, but this also limits the amount of operators and syntax of the formula

 

[Marc L]

As I wrote, it's a very bad idea to use same delimiter
for different types of data : it's just a design problem !

​

 

[Edcronos]

It may be a bad idea, but that's what my skills allow me to do.
besides not having experience with treatment of texts
I'm not wanting to do something with all the features of a formula excel.
just want to imitate the writing syntax of this formula
the rest will be very simple instructions
but at the same time, comparators "AND", "OR", ">", "<", "<>" some more, conversion values gives a very wide variety of possibilities

of course if you know how to improve this situation, I accept
but with concrete ideas, because I'm already confused by this

in the formula, the only one delimiter, really present are the brackets "(...)"
((...) (...)) <> ((... (...)))

AND( Or(test1), Or(test2))
if the test 1 is false, And will be false,
in this case, my macro, will not go to the test 2

AND( Or(test1, Or(test2)))
if the test 1 is false, anyway, will take the test 2, if the test 2 is true, And it will be true

 

[Marc L]

If there is only one delimiter, there is no difficulty !​

Sub Demo2()
    Dim SP$()
        SP = Split("AND( Or(test1, Or(test2)))", "(")
      RED$ = SP(0)
     BLUE$ = SP(1)
    GREEN$ = Split(SP(2), ",")(1)
      BK1$ = Split(SP(2), ",")(0)
      BK2$ = Split(SP(3), ")")(0)
End Sub

 

[Edcronos]

I'm sorry, but it is not, something as simple

consider a situation like this:
"@And(1,@Or(2,@And(3,@Or(4),@Or(5))),@Or(6,@And(7,@Or(@And(8),@And(9)))))"

separate nodes of formula and run it so logically and sequentially excluding the parts already accused false, it is not so simple
And(1
if 1 is false, do not need to run the rest of the formula

if true passes
@Or(2,@And(3,@Or(4),@Or(5)))
@Or(2
if 2 is true, not need to test the rest, and passes the test for theE, who called
if 2 is false, then tests the rest
@And(3,@OR(4),@Or(5))
@And(3

and so on

as you can see it is not something easy to separate and organize the execution

 

[Edcronos]

well, somehow I managed to identify where I am missing
on the function which separates the parts of the formula
to catch the beginning, the function finds the @ and goes to the (to make the count, adding "(" minus ")"
to stay right with the various possibilities, it has to be done by parenthesis
and then back to the @ operator to pick up this part of the formula

the sequence of the parties also have to change
instead of using
1 (x, 2 (3 (x), 4 (x)), 5 (x))
has to be
1 (x, 2 (a (x) b (x)), 3 (x))
when call the 2 would be
1 (2 (x), 3 (x))

still do not know how to do, and I'm still inexperienced with string functions

And as it seems that no one will be able to help me on this, I will close the topic

 

[Edcronos]

It is missing some adjustments

Sub terest()
    aa = "@And(1,@Or(2,@And(3,@Or(4),@Or(5))),@Or(6,@And(7,@Or(@And(8),@And(9)))))"
   MsgBox Contfor(Contfor(aa, 1, 1), 2, 0)
    MsgBox Contfor(aa, 1, 2)
End Sub

Public Function Contfor(ByVal TextoX As String, ByVal OcorrenciaX As Long, Optional ByVal ss As Long) As String
    Dim pos As Long, Ax As Long, Ax1 As Long, ltx As Long, ax2 As Long, dd As Long, posi As Long, aa As String
  
    OcorX = OcorrenciaX
    lent1 = Len(TextoX)
    For Ax = 1 To lent1
        aa = Mid(TextoX, Ax, 1)
        If aa = "(" Then
            If pos = 0 Then Ax1 = Ax
            pos = pos + 1
        End If
        If aa = ")" Then pos = pos - 1: If pos = 0 Then ax2 = Ax: dd = dd + 1

        If dd = OcorX Then GoTo tex:

    Next
    Contfor = "erro"
    Exit Function
tex:

    If ss = 0 Then
        For Ax = Ax1 To 1 Step -1
            If Mid(TextoX, Ax, 1) = "@" Then Ax1 = Ax: Exit For
        Next
        Contfor = Mid(TextoX, Ax1, ax2 - Ax1 + 1)
    End If
  
    If ss > 0 Then
        If ss = 1 Then
            Contfor = Mid(TextoX, Ax1 + 1, ax2 - Ax1 - 1)
        Else
            ss = ss - 1
            Contfor = Contfor(Mid(TextoX, Ax1 + 1, ax2 - Ax1 - 1), 1, ss)
        End If
    End If
  
End Function

 

[Marc L]

still do not know how to do, and I'm still inexperienced with string functions

String functions are at very beginner level ! All is explained in VBA inner help …
See InStr, Left, Mid text functions for examples but often Split is enough.
It's just a design / logic question …​

And as it seems that no one will be able to help me on this, I will close the topic

The more clear explanation, the better help …
For each technical difficulty or step, precise input and desired output,
delimiter used, …
______________________________________________________
If you can’t explain it simply, you don’t understand it well enough … (Albert Einstein)
​

 

[Marc L]

in the formula, the only one delimiter, really present are the brackets "(...)"

Training question : considering source string @A(B,C,D)
how do you parse to separate each part like A or B or C or D
and what is the only one delimiter ?
​

 

[Edcronos]

String functions are at very beginner level ! All is explained in VBA inner help …
See InStr, Left, Mid text functions for examples but often Split is enough.
It's just a design / logic question …

then all the vba and excel, even in other programming languages is level 1, because everything has a detailed explanation of the use,
Yes, everything is a matter of logic
but knowing how it works and know how to use a feature, not always, takes you to the expected result
instructions as like, Replace, Split, VBA.Strings.Filter
by your logic, I do not need anything else besides the combination of Split and VBA.Strings.Filter

The more clear explanation, the better help …
For each technical difficulty or step, precise input and desired output,
delimiter used, …

Yup,
probably I am confused with my own ideas, and open topics, just to understand me self

Training question : considering source string @A(B,C,D)
how do you parse to separate each part like A or B or C or D
and what is the only one delimiter ?

that part I need, is only one, the combination of "(" and ")"
other separations I have already prepared within the macro that does the filtering of data array
but the difficulty is not separate, so you can see what I got in the first function, though not always return all the characters correctly
the difficulty is to take properly and in the correct order
and1(xxxx,(nnn,(yyy)),(ttt))
(nnn,(yyy))..>>all this part is considered as one only to and1
the seqeuncia reading is,
xxxx
(nnn,(yyy))
(ttt)
to separate B, C, D then yes you use the split

 

[Marc L]

As your code does not work, forget it !
Yes Split function could work with a right design …
And since the poor initial post and some after, all is about how to separate !

Starting from a blank paper, as you should do before writing any codeline,
avoiding smashing the wall and a waste of time,
so without Split function from source string @A(B,C,D)
for each part and what is the only delimiter ?
​

 

[Edcronos]

my code works
only need a separate function to pick up the pieces
xx= 0( (1,(1)),(2,(1)),(3,(1),(2),(3),(4)) )
aa=XX.nucleo="(1,(1)),(2,()),(3,(1),(2),(3),(4))"

aa.formula, 1=(1,(1))
aa.formula, 2=(2,(1))
aa.formula, 3=(3,(1),(2),(3),(4))

I do not think just at ease, but also in efficiency

I could have just run each operator in the formula, the result stored in an array, and then scan the array to compare to the order of operators

or leave it be and put each part of the formula to go macro filtering in sequence

but large amounts of data and complex formula would be ineffective

okay, I like to complicate

as I put the topic as solved?

 

[Marc L]

Efficiency means good design …
But to just separate string parts, there is no difficulty, even for the order.

Maybe I didn't understand anything of your need since the first post !
I was just thinking when a thread is created
the code do not work as expected …

So if your code works, yes it is solved !
​

 

[Marc L]

​

 

[Edcronos]

as I said, I'm using an online translator
then, not all that I write is the same literal meaning


I have no experience with the use of texts functions
my use is specific,
the greatest difficulty for me, really is to organize ideas
and I can not explain what I need in sufficient detail
but says not to be discussed about

observation, lacked the macro so I can study and stop being noob

 

[Marc L]

Text functions are very easy to experiment !
It's not a lack of experience but just a design / logic problem …
Nothing specific, just common use of text functions !
Just take a paper and a pen, write a source string and step by step,
write how to separate each data …

A month ago, a similar problem of separating data from string
was solved (logic) by kids around 10 years old in less than 10 seconds !
Their thought was when several delimiters are in a string,
it's so easy to mod it with only one delimiter to parse data !
So my previous screenshots are a function using this 10 years old kids logic ‼

But :​

It is not clear as to what you want , and what are getting.

In any case , what are the results you expect ?

Without a crystal clear explanation with smart samples covering all the needs

The more clear explanation, the better help …
For each technical difficulty or step, precise input and desired output, delimiter used, …

and what is the only one delimiter ?

Several times we asked for a clear explanation and
samples of real strings and expected results :
so without them, no concrete help possible …

It's not difficult to attach a .xlsx workbook with real source strings and
a column for each data expected by function !
With as many rows as possible cases …​

 

[Edcronos]

Como já deixei muitos neurônios pelo caminho, e o que sobrou está sendo ocupado com outras coisas
provavelmente eu esteja com o raciocínio menor que o de uma criança de 10 anos