Need help with C. Pearson's Functions

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have been using the Newsgroup regularly for the past
few months.

In many replies to the questions to the forums, I have
come across a recommendation to use the following site
when the subject is i.e. sorting by background color.

http://www.cpearson.com/excel/colors.htm


I am now attempting to implement some of C. Pearson's
functions, but some of the basic functions don't seem to
work for me (I'm sure that I am doing something wrong).

For instance, I am attempting to display to color index
of a cell by calling the function via (see actual
function between the ******'s below:

=CELLCOLORINDEX(A1,FALSE)


Here's what I do:
- Copy/paste the function into VBA code
- I did NOT add the "Option Explicit" before "Function"
(although I have tried that too and it didn't make a
difference)
- Save code/XLS file
- In the worksheet, change background color of cell A1
to "yellow"
- Copy/paste "=CELLCOLORINDEX(A1,FALSE)" into cell A2


Problem:
I would expect to have cell A2 return the color index of
the value for A1. However, it shows that it can't find
the fuction by displaying the following:
" #Name? ".

In addition, a get a Trace Error (green triangle) -- BTW,
I'm using Excel XP --

What am I doing wrong? Any help is appreciated!

Tom



******************************************
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
******************************************
 
Did you put this function in a general module (not behind a worksheet and not
behind ThisWorkbook)?

I copied and pasted from your post (fixed the wordwrap) and it worked ok for me.
 
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:

****
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function
****


Keep Outlook or other newsgroup reader active
Open Excel
Open your workbook
Activate Excel
Alt + F11 [Activates the visual basic editor (VBE) window]
Select your workbook in the project explorer window [Its the top left
window]
Right click > Insert > Module
Activate your newsgroup program and select the code between but not
including the ****
Copy
Activate the VBE window
Point the mouse at the large code window
Paste
Save
Back into Excel


Give any cell (say A1) a background color
In any cell type:

=cellcolorindex(A1)
Returns the color index of the cell A1

If you want to use this function a lot, it's better that you keep it
in a module in your personal workbook. Search Google or post back for
details on how to create a Personal.xls workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Dave:

It's me again!

This is how I added the code...

- Right-click on worksheet tab
- Select "View Code"
- Copy/Paste code

This is what I see in the top of the window:
- (General) (CellColorIndex)

If I go to Macros, I don't see any functions (at all (I
guess that's the way it's supposed to be).

I then add text & background color to A1 and add
the "=CELLCOLORINDEX(A1,FALSE)" to A2.

Did I miss something?

Tom
 
If you can see the project explorer (hit ctrl-R if you can't), you'll see that
"Sheet1 (Sheet1)" under Microsoft Excel Objects is highlighted. (the names
maybe be different, but the location will match.)

You are in a module that's usually used for code for events on that worksheet
and code for controls from the control toolbox toolbar.

If you right click on your project, you'll get a popup. Choose Insert|Module
(not class module--that's different, too).

Cut the code from Chip's function, from that sheet module and paste it into that
general module. Then go back to excel. Select your cell and hit F2. Then
enter. (excel should find the UDF (userdefined function) and reevaluate for
you.

=====
The module you were in can be very useful. You can have a macro fire when you
change a cell, when you change selection, calculate, and a bunch of other stuff.
 
In fact, all that removeduplicate macro code should also be in a General
module. Is that were you stuck it?
 
Hi Tom!

Here's a general guide that idicates how and where different types of
code should be pasted. You need Option 3 here.

In many cases we show VBA code. For novices the most difficult problem
is how to get the code into their workbook.

The easy way to get subroutines code in your sheet is.


1. With your workbook open
2. Click on the Start Bar icon for the program that contains the
code.
3. Select and copy the code from the text between the first line
with the word Sub and the final line End Sub
4. Click on the Start Bar Excel Icon for your workbook
5. Right click the Sheet Tab
6. Click View Code

[This makes the code sheet for the active sheet ready for receiving
code in the Visual Basic Editor]

We now have three options depending upon the type of subroutine (there
are more options but we can exclude those for now as they are less
common). The code *must* go into the correct type of module.


Option 1. Putting code in a Sheet Module:
7. The large clear window at top right is the code window that
the subroutine goes in. These subroutines will all have the word
"Worksheet_" as part of the name and will run on the occurrence of the
event described in the name of the subroutine. A typical example
subroutine might be one named Private Sub Worksheet_Activate()

OR

Option 2. Putting code in the ThisWorkbook Module:
7. In the top left window (known as the Project Explorer),
double click the ThisWorkbook entry and the clear window at top right
will be where the subroutine goes in. These subroutines will have the
word "Workbook_" as part of the name and will run on the occurrence of
the event described in the name of the subroutine. A typical example
subroutine might be one named Private Sub Workbook_Open().

OR

Option 3. Putting code in a new (ordinary) Module:
7. In other cases the subroutine goes in a new Module. In the
top left window, select the name of the workbook. Right click > Insert
Module. That will insert a new module that is made active and the
Click to expand...
clear window at the top right will be where the subroutine goes in. A
typical example might be one named Sub InsertRow() or Function
AgeReport(Date of Birth As Date)

8. Point to the clear area in the code widow.
9. Right Click > Paste
10. File > Save [or just click the Save icon.]
11. Selecting the Start Bar Excel icon for your workbook. Or Click
the Excel Icon at the left end of the main toolbar.

You'll still get some problems if you're a novice. But that is all
part of the fun!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Formatting changes don't make excel recalculate. Your function only gets
updated when xl recalculates.

You can do:

tools|Options|Calculation tab|calculate now
(or just hit F9).

This is from xl2002's help:

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

(I think that the last one is new with xl2002.)

Norman:

Thanks for your help... now I got this to work.

The problem was that I did NOT enter the function code
into a module. Before I had simply selected View Code
and put the function onto a sheet.

I actually now tried the "=CELLCOLORINDEX(A1,FALSE)"
function. It does show me values such as "41" for the
background, but it does not refresh the value when I
change the background color. In order to see the
updated value it must close the file and reopen it. I
tried to refresh it with function key F5 on the keyboard
but that brings up the GoTo window.

Is there a way to automatically refresh the function?

Thanks again for your help!!!

Tom
-----Original Message-----
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:

****
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function
****


Keep Outlook or other newsgroup reader active
Open Excel
Open your workbook
Activate Excel
Alt + F11 [Activates the visual basic editor (VBE) window]
Select your workbook in the project explorer window [Its the top left
window]
Right click > Insert > Module
Activate your newsgroup program and select the code between but not
including the ****
Copy
Activate the VBE window
Point the mouse at the large code window
Paste
Save
Back into Excel


Give any cell (say A1) a background color
In any cell type:

=cellcolorindex(A1)
Returns the color index of the cell A1

If you want to use this function a lot, it's better that you keep it
in a module in your personal workbook. Search Google or post back for
details on how to create a Personal.xls workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Tom said:
I have been using the Newsgroup regularly for the past
few months.

In many replies to the questions to the forums, I have
come across a recommendation to use the following site
when the subject is i.e. sorting by background color.

http://www.cpearson.com/excel/colors.htm


I am now attempting to implement some of C. Pearson's
functions, but some of the basic functions don't seem to
work for me (I'm sure that I am doing something wrong).

For instance, I am attempting to display to color index
of a cell by calling the function via (see actual
function between the ******'s below:

=CELLCOLORINDEX(A1,FALSE)


Here's what I do:
- Copy/paste the function into VBA code
- I did NOT add the "Option Explicit" before "Function"
(although I have tried that too and it didn't make a
difference)
- Save code/XLS file
- In the worksheet, change background color of cell A1
to "yellow"
- Copy/paste "=CELLCOLORINDEX(A1,FALSE)" into cell A2


Problem:
I would expect to have cell A2 return the color index of
the value for A1. However, it shows that it can't find
the fuction by displaying the following:
" #Name? ".

In addition, a get a Trace Error (green triangle) -- BTW,
I'm using Excel XP --

What am I doing wrong? Any help is appreciated!

Tom



******************************************
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
******************************************
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Tom!

The problem is that a change of format does not trigger a
recalculation. You don't actually need to close and re-open a simple
press of F9 will do the job.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Tom said:
Norman:

Thanks for your help... now I got this to work.

The problem was that I did NOT enter the function code
into a module. Before I had simply selected View Code
and put the function onto a sheet.

I actually now tried the "=CELLCOLORINDEX(A1,FALSE)"
function. It does show me values such as "41" for the
background, but it does not refresh the value when I
change the background color. In order to see the
updated value it must close the file and reopen it. I
tried to refresh it with function key F5 on the keyboard
but that brings up the GoTo window.

Is there a way to automatically refresh the function?

Thanks again for your help!!!

Tom



-----Original Message-----
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:

****
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function
****


Keep Outlook or other newsgroup reader active
Open Excel
Open your workbook
Activate Excel
Alt + F11 [Activates the visual basic editor (VBE) window]
Select your workbook in the project explorer window [Its the top left
window]
Right click > Insert > Module
Activate your newsgroup program and select the code between but not
including the ****
Copy
Activate the VBE window
Point the mouse at the large code window
Paste
Save
Back into Excel


Give any cell (say A1) a background color
In any cell type:

=cellcolorindex(A1)
Returns the color index of the cell A1

If you want to use this function a lot, it's better that you keep it
in a module in your personal workbook. Search Google or post back for
details on how to create a Personal.xls workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Tom said:
I have been using the Newsgroup regularly for the past
few months.

In many replies to the questions to the forums, I have
come across a recommendation to use the following site
when the subject is i.e. sorting by background color.

http://www.cpearson.com/excel/colors.htm


I am now attempting to implement some of C. Pearson's
functions, but some of the basic functions don't seem to
work for me (I'm sure that I am doing something wrong).

For instance, I am attempting to display to color index
of a cell by calling the function via (see actual
function between the ******'s below:

=CELLCOLORINDEX(A1,FALSE)


Here's what I do:
- Copy/paste the function into VBA code
- I did NOT add the "Option Explicit" before "Function"
(although I have tried that too and it didn't make a
difference)
- Save code/XLS file
- In the worksheet, change background color of cell A1
to "yellow"
- Copy/paste "=CELLCOLORINDEX(A1,FALSE)" into cell A2


Problem:
I would expect to have cell A2 return the color index of
the value for A1. However, it shows that it can't find
the fuction by displaying the following:
" #Name? ".

In addition, a get a Trace Error (green triangle) -- BTW,
I'm using Excel XP --

What am I doing wrong? Any help is appreciated!

Tom



******************************************
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
******************************************
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Tom,
After everything is fixed up and your non Event macro code
has been removed from your sheet, there is still one question
you asked about not seeing your function in with the macros.
That is correct and by design. You will find your User Defined
Function in the Function Wizard [fx] list. Look on left-side
for "User Defined" and your User Defined Functions (UDF) will
be displayed on the right. Private macros do not show up
in the macro list (Alt+F8) either.

Event Macros for worksheets are so much more straight forward to
install but they are installed differently than General Macros as you
are now well aware. As a reminder of what you did with Event
macros you can take a look at
http://www.mvps.org/dmcritchie/excel/event.htm
Chip's page on Events is
http://www.cpearson.com/excel/events.htm

Norman's instructions to install in General module should be
clear but I have a page on the subject just in case you get stuck.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

When you use ColorIndex you might want to check on or use
a different number, so you will probably find my page on Color useful.
http://www.mvps.org/dmcritchie/excel/colors.htm
I also think the examples to Chip's color functions are better on
my page than on Chip's own site. Chip prefers to do the technical things
and keep pages short, examples can be rather sparse. (IMO).
You will find lots of examples in the newsgroups by searching
on any of his functions, or any of Excel's builtin functions for that matter. .
http://www.mvps.org/dmcritchie/excel/xlnews.htm


Norman Harker said:
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:
Click to expand...
[ snipped -- the code was a Function, instructions followed ]
 
Norman; Dave:

You have helped a great deal... I put all the info into a
Word doc for future reference.

The help the both of you have provided makes the
NEWSGROUPS what it is today... a fantastic resource!!!

THANKS SO MUCH FOR TAKING OUT TIME TO HELP ME AND OTHERS
DURING THE LEARNING PROCESS.

Tom

-----Original Message-----
Hi Tom!

The problem is that a change of format does not trigger a
recalculation. You don't actually need to close and re- open a simple
press of F9 will do the job.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Tom said:
Norman:

Thanks for your help... now I got this to work.

The problem was that I did NOT enter the function code
into a module. Before I had simply selected View Code
and put the function onto a sheet.

I actually now tried the "=CELLCOLORINDEX(A1,FALSE)"
function. It does show me values such as "41" for the
background, but it does not refresh the value when I
change the background color. In order to see the
updated value it must close the file and reopen it. I
tried to refresh it with function key F5 on the keyboard
but that brings up the GoTo window.

Is there a way to automatically refresh the function?

Thanks again for your help!!!

Tom



-----Original Message-----
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:

****
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of
Click to expand...
the
Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function
****


Keep Outlook or other newsgroup reader active
Open Excel
Open your workbook
Activate Excel
Alt + F11 [Activates the visual basic editor (VBE) window]
Select your workbook in the project explorer window
Click to expand...
[Its
the top left
window]
Right click > Insert > Module
Activate your newsgroup program and select the code between but not
including the ****
Copy
Activate the VBE window
Point the mouse at the large code window
Paste
Save
Back into Excel


Give any cell (say A1) a background color
In any cell type:

=cellcolorindex(A1)
Returns the color index of the cell A1

If you want to use this function a lot, it's better
Click to expand...
that
you keep it
in a module in your personal workbook. Search Google
Click to expand...
or
post back for
details on how to create a Personal.xls workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril
Click to expand...
&
St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace &
Click to expand...
Unity
Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I have been using the Newsgroup regularly for the past
few months.

In many replies to the questions to the forums, I have
come across a recommendation to use the following site
when the subject is i.e. sorting by background color.

http://www.cpearson.com/excel/colors.htm


I am now attempting to implement some of C. Pearson's
functions, but some of the basic functions don't
Click to expand...
seem
to
work for me (I'm sure that I am doing something wrong).

For instance, I am attempting to display to color index
of a cell by calling the function via (see actual
function between the ******'s below:

=CELLCOLORINDEX(A1,FALSE)


Here's what I do:
- Copy/paste the function into VBA code
- I did NOT add the "Option Explicit" before "Function"
(although I have tried that too and it didn't make a
difference)
- Save code/XLS file
- In the worksheet, change background color of cell A1
to "yellow"
- Copy/paste "=CELLCOLORINDEX(A1,FALSE)" into cell A2


Problem:
I would expect to have cell A2 return the color
Click to expand...
index
of
the value for A1. However, it shows that it can't find
the fuction by displaying the following:
" #Name? ".

In addition, a get a Trace Error (green triangle) --
Click to expand...
BTW,
I'm using Excel XP --

What am I doing wrong? Any help is appreciated!

Tom



******************************************
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
******************************************


.
Click to expand...
Click to expand...


.
Click to expand...
 
Dave; Norman:

You have helped a great deal... I put all the info into a
Word doc for future reference.

The help the both of you have provided makes the
NEWSGROUPS what it is today... a fantastic resource!!!

THANKS SO MUCH FOR TAKING OUT TIME TO HELP ME AND OTHERS
DURING THE LEARNING PROCESS.

Tom



-----Original Message-----
Formatting changes don't make excel recalculate. Your function only gets
updated when xl recalculates.

You can do:

tools|Options|Calculation tab|calculate now
(or just hit F9).

This is from xl2002's help:

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

(I think that the last one is new with xl2002.)

Norman:

Thanks for your help... now I got this to work.

The problem was that I did NOT enter the function code
into a module. Before I had simply selected View Code
and put the function onto a sheet.

I actually now tried the "=CELLCOLORINDEX(A1,FALSE)"
function. It does show me values such as "41" for the
background, but it does not refresh the value when I
change the background color. In order to see the
updated value it must close the file and reopen it. I
tried to refresh it with function key F5 on the keyboard
but that brings up the GoTo window.

Is there a way to automatically refresh the function?

Thanks again for your help!!!

Tom
-----Original Message-----
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:

****
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of
Click to expand...
the
Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function
****


Keep Outlook or other newsgroup reader active
Open Excel
Open your workbook
Activate Excel
Alt + F11 [Activates the visual basic editor (VBE) window]
Select your workbook in the project explorer window
Click to expand...
[Its
the top left
window]
Right click > Insert > Module
Activate your newsgroup program and select the code between but not
including the ****
Copy
Activate the VBE window
Point the mouse at the large code window
Paste
Save
Back into Excel


Give any cell (say A1) a background color
In any cell type:

=cellcolorindex(A1)
Returns the color index of the cell A1

If you want to use this function a lot, it's better
Click to expand...
that
you keep it
in a module in your personal workbook. Search Google
Click to expand...
or
post back for
details on how to create a Personal.xls workbook.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril
Click to expand...
&
St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace &
Click to expand...
Unity
Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I have been using the Newsgroup regularly for the past
few months.

In many replies to the questions to the forums, I have
come across a recommendation to use the following site
when the subject is i.e. sorting by background color.

http://www.cpearson.com/excel/colors.htm


I am now attempting to implement some of C. Pearson's
functions, but some of the basic functions don't
Click to expand...
seem
to
work for me (I'm sure that I am doing something wrong).

For instance, I am attempting to display to color index
of a cell by calling the function via (see actual
function between the ******'s below:

=CELLCOLORINDEX(A1,FALSE)


Here's what I do:
- Copy/paste the function into VBA code
- I did NOT add the "Option Explicit" before "Function"
(although I have tried that too and it didn't make a
difference)
- Save code/XLS file
- In the worksheet, change background color of cell A1
to "yellow"
- Copy/paste "=CELLCOLORINDEX(A1,FALSE)" into cell A2


Problem:
I would expect to have cell A2 return the color
Click to expand...
index
of
the value for A1. However, it shows that it can't find
the fuction by displaying the following:
" #Name? ".

In addition, a get a Trace Error (green triangle) -- BTW,
I'm using Excel XP --

What am I doing wrong? Any help is appreciated!

Tom



******************************************
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the
Interior
' (background) of a cell, or, if OfText is true, of the
Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
******************************************


.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Thanks again... please see a more detailed reply in the
other thread.

Tom

-----Original Message-----
Hi Tom!

Here's a general guide that idicates how and where different types of
code should be pasted. You need Option 3 here.

In many cases we show VBA code. For novices the most difficult problem
is how to get the code into their workbook.

The easy way to get subroutines code in your sheet is.


1. With your workbook open
2. Click on the Start Bar icon for the program that contains the
code.
3. Select and copy the code from the text between the first line
with the word Sub and the final line End Sub
4. Click on the Start Bar Excel Icon for your workbook
5. Right click the Sheet Tab
6. Click View Code

[This makes the code sheet for the active sheet ready for receiving
code in the Visual Basic Editor]

We now have three options depending upon the type of subroutine (there
are more options but we can exclude those for now as they are less
common). The code *must* go into the correct type of module.


Option 1. Putting code in a Sheet Module:
7. The large clear window at top right is the code window that
the subroutine goes in. These subroutines will all have the word
"Worksheet_" as part of the name and will run on the occurrence of the
event described in the name of the subroutine. A typical example
subroutine might be one named Private Sub Worksheet_Activate()

OR

Option 2. Putting code in the ThisWorkbook Module:
7. In the top left window (known as the Project Explorer),
double click the ThisWorkbook entry and the clear window at top right
will be where the subroutine goes in. These subroutines will have the
word "Workbook_" as part of the name and will run on the occurrence of
the event described in the name of the subroutine. A typical example
subroutine might be one named Private Sub Workbook_Open ().

OR

Option 3. Putting code in a new (ordinary) Module:
7. In other cases the subroutine goes in a new Module. In the
top left window, select the name of the workbook. Right click > Insert
Module. That will insert a new module that is made
Click to expand...
active and the
clear window at the top right will be where the subroutine goes in. A
typical example might be one named Sub InsertRow() or Function
AgeReport(Date of Birth As Date)

8. Point to the clear area in the code widow.
9. Right Click > Paste
10. File > Save [or just click the Save icon.]
11. Selecting the Start Bar Excel icon for your workbook. Or Click
the Excel Icon at the left end of the main toolbar.

You'll still get some problems if you're a novice. But that is all
part of the fun!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Tom said:
Dave:

It's me again!

This is how I added the code...

- Right-click on worksheet tab
- Select "View Code"
- Copy/Paste code

This is what I see in the top of the window:
- (General) (CellColorIndex)

If I go to Macros, I don't see any functions (at all (I
guess that's the way it's supposed to be).

I then add text & background color to A1 and add
the "=CELLCOLORINDEX(A1,FALSE)" to A2.

Did I miss something?

Tom

wordwrap)
and it worked ok for me. seem
to index
of
BTW,
Click to expand...


.
Click to expand...
 
Dave:

I am just reading through all the info you sent me...

Now, I'm a bit confused... all the RemoveDups stuff were
put into a macro on a sheet (not module) so that I can
see the macro name on the toolbar menu.

As said, it works perfect at this time... all the dups
are being removed and those cells are highlighted so that
I can evaluate the data further (if needed).

Did you suggest I should move the RemoveDups code in a
module too. I then wouldn't to see the name of the macro
any longer, right? Also, I currently keep the
RemoveDups and the ColorIndex function in a different
file ('still playing w/ the color index).

Once I feel more comfortable w/ that stuff, I'll move
both in the same file. If I were to do this in the same
fashion (color index function in the module & RemoveDups
code in the worksheet) would something not work based on
changes in the other? Makes sense?

Tom
 
David:

Thanks for your reply, too!!! Your additional insights
are very helpful.

I have bookmarked your site and I will continue to study
all the info that you, Norman, and Dave sent me via the
Newsgroups.

Since I just now got Chip's functions to work, I will
review his website and then compare it to yours. I'm
sure I'll find useful stuff on both.

Thanks again!

Tom


-----Original Message-----
Hi Tom,
After everything is fixed up and your non Event macro code
has been removed from your sheet, there is still one question
you asked about not seeing your function in with the macros.
That is correct and by design. You will find your User Defined
Function in the Function Wizard [fx] list. Look on left-side
for "User Defined" and your User Defined Functions (UDF) will
be displayed on the right. Private macros do not show up
in the macro list (Alt+F8) either.

Event Macros for worksheets are so much more straight forward to
install but they are installed differently than General Macros as you
are now well aware. As a reminder of what you did with Event
macros you can take a look at
http://www.mvps.org/dmcritchie/excel/event.htm
Chip's page on Events is
http://www.cpearson.com/excel/events.htm

Norman's instructions to install in General module should be
clear but I have a page on the subject just in case you get stuck.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

When you use ColorIndex you might want to check on or use
a different number, so you will probably find my page on Color useful.
http://www.mvps.org/dmcritchie/excel/colors.htm
I also think the examples to Chip's color functions are better on
my page than on Chip's own site. Chip prefers to do the technical things
and keep pages short, examples can be rather sparse. (IMO).
You will find lots of examples in the newsgroups by searching
on any of his functions, or any of Excel's builtin functions for that matter. .
http://www.mvps.org/dmcritchie/excel/xlnews.htm


"Norman Harker" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Hi Tom!

Let's take it step by step and here you might be best printing out
these instructions as well as keeping this post active.

Here's Chip's code:
Click to expand...
[ snipped -- the code was a Function, instructions followed ]


.
Click to expand...
 
Hi Tom!

Thanks for thanks. It's always appreciated and acts as a confirmation
that something works for those who are just watching or doing a Google
Search.

Apart from a masochistic type of enjoyment, we also pick up a lot by
participating in these groups and if you see a question you know the
answer of, then give it a reply. You may makes mistakes but you won't
get flamed here like in other groups; probably because Excel is so
complex that we all make mistakes and learn new things every day.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday:Algeria (Independence Day); Cape Verde
Islands (Independence Day); Czech Republic (St. Cyril & St. Methodius
Day); Isle of Man (Tynwald Day); Rwanda (Peace & Unity Day); Slovakia
(St. Cyril & St. Methodius Day); Thailand (Asalha Puja Day); Venezuela
(Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Which toolbar menu do you mean?

I would put the all the removeduplicate stuff into one module. The macro can be
accessed via Tools|Macro|Macros|click on its name and click Run.

In fact, if I only needed these routines for that one workbook, I'd put them in
that one workbook. I would put each in their own general modules. (Just insert
another Module. You can put them all in one, but sometimes it's easier/safer
not to have all the other code in the way.)

On the other hand, if either of these routines are going to be used for lots
(more than one) of workbooks, you wouldn't want to duplicate the coding in all
of the workbooks. It becomes a pain if you catch one error and have to
backtrack to correct them all.

So if you put the removeduplicate routine into a separate workbook, you could
just open that workbook when you need to run it.

Using that function from a worksheet would change slightly. The formula would
change from:

=cellcolorindex(a1,false)

to something that includes the name of the workbook that holds the
cellcolorindex function. If I named that workbook myfunction.xls, then I'd have
this as the formula:

=myfunction.xls!cellcolorindex(a1,false)

Everything calculates the same way (F9's), but you have to tell excel where to
find it.
 
Dave:

This for the additional feedback. The idea to use the
filename will be helpful... it eliminates my creating the
function in multiple files.

Thanks,
Tom
 
I like Gord's idea. Then you can use this function for any workbook. But keep
the cellcolorindex and the removeduplicates in different workbooks.

If you keep them in the same workbook (as an .xla), then you'll need some way of
executing the removeduplicate macro. (Subs don't show up in the
Tools|Macro|macros list.)

You could make a little commandbar or add an option to an existing menubar
item. When you feel comfortable enough to do that, post back (or even search
google).

I'm sure you'll get lots of help.
 
Back
Top