Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why is excel rounding my numbers in VBA?
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 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why is excel rounding my numbers in VBA?
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 ^^^^^^^^^^ 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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why is excel rounding my numbers in VBA?
"nappyjim" wrote:
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 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop excel from rounding my numbers up? | Excel Discussion (Misc queries) | |||
How do I stop excel from rounding my numbers up? | Excel Discussion (Misc queries) | |||
Problem with Excel rounding numbers | Excel Discussion (Misc queries) | |||
How do I stop excel from rounding numbers? | Excel Discussion (Misc queries) | |||
rounding off numbers with excel | Excel Programming |