Help with Returning muliple values from a function

u123136 · Sep 14, 2008

Please help,
I need to return multiple values from a function with for inputs:
function name(i1,i2,i3,i4) As Variant
Dim d(5)
Dim other variables
function calulation
name = Array(d1,d2,d3,d4,d5)
end function.
This code does not work as I need to horizontaly return each value each column next to each other.
Thanks,
Bill

theozz · Sep 14, 2008

What's Last want?
It Seems step2
but U made step 1 and think step 3 so need step 2 ?

GMorris · Sep 14, 2008

theozz said:
What's Last want?
It Seems step2
but U made step 1 and think step 3 so need step 2 ?

huh?

BThiede · Sep 14, 2008

An Excel formula being used as a UserDefinedFormula can only return a result in the cells where the formula is located, so your options are:

1) pass in an extra variable that tells which "Column" you want to get and paste the formula across your columns with an incrementing "Column" (or even use the =Column() formula)

2) have your return value be a long string that is separated into different sections and then use formulas in the other columns to grab their section out of the result.

This is pretty vague, but it's hard to tell what you want your formula to do. Hopefully this will at least give you enough to ask some more questions.

Good luck,
Brendon

u123136 · Sep 14, 2008

I did not make it clear.
I have a custom function that presently returns one value from 4 input variables works just fine.
I want to expand that function to return 5 values with each value being put in adjacent cells by columns.
I modified the function so I thought it would return the 5 values in adjacent columns but it still only returns the 1st value in its column and no values in the adjacent columns.
Let us say the function name is getvalues the present code I tried in returning the 5 values is:

Function getvalues(variable 1 as Double, ...,variable 4 As Double) As Variant
Dim d(5)
Dim other variables
do calculations
getvalues = (d1,d2,d3,d4,d5)
End Function

theozz · Sep 14, 2008

Hi ! Here Suggest to you <table style="border-collapse: collapse;"colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks. DataSheet= Sheet2</td></tr><tr><td width=30 align="Right"><table cellspacing="1" width=30 rowspan=12><tbody bgcolor="#ffffff"><tr height=18><td></td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">1 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">2 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">3 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">4 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">5 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">6 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">7 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">8 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">9 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">10 </td></tr><tr height=18><td bgcolor=#dcdcdc align="Right">11 </td></tr></table></td><td width=580.8><table bgcolor=#939393 cellspacing="1" colspan=7 rowspan=5 border="0" width=580.8><col width=93.6><col width=19.2><col width=93.6><col width=93.6><col width=93.6><col width=93.6><col width=93.6><tbody bgcolor="#ffffff"><tr height=18><td bgColor=#dcdcdc align="center" style="font-family: verdana">A</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">B</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">C</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">D</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">E</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">F</td><td bgColor=#dcdcdc align="center" style="font-family: verdana">G</td></tr><tr height=18><td bgColor=#ffffff align="Right">20</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">10</td><td bgColor=#ffcc00 align="Right">22.5</td><td bgColor=#ffcc00 align="Right">27.5</td><td bgColor=#ffcc00 align="Right">32.5</td><td bgColor=#ffcc00 align="Right">17.5</td></tr><tr height=18><td bgColor=#ffffff align="Right">25</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">30</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">35</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">40</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tbody></table><table bgcolor=#939393 cellspacing="1" colspan=7 rowspan=6 border="0" width=580.8><col width=93.6><col width=19.2><col width=93.6><col width=93.6><col width=93.6><col width=93.6><col width=93.6><tbody bgcolor="#ffffff"></tr><tr height=18><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">200</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">100</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">250</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">225</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">300</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">275</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">350</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">325</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></tr><tr height=18><td bgColor=#ffffff align="Right">400</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffcc00 align="Right">175</td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td><td bgColor=#ffffff align="Left"></td></table></td></tr></table> <TABLE style="BORDER-COLLAPSE: collapse; " cellSpacing=0 border=6><colspan=5 width=800 rowspan=3 height=54><TBODY><tr height=24><td align=center colSpan=5>Used Formula ...(With Running MicrosoftExcel Ver 2003) If Pink Formula that is FormulaArray !!! WithOut {} Input Formula and Press Control+Shift+Enter</td></tr><tr height=24><td align=center width=4% bgColor=#d3d3d3>No</td><td align=center width=9% bgColor=#d3d3d3>Addr'</td><td align=center width=65% bgColor=#d3d3d3> If use below Formula, You'll Get Result as Right</td><td align=center width=15% bgColor=#d3d3d3>Result</td><td align=center bgColor=#d3d3d3>Formula's</td></tr><tr height=20><td align=center >1</td><td align=center bgColor=#ffcc00>C1:G1</td><td bgColor=#ffcc00>=x(A1,A2,A3,A4) Select All Range and Input Formula </td><td align="Right" >10</td><td Align=right><FORM name=PrNames1><INPUT onclick='window.clipboardData.setData("Text","=x(A1,A2,A3,A4)");' type=button value="Do Copy" name=MyNames1></FORM></td></tr><tr height=20><td align=center >2</td><td align=center bgColor=#ffcc00>C7:C11</td><td bgColor=#ffcc00>=y(A7,A8,A9,A10) Select All Range and Input Formula </td><td align="Right" >100</td><td Align=right><FORM name=PrNames2><INPUT onclick='window.clipboardData.setData("Text","=y(A7,A8,A9,A10)");' type=button value="Do Copy" name=MyNames2></FORM></td></tr><tr height=24><td colSpan=5> If this is not wanted answer, give more detail of Questions How about this suggest? </td></tr><tr height=24><td colSpan=5> Like This ??? </td></tr></TBODY></TABLE> Option Explicit Function x(Get1 As Double, Get2 As Double, Get3 As Double, Get4 As Double) Dim Answ(1 To 5) Answ(1) = (0 + Get1) / 2 Answ(2) = (Get1 + Get2) / 2 Answ(3) = (Get2 + Get3) / 2 Answ(4) = (Get3 + Get4) / 2 Answ(5) = (Get4) / 2 x = Answ End Function Function y(Get1 As Double, Get2 As Double, Get3 As Double, Get4 As Double) Dim Answ(1 To 5, 1 To 1) Answ(1, 1) = (0 + Get1) / 2 Answ(2, 1) = (Get1 + Get2) / 2 Answ(3, 1) = (Get2 + Get3) / 2 Answ(4, 1) = (Get3 + Get4) / 2 Answ(5, 1) = (Get4) / 2 y = Answ End Function Any Bug Or Ask ... Mail Or Post Reply

BThiede · Sep 14, 2008

If your array is being properly returned then you should be able to get it to work right if you have all five cells selected at the same time, and then type in the formula, and then instead of pressing enter when you're done, press ctrl+shift+enter

Good luck,
Brendon

mikerickson · Sep 14, 2008

It sounds like your UDF (like Excel's FREQUENCY) needs to be used as an array function. For example,

Code:

Function CountToFive() as Variant
     CountToFive = Array(1,2,3,4,5)
End Function

Select A1:E1, type =CountToFive() in the formula bar and press Ctrl-Shift-Enter (Cmd+Return for Mac).

Alternatly, in A2, enter =INDEX(CountToFive(), 1, Column()) (no CSE) and drag right.

u123136 · Sep 14, 2008

Thanks All,
Nothing is working. I had previously tried most of the suggested item with no luck. It seems that as one said you can not return values out of the cell that the function is being called. I did not mention the function is in VB if that makes a difference.
Thanks

Norie · Sep 14, 2008

Bill

What are you actually trying to achieve?

Have you considered Edit>Paste Special... with the Transpose option?

Or even the TRANSPOSE worksheet function, or other worksheet functions.

Help with Returning muliple values from a function

u123136

New Member

theozz

Active Member

GMorris

Board Regular

BThiede

New Member

u123136

New Member

theozz

Active Member

BThiede

New Member

mikerickson

MrExcel MVP

u123136

New Member

Norie

Well-known Member

Similar threads

Share this page

Help with Returning muliple values from a function

New Member

Active Member

Board Regular

New Member

New Member

Active Member

New Member

MrExcel MVP

New Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock