Help With Array and Functions in VBA

[excellearnerSF]

Hi Guys,

Very sorry if this is a stupid question but can't find an answer.

I'm trying to pass a selected range in excel into a function
e.g. =arrayresult(A1:E22)

I've tried multiple permutations from threads I found e.g.

function arrayresult(inputarray)
function arrayresult(inputarray() as variant)
function arrayresult(inputarray() as variant) as variant
function arrayresult(inputarray as variant)
function arrayresult(inputarray as variant) as variant

No Matter what I try
arrayresult=inputarray(1,1)
Always populates as #Value please can you advise what I am doing wrong here

 

[Rick Rothstein]

Hi Guys,

Very sorry if this is a stupid question but can't find an answer.

I'm trying to pass a selected range in excel into a function
e.g. =arrayresult(A1:E22)

I've tried multiple permutations from threads I found e.g.

function arrayresult(inputarray)
function arrayresult(inputarray() as variant)
function arrayresult(inputarray() as variant) as variant
function arrayresult(inputarray as variant)
function arrayresult(inputarray as variant) as variant

No Matter what I try
arrayresult=inputarray(1,1)
Always populates as #Value please can you advise what I am doing wrong here

Try it this way...

Function ArrayResul(InputArray As Range)

then assign InputArray to a variant variable within the function itself to turn the range into an array.

 

[excellearnerSF]

Dude your awesome! Much appreciated!