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)andselectActivate
atthattimeyouwillseesomethinglikethis:
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