Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with using custum functions
Hi every1!!!
I know that if you create a custum function you can return avalue and take in some parameters as well. So far i have no problems with this. My problem is could i return more then one value, and not to the same cell, that this function is being called from. Example: This function i need is used on X number or rows. (1000 so far) Maybe all the rows will have data and maybe not,its dynamic. So,lets say we figure this out on the data entered on line 1. This function is called from column D1, and it takes in parameters A1 & B1. It does a few calculations, and returns the result to calling cell D1,let say it returns 34. so it looks like this now: A1 B1 C1 D1 30 4 -- 34 Could i also return another value from that function to cell C1 for example something like 17. so it looks like this: A1 B1 C1 D1 30 4 17 34 and so one for the next rows that have values, the same thing would be done as well.(dynamiclly, as needed) If this is possible how would you do this from VBa, and if its not, is there a way to do-it anyways. Please let me know, i just cant figure-out how to copy data into another cell from a dynamic custum function... Any help or suggestion will be very much appreciated. thx again, PAtrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with using custum functions
In general, the function can only return a value to the cell in which it is
locations. It can not change anything about another cell. It can not change formatting for any cell. The exception (and it really isn't an exception) is a multi-cell array formula. You enter the formula in multiple **contiguous** cells simultaneously and the function returns an array as a result. The elements of the array are returned, one to each cell. So in your specific example, you can update C1 and D1 if you array enter you formula in C1:D1 and it returns an array Public Function myFunc(rng1 as Range, rng2 as Range) myFunc = array(rng1(1)*rng2(1),rng1(1)/rng2(1)) End Function Selection C1:D1, in the formula bar enter =MYFUNC(A1,B1) Entered with Ctrl+Shift+Enter returns values to both C1 and D1. -- Regards, Tom Ogilvy "Patrick" wrote in message ... Hi every1!!! I know that if you create a custum function you can return avalue and take in some parameters as well. So far i have no problems with this. My problem is could i return more then one value, and not to the same cell, that this function is being called from. Example: This function i need is used on X number or rows. (1000 so far) Maybe all the rows will have data and maybe not,its dynamic. So,lets say we figure this out on the data entered on line 1. This function is called from column D1, and it takes in parameters A1 & B1. It does a few calculations, and returns the result to calling cell D1,let say it returns 34. so it looks like this now: A1 B1 C1 D1 30 4 -- 34 Could i also return another value from that function to cell C1 for example something like 17. so it looks like this: A1 B1 C1 D1 30 4 17 34 and so one for the next rows that have values, the same thing would be done as well.(dynamiclly, as needed) If this is possible how would you do this from VBa, and if its not, is there a way to do-it anyways. Please let me know, i just cant figure-out how to copy data into another cell from a dynamic custum function... Any help or suggestion will be very much appreciated. thx again, PAtrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with using custum functions
I'm working on something similar.
You'll need to create a user-defined array function. Your function should return an array containing the values you want to write to C1 and D1. To enter the function into the spreadsheet, select both C1 and D1, type in the function, and instead of hitting Enter use Ctrl+Shift+Enter. Search this group for "User Defined Array Function" for more info. Search Excel's Help for general info on array functions. Good Luck, Dave Patrick wrote: Hi every1!!! I know that if you create a custum function you can return avalue and take in some parameters as well. So far i have no problems with this. My problem is could i return more then one value, and not to the same cell, that this function is being called from. Example: This function i need is used on X number or rows. (1000 so far) Maybe all the rows will have data and maybe not,its dynamic. So,lets say we figure this out on the data entered on line 1. This function is called from column D1, and it takes in parameters A1 & B1. It does a few calculations, and returns the result to calling cell D1,let say it returns 34. so it looks like this now: A1 B1 C1 D1 30 4 -- 34 Could i also return another value from that function to cell C1 for example something like 17. so it looks like this: A1 B1 C1 D1 30 4 17 34 and so one for the next rows that have values, the same thing would be done as well.(dynamiclly, as needed) If this is possible how would you do this from VBa, and if its not, is there a way to do-it anyways. Please let me know, i just cant figure-out how to copy data into another cell from a dynamic custum function... Any help or suggestion will be very much appreciated. thx again, PAtrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custum Cell Format - display 0 (zero) when cell is empty | New Users to Excel | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Custum Excel RMB Context Menu | Excel Programming | |||
excel functions and User defined functions | Excel Programming |