parser for the formulas style of excel

[Edcronos]

Hello again
I'm sorry for being so annoying.

I would like to know if anyone here knows, as it would a parser for the formulas style of excel
I tried to mount a filter for arays in vba, but my ability is not enough, and I still lost the last one I had made, although minimally functional

[Michael Hartlef]

Sorry, can't help you with that. Maybe the others can.

[Edcronos]

Hi
Would be only so that instead of using the default
(Condition And condition) Or (condition And condition)

Use an easier pattern

Or (And (condition, condition), And (condition, condition))

Of course, with few conditions, and if the "()" is accepted, it's not worth it to invent
I even tried to mount something using symbols, to make it easier to build, but it was a mess to see


#2(@And(%day(3,5,6);|-1(@Or(#dz3(%uni(3,5,7,9);%dez(@>(5))))

Type symbols
$ = Plan
! = Sector "Range"
# = array column reference
| = array row reference "in the case referring to the current line of filter analysis"
@ = Ex Functions - >>> And, OR, dir, esq, <, <>,>, day, week, month, year, like, pair, ...
% = Type for conversion --- >> day, month, year, dir, esq, uni, ten, thousand, invert, ...
_ = Return
Also in a hd crash I ended up losing the functional version
Of course it was something limited by my ability

[Petr Schreiber]

Edcronos,

you are not annoying at all, come on!

Have a look at Tokenizer module and its examples in SampleScripts, maybe they could help you to design your own solution.


Petr

[harsh]

I would like to know if anyone here knows, as it would a parser for the formulas style of excelProbably the easiest way to start is using "Regular Expressions". The ThinBASIC unit is called "VBRegExp". Regular expressions are well beyond the scope of an inexperienced programmer (as is the task that you're trying to accomplish) but are a very powerful tool.

There is a lot of guidance available on the Interet about using Python and Regular Expressions to parse equations.

Excel is particularly difficult because of the many available functions (and some of those functions have a variable number of parameters). The SUM function is a good example. There are also functions that have optional arguments and that adds to the complexity.

Excel formulas aren't particularly useful outside of spreadsheets where references to cells, ranges, dates and strings are possible.

[Edcronos]

Hi
thanks for the tip, I'll take a look
I agree when it is said that it is difficult for a beginner or someone not so involved with programming as I am
Even what I had done, where it seemed like a form for swearing , for comics, it was very difficult to implement



But I disagree when you say it would not be useful outside spreadsheets


Excel formulas aren't particularly useful outside of spreadsheets where references to cells, ranges, dates and strings are possible.

A spreadsheet is not much different from an array
And even a table that has a different organization could be compared

A system of manipulating arrays tables and files in the style of formulas of excel would facilitate much thing
Exchange of values, comparisons and conditioned searches,
Without having to create loops and if's for every situation
Even manipulation of different arrays
Just consider arrays like ranges,

[harsh]

A spreadsheet is not much different from an arrayArrays, by definition, are nice rectangular tables of a single data type. In contrast, spreadsheets have a number of data types (strings, numbers, dates, booleans, formulas, hyperlinks) scattered throughout.
Exchange of values, comparisons and conditioned searches,
Without having to create loops and if's for every situation
Even manipulation of different arrays
Just consider arrays like ranges,Again, you're assuming that all things can be compared without having to know what kind of data they are. It isn't reasonable to compare a number against a date or text against a number.

I suspect that like 90% of the population, you're thinking spreadsheet because that's the biggest hammer you have in your tool box. Spreadsheets don't make for a very good tool to store data in. You find this out the first time you don't save your spreadsheet and all the updates you've made are gone.

[Edcronos]

I can be engaged
But it seems that even in C ++ they are already introducing the variant type
What makes variables and arrays much more dynamic
Not to mention functions that can work with different types of data

But even if I did not, I think it would be possible to manipulate arrays of pointers from other arrays
Of course you have to have a base of the type of data you have, to make a comparison, and functions of manipulation and conversion of these values, even in a worksheet is not going to look up the name of someone in a column that only has numbers

A type of search equal to excel avoids getting mounted functions and macros for each type of situation
And can help even where the data type is all the same

Well, I lost my HD along with everything I had mounted, and the data was irrelevant because it was just for testing
This kind of implementation helps not to have to mount macros equal to this one though simple is quite boring to assemble and align with the call macro


Private Sub conta_Acertos(ByRef Array_dados, ByVal Linha_array_Dados As Long, ByRef A_testes, ByRef ResultArray) 'As Long
Dim cont As Long, Str As String, L As Long, M As Long, fLa As Long

c2 = UBound(Array_dados, 2)
M = UBound(A_testes, 1)
Dim lrd(1 To 2) As Long
c1 = 1
For L = 1 To M
If A_testes(L, FCol_ini) > 0 Then c1 = A_testes(L, FCol_ini)
If A_testes(L, FCol_fim) > 0 And A_testes(L, FCol_fim) < c2 And A_testes(L, FCol_fim) > c1 Then c2 = A_testes(L, FCol_fim)
fLa = Linha_array_Dados + A_testes(L, FLin_dif)
art = UBound(A_testes(L, FVal), 1)
For Cx = c1 To c2 'Step d 'Colunas

For na = 1 To art
If A_testes(L, FVal)(na) = Array_dados(fLa, Cx) Then
cont = cont + 1
Str = Str & "," & A_testes(L, FVal)(na)
If A_testes(L, FLin_dif) < lrd(1) Then lrd(1) = A_testes(L, FLin_dif)
If A_testes(L, FLin_dif) > lrd(2) Then lrd(2) = A_testes(L, FLin_dif)
A_testes(L, FVal)(na) = A_testes(L, FVal)(na) + A_testes(L, FVincr)

If A_testes(L, FVal)(na) <= A_testes(L, FVMin) Or A_testes(L, FVal)(na) >= A_testes(L, FVMax) Then
If A_testes(L, FVrst) <> 0 Then
A_testes(L, FVal)(na) = A_testes(L, FVrst)
End If
If A_testes(L, FVziq) = 1 Then
A_testes(L, FVincr) = -A_testes(L, FVincr)
End If
End If
End If
Next
Next
Next

ResultArray(1) = cont
ResultArray(2) = Str
ResultArray(3) = lrd

End Sub




posso estar engado
mas parece que mesmo em C++ já estão introduzindo o tipo variante
oq torna variaveis e arrays muito mais dinamicas
sem falar nas funções que podem trabalhar com diferentes tipos de dados

mas mesmo se não tivesse, acho que seria possivel manipular matrizes de ponteiros de outras matrizes
claro que se tem que ter uma base do tipo de dados que se tem, para se fazer uma comparação, e funções de manipulação e converção desses valores , mesmo em uma planilha não vai se procurar o nome de alguem em uma coluna que só tem numeros

um tipo de busca igual ao excel evita de se ficar montando funções e macros para cada tipo de situação
e pode ajudar mesmo onde o tipo de dados é todo igual

bem, perdi meu HD junto com tudo oq eu tinha montado , e os dados eram irrelevantes por que eram apenas para testes
esse tipo de implementação ajuda a não ter que montar macros iguais a essa que apesar de simples é bastante chata para montar e alinhar com a macro de chamada




Even though it is limited by my ability
The pro formula filter helped a lot
I'm thinking of reassembling
But this time using a vba tool that creates macros at runtime
Maybe I can mount dnamica macros according to the requirement of the formula

[harsh]

But it seems that even in C ++ they are already introducing the variant type
What makes variables and arrays much more dynamic
Not to mention functions that can work with different types of dataVariant types are a very powerful tool, but imagine what happens when you encounter a formula that refers to cells that contain other formulas or worse, circular references.
Of course you have to have a base of the type of data you have, to make a comparison, and functions of manipulation and conversion of these values, even in a worksheet is not going to look up the name of someone in a column that only has numbersBut does it make sense to search text for numbers and what do you do when you encounter something like FACE that could be either a hexadecimal number or a string? Is 28-1-2017 a date or does it have an answer of -1990?

Having to look forward to see what kind of datatype a formula refers to in another cell is where the state machine comes in (and the complexity rises by orders of magnitude).

There are libraries for several languages (Python, PHP, Java, C++) that will help you load data from .XLS files and it may be useful to study their documentation to see how they approach the problem.

If you go back far enough, you can find simple spreadsheet programs that are written in BASIC.

Take it from me, parsing CSV files generated by Excel can be difficult if you don't know what the data is.


Is your goal to write an Excel compatible spreadsheet or are you trying to convert them to something else. Often it is easier to find help if you start with a definition of the project rather than somewhere in the middle where you are stranded.

[Edcronos]

Even in spreadsheets is something that has to be well structured and are widely doable for errors
My intention is not something as sophisticated as the ability of excel formulas,
Would be something only for simple bases and for known data types, as I said I am not a programmer


If you go back far enough, you can find simple spreadsheet programs that are written in BASIC.
This would be interesting if you have any tips on where I can find


eu perdi o que eu estava fazendo , que já usava funções de chamadas
mas tenho uns pedaços salvos do inicio do projeto,
no caso o primeiro esboço
[CODE]

Of course it is far from something that can be taken seriously and has to have a series of support and pre-verification functions of data and structure
But I do not think it's impossible for an experienced programmer

[harsh]

Of course it is far from something that can be taken seriously and has to have a series of support and pre-verification functions of data and structure
But I do not think it's impossible for an experienced programmerThere is no question that it is possible. The question is whether you must reinvent the wheel.

Rather than pasting code block after code block, perhaps you can explain in human terms what it is you're trying to accomplish.

[Edcronos]

Em vez de colar bloco de código após o bloco de código, talvez você possa explicar, em termos humanos o que é que você está tentando realizar.

really,
Sorry
Even because it's not something that's working



Não há dúvida de que é possível. A questão é se você deve reinventar a roda.

I believe that the wheel is always being reinvented,
I think you can and you should

Then, what would be the purpose of thinbasic, freebasic, java, c ++ ...
They all do the same thing differently,


But what I intend to do is not even close to the proposal of these ideas

It would be just to have an option to write
And (test1,Or(testB , test2), test3)
instead of
test1 And (testB Or test2) And test3
Some even accept the "()" to separate tests, but others like the vba have to structure in a very complex way to put everything together
I put a cascade structure


if Test1 then
if testB Or test2 then
if Test3 then
code
end if
end if
end if


As I said "look like the excel style"
My use is for arrays,
So you do not have to keep typing a bunch of lines with "for and if" with each new filter idea, and check
What I had set up despite something rude, and rather limited, worked for simple things

Well, I do not know if you can understand my explanation.

[Petr Schreiber]

Hi Edcronos,

then what about this, which works already:

All(test1, Some(testB , test2), test3)

@Harsh: +1


Petr

[Edcronos]

Hi
Can be a solution for simpler cases
Do these functions solve all the questions, then show the result,

or leave the tests when it is no longer possible a false or true?
All(
Test1 = true
Test2 = false "exits function with value = false"
Test3 = true "is not tested because it has already had a previous false"
Test4 = true
...)
SOME(
Test1 = true "exits function with value = true"
Test2 = false "is not tested because it has already had a previous true"
Test3 = true
Test4 = true
...)

Is there any counting function for true or false tests?
countTrue(
Test1 = true
Test2 = false
Test3 = true
Test4 = true
)
=3 questions true