Using worksheet function randbetween for non-contiguous number ranges

[kualjo]

I need to be able to generate random characters for a string, and can do so where the character set is contiguous. For example, the code below will generate a random upper-case letter.

pwx = Chr(WorksheetFunction.RandBetween(65, 90))

I can do the ranges for lower-case letters and numbers as well, but special characters are spread throughout the character set. Also, if I want to generate a random string using various combinations of upper-case, lower-case, numbers, and special characters, I need to be able to specify the character set to draw from.

Is it possible to modify the code above to do this? Would it make more sense to use a broader character set, then use an IF statement in a loop to eliminate any returned values that I don't want? Are there any other ways to accomplish this?

[Kenneth Hobs]

Make a column with numbers 1 to x. In the 2nd column, put the actual character set reference number. Then lookup based on the random number for column 1 and offset 1 column.

[kualjo]

Thanks for the suggestion Kenneth. That makes sense. I probably should have mentioned that I am building a UserForm and therefore want everything to be done in code. I'd like to avoid using the spreadsheet if at all possible. Building an array seems like a potential solution, but I'm not real good with those and not sure how I would even approach it. Is this possible?

[Kenneth Hobs]

Sure that can be done. VBA will give you more flexibility.

Before I or another shows you, would you want these to be be non-repeating random characters? If one wanted say 6 characters, random-wise with repeats allowed, one could get all the same character.

Some like their random numbers sorted but in your case, I doubt you would want that.

[kualjo]

Repeats are acceptable. My approach so far has been to generate one character at a time, up to the desired number, with each iteration giving all relevant characters equal odds to appear.

I'd be interested to hear your strategy for randomizing characters when the character set numbers are not contiguous, as with special characters (which are in four separate ranges) and when I want to use, for example, upper-case letters with numbers, numbers with special characters, etc. In this scenario, I randomly select a character from each separate range, then randomly select a range and use the character that was selected. This seems to work well enough for my purposes.

I hope this explanation is clear. If not, I can clarify. Thanks.

[Kenneth Hobs]

See attachment.

[snb]

Sub M_snb()
c00 = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzáéíóúýäëïöüÿà èìòùâêîôûÁÉÍÓÚÝÄËÏÖÜÀÈÌÒÙÂÊÎÔÛÃÕÑãÇå"

For j = 1 To 10
c01 = c01 & Mid(c00, Application.RandBetween(1, 110), 1)
Next

MsgBox c01
End Sub