Evaluate formula key in by user at input box of powerpoint

[ShaunLee]

I'm new to add-ins and macro in powerpoint. This few days, I tried to create a project by using vba code able to evaluate the formula keyed in by user at powerpoint textbox.

I had already set up the inputs (A,B,C), its range from integer 0 to 5. Output y will also be integer.
Now in the slide I set up a button, when the user clicked on it, it will pop out inputbox to let user key in formula.
So the problem I faced is I can't able to write the code to let it able to read the formula keyed in by user and evaluate the answer.

For example, the user change the 3 inputs (A=3, B=5, C=0) and key in formula (y=3A*B-C). The output should appear answer is 45.

Hope that anyone understand my problem can provide me some suggestion.
Thank you very much for your help.

[mooseman]

First you will have to evaluate each character in the string (y=3A*B-C or whatever string is entered)

Where c = one character in the string, this will tell you if it's
a letter
If (c >= "a" And c <= "z") Or (c >= "A" And c <= "Z")
a number
if (c >= "0" And c <= "9")
or neither (the = and * characters)

If Not (c >= "a" And c <= "z") and Not (c >= "0" And c <= "9") and Not (c >= "A" And c <= "Z")

You will just need to save these parts of the formula into variables and then exchange the values needed and solve.

[John Wilson]

This is not going to be as easy as Mooseman or you think. What are your vba skills like??

Problem 1 you will need to find and change (for example) 3C to 3 * C
Problem 2 you will need to have error checks to cut out people entering non suitable values and formulae
Problem 3 you need to calculate the result. Excel can do this but not easy in PPT. You will need to set a reference to Excel in VBE > Tools > References

This is the basis of code you might use but it will need work to make it reliable (I won't be doing that for you)


Sub calc()
Dim valA As String
Dim valB As String
Dim valC As String
Dim strFormula As String
valA = InputBox("Enter value for A")
valB = InputBox("Enter value for B")
valC = InputBox("Enter value for C")
strFormula = InputBox("Enter the formula")
MsgBox eval_formula(strFormula, valA, valB, valC)
End Sub


Function eval_formula(strInput As String, valA As String, valB As String, valC As String) As Single
Dim regX As Object
Dim osld As Slide
Dim oshp As Shape
Dim b_found As Boolean
'set a reference to Excel or evaluate will fail
Set regX = CreateObject("vbscript.regexp")
With regX
.Global = True
.Pattern = "(\d+)([A-Z])"
End With
'makes text UPPER CASE
strInput = UCase(strInput)
b_found = regX.Test(strInput)
If b_found = True Then
'replaces eg 5D with 5 * D
strInput = regX.Replace(strInput, "$1 * $2")
End If
strInput = Replace(strInput, "A", valA)
strInput = Replace(strInput, "B", valB)
strInput = Replace(strInput, "C", valC)
' You must have a ref. to Excel or this will error
eval_formula = Evaluate(strInput)
Set regX = Nothing
End Function

[mooseman]

John,
I was just giving shaunlee a starting point, I am not going to do all that coding either.
There is a lot more than that that needs to be coded to parse out the formula.
Powerpoint can do just about anything Excel can do as long as its kept to VBA and not using sheet functions or anything like that.
You just need to get the information into the sub routine and do the processing there.
But you do bring up some great points.

[ShaunLee]

Thank you for both of u answering my question.

I'm just a beginner started to learn VBA one month ago and I don't have any programming background before.
Normally I just check through the MSDN website to set up the animations or basic set up. But I can't find any similar things of this calculation part for me to modified.

Sorry for another stupid question.
"You will need to set a reference to Excel in VBE > Tools > References" -- Can I check with you whether this is because I need to use excel calculation ability in powerpoint and 3C change to 3*C is also want to make it valid for excel?

I will try to read your sample code and figure out how to modify it. I believe this will help me a lot especially you gave me the few ideas and path I need to follow.

Thanks a lot.

[John Wilson]

Evaluating a string value like "3 * 6 *12" is Excel territory. Powerpoint can certainly do simple calculations but you would have to work hard to make in understand what you want. It won't evaluate expressions in the way Excel will.

[ShaunLee]

Ok. Thanks a lot! :friends: Now I'm going to search about how to set up the reference. :banghead:

[mooseman]

Powerpoint can certainly do simple calculations but you would have to work hard to make in understand what you want. It won't evaluate expressions in the way Excel will.
Just what can Excel do in VBA that PowerPoint can't?
I do some very sophisticated programming in powerpoint and Excel, PPT just has more annoying objects that fail when using VBA.
Referencing Excel from PPT has it's own challenges.
ShaunLee, you should be able to do all this within PPT, as it's just VB programming.

[John Wilson]

I'm really not getting at you MooseMan!

To set up a referencxe Open the VB Editor and in the tools menu click References and tick the entry for Microsoft Excel. As MooseMan says this can cause problems expecially if you run the code on a PC with na different version but it should work just fine on your PC.

Excel vba can do many things that PPT cannot. e.g. respond to events like new file (possible but very tricky in PPT), Change the colour of a selected series in a chart or in this case evaluate a string like "3C +4A+3B" given values for A,B and C. I know this can be done in PPT but not in any straightforward way and it would take a lot of code to parse all possible scenarios. I have a fair understanding of sophisticated programming too which is probably why MSFT gave me the Most Valuable Professional Award for PPT programming every year since 2007.

If setting a ref. is a problem it can also be done with late binding to an Excel object. This will run slower though.


Sub calc()
Dim valA As String
Dim valB As String
Dim valC As String
Dim strFormula As String
valA = InputBox("Enter value for A")
valB = InputBox("Enter value for B")
valC = InputBox("Enter value for C")
strFormula = InputBox("Enter the formula")
MsgBox eval_formula(strFormula, valA, valB, valC)
End Sub


Function eval_formula(strInput As String, valA As String, valB As String, valC As String) As Single
Dim regX As Object
Dim osld As Slide
Dim oshp As Shape
Dim b_found As Boolean
Dim xlAPP As Object
Set xlAPP = CreateObject("Excel.Application")
Set regX = CreateObject("vbscript.regexp")
With regX
.Global = True
.Pattern = "(\d+)([A-Z])"
End With
'makes text UPPER CASE
strInput = UCase(strInput)
b_found = regX.Test(strInput)
If b_found = True Then
'replaces eg 5D with 5 * D
strInput = regX.Replace(strInput, "$1 * $2")
End If
strInput = Replace(strInput, "A", valA)
strInput = Replace(strInput, "B", valB)
strInput = Replace(strInput, "C", valC)
eval_formula = xlAPP.Evaluate(strInput)
Set regX = Nothing
Set xlAPP = Nothing
End Function

[mooseman]

I'm really not getting at you MooseMan!
Okay John, but you seem to think you need those "Excel things" to do some simple parsing code.
PowerPoint is the worst at automation code (I know I do a ton of it), but the VBA is almost just like Excel.

BTW - if you are such a pro at PowerPoint VBA, can you answer my question I posted to this forum?

[mooseman]

ActivePresentation.Slides(i).Shapes(j).chart.SeriesCollection(1).Format.Fil l.ForeColor.RGB = RGB(255, 0, 0) Will Change the color of a selected series in a bar chart

ActivePresentation.Slides(i).Shapes(j).chart.SeriesCollection(1).Format.Lin e.ForeColor.RGB = RGB(255, 0, 0) Will Change the color of a selected series in a line chart


And as your first example proves, the Evaluate just need to create a reference to VBScript regular expressions to use .

Sorry to harp on this, but some of your explanations don't seem to quite make sense.
I an ready to be proven wrong here, so that maybe my understanding is clearer.

[John Wilson]

I'm pretty busy right now with a major project in the US so I suggest you just post some code that can evaluate values and a formula without using Excel if you think that's a good idea. It's possible but will take a lot more code. Evaluate is nothing to do with regular expressions by the way (in this case anyway).

[ShaunLee]

May I know is that possible to find multiple patterns and different replacements?
Example:
original string: " ABC "
1st pattern : "([AB])" replace with D
2nd pattern : "([DC])" replace with E
so the result is "E" and if original string is DC will straight change to E.

Do you know any website can learn about this? I only found some REGEX tester online but it only provided some basic definition such as [\d+] meaning any single digit.
I'm reading this website msdn.microsoft.com/en-us/library/ee236360(v=vs.85) and msdn.microsoft.com/en-us/library/1400241x(v=vs.85)

Thank you