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]

What about this:

Excel 2010
	A	B
1	2	4
2	3	9
3	4	16
Sheet2

Cell Formulas
Range	Formula
B1:B3	{=GetMathAnswer($A$1:$A$3)}
Press CTRL+SHIFT+ENTER to enter array formulas.

[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 To [COLOR=#0000ff]UBound[/COLOR](a)
      a(i, 1) = a(i, 1) * a(i, 1)
[COLOR=#0000ff]  Next [/COLOR]i
  
  GetMathAnswer = a
[COLOR=#0000ff]End Function[/COLOR]

Don't really see how it's any different than A1*A1.....

 

[aircrew]

Thank you very very much, thats exactly what I needed. As I said I need to adjust it for a more complex function, not just squared and this is very helpful. Thanks

 

[Rick Rothstein]

Thank you very very much, thats exactly what I needed. As I said I need to adjust it for a more complex function, not just squared and this is very helpful. Thanks

I think the point of mrmmickle1's final comment (and my comment in Message #4) is that no matter how complex the function, you should be able to create an Excel formula using native worksheet functions that can be copied into each output cell which will do its calculations more efficiently than an array-entered UDF that outputs the same values into those same cells.