Error while using function

[dolphin_m70]

hi
i created a function called ColorFunction
but when i try to access the function it says

Book1.xls!Module1.ColorFunction
when i select this it says

Function Arguments
No help available
The function takes no arguments
Formula result =

but when i run it for the first time
it worked, but when i try to use again after saving the book .it doesnt work
any help appreciated

[stanleydgromjr]

dolphin_m70,

Please post your code.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]

[dolphin_m70]

Please Login or Register  to view this content.

[stanleydgromjr]

dolphin_m70,

You can not run a Function like you do a macro.

To use the Function:
ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

"rColor As Range" is the cell address of, say $A$1, that contains the ".Interior.ColorIndex" to be used for the "SUM" result.

"rRange As Range" is the range to search for the ".Interior.ColorIndex" to sum.

"Optional SUM As Boolean" try setting this to "TRUE"


In a cell, say C1, enter:
=ColorFunction($A$1, $B$1:$B$100, TRUE)


Your Function code should be placed in a Module:

Adding the Macro/Function
1. Copy the Function, by highlighting the Function code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.


If the above does not work, please post your workbook - scroll down and see "Manage Attachments".

[dolphin_m70]

hi
what i want to do is sum the no of different colored cells
like red blue green and so on with the use of this function

[stanleydgromjr]

dolphin_m70,

See the attached workbook "Function ColorFunction - dolphin_m70 - SDG10.xls", Sheet2.

[dolphin_m70]

hi stan
thx for the file
could u pls explain how to use it
tht wud be very helpful as im not a pro
thx

[stanleydgromjr]

dolphin_m70,

i created a function called ColorFunction

Did you create this Function?


To use the Function:
ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

"rColor As Range" is the cell address of, say $A$1, that contains the ".Interior.ColorIndex" to be used for the "SUM" or "COUNT" result.

"rRange As Range" is the range to search for the ".Interior.ColorIndex" to sum or count.

"Optional SUM As Boolean" if "TRUE", then the Function will return the SUM of the range if the ".Interior.ColorIndex" of the range is equal to the "rColor As Range".

"Optional SUM As Boolean" if blank, the the Function will return the COUNT of the range if the ".Interior.ColorIndex" of the range is equal to the "rColor As Range".


Please refer to the cells in Sheet2, cells G1:H4, to understand the above.

[dolphin_m70]

hi
thx for the reply
it works

Is it poss to make it a button or control independant of any workbook, so that it can used in any workbook
cud anyone pls help

[stanleydgromjr]

dolphin_m70,

Is it poss to make it a button or control independant of any workbook, so that it can used in any workbook

No.


You could copy the Function into a Module in your "PERSONAL.XLS".

Then, you could use the Function in any workbook/worksheet that you have open on your computer.


Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. On the left side of the screen, click on the box with the plus sign next to "VBAProject(PERSONAL.XLS).
5. Click on the plus sign next to "Microsoft Excel Objects"
6. Press the keys ALT+I to activate the Insert menu
7. Press M to insert a Standard Module
8. Paste the code by pressing the keys CTRL+V
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Please Login or Register  to view this content.

To use the Function in any open workbook/worksheet on your computer:
ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

"rColor As Range" is the cell address of, say $A$1, that contains the ".Interior.ColorIndex" to be used for the "SUM" or "COUNT" result.

"rRange As Range" is the range to search for the ".Interior.ColorIndex" to sum or count.

"Optional SUM As Boolean" if "TRUE", then the Function will return the SUM of the range if the ".Interior.ColorIndex" of the range is equal to the "rColor As Range".

"Optional SUM As Boolean" if blank, the the Function will return the COUNT of the range if the ".Interior.ColorIndex" of the range is equal to the "rColor As Range".


Please refer workbook "Function ColorFunction - dolphin_m70 - SDG10.xls", to the cells in Sheet2, cells G1:H4, to understand the above.

[dolphin_m70]

thx stan for ur help