Excluding formatted numbers from SUM function?

[Jason O]

Hi,

I've been trying to figure out how to do something. I keep a very simple
spreadsheet of my installed downloads, with a column detailing the memory
used by each download, and a SUM function totalling these data. When I
delete the intsalled app I simply change the font to red & strikethrough.
How can I change the SUM function to exclude any cells where the number is
formatted in red & strikethrough?

Ahy help greatly appreciated,


Cheers,

Jason
___
*Replace .invalid with .com for replies*

 

[Ken Johnson]

Hi Jason,
I did a Google search using Summing Coloured Cells. There are plenty of
suggestions supplied. I'm pretty sure you can't use any of the inbuilt
functions, font formats are not detectable by any of them (except for
the use of red font indicating negative values).
Tom Ogilvy suggests a User Defined Function which you could paste into
a Module in the workbook's VBA Editor or into your PERSONAL.XLS Macro
Workbook. You can then access it as you would any worksheet function.
I've changed Tom's code so that it only sums cells with the automatic
font colour (ColorIndex value = -4105)

Function SumAutoColor(rng As Range)
Dim total As Double
Dim cel As Range
total = 0
For Each cel In rng
If IsNumeric(cel) Then
If cel.Font.ColorIndex = -4105 Then
total = total + cel.Value
End If
End If
Next
SumAutoColor = total
End Function

Ken Johnson

 

[Jason O]

Hi Jason,
I did a Google search using Summing Coloured Cells. There are plenty of
suggestions supplied. I'm pretty sure you can't use any of the inbuilt
functions, font formats are not detectable by any of them (except for
the use of red font indicating negative values).
Tom Ogilvy suggests a User Defined Function which you could paste into
a Module in the workbook's VBA Editor or into your PERSONAL.XLS Macro
Workbook. You can then access it as you would any worksheet function.
I've changed Tom's code so that it only sums cells with the automatic
font colour (ColorIndex value = -4105)

Function SumAutoColor(rng As Range)
Dim total As Double
Dim cel As Range
total = 0
For Each cel In rng
If IsNumeric(cel) Then
If cel.Font.ColorIndex = -4105 Then
total = total + cel.Value
End If
End If
Next
SumAutoColor = total
End Function

Ken Johnson

Fantastic Ken - I REALLY appreciate it. I've inserted a new module into my
personal macro workbook & pasted the above code in and voila! It works
perfectly!

And besides that, it's given me an insight into user-defined
functions..hadn't really considered the possibility before.

Most appreciated. (Your reply has made it into my 'Reference' folder)

Regards,

Jason
___

 

[Ken Johnson]

Hi Jason,
That's great, thanks for the feedback.
Ken Johnson

 

[Jason O]

Hi Jason,
I did a Google search using Summing Coloured Cells. There are plenty of
suggestions supplied. I'm pretty sure you can't use any of the inbuilt
functions, font formats are not detectable by any of them (except for
the use of red font indicating negative values).
Tom Ogilvy suggests a User Defined Function which you could paste into
a Module in the workbook's VBA Editor or into your PERSONAL.XLS Macro
Workbook. You can then access it as you would any worksheet function.
I've changed Tom's code so that it only sums cells with the automatic
font colour (ColorIndex value = -4105)

Function SumAutoColor(rng As Range)
Dim total As Double
Dim cel As Range
total = 0
For Each cel In rng
If IsNumeric(cel) Then
If cel.Font.ColorIndex = -4105 Then
total = total + cel.Value
End If
End If
Next
SumAutoColor = total
End Function

Ken Johnson

Hi Ken,

Just a quick follow-up question. Do user-defined functions behave
differently to normal functions? In the example above, the SumAutoColor
doesn't re-calculate instantly if cell content colors are changed.

With a normal SUM, if say, a cell's numerical content is changed, the SUM
result is obviously changed instantly. However, SumAutoColor only
recalculates (if one of the range cells' colors is changed) when the
workbook is saved, closed & re-opened?

Is there a reason for this & can it be circumvented?

TIA,

Jason
__

 

[JE McGimpsey]

Just a quick follow-up question. Do user-defined functions behave
differently to normal functions? In the example above, the SumAutoColor
doesn't re-calculate instantly if cell content colors are changed.

With a normal SUM, if say, a cell's numerical content is changed, the SUM
result is obviously changed instantly. However, SumAutoColor only
recalculates (if one of the range cells' colors is changed) when the
workbook is saved, closed & re-opened?

Is there a reason for this & can it be circumvented?

It's not a function of the UDF, per se.

The reason is that changing format doesn't fire a recalculation, whereas
changing a value used as an argument in a function (whether UDF or
built-in) does.

You can force recalculation by typing SHIFT-F9 or CMD-SHIFT-=

There's nothing much you can do to circumvent this automatically. About
the only way I know is to use an OnTime macro to recalculate the
worksheet every second or two.

 

[Ken Johnson]

Hi Jason,
I assume you've read JE's reply.
I've never used OnTime so I just had to give it a try. After a bit of
fiddling around I came up with the following solution:

Private Sub Workbook_Open()
RptCalc
End Sub

When the workbook is first opened the above Workbook_Open Sub in the
ThisWorkbook Code module starts the next macro, which resides in a
standard module...

Public Sub RptCalc()
Application.OnTime Now + TimeValue("00:00:5"), "CalcNow"
End Sub

which, after a 5 second delay, runs the next macro residing in the same
module...

Public Sub CalcNow()
Sheets(1).Calculate
RptCalc
End Sub

which calculates the first sheet in the workbook then re-runs the
RptCalc macro. This creates repeated calculation of sheet1 every 5
seconds.

One other small change is make the original UDF, SumAutoColor, volatile
by adding the following line (first line)....

Application.Volatile

I don't know if this is the best way to achieve repeated calculation.
If you (or anybody else reading this post) know of a better way don't
hesitate to let me know.

Ken Johnson

 

[Jason O]

It's not a function of the UDF, per se.

The reason is that changing format doesn't fire a recalculation, whereas
changing a value used as an argument in a function (whether UDF or
built-in) does.

You can force recalculation by typing SHIFT-F9 or CMD-SHIFT-=

There's nothing much you can do to circumvent this automatically. About
the only way I know is to use an OnTime macro to recalculate the
worksheet every second or two.

Thanks for the info, but SHIFT-F9 just performs a slow expose?, and
CMD-SHIFT-= doesn't seem to do anything? Am I doing something wrong? The UDF
remains un-recalculated. If I select the cell & <return> at the end of the
function line it recalculates? But other than that & saving,closing,opening
I can't seem to get it to re-calculate?


Cheers,

Jason
___
*Replace .invalid with .com for replies*

 

[JE McGimpsey]

Thanks for the info, but SHIFT-F9 just performs a slow expose?

It took me a minute to figure out what you meant by a "slow expose". I
got fed up with Expose after about the first day, and I've disabled all
keyboard and hot corner shortcuts.

and CMD-SHIFT-= doesn't seem to do anything? Am I doing something
wrong? The UDF remains un-recalculated. If I select the cell &
<return> at the end of the function line it recalculates? But other
than that & saving,closing,opening I can't seem to get it to
re-calculate?

No, you didn't do anything wrong - the keyboard shortcuts won't do
anything if there's nothing to indicate that there's a need to calculate
on the sheet.

To force a function to calculate, even if no other calcs are done on the
sheet, add this line as the first line of the function:

Application.Volatile

Any of the recalc functions will then work - F9, CTRL-=, etc.

 

[JE McGimpsey]

I've never used OnTime so I just had to give it a try. After a bit of
fiddling around I came up with the following solution:

This will work, though the two functions could be combined into one:

Public Sub RptCalc()
Const dFIVE_SECS As Double = 0.0000578703703703704
Sheets(1).Calculate
Application.OnTime Now + dFIVE_SECS, "RptCalc"
End Sub

There are a few problems with both simple approaches, though - for
instance, if you close the workbook containing the macro(s), XL will
reopen it when the 5 seconds is reached.

You can find information on more sophisticated methods here

http://cpearson.com/excel/ontime.htm

 

[Ken Johnson]

Thanks for that JE.
Ken Johnson

 

[Jason O]

It took me a minute to figure out what you meant by a "slow expose". I
got fed up with Expose after about the first day, and I've disabled all
keyboard and hot corner shortcuts.


No, you didn't do anything wrong - the keyboard shortcuts won't do
anything if there's nothing to indicate that there's a need to calculate
on the sheet.

To force a function to calculate, even if no other calcs are done on the
sheet, add this line as the first line of the function:

Application.Volatile

Any of the recalc functions will then work - F9, CTRL-=, etc.

Sorry to be a pain, but I *still* can't get it to work?! I've added the
application.volatile as the 1st line of the function, and even disabled the
hot corners in case the F9 funcionality was affected. And still nothing? The
recalculations remain not done & the shift-F9 and COMM-SHIFT-= still do
nothing, but I have been getting a "Compile Error: Invalid outside
procedure" message & #NAME? Error in the cell with the udf? When I remove
the Application.volatile these error msgs no longer appear.

Forgive my stumblings.

Jason
:-/

 

[Ken Johnson]

Hi Jason,
I recognise that error. Looks like the Application.Volatile is not
inside the Function. You shouldn't get that error if the code looks
like..

Function SumAutoColor(rng As Range)
Application.Volatile
Dim total As Double
Dim cel As Range
total = 0
For Each cel In rng
If IsNumeric(cel) Then
If cel.Font.ColorIndex = -4105 Then
total = total + cel.Value
End If
End If
Next
SumAutoColor = total
End Function

Try that

Ken Johnson

 

[Jason O]

Hi Jason,
I recognise that error. Looks like the Application.Volatile is not
inside the Function. You shouldn't get that error if the code looks
like..

Function SumAutoColor(rng As Range)
Application.Volatile
Dim total As Double
Dim cel As Range
total = 0
For Each cel In rng
If IsNumeric(cel) Then
If cel.Font.ColorIndex = -4105 Then
total = total + cel.Value
End If
End If
Next
SumAutoColor = total
End Function

Try that

Ken Johnson

That's done it!....I had the application.volatile line at the *very* top...
I read JE's post re putting it as the first line of the function & didn't
realise that the Function SumAutoColor line was actually just defining the
function, so the *next* line would actually be the first line.....Told you I
was a Visual Basic virgin ;-) Oh well, I'm learning more every day.

Thanks again (you too JE!)

Regards

Jason
___

 

[Ken Johnson]

Hi Jason,
Just curious, are you achieving recalculation via the keyboard or
automatically with the OnTime code?
I thought I had the OnTime code working OK then JE informed me that
while Excel is running it will cause the workbook to automatically
re-open 5 seconds after being closed. JE kindly supplied a link where I
could find a solution. When, and if, I solve it I'll reply back here.

Ken Johnson

 

[Bob Greenblatt]

Hi,

I've been trying to figure out how to do something. I keep a very simple
spreadsheet of my installed downloads, with a column detailing the memory
used by each download, and a SUM function totalling these data. When I
delete the intsalled app I simply change the font to red & strikethrough.
How can I change the SUM function to exclude any cells where the number is
formatted in red & strikethrough?

Ahy help greatly appreciated,


Cheers,

Jason
___
*Replace .invalid with .com for replies*

While you are changing the font to red, simply precede the value with an
apostrophe.

 

[Jason O]

Hi Jason,
Just curious, are you achieving recalculation via the keyboard or
automatically with the OnTime code?
I thought I had the OnTime code working OK then JE informed me that
while Excel is running it will cause the workbook to automatically
re-open 5 seconds after being closed. JE kindly supplied a link where I
could find a solution. When, and if, I solve it I'll reply back here.

Ken Johnson

Hi Ken,

At the moment I'm recalculating via the keyboard, as I haven't had time to
play around with OnTime yet. I would do tonight but I've got a job
interview tomorrow so intend doing some preparation. [ he says, while
distracting himself with other stuff ]

When, and if, I solve it I'll reply back here.

I'd be interested in what you find out, so if you could post any progress
that'd be great.

Cheers,

Jason
___
*Replace .invalid with .com for replies*

 

[Ken Johnson]

Hi Bob,
Funny how the simplest solutions take time to filter through.
Oh well, at least I learnt some new VBA.
Thanks Bob
Ken Johnson