M
Mats Samson
Hello,
I’ve run into a strange problem in connection with using decimal values.
I asked about it before but nobody replied to it but it really bothers me as
I cannot find a solution. Or maybe I have found the fault and in
contradiction to what have been written previously I dare to say that
WindowsXP or Excel2003 has a problem with the localisation!
I have written a registration program for purchase, orders and invoices.
The registration is done in a Form that is connected to a workbook.
I have different functions to save and retrieve previously registered
documents
and the heart of the processing is a 2 column, 30 row part of a spreadsheet
where all different values are registered. These Cells are connected to the
Form via the .Controlsource property of Textboxes, ComboBoxes, etc.
None of the controls are linked via variables, the connection is quite
straight, nor does the Form code much of calculation, this is mostly done by
the spreadsheet and any resulting values are presented in Labels in the form.
So that is basically what is done, I put in the parameters like quantity,
price and currency, the spreadsheet calculates and the result is returned and
presented.
My problem concern one of the cells and it’s connected Textbox, the Quantity!
Rightfully, it’s four cells, because I have quadrupled the rows and then I’m
able to register 4 lines in the purchases/order etc.
How stupid it ever sounds:
If I put a QUANTITY IN THE SPREADSHEET BEFORE STARTING THE FORM (f.i. when
reading in an old document), with decimal value other than 0, e.g. 4,1 AND
THEN LOAD THE FORM, Excel runs up over 50% CPU usage and mostly it hangs!
If the value is 4,0 it starts/runs fine! If I have a purchase-price with
decimals, there is no problem either, 286,70 works fine! If I register a
decimal-value IN the form it works as well. The problem occurs if I read back
a document or start the form with a decimal value above 0. There is no
difference between these two textboxes, they are set up in exactly the same
manner and they are even adjacent to each other within the same frame in the
form:
In the Userform_Initialize () part
Worksheets("System").Activate
TxB404.ControlSource = "QY1"
PuTxB405.ControlSource = "PP1"
and so on…….
Later in the code where the responses to the input take place I have only a
simple code part for these two:
Public Sub TxB404_AfterUpdate()
OSumUpdate
End Sub
Public Sub PuTxB405_AfterUpdate()
OSumUpdate
End Sub
The call to OSumUpdate is used to that every change of a value will cause a
recalculation of the underlying spreadsheet and I want the change to be
reflected in the result -“labels†in the form like:
PuLa408.Caption = Format(Range("PVAL1"), "##,##0.00")
This is the resulting purchase value from my quantity and price above.
I cannot solve it. But finally I changed the localisation to English (UK)
where decimals are . (periods) not , (commas) as we do in Sweden. (I have
English versions of both Windows XP Professional SP2,
and Office 2003 Professional, SP2)
THE PROBLEM DISAPPEARED!
I can use whatever numbers with decimals, I like and the code works
excellently!
What is wrong, my programming or my programs!
I’ve posted this question to both the Excel Programming and Suggestion for
Microsoft.
I hope someone can solve it!
Best regards
Mats
I’ve run into a strange problem in connection with using decimal values.
I asked about it before but nobody replied to it but it really bothers me as
I cannot find a solution. Or maybe I have found the fault and in
contradiction to what have been written previously I dare to say that
WindowsXP or Excel2003 has a problem with the localisation!
I have written a registration program for purchase, orders and invoices.
The registration is done in a Form that is connected to a workbook.
I have different functions to save and retrieve previously registered
documents
and the heart of the processing is a 2 column, 30 row part of a spreadsheet
where all different values are registered. These Cells are connected to the
Form via the .Controlsource property of Textboxes, ComboBoxes, etc.
None of the controls are linked via variables, the connection is quite
straight, nor does the Form code much of calculation, this is mostly done by
the spreadsheet and any resulting values are presented in Labels in the form.
So that is basically what is done, I put in the parameters like quantity,
price and currency, the spreadsheet calculates and the result is returned and
presented.
My problem concern one of the cells and it’s connected Textbox, the Quantity!
Rightfully, it’s four cells, because I have quadrupled the rows and then I’m
able to register 4 lines in the purchases/order etc.
How stupid it ever sounds:
If I put a QUANTITY IN THE SPREADSHEET BEFORE STARTING THE FORM (f.i. when
reading in an old document), with decimal value other than 0, e.g. 4,1 AND
THEN LOAD THE FORM, Excel runs up over 50% CPU usage and mostly it hangs!
If the value is 4,0 it starts/runs fine! If I have a purchase-price with
decimals, there is no problem either, 286,70 works fine! If I register a
decimal-value IN the form it works as well. The problem occurs if I read back
a document or start the form with a decimal value above 0. There is no
difference between these two textboxes, they are set up in exactly the same
manner and they are even adjacent to each other within the same frame in the
form:
In the Userform_Initialize () part
Worksheets("System").Activate
TxB404.ControlSource = "QY1"
PuTxB405.ControlSource = "PP1"
and so on…….
Later in the code where the responses to the input take place I have only a
simple code part for these two:
Public Sub TxB404_AfterUpdate()
OSumUpdate
End Sub
Public Sub PuTxB405_AfterUpdate()
OSumUpdate
End Sub
The call to OSumUpdate is used to that every change of a value will cause a
recalculation of the underlying spreadsheet and I want the change to be
reflected in the result -“labels†in the form like:
PuLa408.Caption = Format(Range("PVAL1"), "##,##0.00")
This is the resulting purchase value from my quantity and price above.
I cannot solve it. But finally I changed the localisation to English (UK)
where decimals are . (periods) not , (commas) as we do in Sweden. (I have
English versions of both Windows XP Professional SP2,
and Office 2003 Professional, SP2)
THE PROBLEM DISAPPEARED!
I can use whatever numbers with decimals, I like and the code works
excellently!
What is wrong, my programming or my programs!
I’ve posted this question to both the Excel Programming and Suggestion for
Microsoft.
I hope someone can solve it!
Best regards
Mats