• Calculate string expression – Word2003 VBA

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Calculate string expression – Word2003 VBA

    Author
    Topic
    #2352565

    Sub test()
    Dim sng As Single
    Dim strExpression As String
    strExpression = “60+144”
    ‘ Dim strVar As Variant
    ‘ strVar = strExpression
    ‘ sng = evaluate(strVar)
    sng = CSng(strExpression)
    End Sub

     

    I have been struggling off and on throughout the day to resolve the arithmetic value of a presented string.

    I am given an x,y co-ordinate as a line of a text file:-

    ORIGIN 60+144,111

    and I want to obtain the horizontal co-ordinate as 204. For input into a Shapes.AddLine statement, since you ask.

    Excel has an Evaluate function – sng = Evaluate(strExpression) – which works very well in Excel2003.

    But I am staggered that I cannot perform a straight arithmetic calculation from a string in Word2003 VBA.

     

    I have previously used =Formula to insert a crafted SUM function in a Word2003 Table, but would appreciate any tips on how to do simple arithmetic from a string expression within VBA.

     

    The application is basically simulating a flatbed plotter, and moves the pen (raised or lowered) between pairs of points. If a room is two rooms down and the rooms are sixty inches and one hundred forty four inches, it is convenient for the user to transcribe this data as “ORIGIN 60+144,111” rather than trust to the user’s mental arithmetic.

    Besides which, computers are good at doing boring and repetitive work.

    Thank you

    Chris Greaves

    Unless you're in a hurry, just wait.

    Viewing 4 reply threads
    Author
    Replies
    • #2352578

      I suspect you might be asking Word to do something that only Excel is designed to do. These references may be of some assistance:
      https://docs.microsoft.com/en-us/office/vba/word/concepts/miscellaneous/concepts-word-vba-reference
      https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office

      Would it be possible to embed an Excel spreadsheet into the Word Document, rather than trying to use a table?

    • #2352622

      I don’t think that you can evaluate “60+144” to get 204.
      Instead, extract out the digits as numbers then evaluate.

      It unclear to me why you want to convert from double to single precision in the last line.

      This code works in Word 2019, not tested in Word 2003

      Sub test()
      Dim sng As Single
      Dim strExpression As String
      Dim x1, x2 As Double
      strExpression = “60 + 144”
      x1 = CDbl(Left(strExpression, InStr(strExpression, “+”) – 1))
      x2 = CDbl(Right(strExpression, Len(strExpression) – InStr(strExpression, “+”)))
      sng = CSng(x1 + x2)
      End Sub

      1 user thanked author for this post.
    • #2352687

      I suspect you might be asking Word to do something that only Excel is designed to do. These references may be of some assistance:
      https://docs.microsoft.com/en-us/office/vba/word/concepts/miscellaneous/concepts-word-vba-reference
      https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office

      Would it be possible to embed an Excel spreadsheet into the Word Document, rather than trying to use a table?

      Thanks for the response, Kirsty.
      I think that the two references are a bit too generic for me. I really thought I knew Word/VBA after 20+ years, but yesterday was surprised to find no example of this sort of calculation in my 900-function library UW.dot nor in my 37MB searchable library of VBA code. That made me scratch my head. On top of that I used to work in compiler development and still can’t believe I haven’t written a parser to evaluate arithmetic expressions!

       

      Would it be possible to embed an Excel spreadsheet into the Word Document, rather than trying to use a table?

      Yes it would, except that I am not using a table. The user walks around the house with a steel tape measure and records the lengths of each wall segment. Then the user keys that data into a text file. Where two rooms run along the other side of the wall and the lengths of the rooms are, say, 60 inches and 144 inches it makes more sense for the user to key in the data as “60+144” rather than get the mental arithmetic wrong.

      Besides which, if we can’t use computers to do arithmetic, then why are we using them? (grin).

      In the main I was staggered that visual BASIC couldn’t evaluate a simple expression for me!

      More in my next response…

      Thanks

      Chris

      Unless you're in a hurry, just wait.

    • #2352697

      I don’t think that you can evaluate “60+144” to get 204.
      Instead, extract out the digits as numbers then evaluate.

      It unclear to me why you want to convert from double to single precision in the last line.

      This code works in Word 2019, not tested in Word 2003

      Sub test()
      Dim sng As Single
      Dim strExpression As String
      Dim x1, x2 As Double
      strExpression = “60 + 144”
      x1 = CDbl(Left(strExpression, InStr(strExpression, “+”) – 1))
      x2 = CDbl(Right(strExpression, Len(strExpression) – InStr(strExpression, “+”)))
      sng = CSng(x1 + x2)
      End Sub

      “It unclear to me why you want to convert from double to single precision in the last line.”

      Hi, and thanks for the response.

      I am coercing to single because the Word2003/VBA .AddShapes requires single precision as parameters – and I was trying to follow the rules (for once!)

      “This code works in Word 2019, not tested in Word 2003”

      And thank you too for this; I had not explored CDbl in all my years. I have your code working in Word2003/VBA and shall write a small parser for myself that deals with addition and subtraction only for now; this has got to be shipped out of the door.

      Cheers

      Chris

      Unless you're in a hurry, just wait.

    • #2352698

      Sorry for posting as picture, but spam filter does not like my IF clause repetition.
      Its evaluated as spam, thats why I posted the picture of my code. It definatelly needs some debugging, but you can get the idea.

      excel-VBA

      Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

      HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

      PRUSA i3 MK3S+

      • #2352701

        And I see my mistake now..
        in 2nd, 3rd and 4th IF section, you should replace “+” with correct sign..

        Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

        HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

        PRUSA i3 MK3S+

      • #2352722

        Hi Doriel, Your reply tried to cross with mine; mine failed:-Apparently too many of my posts are considered spam! So I have attached some code withouyt any commentary. Let’s see if this gets through.

        Cheers

        Chris

        Unless you're in a hurry, just wait.

        1 user thanked author for this post.
        • #2352748

          Exactly the same issue as mine. Maybe we could start a topic to ask if other contribbutors face the same problems. Spam filter seems to be set very aggressively. I have also problem posting links.

          Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

          PRUSA i3 MK3S+

          • #2352756

            Posting code has already been addressed – you may find these tips make your attempts to post more successful:
            https://www.askwoody.com/forums/topic/the-code-feature-is-practically-useless/#post-104645

            You are also less likely to have problems using the Text tab in the text editor (instead of the Visual tab) as it is promoted as better for posting code.

            As to the number of links in one post, that is a spam-filter setting, designed to send posts for moderation (rather than directly to spam) if the links themselves aren’t deemed potentially dangerous, where there are more than a set number (information limited due to spam activities).

            1 user thanked author for this post.
            • #2352764

              “You are also less likely to have problems using the Text tab in the text editor (instead of the Visual tab) as it is promoted as better for posting code.”

              Hi Kirsty.

              Are you suggesting that we NOT type the body of a reply in Visual mode, but only in Text mode?

              If so, please and thank you, what is the purpose of the Visual tab?

              Thanks

              Chris

              Unless you're in a hurry, just wait.

            • #2353015

              The topic is closed already.

              I use the text editor from the first time I went to askwoody, never visual.
              I prefer to see whats happening with my text.
              Neverteless, I have also problems posting links. Maybe little patience could be useful for me, but this happens A LOT recently. I have to wait like 30 mnutes before it works again. Also my posts are evaluated as spam. Does your AI does not like my posts or what? 🙂

              Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

              HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

              PRUSA i3 MK3S+

          • #2352761

            “Exactly the same issue as mine. Maybe we could start a topic to ask if other contributors face the same problems. Spam filter seems to be set very aggressively. I have also problem posting links.”

            Hi Doriel. It’s not the first time for me, but it has been one of the reasons I have been reluctant to post here – spending far too much time trying to get something to actually GO. This time around the spam message indicated something along the lines of “too many posts in too short a time”, but Tom and Fergus dropped in for coffee and dog-biscuits while I was composing that reply to your posts,, so we are talking at least 60 minutes between posts –  and that is spam?

            On another board there is a 60-second repeat rate, and I sometimes fall afoul of that, because some answers require a short acknowledgement, but 60 minutes seems a bit much IMNSHO.

             

            Cheers,

            “Let’s see if this works” of Bonavista.

            Unless you're in a hurry, just wait.

            1 user thanked author for this post.
    Viewing 4 reply threads
    Reply To: Calculate string expression – Word2003 VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: