custom function

[kalikoi]

i wrote a custom function earlier as follows

function mycustom(stra,strb)
dim c
c=stra*strb
mycustom=c
end function


in sheet when i used this as =mycustom(A1,D1) i used to get a value..

but now am returning an array as the output from my function as follows

function mycustom(stra,strb)
dim c
c=array("1","2","3")
mycustom=c
end function

in sheet when i used this as =mycustom(A1,D1) am not getting the required result


can any one help me on this..

Regards
Kalyan

 

[Trevor G]

Try this

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> myCustom(<SPAN style="color:#00007F">ByVal</SPAN> Var1, <SPAN style="color:#00007F">ByVal</SPAN> Var2)<br>myCustom = Var1 * Var2<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[Peter_SSs]

What is your new function suposed to do?
You are feeding A1 and D1 into the function as stra and strb but not using them anywhere in you function.

 

[kalikoi]

am using them as inputs for a query that is used in database side ....

set rs=cnn.execute("Select * from table where c1='" & stra & "' and c2='" strb")
finalval=rs.getstring(2,,",",",")
arrfinalval=split(finalval,",")
myCustom=arrfinalval

 

[Ruddles]

... now am returning an array as the output from my function... in sheet when i used this as =mycustom(A1,D1) am not getting the required result

What is your desired result? Are you trying to get the array into a single cell?

Your function returns an array. A formula (or function call) in a worksheet cell can only change the contents of that worksheet cell.

What do you want that cell to contain when the function returns an array with more than one value in it?

 

[kalikoi]

the array returned should be dumped into the sheet

 

[Ruddles]

I don't think you read my post very carefully, or maybe I didn't explain it very well.

Your function returns an array. A formula (or function call) in a worksheet cell can only change the contents of the worksheet cell in which the function call appears.

If you want your data to appear in more than one cell, then you cannot do this with a function call which only appears in one cell. You cannot "dump an array into a sheet" via a function call. The result of a function call can only appear in the cell from which it was invoked. Even when a function returns an array, if you call it from a worksheet cell then the returned value can only appear in that cell, and I'm fairly sure you can't place an array in a single cell.

The only way you might do this is to Join() the elements of the array and return a String to the worksheet cell.

You will need to change your code so that instead of invoking a function which returns a value, you call a subroutine (Sub) which writes to the worksheet directly, maybe using the Range.CopyFromRecordset command.

 

[kalikoi]

so u mean to say that if i change the code as follows

sub mycustom(stra,strb)
dim c
c=array("1","2","3")
activecell.value=c
end sub

in the sheet i use it as somecell=mycustom(A1,B2)

will this work?

 

[Ruddles]

No, you can't assign the value of a Sub to a cell because a Sub doesn't return a value. You have to run or 'execute' a Sub, for example by calling it from a command button on the worksheet or by using an event handler to run it. The Sub can then write to the worksheet.

You have two options:-

A function can return a value and you can put =functionname in the worksheet, but it can only return a value to that one cell.

A subroutine cannot return a value and you can't call it from a worksheet cell, but you can call it from an event handler which is triggered, for example, by a change to a worksheet cell. Subroutines can be called from command buttons and they can write directly to a worksheet.

 

[kalikoi]

i have to fulfill the requirement by writing the code in addin(*.xla) file

so can u help me on this...

that is when the user opens a sheet he adds the myaddin from the menu

as soon as the my-addin is installed a custom menu will be created which is loaded with items from the database

in one cell(A1) he enters a value...click on the custom menu to add an item and that will be added to B1(assume)

now in C1 if i've to specify some function name as =getvalue that returns an array(say 1,2,3)

so the final output should be C1(1)...D1(2) and E1(3)

earlier response is highly appreciated..

Regards,
Kalyan