Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: parser for the formulas style of excel

  1. #1

    parser for the formulas style of excel

    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

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

  3. #3
    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
    Last edited by Edcronos; 04-01-2017 at 20:11.

  4. #4
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732
    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
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  5. #5
    Quote Originally Posted by Edcronos View Post
    I would like to know if anyone here knows, as it would a parser for the formulas style of excel
    Probably 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.

  6. #6
    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,
    Last edited by Edcronos; 27-01-2017 at 23:31.

  7. #7
    Quote Originally Posted by Edcronos View Post
    A spreadsheet is not much different from an array
    Arrays, 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.

  8. #8
    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

  9. #9
    Quote Originally Posted by Edcronos View Post
    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
    Variant 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 numbers
    But 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.

  10. #10
    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
    Last edited by Edcronos; 29-01-2017 at 09:48.

Page 1 of 2 12 LastLast

Similar Threads

  1. Excel COM question...
    By Gerald Sutherland in forum COM
    Replies: 77
    Last Post: 20-08-2014, 01:41
  2. MLGrid for evaluating formulas
    By Petr Schreiber in forum Sources, Templates, Code Snippets, Tips and Tricks, Do you know ...
    Replies: 0
    Last Post: 27-01-2009, 14:04
  3. MS Excel as 3D engine :D
    By ErosOlmi in forum Development
    Replies: 3
    Last Post: 13-03-2008, 01:09
  4. Equivalent VBA Excel code
    By marcel in forum COM
    Replies: 12
    Last Post: 08-11-2007, 16:18

Members who have read this thread: 0

There are no members to list at the moment.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •