Generate random number when activating worksheet in Excel

3

I am here because I believe that only with the help of VBA I will achieve what I intend to do, which is the following:

I'm developing a small game in Excel and I have to use random numbers, so every time I load, remove, or write something, the values change. I'm looking for a way where random numbers are only generated when I enter the desired sheet (spreadsheet).

How can I do this?

    
asked by anonymous 27.11.2014 / 22:18

1 answer

4

Each worksheet in an Excel file has an event called Activate , which is executed when the worksheet is activated, you can use this event and generate the random numbers in it.

Open your Excel file and enter VBA through the ALT + F11 key combination, look in the Project Explorer , which is usually in the upper left corner, the worksheet you want to create the macro, and double-click it .



Awindowwillappearwhereyoucanenteryourcodes,inthiswindowtherearetwocombos,onerepresentingtheobject(usuallywritten(Geral))andtheotherrepresentingtheevent(usuallywritten(Declaração)).Toselecttheevent,youneedtoselecttheobjectfirst,thenontheobjectselectWorksheet(whenyoudothis,aneventwillbeautomaticallyaddedtoyourcode,youcanremoveit)andselectActivateatthattimeyouwillseesomethinglikethis:

PrivateSubWorksheet_Activate()EndSub

AssumingyouwanttodisplaytherandomnumberincellA1,thenyoucando:

PrivateSubWorksheet_Activate()Randomize'onúmero6representaovalormáximoeo1ovalormínimo,'essesnúmerospodemseralteradosconformesuanecessidadeActiveSheet.Range("A1").Value = CInt(Int((6 * Rnd()) + 1))
End Sub

In this way, every time you turn on Plan1 , the above code will be executed, generating a random number and placing it in the cell A1 .

  

Tip : If you want to make a macro but have no idea how to encode and which properties to use, you can use the functionality provided by Excel and start recording a macro, so you do manually what you want in the worksheet and then finish the recording, done this will be generated a module (in VBA ) that contains all the code referring to what you did manually.

Here is some information about the code used: - Randomize - initializes the random number generator.
ActiveSheet - represents the active worksheet. , a row, a column, or a range.
Range - converts an expression to an integer.% CInt - returns the integer part of a number.% Int - generates a random number less than 1 and greater than or equal to 0, if Rnd is not used, every time the worksheet is opened the same sequence of numbers is generated, as reported

28.11.2014 / 05:22