Str to Currency round problem
 

Hi,
I have a problem with currency rounding (Excel automatically rounds
currency to 2 decimals).
I have an InputBox in WorkBook_Open event where user enters the currency
....

The procedure goes something like this:

currencystr = InputBox("Please enter currency: ", "Currency ¤")
If currencystr = "" Then
Msgresult = MsgBox("No currency entered ... bla bla, enter ... bla ...",
vbOKOnly,"Error")
Exit sub
Else
currency = currencystr
Sheets(1).Cells(1,1).NumberFormat = "#,##0.0000 $"
Sheets(1).Cells(1,1).Value = Currency
end if

Well, the problem is that the value in the Cells(1,1) is always rounded to
2 decimals and I need 4 !
If I enter 7,4791 Excel rounds it up to 7,5000, and if I enter 7,2319 Excel
shows 7,2300 !

I googled but couldn't find the solution ... can anyone help please ?

Thanks in advance !

--
Pozdrav
Revenger
29.01.2007 09:07:57
Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da
itko sere po njemu.

Str to Currency round problem
 

Try using double instread of the currency data type.

Also currency = currencystr should produce an error because currency is a
reserved word.

You may also want to check for numerical values on your input.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Revenger" wrote:

Hi,
I have a problem with currency rounding (Excel automatically rounds
currency to 2 decimals).
I have an InputBox in WorkBook_Open event where user enters the currency
....

The procedure goes something like this:

currencystr = InputBox("Please enter currency: ", "Currency ‚¬")
If currencystr = "" Then
Msgresult = MsgBox("No currency entered ... bla bla, enter ... bla ...",
vbOKOnly,"Error")
Exit sub
Else
currency = currencystr
Sheets(1).Cells(1,1).NumberFormat = "#,##0.0000 $"
Sheets(1).Cells(1,1).Value = Currency
end if

Well, the problem is that the value in the Cells(1,1) is always rounded to
2 decimals and I need 4 !
If I enter 7,4791 Excel rounds it up to 7,5000, and if I enter 7,2319 Excel
shows 7,2300 !

I googled but couldn't find the solution ... can anyone help please ?

Thanks in advance !

--
Pozdrav
Revenger
29.01.2007 09:07:57
Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da
itko sere po njemu.

Str to Currency round problem
 

On Mon, 29 Jan 2007 03:09:01 -0800, Martin Fishlock wrote:

Try using double instread of the currency data type.

Also currency = currencystr should produce an error because currency is a
reserved word.

You may also want to check for numerical values on your input.

I've resolved the issue ...
I didn't use currency as variable name, my code is not in "english" so I
quickly typed "translated" version of my code ...

The problem was with decimal simbol, since my decimal simbol is "," and
digit grouping symbol is "." but VBA wont work like that.
So i had to replace the "." and "," symbols in currencystr and then write
the value to the cell.

Thanks for the reply !

--
Pozdrav
Revenger
29.01.2007 15:27:43
Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da
itko sere po njemu.

Str to Currency round problem
 

A numberformat of currency can be troublesome.

This worked for me with my USA settings:

Option Explicit
Sub testme()

Dim CurrencyStr As String
Dim myCurrency As Currency

CurrencyStr = InputBox("Please enter currency: ", "Currency ¤")
If CurrencyStr = "" Then
MsgBox "No currency entered ... bla bla, enter ... bla ...", _
vbOKOnly, "Error"
Exit Sub
Else
If IsNumeric(CurrencyStr) Then
myCurrency = CurrencyStr
Sheets(1).Cells(1, 1).NumberFormat = "#,##0.0000 $"
Sheets(1).Cells(1, 1).Value2 = myCurrency
Else
Beep
End If
End If
End Sub

Just in case you find that you still had trouble. (Note the .value2 property.)

Revenger wrote:

On Mon, 29 Jan 2007 03:09:01 -0800, Martin Fishlock wrote:

Try using double instread of the currency data type.

Also currency = currencystr should produce an error because currency is a
reserved word.

You may also want to check for numerical values on your input.

I've resolved the issue ...
I didn't use currency as variable name, my code is not in "english" so I
quickly typed "translated" version of my code ...

The problem was with decimal simbol, since my decimal simbol is "," and
digit grouping symbol is "." but VBA wont work like that.
So i had to replace the "." and "," symbols in currencystr and then write
the value to the cell.

Thanks for the reply !

--
Pozdrav
Revenger
29.01.2007 15:27:43
Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da
itko sere po njemu.

--

Dave Peterson

Str to Currency round problem
 

On Mon, 29 Jan 2007 08:46:17 -0600, Dave Peterson wrote:

Sheets(1).Cells(1, 1).Value2 = myCurrency

Just in case you find that you still had trouble. (Note the .value2 property.)


Thanks for the reply,
I've got it working now,
but this is a good tip (.value2) ...

Thanks for the tip ... didn't know that ...

--
Pozdrav
Revenger
30.01.2007 10:27:25
Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da
itko sere po njemu.