software

I'm trying to write a custom function in VBE to determine if my data falls
into one of three categories, active, inactive, or future, for functions that
I use all the time in multiple workbooks. The HELP in Excel isn't much help
for something so complex.

Here is a stripped down example of my function:
=IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))

Any tips on how to get started are much appreciated!

Function Status(rCell as Range) as String
Select Case rCell.Value
case 2 to 4 : Status = quot;Activequot;
case 5 to 8 : Status = quot;Inactivequot;
case 9 to 11 : Status = quot;Futurequot;
case else: Status = quot;Unknownquot;
End Select
End Sub

HTH
--
AP

quot;mary squot; gt; a écrit dans le message de news:
...
gt; I'm trying to write a custom function in VBE to determine if my data falls
gt; into one of three categories, active, inactive, or future, for functions
gt; that
gt; I use all the time in multiple workbooks. The HELP in Excel isn't much
gt; help
gt; for something so complex.
gt;
gt; Here is a stripped down example of my function:
gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
gt;
gt; Any tips on how to get started are much appreciated!
If you'd like to write more functions yourself, read a book or follow a tutorial. There are many.
Here's one:

www.datapigtechnologies.com/ExcelMain.htmSee the quot;Getting started with VBAquot; section. Don't forget to switch sound on.

--
Kind regards,

Niek Otten

quot;Ardus Petusquot; gt; wrote in message ...
| Function Status(rCell as Range) as String
| Select Case rCell.Value
| case 2 to 4 : Status = quot;Activequot;
| case 5 to 8 : Status = quot;Inactivequot;
| case 9 to 11 : Status = quot;Futurequot;
| case else: Status = quot;Unknownquot;
| End Select
| End Sub
|
| HTH
| --
| AP
|
| quot;mary squot; gt; a écrit dans le message de news:
| ...
| gt; I'm trying to write a custom function in VBE to determine if my data falls
| gt; into one of three categories, active, inactive, or future, for functions
| gt; that
| gt; I use all the time in multiple workbooks. The HELP in Excel isn't much
| gt; help
| gt; for something so complex.
| gt;
| gt; Here is a stripped down example of my function:
| gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
| gt;
| gt; Any tips on how to get started are much appreciated!
|
|
Insert this into a Module. It should be somewhat self explanitory. Change
what you need. To use this you enter =MyFunc(D2) into a cell and it should
work fine.

quot;mary squot; wrote:

gt; I'm trying to write a custom function in VBE to determine if my data falls
gt; into one of three categories, active, inactive, or future, for functions that
gt; I use all the time in multiple workbooks. The HELP in Excel isn't much help
gt; for something so complex.
gt;
gt; Here is a stripped down example of my function:
gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
gt;
gt; Any tips on how to get started are much appreciated!

The quot;numbersquot; aren't really numbers. They actually represent different data
labels. I wanted to have a basic formula that I could use to apply to a
number of different situations.

quot;Ardus Petusquot; wrote:

gt; Function Status(rCell as Range) as String
gt; Select Case rCell.Value
gt; case 2 to 4 : Status = quot;Activequot;
gt; case 5 to 8 : Status = quot;Inactivequot;
gt; case 9 to 11 : Status = quot;Futurequot;
gt; case else: Status = quot;Unknownquot;
gt; End Select
gt; End Sub
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;mary squot; gt; a écrit dans le message de news:
gt; ...
gt; gt; I'm trying to write a custom function in VBE to determine if my data falls
gt; gt; into one of three categories, active, inactive, or future, for functions
gt; gt; that
gt; gt; I use all the time in multiple workbooks. The HELP in Excel isn't much
gt; gt; help
gt; gt; for something so complex.
gt; gt;
gt; gt; Here is a stripped down example of my function:
gt; gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
gt; gt;
gt; gt; Any tips on how to get started are much appreciated!
gt;
gt;
gt;

I'm still having trouble with this. Maybe VBE is a little too far over my
head for now. I've tried to record a macro to insert the function, but my
function is too long for the macro and it won't record. Also, I've tried
typing the function in a different cell (A1) to quot;store itquot; and pasting
special into the column I want but it changes all of the cell references to
#REF. Is there anything else you can think of that I can do to get this
function where I want it?

quot;Abodequot; wrote:

gt; Insert this into a Module. It should be somewhat self explanitory. Change
gt; what you need. To use this you enter =MyFunc(D2) into a cell and it should
gt; work fine.
gt;
gt; quot;mary squot; wrote:
gt;
gt; gt; I'm trying to write a custom function in VBE to determine if my data falls
gt; gt; into one of three categories, active, inactive, or future, for functions that
gt; gt; I use all the time in multiple workbooks. The HELP in Excel isn't much help
gt; gt; for something so complex.
gt; gt;
gt; gt; Here is a stripped down example of my function:
gt; gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
gt; gt;
gt; gt; Any tips on how to get started are much appreciated!

You'll need to insert the function into a VBA Module. Open VBA and on the
left there will be a list of all your open workbooks and the spreadsheets
inside. Rightclick on the workbook you want this to work in and click insert
gt; module Then make sure that Module1 is open and active (doubleclick just ot
be sure) and paste the Function there. That ~should~ work if that is in fact
the problem you are having. To use the fuction in Excel type type
=MyFunc(d2) into a cell and it should all work well. If this isn't your
problem please be a little more specific as to what is going wrong.
Hopefully this all works for you though. VBA functions and subs have helped
me out a BUNCH since I stumbled through learning them.

quot;mary squot; wrote:

gt; I'm still having trouble with this. Maybe VBE is a little too far over my
gt; head for now. I've tried to record a macro to insert the function, but my
gt; function is too long for the macro and it won't record. Also, I've tried
gt; typing the function in a different cell (A1) to quot;store itquot; and pasting
gt; special into the column I want but it changes all of the cell references to
gt; #REF. Is there anything else you can think of that I can do to get this
gt; function where I want it?
gt;
gt; quot;Abodequot; wrote:
gt;
gt; gt; Insert this into a Module. It should be somewhat self explanitory. Change
gt; gt; what you need. To use this you enter =MyFunc(D2) into a cell and it should
gt; gt; work fine.
gt; gt;
gt; gt; quot;mary squot; wrote:
gt; gt;
gt; gt; gt; I'm trying to write a custom function in VBE to determine if my data falls
gt; gt; gt; into one of three categories, active, inactive, or future, for functions that
gt; gt; gt; I use all the time in multiple workbooks. The HELP in Excel isn't much help
gt; gt; gt; for something so complex.
gt; gt; gt;
gt; gt; gt; Here is a stripped down example of my function:
gt; gt; gt; =IF(OR(D2=quot;2quot;,D2=quot;3quot;,D2=quot;4quot;),quot;Activequot;,IF(OR(D2=quot;5quot; ,D2=quot;6quot;DK2=quot;7quot;,D2=quot;8quot;),quot;Inactivequot;,IF(OR(D2=quot;9quot;,D2= quot;10quot;,D2=quot;11quot;),quot;Futurequot;)))
gt; gt; gt;
gt; gt; gt; Any tips on how to get started are much appreciated!