VBA get random string from array

Question

I am trying to be able to return a string from an array that will be put into a label in a userform. I've seen a lot of things for this, but none of them seem to work. I have an array named Compliments that I want to pull one of the strings for that array and pass it through to my label6 but because this is a userform that only shows up when the sheet is opened and is opened momentarily I want to be able to make it random. Each time someone opens the sheet they will get a different compliment to help start their day. My code for everything is below:

Private Sub UserForm_Activate()
    TextBox1.Value = Date
    TextBox2.Value = Time
    TextBox3.Value = MainMenu.TextBox1.Value
    Label6.Caption = "RANDOM ARRAY GOES HERE"

    Application.Wait (Now + TimeValue("00:00:05"))

    Welcome.Hide
End Sub

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = (Application.Height / 2) - Me.Height / 2
    Me.Left = (Application.Width / 2) - Me.Width / 2

    Compliments = Array("Good Morning, You are Beautiful Today", _
        "I think you're pretty awesome", "That outfit looks great on you", _
        "You're a great engineer", "You rock Dude", "Nobody can get you down", _
        "Your makeup is spot on")
End Sub

JNevill · Accepted Answer · 2018-06-29 15:34:40Z

8

You can use the Rnd() function in VBA to generate a random number. With some math you can force it to be between two integers. In this case it will be between 0 and the upper bound of your Compliments array. Something like the following should work:

Function Compliments()
    'Function to return the array
    Compliments = Array("Good Morning, You are Beautiful Today", _
            "I think you're pretty awesome", "That outfit looks great on you", _
            "You're a great engineer", "You rock Dude", "Nobody can get you down", _
            "Your makeup is spot on")
End Function

Private Sub UserForm_Activate()
    TextBox1.Value = Date
    TextBox2.Value = Time
    TextBox3.Value = MainMenu.TextBox1.Value

    randArrIndex = Int ((Ubound(Compliments) +  1) * Rnd )
    Label6.Caption = Compliments(randArrIndex)

    Application.Wait (Now + TimeValue("00:00:05"))

    Welcome.Hide
End Sub

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = (Application.Height / 2) - Me.Height / 2
    Me.Left = (Application.Width / 2) - Me.Width / 2        
End Sub

answered Jun 29, 2018 at 15:34

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcucciboy2 Over a year ago

Just my personal take on this - I think it would also make sense for the Function to be something like GetCompliment() as String and access a random string in the array within the function to only return the string result

JNevill Over a year ago

Oh yea. Totally. I think there a lot of ways to clean this up further. I just tossed Compliments into it's own function so it could be treated more as a constant and be referred to by any sub/function in the userform since it looked like OP initialized in the _Initilize event and then extracted the value in the _Activate() event.

Stacie Over a year ago

I know it's been a little while since you helped me with this. So this works, except it only puts out "You rock dude". It won't pull any other strings from this array

JNevill Over a year ago

Probably need to call randomize command once to reset the seed before making use of and function. It's picky like that

Collectives™ on Stack Overflow

VBA get random string from array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related