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 SolutionBelow is the function I provided, slightly modified.
str As String
val As Double
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
UseNum = NumToUse
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))
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
For i = ArrLBound To UseNum
outtemp = outtemp & IIf(Len(outtemp) > 0, sep, "") & arr(i).str
RandomConcat = outtemp
Notes on the Solution
- 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.
- 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.
- 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.
- Once I saw the number of times I used Ubound(arr), ArrUBound made sense.
- 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.