News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Calculate string expression – Word2003 VBA

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

    Viewing 5 reply threads
    • Author
      Posts
      • #2352565
        Chris Greaves
        AskWoody Plus

        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.

      • #2352578
        Kirsty
        Manager

        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
        anonymous
        Guest

        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
        Chris Greaves
        AskWoody Plus

        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
        Chris Greaves
        AskWoody Plus

        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
        doriel
        AskWoody Lounger

        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 E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

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

        Attachments:
        • #2352701
          doriel
          AskWoody Lounger

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

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

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

        • #2352722
          Chris Greaves
          AskWoody Plus

          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.

          Attachments:
          1 user thanked author for this post.
          • #2352748
            doriel
            AskWoody Lounger

            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 E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

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

            • #2352756
              Kirsty
              Manager

              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
                Chris Greaves
                AskWoody Plus

                “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
                doriel
                AskWoody Lounger

                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 E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

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

            • #2352761
              Chris Greaves
              AskWoody Plus

              “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.

    Viewing 5 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Calculate string expression – Word2003 VBA

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