Hello --

I need to create a custom function that I will use in a formula, and it
needs to take action based on the contents of another cell on another
worksheet in the same workbook. The idea is to get a different from the
formula based on the color of the cell on the other worksheet. Sort of like
this:

Public Function ComputeCount (<cell reference) as integer

if <cell reference.Interior.Color = vbRed then
'* do the math one way
else
'* do the math another way
end if

end Function

While I'm pretty proficient at VBA, I'm just not seeing how to get the
function pointing to the cell of interest on the worksheet of interest. I'm
used to setting an Worksheet object variable to reference a given worksheet
and a Range object variable to reference a given cell (such as in
batch-oriented processing), but I've never tried this in a function to be
used in a formula.

Any suggestions? Thanks in advance,

Mark

You need to pass the cell of interest as a argument.

Public Function ComputeCount(CellRef As Range) As String
If CellRef.Interior.Color = vbRed Then
ComputeCount = "It's Red"
Else
ComputeCount = "It's not Red"
End If
End Function

And call it with
=ComputeCount(A1)

NickHK

"Mark VII" ...
Hello --

I need to create a custom function that I will use in a formula, and it
needs to take action based on the contents of another cell on another
worksheet in the same workbook. The idea is to get a different from the
formula based on the color of the cell on the other worksheet. Sort of
like
this:

Public Function ComputeCount (<cell reference) as integer

if <cell reference.Interior.Color = vbRed then
'* do the math one way
else
'* do the math another way
end if

end Function

While I'm pretty proficient at VBA, I'm just not seeing how to get the
function pointing to the cell of interest on the worksheet of interest.
I'm
used to setting an Worksheet object variable to reference a given
worksheet
and a Range object variable to reference a given cell (such as in
batch-oriented processing), but I've never tried this in a function to be
used in a formula.

Any suggestions? Thanks in advance,

Mark

Something like this perhaps... The range object does not have to be premised
with the sheet it comes from as that is inherant in the range object
itself... Use the formula like this =computecount(Sheet2!A1)

Public Function ComputeCount(ByVal Cell As Range) As Integer
If Cell.Interior.Color = vbRed Then
'* do the math one way
ComputeCount = 1
Else
'* do the math another way
ComputeCount = 2
End If
End Function

It is important to note that changing the colour of a cell does not trigger
a calculation, nor does it dirty the cell to force a recalculation of the
cells pointing at it... You can add application.volatile to recalc the cell
but you will still have to force a calc when the colour is changed. This will
mean that there will be a fair bit of overhead to this function. Just
something to be aware of...
--
HTH...

Jim Thomlinson


"Mark VII" wrote:

Hello --

I need to create a custom function that I will use in a formula, and it
needs to take action based on the contents of another cell on another
worksheet in the same workbook. The idea is to get a different from the
formula based on the color of the cell on the other worksheet. Sort of like
this:

Public Function ComputeCount (<cell reference) as integer

if <cell reference.Interior.Color = vbRed then
'* do the math one way
else
'* do the math another way
end if

end Function

While I'm pretty proficient at VBA, I'm just not seeing how to get the
function pointing to the cell of interest on the worksheet of interest. I'm
used to setting an Worksheet object variable to reference a given worksheet
and a Range object variable to reference a given cell (such as in
batch-oriented processing), but I've never tried this in a function to be
used in a formula.

Any suggestions? Thanks in advance,

Mark

Jim --

It is important to note that changing the colour of a cell does not trigger
a calculation, nor does it dirty the cell to force a recalculation of the
cells pointing at it...<

That's an interesting point. Is there a way to detect that the cursor has
left a particular cell, or is there a way to capture the cell that was last
edited? Under my set of somewhat arcane circumstances, I can work around the
lack of automatic recalculation when cell color changes if I can monitor a
given area of the worksheet for edits. My thought is that if I can detect
that a particular cell was edited, I can force a calculation in the dependent
area of my workbook.

Thanks,
Mark

You can add event code that will reclac on selection change. There is no
event generated when a cell is coloured so selection change is about as close
as you will get. This will force a recalc of all dirty cells each time the
cursor is moved. By adding application.volatile to the original code you get
very close to what you were looking for...

'**In a standard code module
Public Function ComputeCount(ByVal Cell As Range) As Integer
Application.Volatile
If Cell.Interior.Color = vbRed Then
'* do the math one way
ComputeCount = 1
Else
'* do the math another way
ComputeCount = 2
End If
End Function

'** In the Thisworkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sh.Calculate
End Sub
--
HTH...

Jim Thomlinson


"Mark VII" wrote:

Jim --

It is important to note that changing the colour of a cell does not trigger
a calculation, nor does it dirty the cell to force a recalculation of the
cells pointing at it...<

That's an interesting point. Is there a way to detect that the cursor has
left a particular cell, or is there a way to capture the cell that was last
edited? Under my set of somewhat arcane circumstances, I can work around the
lack of automatic recalculation when cell color changes if I can monitor a
given area of the worksheet for edits. My thought is that if I can detect
that a particular cell was edited, I can force a calculation in the dependent
area of my workbook.

Thanks,
Mark

Just to add:


to the OP
Since you indicated the dependent area was in a separate sheet, you might be
a bit more discerning:

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
if Sh.Name = "Colors" then
worksheets("UDFs").Calculate
end if
End Sub

the disadvantage is that this assumes the user will make a selection on that
sheet after changing the color of the subject cell. To account for the
fact that that might not happen, you might want to also put in the
worksheets("UDFs").Calculate command in the sheet deactive event for the
"colors" sheet.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

You can add event code that will reclac on selection change. There is no
event generated when a cell is coloured so selection change is about as close
as you will get. This will force a recalc of all dirty cells each time the
cursor is moved. By adding application.volatile to the original code you get
very close to what you were looking for...

'**In a standard code module
Public Function ComputeCount(ByVal Cell As Range) As Integer
Application.Volatile
If Cell.Interior.Color = vbRed Then
'* do the math one way
ComputeCount = 1
Else
'* do the math another way
ComputeCount = 2
End If
End Function

'** In the Thisworkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sh.Calculate
End Sub
--
HTH...

Jim Thomlinson


"Mark VII" wrote:

Jim --

It is important to note that changing the colour of a cell does not trigger
a calculation, nor does it dirty the cell to force a recalculation of the
cells pointing at it...<

That's an interesting point. Is there a way to detect that the cursor has
left a particular cell, or is there a way to capture the cell that was last
edited? Under my set of somewhat arcane circumstances, I can work around the
lack of automatic recalculation when cell color changes if I can monitor a
given area of the worksheet for edits. My thought is that if I can detect
that a particular cell was edited, I can force a calculation in the dependent
area of my workbook.

Thanks,
Mark