Solved: VBA EXCEL - Best way to add, divide, subtract and multiply.

[jmaocubo]

Good morning,

I'm creating an application consisting mainly of textboxes.
The values (numbers only) will be added later, split, ... depending on the calculated required.

The biggest problem I have (as attached workbook) is that these users can use textboxes "," or "." as decimal, depending on the country (used in Portugal "," but in other delegations we use ".").

I'm trying to work around the problem using Replace (...., ",", ".") But the result of the calculation is not correct.

It's important that the user can choose between "," and "." to represent a decimal number (or use both).



Any suggestions?


Thanks in advance.

Miguel

[Bob Phillips]

[vba]
Private Sub CommandButton1_Click()
Dim decsep As String

decsep = Application.International(xlDecimalSeparator)

' #### OK Button ####
Sheets("Auxiliar").Range("H3:J3").ClearContents

If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> 0 Then

Sheets("Auxiliar").Range("H3").Value = Replace(TextBox1.Value, decsep, ".")
Sheets("Auxiliar").Range("I3").Value = Replace(TextBox2.Value, decsep, ".")
Sheets("Auxiliar").Range("J3").Value = Replace(TextBox3.Value, decsep, ".")

' sum values from textbox1 and textbox2 and divide with textbox3 value. Display result in sheet "auxiliar" range "d3" as a percentage

Sheets("Auxiliar").Range("D3").Value = Format(((Replace(TextBox1.Value, decsep, ".") _
+ Replace(TextBox2.Value, decsep, ".")) _
/ (Replace(TextBox3.Value, decsep, "."))), "#0,0%")


Call P2_NORM ' Module2


TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""

End If

Select Case Sheets("Auxiliar").Range("E3").Value
Case Is < 0
[F3] = "E"
Case Is < 0.1
[F3] = "D"
Case Is <= 0.4
[F3] = "C"
Case Is <= 0.7
[F3] = "B"
Case Is < 1
[F3] = "A"
Case Else
[F3] = "A+"
End Select

UserForm2.Hide
End Sub
[/vba]

[jmaocubo]

xld Thanks for the quick response.

Still does not work.

For example:

Textbox1 = 2.5
Textbox2 = 1.45
TextBox3 = 15

The result should be: 26.33% but is giving 167.63% (sheets ("Auxiliar"). Range ("D3"). Value)

Why??

Thanks again for the help.

[Kenneth Hobs]

Maybe because you mixed up the Textboxes?

There is also the issue of putting ()'s around the numerator.

Paul=TextBox3 = 15
IUL=TextBox1=2.3
IULmax=TextBox2=1.5

[D3]=26.67%

I am not sure that you need conversions like that. The user should be using the decimal and commas for their setup. I left part of the decsep work in it but I don't see a need.

[vba]Private Sub CommandButton1_Click()
Dim decsep As String

decsep = Application.International(xlDecimalSeparator)

' #### OK Button ####
Sheets("Auxiliar").Range("H3:J3").ClearContents

If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> 0 Then

Sheets("Auxiliar").Range("H3").Value2 = CDbl(TextBox1.Value)
Sheets("Auxiliar").Range("I3").Value2 = CDbl(TextBox2.Value)
Sheets("Auxiliar").Range("J3").Value2 = CDbl(TextBox3.Value)

' sum values from textbox1 and textbox2 and divide with textbox3 value. Display result in sheet "auxiliar" range "d3" as a percentage

Sheets("Auxiliar").Range("D3").Value = (CDbl(TextBox1.Value) + CDbl(TextBox2.Value)) _
/ CDbl(TextBox3.Value)
Call P2_NORM ' Module2


TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""

End If

Select Case Sheets("Auxiliar").Range("E3").Value
Case Is < 0
[F3] = "E"
Case Is < 0.1
[F3] = "D"
Case Is <= 0.4
[F3] = "C"
Case Is <= 0.7
[F3] = "B"
Case Is < 1
[F3] = "A"
Case Else
[F3] = "A+"
End Select

UserForm2.Hide
End Sub[/vba]

[Bob Phillips]

I only addressed the decimal separator issue, the rest is all yours. If you are getting the wrong result, check your calculation.

[jmaocubo]

Hello Kenneth Hobs

Thanks for the help.

Apparently everything seemed to work, however only "," functions as a decimal.

Exemple:

Textbox3=15
Textbox2=2.5
Textbox1=1.45

The Excel considers 145 instead of 1.45



Is there a possibility for both "." as "," works as a decimal?

Thanks again

[Kenneth Hobs]

I would let the user do what they want but it is your call.

Try:
[VBA]Private Sub CommandButton1_Click()
Dim decsep As String

decsep = Application.International(xlDecimalSeparator)

' #### OK Button ####
Sheets("Auxiliar").Range("H3:J3").ClearContents

If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> 0 Then

Sheets("Auxiliar").Range("H3").Value = Replace(TextBox1.Value, decsep, ".")
Sheets("Auxiliar").Range("I3").Value = Replace(TextBox2.Value, decsep, ".")
Sheets("Auxiliar").Range("J3").Value = Replace(TextBox3.Value, decsep, ".")

' sum values from textbox1 and textbox2 and divide with textbox3 value. Display result in sheet "auxiliar" range "d3" as a percentage

Sheets("Auxiliar").Range("D3").Value = (CDbl(Replace(TextBox1.Value, decsep, ".")) _
+ CDbl(Replace(TextBox2.Value, decsep, "."))) _
/ CDbl(Replace(TextBox3.Value, decsep, "."))


Call P2_NORM ' Module2


TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""

End If

Select Case Sheets("Auxiliar").Range("E3").Value
Case Is < 0
[F3] = "E"
Case Is < 0.1
[F3] = "D"
Case Is <= 0.4
[F3] = "C"
Case Is <= 0.7
[F3] = "B"
Case Is < 1
[F3] = "A"
Case Else
[F3] = "A+"
End Select

UserForm2.Hide
End Sub[/VBA]

[jmaocubo]

Once again thank you for your help.

The problem persists!!

The code always consider values without decimal.

For example:

Textbox1 = 2.5
Textbox2 = 1.45
TextBox3 = 15

Correct result would be: 0.263

The calculation is done is (and wrong): 11.33(3)

What he is doing is:

Textbox1 = 25
Textbox2 = 145
TextBox3 = 15

The same problem if you use "," as decimal separator.

[Bob Phillips]

I am struggling to see how you come up with your result. There is no combination of those three numbers that provides the result that you show. Regardless, if you are getting 11.333, your calculation is wrong as I said earlier, you are not dividing by the correct element. If you named your textboxes, you might be able to see the flaw in your calculation.

[Kenneth Hobs]

Do you know how to view and change the name of a Textbox control? In the VBE with your Userform1 active, select View > Properties Window or F4. I showed you how your display of textbox controls differ from the order that you show.

[jmaocubo]

thanks to everyone.

I resolved like this:

[vba]Private Sub CommandButton1_Click()
'By FreiNando
[D3:J3].ClearContents

If Not IsNumeric(PAUL) Then
MsgBox "Introduza um numero"
PAUL.SetFocus
ElseIf Not IsNumeric(IUL) Then
MsgBox "Introduza um numero"
IUL.SetFocus
ElseIf Not IsNumeric(IULmax) Then
MsgBox "Introduza um numero"
IULmax.SetFocus
Else
Dim P As Double, I As Double, M As Double
P = PAUL: PAUL = ""
I = IUL: IUL = ""
M = IULmax: IULmax = ""
[J3] = P
[H3] = I
[I3] = M
[D3] = (I + M) / P

Select Case Sheets("Auxiliar").Range("E3").Value
Case Is < 0
[F3] = "E"
Case Is < 0.1
[F3] = "D"
Case Is <= 0.4
[F3] = "C"
Case Is <= 0.7
[F3] = "B"
Case Is < 1
[F3] = "A"
Case Else
[F3] = "A+"
End Select

UserForm2.Hide
End If
End Sub[/vba]