Can this procedure be made more efficient?

[DennisE]

Excel lovers,

I have an Excel program with user forms containing many textboxes
that people want to change values in by manipulating them in an
algebraic fashion without reaching for their calculators or leaving the
user form or the textbox involved.

The textboxes contain formatted text such as $2,345,678.50 and to ease
the burden of increasing the value by 15%, and/or dividing it in half, and/or
adding or subtracting $12,456, I've written the brute force procedure below
that centers around the Evaluate() method. That way a user could replace the
value
shown by 1.15*($2,345,678.50 + 12456)/2 without a calculator or resorting
to an intermediate spreadsheet, and when done with that variable quickly
move on to the next textbox, etc.

Any suggestions in the way of making the procedure "tighter" or
an alternate approach would be welcome.

-- Dennis Eisen

Private Sub MyUserForm.MyTextBox_AfterUpdate()
Dim ResultString as String
Algebra MyUserForm.MyTextBox.Text, ResultString
MyUserForm.MyTextBox.Text = ResultString
'Code goes here that reformats MyTextBox.Text with $ signs or % signs, etc,
End Sub

Sub Algebra(InputString As String, OutputString As String)
Dim Fox As String, Char As String, Foxx As String
Fox = InputString
If InStr(Fox, "+") > 0 Or InStr(Fox, "*") > 0 Or InStr(Fox, "-") > 0 Or
InStr(Fox, "/") > 0 Then
L = Lenx(Fox) 'gets length of string
Foxx = ""
For i = 1 To L
Char = Midx(Fox, i, 1) 'gets ith character
If Not (Char = "$" Or Char = "%" Or Char = ",") Then
Foxx = Foxx + Char
End If
Next i
OutputString = Evaluate(Foxx)
Else
OutputString = Fox
End If
End Sub

 

[Melanie Breden]

Hi Dennis,

The textboxes contain formatted text such as $2,345,678.50 and to ease
the burden of increasing the value by 15%, and/or dividing it in half, and/or
adding or subtracting $12,456, I've written the brute force procedure below
that centers around the Evaluate() method. That way a user could replace the
value
shown by 1.15*($2,345,678.50 + 12456)/2 without a calculator or resorting
to an intermediate spreadsheet, and when done with that variable quickly
move on to the next textbox, etc.

Any suggestions in the way of making the procedure "tighter" or
an alternate approach would be welcome.

use a Function instead of a Sub:

Private Sub MyTextBox_AfterUpdate()
MyTextBox.Text = Algebra(MyTextBox.Text)
End Sub

Function Algebra(InputString As String) As String
Algebra = InputString
If InputString Like "*[+*-/]*" Then
InputString = Replace(InputString, "$", "")
InputString = Replace(InputString, "%", "")
InputString = Replace(InputString, ",", "")
Algebra = Evaluate(InputString)
End If
End Function

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[DennisE]

Melanie,
Thaks so much for your suggestion. I needed to make one adjustment to your
code, because the * symbol within the brackets must be enclosed in single quote
marks for the procedure to work properly. That is, you must code:

If InputString Like "*[+'*'-/]*" then

-- Dennis Eisen

 

[Melanie Breden]

Hi Dennis,

Thaks so much for your suggestion. I needed to make one adjustment to your
code, because the * symbol within the brackets must be enclosed in single quote
marks for the procedure to work properly. That is, you must code:

If InputString Like "*[+'*'-/]*" then

I don't understand this.
In my tests with Win XP and XL 97 to XL2003 (German Version) it
works fine with "*[+*-/]*".

The string "1,15*$2345678,50" will be proper find and calculate.
Which OS and Excel version do you use?

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[DennisE]

Hi, Melanie

I'm using Windows XP and Excel 2002 and Excel 2003 (American versions).

I guess the difference between needing to enclose the * symbol in single quotes
'*'
in the Like operator must be one of those differences like commas versus
decimal points or spaces versus commas in the European versus American versions
of Excel. To make sure, could you ask one the American Excel MVP's to confirm
this
quirk?

-- Dennis Eisen

 

[Tom Ogilvy]

the single quotes add noting. They just make the single quote as a
character that triggers the evaluation.

to demonstrate:

? "1+3" like "1*3"
True
? "1+3" like "1[*]3"
False
? "1*3" like "1[*]3"
True
? "1*3" like "1['*']3"
True
? "1'3" like "1['*']3"
True


As the help says, almost any character used in the grouping brackets is
taken as literal. It could be written better, but that is what it says.
It also says: "To match the special characters left bracket ([), question
mark (?), number sign (#), and asterisk (*), enclose them in brackets."
This is poorly placed in the help because it comes right after the
explanation of the group symbol and sounds like it is referring to using the
special characters within a group - however, what it is saying is that using
them in a group makes them literal.

Function Algebra(InputString As String) As String
Algebra = InputString
If InputString Like "*[+*-/]*" Then
InputString = Application.Substitute(InputString, "$", "")
InputString = Application.Substitute(InputString, "%", "")
InputString = Application.Substitute(InputString, ",", "")
Algebra = Evaluate(InputString)
End If
End Function

Sub Tester1()
Dim sStr As String
sStr = "1.15*$2345678.50"
Debug.Print Algebra(sStr)
sStr = "1.15+($2,345,678.50+12456)/2"
Debug.Print Algebra(sStr)
sStr = "3.1417/sqrt(12)"
Debug.Print Algebra(sStr)
End Sub

all worked fine.

Excel 97, US English

 

[DennisE]

Tom and Melanie:

I went back to my code and removed the single quote marks surounding the *
symbol within the brackets, changing
Like "*[+-/'*']*" to Like "*[+-/*]*" For reasons unknown, this time it worked!

You may all disagree with me, but I would swear Excel's performance can
sometimes vary with sunspots, phases of the moon, and my wife's mood.

-- Denis Eisen

 

[Melanie Breden]

Hi Dennis,

I went back to my code and removed the single quote marks surounding the *
symbol within the brackets, changing
Like "*[+-/'*']*" to Like "*[+-/*]*" For reasons unknown, this time it worked!

You may all disagree with me, but I would swear Excel's performance can
sometimes vary with sunspots, phases of the moon, and my wife's mood.

thanks for your feedback.
Now I can sleep again calmed down

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)