VBA Array Output for math function

[aircrew]

Hello, please help me with my problem. I need to create a function in VBA that will take an array as an argument and will return array containing squared values of the input array. That is, if A1=2 and B1 = 3, my function should return two cells containing numbers 4 and 9. Thanks for any advice

 

[mrmmickle1]

Maybe something like this:

[COLOR=#0000ff]Function[/COLOR] GetMathAnswer(r [COLOR=#0000ff]As[/COLOR] Range)
[COLOR=#0000ff]  Dim[/COLOR] a [COLOR=#0000ff]As Variant[/COLOR]
[COLOR=#0000ff]  Dim [/COLOR]str [COLOR=#0000ff]As String[/COLOR]
  a = r
[COLOR=#0000ff]  For[/COLOR] i = 1[COLOR=#0000ff] To UBound[/COLOR](a)
      a(i, 1) = a(i, 1) * a(i, 1)
      str = str & a(i, 1) & ","
[COLOR=#0000ff]  Next[/COLOR] i
  
  GetMathAnswer = Mid(str, 1, Len(str) - 1)
[COLOR=#0000ff]End Function[/COLOR]

Excel 2010
	A	B
1	2	4,9,16
2	3
3	4
Sheet1

Cell Formulas
Range	Formula
B1	=GetMathAnswer(A1:A3)

 

[aircrew]

Thank you, thats very helpful however, I need the output as a range as well (for greater clarity). Any idea how to modify that function to satisfy that? Thank you for help anyway!

 

[Rick Rothstein]

I need to create a function in VBA that will take an array as an argument and will return array containing squared values of the input array. That is, if A1=2 and B1 = 3, my function should return two cells containing numbers 4 and 9.

Why do you want to create a UDF that takes an array, processes it, and then outputs an array (which would require you to preselect the output cells and use CTRL+SHIFT+ENTER to commit the UDF to all of the selected cells)? That would be kind of inefficient when you can just use a simple, normally entered Excel formula and copy it down or across as needed. For example, if A1=2 and B1=3, but this formula in A2 and copy it across to B2...

=A1*A1

 

[aircrew]

Well the actual function which I need is different and more complicated, square function was just simplification. And the input on which the function should be applied is spread over many sheets. So the function returning a range of values would be a great help.

 

[Rick Rothstein]

Well the actual function which I need is different and more complicated, square function was just simplification. And the input on which the function should be applied is spread over many sheets. So the function returning a range of values would be a great help.

You originally said "I need to create a function in VBA that will take an array as an argument"... input from multiple sheets is not an array in Excel, an Excel array is a contiguous range of cells. We can create a function where you can specify individual ranges and those can be combined into a VB array within the function, but I think there is a limit to the number of individual arguments that a function can take. You also said "...and will return array containing squared values of the input array"... where did you envision the array output from the function going, across multiple sheets? If so, I am almost positive that cannot be done. I have an idea... drop the simplification (it is almost always a bad idea to simplify a problem for us as the solution you get rarely can be scaled up to your actual problem) and tell us your exact needs.

 

[aircrew]

[TABLE="width: 500"]
<tbody>[TR]
[TD]x[/TD]
[TD]polynomial[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]
Ok, sorry for that. I need a function with an array as an argument that will return polynomial of that argument - also an array, in each cell polynomial function of a value in particular cell of an array argument. I included table to depict the situation (for squared values - i can hopefully adjust formula then). I need formula so that if i type =polynomial(A2:A4) into B2:B4 and hit CTRL+SHIFT+ENTER it returns 4,9,16 into B2:B4. Thank you for any advice

 

[mikerickson]

Better than a UDF, the Excel native array formula =A2:A4*A2*A4 will do what you want.

If you want something more complicated than a simple squaring function, describe what it is that you want.
UDF's are very resource intensive (even more than array formulas) and there is a reasonable chance that what you want can be done with native Excel.

 

[mrmmickle1]

When I originally posted my solution I thought about a few other options as well. What is your end goal aircrew? We can assist you better if you are clear and concise.

 

[aircrew]

Hopefully I described problem in my last post. Your solution was helpful, I would just prefer that the output was an array of values and not single cell containing all values. I know I can do it using excel only, but I need to use that formula very often and it would save me significant time.