## 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.