Need ideas on VBA to randomly select words in string

[Ceeyee]

Hi,

Say I have a string

"I {would like to|want to|love to} do {it|that}."

What is the fastest way to make a function to randomly choose a selection within each {} and returns a random string such as "I want to do that."

Any ideas? Thanks.

 

[bensonsearch]

dim firstarray(3) as string
dim second(3) as string
dim asd as integer
dim message as string
dim message2 as string


firstarray(1) = "would like to"
firstarray(2) = "want to"
firstarray(3) = "love to"

second(1) = "it"
second(2) = "that"
second(3) = "that"

randomize
asd = Int((3 * Rnd) + 1)
message = firstarray(asd)
message2 = second(asd)
msgbox("I " & message & " do" & message2)

 

[mikerickson]

This UDF should do what you want

Function RandSubString(testStr As String) As String
    Dim Delimiter As String: Delimiter = Chr(5)
    Dim sections As Variant
    Dim words As Variant
    Dim i As Long
    'Application.Volatile: Rem optional volatility

    sections = Split(Replace(Replace(testStr, "{", Delimiter), "}", Delimiter), Delimiter)
    Randomize
    For i = 1 To UBound(sections) Step 2
        words = Split(sections(i), "|")
        sections(i) = words(Int(Rnd() * (UBound(words) + 1)))
    Next i
    RandSubString = Application.Trim(Join(sections, " "))
End Function

 

[Ceeyee]

Is it possible to make it so that every time I press F9 (or save the workbook) to recalculate the worksheet it regenerates another random result?

Thank you so much.

This UDF should do what you want

Function RandSubString(testStr As String) As String
    Dim Delimiter As String: Delimiter = Chr(5)
    Dim sections As Variant
    Dim words As Variant
    Dim i As Long
    'Application.Volatile: Rem optional volatility

    sections = Split(Replace(Replace(testStr, "{", Delimiter), "}", Delimiter), Delimiter)
    Randomize
    For i = 1 To UBound(sections) Step 2
        words = Split(sections(i), "|")
        sections(i) = words(Int(Rnd() * (UBound(words) + 1)))
    Next i
    RandSubString = Application.Trim(Join(sections, " "))
End Function

 

[mikerickson]

That's what the optional Application.Volatile line is for.

Actualy, that could be controled with an argument (optional in this case).

Function RandSubString(testStr As String, Optional Volatile As Boolean) As String
    Dim Delimiter As String: Delimiter = Chr(5)
    Dim sections As Variant
    Dim words As Variant
    Dim i As Long
    If Volatile Then Application.Volatile: Rem optional volatility

    sections = Split(Replace(Replace(testStr, "{", Delimiter), "}", Delimiter), Delimiter)
    Randomize
    For i = 1 To UBound(sections) Step 2
        words = Split(sections(i), "|")
        sections(i) = words(Int(Rnd() * (UBound(words) + 1)))
    Next i
    RandSubString = Application.Trim(Join(sections, " "))
End Function

 

[Ceeyee]

Works like a charm.
Thank you very much.

 

[Ceeyee]

I added an parameter in your code so that I can also force the code to always select the first item. But I have a question. I currently have Option Base 1. Would this words(0) and your (Int(Rnd() * (UBound(words) + 1))) work in Option Base 1?

Function RanStr(testStr As String, Optional first As Boolean = False, Optional Volatile As Boolean = True) As String
...
For i = 1 To UBound(sections) Step 2
words = Split(sections(i), "|")
If first = True Then
sections(i) = words(0)
Else
sections(i) = words(Int(Rnd() * (UBound(words) + 1)))
End If
Next i
...

 

[mikerickson]

"Would it work?" I'm not sure. What happened when you tried it?

As I recall, Split returns a 0 based array no matter how Option Base is set (similarly the VBA function Array), so the code should work.

 

[Ceeyee]

I tried to modify your code to handle nested {}, but failed..

"I {would like to|{want|love} to} do it"

I am not familiar with working with Delimiters. Would you mind giving me some guidance on how we can approach this?

Thank you very much.

 

[mikerickson]

The approach I used wouldn't work with nested {}.
Would an un-nested version of your example be
"I {would like to|want to|love to} do it"
or
"I {would like|want|love} to do it"