配列の中身をランダムに並べ替える

Function ShuffleArr(targetArr() As Variant) As Variant()

    Dim tmpArr As Variant
    Dim rndNum As Long
    Dim i As Long
    Dim buf As Variant

    '元の配列を変更しないように別の配列に代入して操作
    tmpArr = targetArr
    
    For i = LBound(tmpArr) To UBound(tmpArr)
    
        '交換対象indexを乱数で決める
        rndNum = WorksheetFunction.RandBetween(LBound(tmpArr), UBound(tmpArr))
        '交換
        buf = tmpArr(i)
        tmpArr(i) = tmpArr(rndNum)
        tmpArr(rndNum) = buf
        
    Next

    ShuffleArr = tmpArr
End Function

'動作確認用
Sub shuffleTest()
    Dim arr() As Variant
    arr = Array("a", "b", "c", "d", "e", "f", "g")
    Debug.Print "after:" & Join(ShuffleArr(arr), ",")
    Debug.Print "before:" & Join(arr, ",")
End Sub