Use Excel to get result

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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?[/quote]
Yes, with a bit of work that could be done.

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.[/quote]
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.

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
[Fmr MS MVP - Word]

Reply | Quote

You don’t even need a workbook if you use Evaluate:

Code:

xlApp.evaluate(strinput)

for example.

Reply | Quote

that makes life even simpler. Thanks.

Fred

Reply | Quote