Microsoft Office Forums Custom SUM functions are ignoring or rounding up decimals
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-14-2022, 07:25 AM
 Matt C Windows 10 Office 97-2003 Advanced Beginner Join Date: May 2021 Location: London, UK Posts: 30
Custom SUM functions are ignoring or rounding up decimals

Hi folks

I have a handful of customised "SUM" functions in my spreadsheet, e.g. SUMSUB, SUMCAT, which calculate the values of cells with specific style names in a column.

Today I noticed that these functions are either ignoring the decimal places or rounding them up to the nearest full number.

Example: 0.50 and below is treated as "0", whereas 0.51 and above is treated as "1".

(Any formulas which use the default "SUM" are working correctly.)

Quote:
 Function SumCat(WorkRng As Range) ' ' Calculates cells formatted as "Total Category" ' Example: =SUMCAT(A1:C9) ' Application.Volatile Dim rng As Range Dim xSum As Long For Each rng In WorkRng ' Finds cells with "Total Category" stylename If rng.Style = "Total Category" Then xSum = xSum + rng.Value End If Next SumCat = xSum End Function
Can anyone help? Number formatting is set to two decimal places and I would like my customised Functions to calculate them accurately.
#2
05-15-2022, 12:06 PM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 345

I think the Long Data type is for whole numbers only, try variant or double
#3
05-15-2022, 01:19 PM
 Matt C Windows 10 Office 97-2003 Advanced Beginner Join Date: May 2021 Location: London, UK Posts: 30

Quote:
 Originally Posted by Purfleet I think the Long Data type is for whole numbers only, try variant or double
Thanks, Purfleet. Would you mind posting an example of the code line I need to add/amend? I'm still feeling my way through this area of VBA programming.
#4
05-15-2022, 01:30 PM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 345

This row

Dim xSum As Long

Change to variant
#5
05-16-2022, 10:30 AM
 Matt C Windows 10 Office 97-2003 Advanced Beginner Join Date: May 2021 Location: London, UK Posts: 30

Quote:
 Originally Posted by Purfleet This row Dim xSum As Long Change to variant
That works. Many thanks.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post NickFazer Excel 7 11-09-2018 03:26 PM paulkaye Excel 8 09-03-2016 11:12 PM gamouning Excel 1 11-12-2015 11:03 AM venkys4u Excel 1 08-14-2012 07:45 PM Welshie82 Mail Merge 2 05-04-2012 01:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:37 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top