ByRef argument type mismatch

[afndst]

I got from VBA Express : Excel - Function to return random sample, with or without replacement the following function:

Function Sample(Source As Range, Take As Long, Optional Replacing As Boolean = False, Optional Unique As Boolean = False)​

' Function by Patrick Matthews

' Function looks at specified range (Source argument) and returns an array of randomly-
' sampled data from that range. Take argument specifies the number of items in the sample

' Optional argument Replacing indicates whether sampling is done with replacement (i.e.,
' any item in the source may be selected more than once, indicated by True) or without
' replacement (any given item may only be selected once, indicated by False)

' Optional argument Unique indicates whether the samples are drawn from all items in the
' Source range (False), or just from the unique elements (True)

This site also provides the macro below to elaborate game fixtures:

Private Sub Tourney()

Dim arr As Variant
Dim Counter As Long

[d2:e32].ClearContents

arr = Sample([a2:a33], 32)

For Counter = 1 To 16
Cells(Counter * 2, 4) = arr((Counter * 2) - 1)
Cells(Counter * 2, 5) = arr(Counter * 2)
Next

MsgBox "Done"

End Sub

I am trying to make a function that makes the same operation, but I am getting the ByRef type error when computing the function Sample.


Function MYxlTourney(n As Integer)

Dim i As Long
Dim arr As Variant
Dim Player() As Variant

With WorksheetFunction

ReDim Player(1 To n)

For i = 1 To n
Player(i) = "Player " & i
Next i

arr = Sample(Player, 2) ' Error here

MYxlTourney = arr

End With

End Function

 

[GWteB]

You are passing a Variant/Array data type variable (named Player) as the first argument on to the Sample function, where as the Sample function is expecting a Range data type.

 

[afndst]

How can I convert the array to a range?

I tried to define both as range and as variant. In opne case I get the ByRef error and in the other the resilt "VALUE!

Regards

 

[GWteB]

I might have confused you with my previous post, because a Range is not a data type, it's an Excel object type, ie a worksheet range, while a Variant/Array is a VBA data type.
So if you first copy the data from that Array to a worksheet range, you then can pass that range as a parameter to that Sample function.
Code to do as mentioned could look like this.

Function MYxlTourney(n As Integer)

    Dim i As Long
    Dim arr As Variant
    Dim Player() As Variant

    ReDim Player(1 To n, 1 To 1)                    ' <<< initialize a 2-Dimensional array

    For i = 1 To n
        Player(i, 1) = "Player " & i
    Next i

    Dim rng As Range                                ' <<< declare a Range object type variable
    Set rng = ActiveSheet.Range("A1").Resize(n)     ' <<< set reference to desired worksheet range and resize to fit
    rng.Value = Player                              ' <<< copy data from 2-D array to worksheet range

    arr = Sample(rng, 2)                            ' <<< invoke your function

    MYxlTourney = arr

End Function

 

[afndst]

Hello

It works:

Thank you very much

Regards

 

[GWteB]

You're welcome and thanks for letting me know.