Userform Textbox Formula

Hi All,

Was hoping someone could help out -

On my userform, i have a TextBox that i would like to prepopulate with data when the form initializes..

Usually, i use the simple code of..

Please Login or Register  to view this content.

But, i would like to know if it would be possible for a random number to generated instead?

For example, if i was to just type the formula normally into exel, it would be...

=RANDBETWEEN(1000,9999)

But this wont work if i write it in VB. Can you tell me how i can get this formular, to run each time the form initializes to the TextBox is filled with a random number?

Many thanks for your help in advance

Sure, have a look at the "Rnd Function" topic in your VBA helpfile. It shows you how to generate a random number between two values. Also, particularly note the remark about the Randomize function.

Do you mean

Please Login or Register  to view this content.

(1) You need to put the randomize statement before it (see my comment in my first post).
(2) Your lower bound is wrong. It should be one thousand, not ten thousand.

Please Login or Register  to view this content.

Application.WorksheetFunction does not have a method called Randbetween.

Yes it does, certainly in Excel 2010

Not in 2003. Besides, calling VBA.Rnd is going to be quicker.

The OP seems to be using 2010

True, and I'll take it as read that you are correct that it was added as a method after XL 2003 (I don't have XL2010 to hand to check). I have no idea why they would have done that though; I'm certain that calling VBA.Rnd will be more efficient than calling Application.WorksheetFunction.Randbetween(). Any thoughts why they might have done it, Roy?

Nevermind - I figured it out. It's part of the Analysis Toolpak functions in 2003, so it was integrated in 2007. My advice is still to go with VBA.Rnd() though.

I think it was when they integrated the ToolPak into Excel's general functions. I can't remember whether this was 2007 or 2010.

It run's pretty quick in a userform that I just created

think it was when they integrated the ToolPak into Excel's general functions. I can't remember whether this was 2007 or 2010.

Yes, thanks. I realised that shortly after I asked.

It run's pretty quick in a userform that I just created

Okay, if I get a chance this weekend I'll run some tests in 2010 to see how the performance compares (I was going with my gut feeling).

I'm not saying it's quicker, but it would be interesting

This is the sort of thing Colin does for fun anyway.

In case anyone's interested, I just ran a little test. On my machine, in XL2010:

Please Login or Register  to view this content.

was, on average, roughly 40 times faster than:

Please Login or Register  to view this content.