Why is excel rounding my numbers in VBA?

N

nappyjim

Im very new to VBA and wouldent even say I know the basics, I prett
much took something that worked and tried to modify it and it doesn
work quite right.

I have a VBA sheet, code seen below. When I input 3 into a cell,
expect the cell next to it to show 36.88, as the VBA says. But instead
it rounds its and show 38. I have the cell setup to show 2 decima
places.


Function StateAllowance(pVal As String) As Long
Select Case pVal
Case "0"
StateAllowance = 50
Case "1"
StateAllowance = 45.96
Case "2"
StateAllowance = 41.92
Case "3"
StateAllowance = 36.88
Case "4"
StateAllowance = 33.85
Case "5"
StateAllowance = 29.81
Case "6"
StateAllowance = 25.77
Case "7"
StateAllowance = 21.73
Case "8"
StateAllowance = 17.69
Case "9"
StateAllowance = 13.65
Case "10"
StateAllowance = 9.62
Case Else
StateAllowance = 50
End Select
End Functio
 
C

Claus Busch

Hi,

Am Sun, 10 Feb 2013 18:26:55 +0000 schrieb nappyjim:
Im very new to VBA and wouldent even say I know the basics, I pretty
much took something that worked and tried to modify it and it doesnt
work quite right.

I have a VBA sheet, code seen below. When I input 3 into a cell, I
expect the cell next to it to show 36.88, as the VBA says. But instead,
it rounds its and show 38. I have the cell setup to show 2 decimal
places.

Function StateAllowance(pVal As String) As Long
Click to expand...
^^^^^^^^^^
try:

Function StateAllowance(pVal As Integer) As Double
Select Case pVal
Case 0
StateAllowance = 50
Case 1
StateAllowance = 45.96
Case 2
StateAllowance = 41.92
Case 3
StateAllowance = 36.88
Case 4
StateAllowance = 33.85
Case 5
StateAllowance = 29.81
Case 6
StateAllowance = 25.77
Case 7
StateAllowance = 21.73
Case 8
StateAllowance = 17.69
Case 9
StateAllowance = 13.65
Case 10
StateAllowance = 9.62
Case Else
StateAllowance = 50
End Select
End Function


Regards
Claus Busch
 
J

joeu2004

nappyjim said:
I have a VBA sheet, code seen below. When I input 3 into
a cell, I expect the cell next to it to show 36.88, as the
VBA says. But instead, it rounds its and show 38. [....]
Function StateAllowance(pVal As String) As Long
Select Case pVal [....]
Case "3"
StateAllowance = 36.88
Click to expand...

In case Claus's solution does not work for you __exactly__ as he wrote (he
made an unrelated and unnecessary change which is presumptuous), the
__minimum__ correction is:

Function StateAllowance(pVal As String) As Double

To answer your question: VBA is rounding 36.88 because you told it to(!).

Type Long is an integer. And VBA rounds non-integers when assigning to an
integer.

If you want VBA to return non-integers, you need to declare StateAllowance
with a non-integer type.

Type Double is a good choice, since that is the type that Excel uses for all
numbers (including integers).
 
Top