Function: Random Concatenation

The Problem

A reader at MrExcel.com wanted to be able take a range with names in each cell, randomize them, then concatenate them into a single cell. 

The Solution

Below is the function I provided, slightly modified.

Option Explicit

Type StrWVal
    str As String
    val As Double
End Type

Function RandomConcat(rng As Range, Optional sep As String, Optional NumToUse As Double)
    Const ArrLBound = 1
    Dim ArrUBound As Double
    Dim UseNum As Double
    Dim arr() As StrWVal
    Dim sorttemp As StrWVal
    Dim outtemp As String
    Dim i As Double
    Dim j As Double
    Dim cel As Range
    ArrUBound = rng.Cells.Count
    ReDim arr(ArrLBound To ArrUBound)
    If NumToUse = 0 Then
        UseNum = ArrUBound
    Else
        UseNum = NumToUse
    End If
    i = 0
    For Each cel In rng
        i = i + 1
        arr(i).str = cel.Value2
        arr(i).val = Int((UBound(arr) - LBound(arr) + 1) * Rnd + LBound(arr))
    Next cel
    For i = ArrLBound To ArrUBound - 1
      For j = i + 1 To ArrUBound
        If arr(i).val > arr(j).val Then
          sorttemp = arr(i)
          arr(i) = arr(j)
          arr(j) = sorttemp
        End If
      Next j
    Next i
    For i = ArrLBound To UseNum
        outtemp = outtemp & IIf(Len(outtemp) > 0, sep, "") & arr(i).str
    Next i
    RandomConcat = outtemp
End Function

Notes on the Solution

1. It's a bit longer than my usual functions.  That is because I did it on the fly and would usually split out things like the bubble sort.
2. There may be some extraneous Dim statements and the like.  (oops, while writing this I found about 4 lines of flotsam.) That is also because I did it on the fly and did not take time to scrub and clean.
3. As usual, I took two assumptions and turned them into parameters
• The Separator allows the user to indicate whether to use a colon, dash, space, multiple characters, or nothing at all (the default.
• The Number to use allows the user to specify how large a subset to include.  The default is all.
4. My data naming on this reflects I did it on the fly.  The on-the-fly style seems to fit where I posted it.
5. The constant ArrLBound and variable ArrUBound are something I had not used before.  
1. Once I saw the number of times I used Ubound(arr), ArrUBound made sense.  
2. When I considered it is cheaper, from a maintenance perspective, to use a constant at the top than to search through code for them, ArrLBound made sense, too.