REARRANGE STRINGS IN ARRAY !!!!

[jay dean]

Hello -

I have 2 questions:

1. Array Str()is a 1 x 1 array containing strings. How do I randomly
rearrange the token positions? For example: if Str(0)= Ty, Str(1)=Jo,
Str(2)=Ba ... Str(ubound(Str))=Zo, then after running the code, the
tokens will have different indexes where for example, Str(0) will now
contain Ba, Str(1)=Ty...etc

2. If I already have Excel 2003 installed on my PC, will installing
Excel 2007 "over" the 2003 cause any issues with my older files compiled
in Excel 2003?

Thanks
Jay

 

[ozgrid.com]

Hi Jay

This should get you started;

Sub Test()
Dim str
Dim lValue As Long, lValue2 As Long


str = Array("one", "two", "three", "four", "five", "six")

lValue = 0
lValue2 = Int((5 * Rnd) + 1)

str(lValue) = Choose(lValue2, "one", "two", "three", "four", "five", "six")
MsgBox str(lValue)
End Sub

 

[ozgrid.com]

Another way

Sub Test()
Dim str, rcell As Range
Dim lValue As Long, lValue2 As Long


str = Array("one", "two", "three", "four", "five", "six")
MsgBox str(0)
With Range("A1:A" & UBound(str) + 1)
.Cells = Application.Transpose(str)
.Offset(0, 1).FormulaR1C1 = "=RAND()"
.Resize(.Rows.Count, 2).Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlNo
End With


For Each rcell In Range("A1:A" & UBound(str) + 1)
str(rcell.Row - 1) = rcell
Next rcell

MsgBox str(0)
End Sub

 

[Chip Pearson]

You don't necessarily need to re-arrange the array. Instead, you could
simply read the elements of the array in arbitrary order, without
replacement. That is, get an array of N unique random numbers between
the lower bound upper bound of your array and then use these values to
access the elements in the array. Since you are reading the elements
in random order, the effect is the same as actually rearranging the
array order, but faster.

I have code at http://www.cpearson.com/excel/randomNumbers.aspx to get
an array of non-repeating random long values. Using the
UniqueRandomLongs function described on that page, you can use code
like the following:


Dim S() As String
Dim N As Long
Dim Pos() As Long
ReDim S(1 To 5)
' load up some test values
S(1) = "a"
S(2) = "b"
S(3) = "c"
S(4) = "d"
S(5) = "e"
' get random indexes
Pos = UniqueRandomLongs(Minimum:=LBound(S), _
Maximum:=UBound(S), _
Number:=UBound(S) - LBound(S) + 1)
For N = LBound(Pos) To UBound(Pos)
Debug.Print S(Pos(N))
Next N

This will Debug.Print the elements of the array S random order provide
by the values of the Pos array of position indexes. If you really do
want them in a new array, use code like the following instead. T is
the new array with the elements of S in random order:

Dim S() As String
Dim T() As String
Dim N As Long
Dim Pos() As Long
ReDim S(1 To 5)
' load up some test values
S(1) = "a"
S(2) = "b"
S(3) = "c"
S(4) = "d"
S(5) = "e"
' get random indexes
Pos = UniqueRandomLongs(Minimum:=LBound(S), _
Maximum:=UBound(S), _
Number:=UBound(S) - LBound(S) + 1)
ReDim T(LBound(S) To UBound(S))
For N = LBound(Pos) To UBound(Pos)
T(N) = S(Pos(N))
Debug.Print T(N)
Next N

I also have a page with example for shuffling a single array. See
http://www.cpearson.com/excel/ShuffleArray.aspx .


Regarding your second question about installing Excel, I would
recommend that you keep your previous version of Excel. One of the
installation options is to keep existing versions. Use that option to
have both versions of Excel on the same machine. I have several
versions of Excel (XL2002, XL2003, XL2007, XL2010) on my main box and
have no problems. Note, though, that you cannot have more than one
version of Outlook on a machine. You can upgrade to Outlook 2007 or
you can continue to use Outlook 2003, but you cannot have both.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com