Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Use Excel to get result

    Hi All,

    Is it possible to call Excel from another program to pass it a string and get back results?

    We have some VBA code in PowerPoint and Word. It accepts some user inputs in the form of an answer to a math problem.

    Rather than trying to parse the code in the VBA for those programs (or store some number of "acceptable" answers since you just never know what alternative, but correct, form a user might try) and get back a result (including an error), we thought it might be easier to let Excel figure it out, as if the string had been entered into a cell as a formula (or a constant), and then get back the result of that formula.

    For example, if the answer to a problem was 2, the input could be just 2, or sqrt(4), or any other expression that reduces to 2. If the user put in srqt(4), that should result in a #ERROR of some kind (probably a #NAME). In any case, if Excel comes back with 2, then whatever form the user originally put in is ok. If the answer comes back as anything else, then the user's input is either mathematically wrong (eg, Excel comes back with 2.5) or is syntactically wrong (srqt instead of sqrt).

    For trig questions, this could get tricky. For example, sin(60 deg) = sqrt(3)/2. The user might input that and it would be exactly right. Problem with above is that Excel would return 0.866... Not quite sure how to deal with that yet.

    Any suggestions for an alternative approach would be great. Just trying to avoid from storing strings that we think represent all possibilities of a correct answer, since history proves we can never think of all correct forms.

    TIA

    Fred

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Sure is, but it's a lot of overhead to add to your code:
    Code:
    Function EvaluateInput(StrInput As String) As String
    Dim xlApp As Object, xlWkBk As Object, bEq As Boolean
    bEq = True
    If Left(StrInput, 1) <> "=" Then
      bEq = False
      StrInput = "=" & StrInput
    End If
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
      EvaluateInput = "Error! Can't start Excel."
      Exit Function
    End If
    With xlApp
      .Visible = False
      Set xlWkBk = .Workbooks.Add
      With xlWkBk.Sheets(1)
        .Range("A1").Value = StrInput
        If .Range("A1").Text = "#NAME?" Then
          If bEq = False Then StrInput = Mid(StrInput, 2, Len(StrInput) - 1)
          EvaluateInput = "Invalid data: " & StrInput
        Else
          EvaluateInput = .Range("A1").Value
        End If
      End With
    End With
    xlWkBk.Close False: xlApp.Quit
    Set xlWkBk = Nothing: Set xlApp = Nothing
    End Function
    which you could call with code like:
    Code:
    Sub Demo()
    Dim StrInput As String
    StrInput = InputBox("String to evaluate")
    MsgBox (EvaluateInput(StrInput))
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Paul,

    Doesn't seem like a lot of code to me.

    A couple of quick things:
    - I gave the #NAME error as an example. I noticed you "trapped" that explicitly. How can we be more general?
    - I also noticed you had an error for not being able to start Excel. Why would that be? Do we need Excel to be in a certain location on the "using" computer (where the ppt or Word VBA is running)?

    Those are some quick thoughts. We'll have a go at it and see what happens.

    I kind of thought it would be possible and you'd be the one to figure it out. Thanks.

    Fred

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The overhead is mainly to do with the time it takes to automate Excel to do the calculations. Ideally, you'd only call the function when necessary for a numeric result. There'd be no point in calling it if the answer given was 2, for example. Similarly, for your trig calculations, if sqrt(3)/2 is a valid answer, don't have Excel evaluate it. You'll also have to decide whether, say sqrt(2), 2^0.5, 2^(), √2, 2 & 1.414, are all valid answers without evaluation, as Excel can't evaluate √2 or 2^() and its evaluation of sqrt(2) is rather more precise than 1.414. And that's without considering whether someone uses superscripts, etc.

    The #NAME? error is trapped because that's what any input string that can't be evaluated as a formula in Excel will produce.

    The error for not being able to start Excel allows for a user who has a faulty Office installation, for example, or no Excel.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    If I'm understanding the overhead, it seems that your code is opening Excel and maybe even creating a workbook for every problem the student does. Could we take care of starting Excel when we open ppt/Word and open a workbook only at that time?

    Then when the user does another problem, we already have an open workbook. I think I can see how to shuffle your original code to just take the answer to the current problem and pass it to Excel for evaluation. Could we make the part of your code that actually does the evaluation into a function callable from our VBA code?

    With xlWkBk.Sheets(1)
    .Range("A1").Value = StrInput
    If .Range("A1").Text = "#NAME?" Then
    If bEq = False Then StrInput = Mid(StrInput, 2, Len(StrInput) - 1)
    EvaluateInput = "Invalid data: " & StrInput
    Else
    EvaluateInput = .Range("A1").Value
    End If
    End With
    Anyway, let us tinker with this for a while.

    Also, in your last email you mentioned different forms of the sqrt(2) as

    sqrt(2), 2^0.5, 2^(), √2, 2 & 1.414
    First, I'm assuming that 2 & 1.414 should have been 2 ^ 1.414.

    I don't think we have to worry about an input of "radical 2" since there's no way to input that from a keyboard. Similarly, I don't see an issue with the "half" character since that can't be input either (but as a single character, are parens needed here?). But we could get "1/2" (hopefully in parens). And lastly, while valid, I don't think anyone will input 2 ^ 1.414.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by fburg View Post
    If I'm understanding the overhead, it seems that your code is opening Excel and maybe even creating a workbook for every problem the student does. Could we take care of starting Excel when we open ppt/Word and open a workbook only at that time?

    Then when the user does another problem, we already have an open workbook. I think I can see how to shuffle your original code to just take the answer to the current problem and pass it to Excel for evaluation. Could we make the part of your code that actually does the evaluation into a function callable from our VBA code?
    Yes, with a bit of work that could be done.
    Quote Originally Posted by fburg View Post
    Also, in your last email you mentioned different forms of the sqrt(2) as

    First, I'm assuming that 2 & 1.414 should have been 2 ^ 1.414.
    No, that was that 2 & 1.414, though I don't recall why the '2 &' is there (maybe because I was thinking of adding a superscripted or 0.5) - I certainly didn't mean 2 ^ 1.414.
    Quote Originally Posted by fburg View Post
    I don't think we have to worry about an input of "radical 2" since there's no way to input that from a keyboard. Similarly, I don't see an issue with the "half" character since that can't be input either (but as a single character, are parens needed here?). But we could get "1/2" (hopefully in parens). And lastly, while valid, I don't think anyone will input 2 ^ 1.414.
    The and √ are easily input from the keyboard for anyone who knows the key codes, and superscripting for powers (thus obviating the need for ^) is trivial, too.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You don't even need a workbook if you use Evaluate:
    Code:
    xlApp.evaluate(strinput)
    for example.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    that makes life even simpler. Thanks.

    Fred

Posting Permissions

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