ExcelBanter - Function debuging help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Function debuging help (https://www.excelbanter.com/excel-programming/301920-function-debuging-help.html)

Function debuging help

In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????

Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

Function debuging help

JMay,

Looks to me like the first argument in the function should be changed...
from: CellColor As Range
to: CellColor As Integer
Also change...
from: iColor = CellColor.Interior.ColorIndex
to: iColor = CellColor

The other solution would be to simply specify a range as the first
argument when calling the function.

Regards,
Jim Cone
San Francisco, CA

"JMay" wrote in message news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????
Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

Function debuging help

JMay,

You define the first of the function arguments as a range, but pass a number
to the function. You can either

pass the range with the colour
=SumByColor(A1,B4:B15)

maintain the call as is and change the code to handle an index

Function SumByColor(CellColor As Long, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
iColor = CellColor
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

or allow either a range or an index

Function SumByColor(CellColor, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal

If TypeName(CellColor) = "Range" Then
iColor = CellColor.Interior.ColorIndex
Else
iColor = CellColor
End If
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

By the way, as you are iterating through each cell, you don't need to sum
that range, that is

myTotal = WorksheetFunction.Sum(mytestcell) + myTotal

can be written more simply as

myTotal = mytestcell.Value + myTotal

Ande finally, as we see you so often, any chance of a more persoanl handle
that we can address you by?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????

Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

Function debuging help

Bob:

Thanks for your usual kind assistance; What I posted (the function) came
straight from my most recent book purchase - the title and author I will
leave unnamed (to protect unnecessary flaming,,,) another page which
bypassed the edit room (LOL)...

I changed my user name per your request (I'm Jim May - Virginia USA).

Again thanks,
Jim May

"Bob Phillips" wrote in message
...
JMay,

You define the first of the function arguments as a range, but pass a
number
to the function. You can either

pass the range with the colour
=SumByColor(A1,B4:B15)

maintain the call as is and change the code to handle an index

Function SumByColor(CellColor As Long, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
iColor = CellColor
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

or allow either a range or an index

Function SumByColor(CellColor, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal

If TypeName(CellColor) = "Range" Then
iColor = CellColor.Interior.ColorIndex
Else
iColor = CellColor
End If
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

By the way, as you are iterating through each cell, you don't need to sum
that range, that is

myTotal = WorksheetFunction.Sum(mytestcell) + myTotal

can be written more simply as

myTotal = mytestcell.Value + myTotal

Ande finally, as we see you so often, any chance of a more persoanl handle
that we can address you by?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????

Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

Function debuging help

Thanks for pointing that out. This function came straight from a newly
acquired Excel VBA book (I'm reading through).....

"Jim Cone" wrote in message
...
JMay,

Looks to me like the first argument in the function should be changed...
from: CellColor As Range
to: CellColor As Integer
Also change...
from: iColor = CellColor.Interior.ColorIndex
to: iColor = CellColor

The other solution would be to simply specify a range as the first
argument when calling the function.

Regards,
Jim Cone
San Francisco, CA

"JMay" wrote in message
news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????
Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

Function debuging help

Hi Jim from Va,

You're right, that's quite an editorial gaffe. That SUM call is so
unnecessary as well, so all-in, a bit slap-dash, I hope it's not one that I
recommend<g.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
news:Rr%Ac.3061$HN5.1905@lakeread06...
Bob:

Thanks for your usual kind assistance; What I posted (the function) came
straight from my most recent book purchase - the title and author I will
leave unnamed (to protect unnecessary flaming,,,) another page which
bypassed the edit room (LOL)...

I changed my user name per your request (I'm Jim May - Virginia USA).

Again thanks,
Jim May

"Bob Phillips" wrote in message
...
JMay,

You define the first of the function arguments as a range, but pass a
number
to the function. You can either

pass the range with the colour
=SumByColor(A1,B4:B15)

maintain the call as is and change the code to handle an index

Function SumByColor(CellColor As Long, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
iColor = CellColor
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

or allow either a range or an index

Function SumByColor(CellColor, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal

If TypeName(CellColor) = "Range" Then
iColor = CellColor.Interior.ColorIndex
Else
iColor = CellColor
End If
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

By the way, as you are iterating through each cell, you don't need to
sum
that range, that is

myTotal = WorksheetFunction.Sum(mytestcell) + myTotal

can be written more simply as

myTotal = mytestcell.Value + myTotal

Ande finally, as we see you so often, any chance of a more persoanl
handle
that we can address you by?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????

Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function