When you create a new workbook in Excel the need often arises to test your work with some sample data. If real data isn't available you have to create some. I find making up and
entering sample data into a worksheet extremely tedious so I built a macro like this one to do it for me.
The macro enters random numbers into the selected cells by making use of Excel's RAND() function.
Create the Macro
In this exercise you will start by recording a macro that enters random numbers into the selected cells. You will then edit the macro to refine it and add additional features.
Step 1: Start Excel and open a new empty workbook.
Step 2: Select a block of cells (anything will do).
Step 3: Open the Tools menu and choose Macro then Record New Macro.
Step 4: In the Record Macro dialog enter the Macro name RandomNumbers and choose to Store macro in the Personal Macro Workbook . Add a short Description such as Fills the selected cells with random numbers.
Do not click on the worksheet!
Step 5: Type:=int(rand()*100)
Then hold down the [Control] key while you press [Enter].
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 7: Open the Edit menu and choose Paste Special.
In the Paste Special dialog box choose Values then click the OK button.
This action copied the selected cells then pasted just the displayed values down into the same cells, discarding the underlying formulas.
Step 8: Press the [Escape] key to take Excel out of copy mode.
Step 9: Press the Stop button on the Stop Recording toolbar.
The recording process is now finished. Now it's time to test the macro...
Step 10: Select a block of cells and press [Alt]+[F8] (or go to Tools > Macro > Macros) to open the Macros dialog box.
Choose the RandomNumbers macro and click the Run button.
Each of the selected cells should now contain a number between zero and 99. If it does not, of if you see an error message (click the End button on the error message box), check your code in the next step.
Now it's time to edit the code and make some improvements to the macro.
Step 11: Press [Alt]+[F11] to open the Visual Basic Editor.
In the Project Explorer locate the Modules folder for VBAProject
(Personal.xls) and if necessary click the sign next to it to display the modules within.
The macro you just recorded should be in a module named ModuleX where X is the highest number.
Step 12: Double-click the module name to display its code in the code window of the Visual Basic Editor.
Find the code for the RandomNumbers macro. It should look like this:
The PasteSpecial statement contains more information than is necessary.
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 13: Delete the code starting with the comma after xlPasteValues until the end of the second False as shown below (select the text and press the [Delete] key).
Your code should now look like this:
Note that the keyword Selection is repeated several times on consecutive lines. Since each of these refers to the same selection you can improve the code by changing it into a With Statement.
Step 14: Insert an empty line above the first Selection... line and type:
With Selection
Step 15: Replace each of the three existing Selection keywords with a tab (an easy way to do this is to double-click on the word to select it then press the [Tab] key).
Step 16: Now insert an empty line above t he line starting Application... and type:
End With
Your code should now look like this:
The macro can be enhanced by offering the user the opportunity to set a maximum value for the random numbers generated. For this you will use an Input Box. The user's choice will be stored in a variable which must be declared first.
Step 17: Insert an empty line above the line starting With... and type:
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 19: In the random number formula replace 100 with:
" & lngMax & "
The code should now look like this:
It's time to test the macro...
Step 20: Open the Debug menu and choose Compile VBAProject.
Step 21: Press [Alt]+[F11] to switch to Excel and select a block of cells.
Step 22: Press [Alt]+[F8] to open the Macro dialog box then choose the RandomNumbers macro and click Run.
When you run the macro the Input Box you programmed is displayed. Note how the box title and its message (called the "prompt") correspond to your code and that a default entry of 100 is already entered...
So far, the random numbers have been supplied as whole numbers because the INT() function has been used in conjunction with the RAND() function to create an integer (a whole number). But the user might not want whole numbers. The macro can be further modified to allow decimal places.
For this you will use another Input Box to prompt the user for the number of decimal places they require. This also needs a variable to hold their response.
Step 23: Make a new line underneath the line starting Dim... and type:
Dim intDecimals As Integer
Step 24: Make another new line underneath the line starting lngMax... and type:
intDecimals = InputBox("Enter a whole number.", "Decimals?", "0") The formula now has to be modified to allow decimal places if required. To do this the INT() function will be replaced by the ROUND() function.
Step 25: Replace the formula as follows:
"=ROUND(RAND()*" & lngMax & ", " & intDecimals & ")"
Your code should now look like this:
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 26: Compile and test the macro again as described in Steps 20-22.
This time a second Input B ox is displayed asking the user to specify how many decimal places they require, the default being zero...
The macro is almost finished. But what happens if the user makes a mistake? If they fail to enter a number in one of the Input Boxes the default value is used. But if they leave the input box completely empty, enter a piece of text, or enter anything that can no t be interpreted as a whole number an error will occur...
To prevent error messages like this from causing problems for the user VBA the usual practice is to include code in a macro which takes charge of the error, tries to rescue the situation, and prevents the macro from "crashing". This code is called an "error handler".
The simplest way of handling an error is to ignore it, abandon the line of code that is causing the error, and move on to the next job. This method should be used with caution, but since nothing disastrous can happen if that approach is used in this example, this is the method we'll use here...
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 28: Finally, compile and test the macro again as described in Steps 20-22.
This completes this Step-by-Step exercise.
Summary
This is a very useful macro. It was one of the first macros I recorded and I still use it when building and testing spreadsheets.
It illustrates several techniques commonly used in VBA programming:
• Information is held temporarily in Variables.
• The variables are declared at the start of the macro so that the Excel can set aside an appropriate amount of memory to hold the data when it arrives.
• The variables are defined with a specific data type reflecting the type of data they will be used for.
• Programmers usually add a prefix (such as int for Integer or lng for Long) to the name of the variable to indicate the data type (a handy reminder when reading the code later).
• An Input Box is a kind of message box that can be used to gather input from the user. The input is usually placed into a variable.
• As well as a message and title, an Input Box can be given a Default value. This serves to assist the user and helps avoid them closing it without making an entry.
• A With Statement can often be used to replace repeated references to an object that occur on consecutive code lines. They also help speed up code and reduce the amount of typing necessary.
• Formulas can be written directly into cells by using their FormulaR1C1 property. The formula is entered as a text string, enclosed in quotes.
• A values held in a Variable can be written into a formula and replace "hard-coded"
numbers. This is done by concatenating (joining together) the string and the variable using an ampersand ("and") character (& ).