Pass a named range into function issue

[demetre]

Afternoon All

I am wanting to pass a dynamic named range into a function. The function is a 2d array. So I have for instance a 2d array in column A on worksheet named 'Hello', and I have a dynamic named range labelled 'Array1' from the worksheet. How would I call this function using this named range.

Here is an skeleton example of the code... I know my code is ok, but it is just this calling of the dynamic range that is troubling me...

[vba]

Function XXXX(ArrayIn() As Double, rStart As Range, T As Double, S As Double, v As Double) As Double

'Some code here....
Dim ....
Dim ... etc...

End Function

[/vba]
thanks for the help

[Paul_Hossler]

Could you pass ArrayIn as a Range, and then put it into an array in your function?

Paul

[demetre]

Paul

thanks for your reply

I was hoping to try and see if I can pass it in the function as is... with a a Sub above it.... but it looks like the method I am proposing is not possible... so I guess I would either need to specify outside the function the array, and as you kindly suggested pass it into the function... or possibly not allow for it to be a dynamic range... perhaps... hmmm...

thanks

D.

[mikerickson]

Do you want to pass the named range Array1 as the first or second argument of that function.

You may want to modify the function so that the first argument can be either a Range or an Array. Here's one example of that.
[VBA]Function myFunction(ByVal myArgument As Variant)
Select Case TypeName(myArgument)
Case Is = "Range"
If myArgument.Rows.Count = 1 Then
If myArgument.Columns.Count = 1 Then
Rem single cell
myArgument = Array(myArgument.Value)
Else
Rem myArgument is single column
myArgument = Application.Transpose(myArgument.Value)
End If
Else
If 1 < myArgument.Columns.Count Then
Rem rectangular range
Exit Function
Else
Rem myArgument is single row
myArgument = Application.Transpose(Application.Transpose(myArgument))
End If
End If
Case Is = "Double()", "Variant()"
Rem do nothing
Case Else
MsgBox "Un-handled data type: " & TypeName(myArgument)
End Select
myFunction = UBound(myArgument)
End Function[/VBA]

[Paul_Hossler]

Well, a NameRange is still a Range

How about something like this?

[vba]
Sub TopSub()
Dim r As Range

Set r = Worksheets("Sheet1").Range("A1:C3")

r.Name = "MyRange"


MsgBox XXXX([MyRange], Worksheets("Sheet1").Range("A1"), 1, 2, 3)


End Sub

Function XXXX(ArrayIn As Variant, rStart As Range, T As Double, S As Double, v As Double) As Double
XXXX = Application.WorksheetFunction.Sum(ArrayIn)
End Function
[/vba]

where the [ ] forces evaluation

Paul

[demetre]

Paul and Mike

thanks for your replies i'll go back and redo with your suggestions.


D.