VBA Array Output for math function

aircrew

New Member
Joined
May 21, 2016
Messages
6
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Maybe something like this:

Code:
[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
AB
124,9,16
23
34
Sheet1
Cell Formulas
RangeFormula
B1=GetMathAnswer(A1:A3)
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
[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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top