Range Parameters in Function

[luongmq]

Greetings All,

I am trying to understand why this function does not work. Any
suggestions would greatly be appreciated it.

Public Function GetArray(ByVal rng1 As Range, ByVal rng2 As Range) As
String
' rng1 is a 1-cell range; rng2 > 1-cell range
Dim vReturn As Variant
Dim oSecret As Object
Dim i As Integer

On Error GoTo ErrorHandler
Set oSecret = CreateObject("SomeDLL.Class")
vReturn = oSecret.GetSecretArray(rng1.Cells.Value)

GetArray = vbNullString
For i = 0 to UBound(vReturn) Step 1
GetArray = GetArray & vReturn(i) ' this is line is fine
rng2.Cells(i + 1).Value = vReturn(i) ' this line kicks the function
out; rng2.Offset(i) does not work either, rng2.<whatever>.FormulaR1C1
does not help either
Next i
Exit Function
:ErrorHandler
MsgBox Err.Description
End Function

 

[Tushar Mehta]

The only thing you can do in a user defined function (UDF) is return a
value.

That value can be an array of values. Suppose you want the returned values
in a range of contiguous cells in a column, say C4:C14. Then, select C4:C14
and array enter the formula =GetArray(A4:A14).

GetArray should look like:

Public Function GetArray(ByVal rng1 As Range)
Dim oSecret As Object

On Error GoTo ErrorHandler
Set oSecret = CreateObject("SomeDLL.Class")
getarray=application.worksheetfunction.transpose ( _
oSecret.GetSecretArray(rng1.Cells.Value))
ErrorHandler:
getarray=Err.Description
End Function

You can use application.caller to figure out if the range in which the
function is entered is a single row or a single column or something else
altogether and accordingly adjust the returned value. For example, if the
cells are contiguous in a single row then don't do the transpose.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just ENTER.
If done correctly, XL will display curly brackets { and } around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions