Custom function in VBE
 

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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!

Custom function in VBE
 

Function Status(rCell as Range) as String
Select Case rCell.Value
case 2 to 4 : Status = "Active"
case 5 to 8 : Status = "Inactive"
case 9 to 11 : Status = "Future"
case else: Status = "Unknown"
End Select
End Sub

HTH
--
AP

"mary s" a écrit dans le message de news:
...
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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!

Custom function in VBE
 

If you'd like to write more functions yourself, read a book or follow a tutorial. There are many.
Here's one:

http://www.datapigtechnologies.com/ExcelMain.htm


See the "Getting started with VBA" section. Don't forget to switch sound on.

--
Kind regards,

Niek Otten

"Ardus Petus" wrote in message ...
| Function Status(rCell as Range) as String
| Select Case rCell.Value
| case 2 to 4 : Status = "Active"
| case 5 to 8 : Status = "Inactive"
| case 9 to 11 : Status = "Future"
| case else: Status = "Unknown"
| End Select
| End Sub
|
| HTH
| --
| AP
|
| "mary s" a écrit dans le message de news:
| ...
| 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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))
|
| Any tips on how to get started are much appreciated!
|
|

Custom function in VBE
 

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.

"mary s" wrote:

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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!

Custom function in VBE
 

The "numbers" 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.

"Ardus Petus" wrote:

Function Status(rCell as Range) as String
Select Case rCell.Value
case 2 to 4 : Status = "Active"
case 5 to 8 : Status = "Inactive"
case 9 to 11 : Status = "Future"
case else: Status = "Unknown"
End Select
End Sub

HTH
--
AP

"mary s" a écrit dans le message de news:
...
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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!

Custom function in VBE
 

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 "store it" 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?

"Abode" wrote:

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.

"mary s" wrote:

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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!

Custom function in VBE
 

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
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.

"mary s" wrote:

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 "store it" 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?

"Abode" wrote:

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.

"mary s" wrote:

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="2",D2="3",D2="4"),"Active",IF(OR(D2="5" ,D2="6"DK2="7",D2="8"),"Inactive",IF(OR(D2="9",D2= "10",D2="11"),"Future")))

Any tips on how to get started are much appreciated!