Returning an array from a VBA function to Excel spreadsheet

Hi markiv

Here is the code for a Function that returns an array. You of course have to select an array of cells before enntering the function and then enter with Ctrl+Shift+Enter.

Hope it helps
Dave

OzGrid Business Applications

Function Threshold(argRange As Range, Thresh As Variant)
Dim i As Long, j As Integer
Dim inputRows As Long, inputColumns As Integer
Dim answerarray
inputRows = argRange.Rows.Count
inputColumns = argRange.Columns.Count
ReDim answerarray(1 To inputRows, 1 To inputColumns)
For i = 1 To inputRows
For j = 1 To inputColumns
If argRange.Cells(i, j) >= Thresh Then
answerarray(i, j) = 1
Else
answerarray(i, j) = 0
End If
Next j
Next i
Threshold = answerarray
End Function

Hope it helps

OzGrid Business Applications

See:

Sub try_array()
Dim data As Variant
data = Range("A2:A6").Value
Range("C2:C6").Value = data
End Sub

This was the most simple I could think of...
OK?
R.

Re: Just populates the top-left cell.

Hi Dave,
I have tried it but it only populates one cell (the top left one).

Let me clarify myself a bit more.
The function that I writing is a User-defined function (one that can be invoked thru the Insert Menu of the Excel Toolbar, Function sub-menu)and that function requires an output as an array.

Hope I am able to explain my situation.

Thanks for the help, I appreciate it.
markiv.

Re: The solution does not work in this case.

Hi Robert,

Thanks for the help, I appreciate it. But the solution does not work in my case.

Let me clarify myself a bit more.
The function that am I writing is a User-defined function (one that can be invoked thru the Insert Menu of the Excel Toolbar, Function sub-menu)and that function requires an output as an array.

So, I wouldnt be able to use functions such as Range to produce an output as it gives me an error.

I hope I am able to explain my problem.

markiv.

Re: Just populates the top-left cell.

Hi again markiv

For my array example to work put the numbers 1 to 10 in cells A1:A10 then highlight cells B1:B10 now put: =Threshold(D7:D16,4) in the activecell (B1) and push Ctrl+Shift+Enter. The results won't mean anything but the code should help.

DaveOzGrid Business Applications

Re: Thanks Dave

Hi Dave,
Your function works great.
Thanks for your help. I appreciate it.

Warm Regards,
markiv.