excel - Using arrays inside functions in VBA -

i can't seem find answer question: can create user-defined function in excel vba uses array sub-procedure, , in excel use function return value?

from site (http://www.cpearson.com/excel/passingandreturningarrays.htm) found example:

sub aaatest()     dim staticarray(1 3) long     dim result long     staticarray(1) = 10     staticarray(2) = 20     staticarray(3) = 30     result = sumarray(arr:=staticarray)     debug.print result end sub  function sumarray(arr() long) long     '''''''''''''''''''''''''''''''''''''''''''     ' sumarray     ' sums elements of arr , returns     ' total.     '''''''''''''''''''''''''''''''''''''''''''     dim n long     dim total long     n = lbound(arr) ubound(arr)         total = total + arr(n)     next n     sumarray = total end function 

how should use function (sumarray) in excel cell?

what arguments required?

i think i've tried every possible combination of things, can't seem working. i want cell udf return value (which total).

thanks in advance!

---

added more info

thanks answers! i'll try rephrase question different angle. want achieve user-defined function can use in excel cell, example "=myownfunction(n,m)" takes 2 parameters input: value n , value m. using these values function myownfunction finds closest "combination" of values n , m 3d array specified , generated in separate sub() , returns corresponding value 3rd row of third axis of 3d array. first , second row of 3d array n , m respectively.

so make task simple possible, want happen?

1. generate 3d array dimensions of 100*100*3 in subprocedure , fill values using functions not relevant problem here.
2. generate function (myownfunction) uses data in generated 3d array , finds closest combination or pair of values n , m, user-given inputs.
3. i can insert =myownfunction(n,m) n , m of choice in excel cell , corresponding result. correspondence mean example 3darray(14,14,1) represents value of n parameters (14,14) , 3darray(14,14,2) represents value of m parameters (14,14) , value of (14,14,3) 1 want returned. function myownfunction goes through 3darray values , finds nearest match of user-given input values.

the reason why don't include creation of 3d array in same function it's quite large , takes few seconds generate. want use data of generated 3darray have calculate once.

i hope makes sense.

like usa president said: yes can!

• create module , put sumarray in module (it don't work if function not in module)
• you can't pass array parameter in because excel won't understand

you have choices:

if it's same subprocedure: retrieve directly array in sumarray function

if datas excel sheet: pass range in function function sumarray(byval rng range) long , use range array

other cases: precise how decide array have use, , how it