custom function - with built-in function

[VilMarci]

Hi,

I have a small excel function that returns the background color value of a
cell:

Function ColorOfCell(cellaneve As Range)

Dim CellColor As Integer

CellColor = cellaneve.Interior.ColorIndex
ColorOfCell = CellColor

End Function

That's ok. Works from excel like =ColorOfCell(A1)
But how can I make it work to use with built-in functions?

Like:
=ColorOfCell(HLOOKUP($A$1;Personal!$B$20:$Q$51;J6;FALSE))

Is there any general solution to do this?

Marton

 

[keepITcool]

functions like index and offset will return a reference,
but the lookup functions return a value.

so:
either reconstruct your lookup with match and index.
or:
create a new function (with more arguments)
that incorporates the vlookup inside VBA


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

 

[VilMarci]

Hi,

Thanks for the reply

Found an other way that should work, but...

ADDRESS(L4;$K$4;1;TRUE;"Personal") returns Personal!B21
ColorOfCell(Personal!B21) returns 3

BUT

colorofcell(ADDRESS(L4;$K$4;1;TRUE;"Personal"))
Gives "VALUE!" error message

Why not working?

Marton

 

[Bob Phillips]

Marton,

You function is being passed a range as its argument. HLOOKUP returns the
cell value, not the cell, so they have a basic dilemma.

You need to use a different function. MATCH will get you the column that A1
is in,

MATCH($A$1,$B$20:$B$51,0)

and INDEX can return the reference at the intersection of a column and a row

INDEX($B$20:$Q$51,J6, the_col)

Join them together and integrate with your function, and you get

=colorofcell(INDEX($B$20:$Q$51,J6,MATCH($A$1,$B$20:$B$51,0)))

But also note that there is a basic problem with your function, in that it
will not update automatically if the cell colour is changed, you will need
to force a recalculation of the value with F9.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

almost there..
Address returns a STRING, you need a reference.
try colorofcell(indirect(address



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


VilMarci wrote :

 

[Jim May]

"functions like index and offset will return a reference,
but the lookup functions return a value."

Usage of the above has always confused me.
Clearly tell me the difference in "reference" and "value".
All I can presently say is that reference might be "B4",
whereby the value in B4 might be "100" or "testing".
I'm a bit more confused with the reference-side.
TIA,

 

[VilMarci]

Thank you very much. Now it's working.
Now it does all I really wanted.
Yes I know the bug that it won't update if color changes...
Is it possible (and how complicate?) to modify the code to update like
built-in functions?
Thanks,
Marton

 

[Tom Ogilvy]

in VBA

Dim rng as Range
Dim v as Variant
set rng = Range("B9")
v = Range("B9").Value

rng is a reference to the cell B9
v is a variable containing the value stored in B9.

If a function returns a reference to the cell, then it is designed to be
utilized by another function which requires a reference. if used alone,
then it is similar to using the default value of a range (which is its
value).

 

[Tushar Mehta]

The fact that your function doesn't recalculate when the color changes
is not a bug. It's a design decision by the XL team, which decided
many versions ago that many changes, typically formatting related,
could not possibly have an impact on the contents of a worksheet.
Unfortunately, people have found ways of making that happen. ;-)

Also note that your function will fail if passed a range with more than
1 cell.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

As Tushar says, it is not a bug but a 'feature'.

The only way I have gotten around it is to have a button that changes the
cell colour, which includes a forced re-calculate.

Note also that it doesn't work on conditional formatted coloured cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[VilMarci]

Nice feature

Anyway that's ok, I use a list, so if user change the name, it also reruns
the whole stuff...

Thanks everyone!

 

[Guest]

In cell A2 - I have a number 123.45

Sub foo()
Dim rng As Range
Dim v As Variant
Set rng = Range("A2")
v = Range("A2").Value
End Sub

If I F8 down thru to End Sub without passing End Sub

In the Immediate Window I get:
? rng
123.45
? v
123.45

Can you provide a more diverse example of the useage
of Ref Vs Value?

Not sure I'm getting what you are saying...

 

[Tom Ogilvy]

What would you expect to see for the reference rng?

When you do
? rng
it is the same as
? rng.Value

both rng and v are variables. Each points to a location in memory. v
points to a location that stores the number 123.45 rng points to a
location in memory that has a pointer to the memory structure for
Range("A2") of the activesheet. If you refer to the rng variable directly
, it can't show you that memory structure, so it returns what is stored in
the default property which is the value property so you see 123.45.

 

[keepITcool]

if you type ?rng in the immediate window
he'll give you the Range objects's default property
which is.. you guessed it.. .Value

make sure the the "LOCALS" window is visible.
now use F8 to step thru again, and keep a close eye on that window.

but in the locals window you can see (and learn)
about all the properties of your object variables.

 

[VilMarci]

Hi,

Hm.. something still not good... however it's not on the function side...

I did some select case mods at the end to display text rather than color
code.
The function is ok now, but:

1. ColorOfCell(I7) returns "PH"
2. ColorOfCell(I7)="PH" returns TRUE

3. ColorOfCell(INDIRECT(ADDRESS($P8;$O$4;4;TRUE;"Personal"))) returns "PH"
4. ColorOfCell(INDIRECT(ADDRESS($P8;$O$4;4;TRUE;"Personal")))="PH" returns
"volatile"

I don't understand why line 4 is not working. I'd like to work with the
returned value further...
One more thing:
Conditional formatting based on the "PH" string entered into the cell by the
finction is working...

What's the difference between line 2 and 4?

Thanks for any help,

Marton