Re: Range Parameters in Function
- From: Tushar Mehta <tmUnderscore200310@xxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 2 Feb 2006 22:11:15 -0500
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
In article <1138935467.912091.172480@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
luongmq@xxxxxxxxx says...
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
- References:
- Range Parameters in Function
- From: luongmq
- Range Parameters in Function
- Prev by Date: Range Parameters in Function
- Next by Date: Create Toolbar with VBA
- Previous by thread: Range Parameters in Function
- Next by thread: Create Toolbar with VBA
- Index(es):
Relevant Pages
|
