Generate random number when activating spreadsheet in Excel

Question

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

I am developing a small game in Excel and I have to use random numbers, it happens that whenever I load, remove or write something, the values change. I look for a way where random numbers are only generated when I enter the sheet (spreadsheet) intended.

How can I do that?

Answer 1

Each spreadsheet of an Excel file has an event called Activate, that runs when the spreadsheet is activated, you can use that event and generate the random numbers in it.

Open your Excel file and sign in to VBA through the combination of keys ALT + F11, look in the Project Explorer, which usually sits in the upper left corner, the spreadsheet you want to create the macro and double-click on it.

A window will be displayed where you can enter your codes, in this window there are two combos, one that represents the object (that usually comes written (Geral)) and the other representing the event (which is usually written (Declaração)). To select the event, you need to select the object first, then on the object select Worksheet (when you do this, you will be added to your code an event automatically, you can remove it) and in the event select Activate, at that moment you will see something like this:

Private Sub Worksheet_Activate()

End Sub

Assuming you want to display the random number in the cell A1, then you can do:

Private Sub Worksheet_Activate()
    Randomize
    ' o número 6 representa o valor máximo e o 1 o valor mínimo,
    ' esses números podem ser alterados conforme sua necessidade
    ActiveSheet.Range("A1").Value = CInt(Int((6 * Rnd()) + 1))
End Sub

That way, every time you activate the 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 offered by Excel and start writing a macro, then you manually do what you want in the spreadsheet and then finish the recording, this will be generated a module (no 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 spreadsheet.
Range - represents a cell, a row, a column or an interval.
CInt - converts an expression to an integer.
Int - returns the entire part of a number.
Rnd - generates a random number less than 1 and greater than or equal to 0, if the Randomize is not used, every time the spreadsheet is opened the same sequence of numbers is generated, as is informed here.

Note: as you are working with Macros, you will have to save your Excel file as Pasta de Trabalho Habilitada para Macro Excel.