Results 1 to 8 of 8
Thread: Use Excel to get result

20160218, 12:20 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160218, 17:31 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,240
 Thanks
 2
 Thanked 470 Times in 387 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
Code:Sub Demo() Dim StrInput As String StrInput = InputBox("String to evaluate") MsgBox (EvaluateInput(StrInput)) End Sub
Cheers,
Paul Edstein
[MS MVP  Word]

20160218, 18:39 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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

20160218, 19:02 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,240
 Thanks
 2
 Thanked 470 Times in 387 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]

20160218, 21:36 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 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
Also, in your last email you mentioned different forms of the sqrt(2) as
sqrt(2), 2^0.5, 2^(½), √2, 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.

20160218, 22:23 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,240
 Thanks
 2
 Thanked 470 Times in 387 Posts
Yes, with a bit of work that could be done.
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.
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]

20160222, 07:07 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
You don't even need a workbook if you use Evaluate:
Code:xlApp.evaluate(strinput)
Regards,
Rory
Microsoft MVP  Excel

20160222, 08:03 #8
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
that makes life even simpler. Thanks.
Fred