Ho to return a decimal value in a VBA code

Question

Hi 

I am new to VBA's and created a VBA to calculate the highlighted cells in each specific column (see picture below).

The VBA worked but instead of returning the exact number with 2 decimal places, it returned a whole number.

see cell W47 and cell W48  both show 2 decimal places but are rounded up as whole numbers.

Cell W45 = $400,777.03 but returns $400,777.00

I want to display the exact number with 2 decimal places as currency, not a rounded up number.

I read multiple forums and they spoke about using a double instead of an integer in the VBA, which I did, but the result is still rounding to a whole number.

Being a beginner in VBA, I read forums with VBA codes to convert the rounded number to a number with 2 decimal places, but wasn't sure if I need to add this code to the existing VBA module or create a new module. 

I appreciate you help in advanced.

Below is the code I wrote in VBA

Function SumColor(Color As Range, Range As Range) As Long

Dim Cell As Range

Dim ColorIndexNumber As Integer

Dim ColorSum

'Get The Color Index

ColorIndexNumber = Color.Interior.ColorIndex

'For each cell we highlighted

For Each Cell In Range

    If Cell.Interior.ColorIndex = ColorIndexNumber Then

        ColorSum = WorksheetFunction.Sum(Cell.Value) + ColorSum

    End If

Next Cell

    SumColor = ColorSum

End Function

I have added the workbook to onedrive but noticed, that when I open the onedrive folder it returns an error NAME? and not the actual result. Not sure if this a onedrive problem, but the file was saved as a macro enabled worksheet. I am only starting on macros and would appreciate any help you can offer as forums and videos are not specific when trying to solve issues.

See link below

http://1drv.ms/1MHSYHA

Regards

Frank

Answer 1

Hi,

You must set the function to return a DOUBLE data type like this, the only change is in bold.

Function SumColor(Color As Range, Range As Range) As Double

 Dim Cell As Range

 Dim ColorIndexNumber As Integer

 Dim ColorSum

 'Get The Color Index

 ColorIndexNumber = Color.Interior.ColorIndex

 'For each cell we highlighted

 For Each Cell In Range

     If Cell.Interior.ColorIndex = ColorIndexNumber Then

         ColorSum = WorksheetFunction.Sum(Cell.Value) + ColorSum

     End If

 Next Cell

     SumColor = ColorSum

 End Function

Answer 2

Hi Mike 

That worked great but it rounded the number up from $33,098.013 to $33,098.02.

Should it not have rounded down to $33,098.01 not up.

Other than that your your answer was great

regards

Frank

Answer 3

Hi,

If the cell with the function call in is formatted as general then it shouldn't have done either of those things, it should have returned the number of decimal places with no rounding. If formatted as number with 2 decimal places then it should have rounded down to .01

I'm at a loss to understand why it did that and would need to see your data to explain it. Can you upload a copy of the workbook to onedrive and post the link?

Answer 4

Hi Mike

The formats are in currency not general. There is a link to the spreadsheet through one drive but I'm not sure if it opens with macros. It was saved as a macro enabled workbook but when you open on one drive it shows NAME? error instead of the correct answer.

On my PC it opens correctly so I don't know if One drive has a different format setup when you create a link. 

Please view link below

http://1drv.ms/1MHSYHA

Regards

Frank