I have an Excel sheet in with 2 columns: Column A holds a date in the European format "dd/mm/yyyy" and
column B holds a number in the European format "#.###.##0,00".
This data is used to complete a listbox in a
userform so the user can choose with which date and number to continue some calculations.
Now, when completing the
as follows, the values are casted to strings with the wrong value:
UCList.List(UCList.ListCount - 1, 0) = MyCell.Value
UCList.List(UCList.ListCount - 1, 1) = MyCell.Offset(0, 1).Value
If I check in the "Wachtes" window, mycell.value = 65,864356134631 (sixty five and then some decimals) and
mycell.offset(0,1).value = 03/02/2008 (February 3rd). After assigning the values to the list, these value become
65864356134631 and "02/03/2008". If I later on call this date form the UCList, the date becomes the 2nd of March and the
number a huge number (=+/- 6*10^14 ).
The problem probably is most likely caused by the cast to string when
completing the list and the cast to double or date when reading the list values.
Is there a way I can tell VBA
that I live in Europe and that we use a different date system? Just like you can set it in Excel in
I can solve this by using
UCList.List(UCList.ListCount - 1, 1) = format(MyCell.Value,""#,###,##0.00")
UCList.List(UCList.ListCount - 1, 1) = format(MyCell.Offset(0, 1).Value,"dd/mm/yyyy")
but this is just one of those patches which does not really solve the root problem.
In addition, this continuously
switching from US to European date and number format, makes my code quite confusing.
Any help on this will be