VBA calculation with a decimal problem

[andrewvanmarle]

Hi Everyone, I'm Andrew, and new here. (and I have a question of course)

I made a lovely feature for our invoices, a userform that calculated a fee by percentage

courtage is a field where apercentage is enetered (for instance 1.5) and koopsom is the field where a large amount (lets say 500,000) is entered

I run the following code :

Private Sub koopsom_Change()    If koopsom = vbNullString Then Exit Sub
     
    If Not IsNumeric(TextBox1) Then
        MsgBox "alleen getallen"
        koopsom = vbNullString
    End If
     koopsom.Value = Format(koopsom.Value, "#,###,##")
End Sub
Private Sub invoerButton1_Click()
bedrag = (courtage / 1000) * koopsom
ActiveSheet.Range("E20").Value = bedrag.Value
ActiveSheet.Range("D20").Value = "Courtage conform afspraak à " & courtage.Value & " %"
ActiveSheet.Range("B20").Value = 1


End Sub

here is the strange thing that happens if I enter a whole number in courtage then the calculatioon works, but if I enter for instance 1.2, then the result is ten times too large. If I enter 1.22 the result is 100 times too large etc.

It seems the code does something wierd with the decimal point when calculating. Can anyone see what is wrong?

 

[Jerry Sullivan]

Hi Andrew and Welcome to MrExcel,

This is probably caused by the fact that your koopsom_Change procedure is formatting the number entered by the user as "#,###,##" (a Dutch format?). Then that formatted Text is being used directly in calculation of bedrag.

Since Excel is performing a coerced type conversion from text to number, the result is not what you intended. The results will probably vary depending on your language and number separator settings.

If you need to display a formatted value in a UserForm TextBox, consider either:
1. Storing the unformatted number in a hidden control or Tag property and using that value for calculations.
2. Employing two helper functions to covert the value from numeric to formatted and formatted to numeric.

btw if you are working with percentages of 100, then I believe your formula should read...

bedrag = (courtage / 100) * koopsom

 

[andrewvanmarle]

Hi Jerry,
Thanks for the welcome

I thought of the formatting thing as well but.....

When I remove that sub (and save) it still does the same.

(I divided by a 1000 at some point to compensate, before I realised that when I add another decimal it goes times 10 again.)

I'm stumped....

 

[andrewvanmarle]

Ifigured out the problem, now to find a solution.

It isn't in the formatting of the value in koopsom, it's actually in the formatting of the value in courtage.

The value will usuall be something like 1,2 (dutch decimal marker is a comma) but when I use the numkeypad and hit the decimal there it won't show a comma it will show a fullstop. (1.2 instead of 1,2) which it will do when I do the same in excel itself.

For now I can use the form by hitting the comma key, but is there a way to have the textbox realise it is a numer and show a comma when using the decimal key?

 

[pgc01]

Hi

Could you not simply replace the dots by commas, like:

Private Sub TextBox1_Change()
TextBox1.Value = Replace(TextBox1.Value, ".", ",")
End Sub

 

[andrewvanmarle]

Edit, never mind, it seems to work, thanks!