Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Custom function to simplify Index(match)) formula | Excel Discussion (Misc queries) | |||
Custom Function | Excel Discussion (Misc queries) | |||
Custom function returning VALUE error | Excel Discussion (Misc queries) |