We learn how to refer to Excel worksheet cells by using a range object in a macro in Excel and then perform actions on it. If you set an object variable to a Range object, you can easily manipulate the Excel range by using the variable name in Microsoft Excel. The following procedure creates the object variable RandomNums and then assigns the variable to Excel range B2:B6 on Sheet1 in the active Excel workbook. Subsequent statements modify properties of the Excel range by substituting the variable name for the range object.
Dim RandomNums As Range
Set RandomNums = Worksheets(“Sheet1”).Range(“B2:B6”)
RandomNums.Formula = “=RAND()”
RandomNums.Font.ColorIndex = 3
The Excel training video below shows how to create and execute the Excel macro.
Implementing the macro:
- Click on Tools in the menu bar, select Macro and from the drop-down list select Visual Basic Editor
- In the Visual Basic editor window click on the menu Insert and select module
- First define the name UsingRangeObject()
- Next use Dim, short for dimension, to set the data type for the variable RandomNums
- Now assign RandomNums to a specific Excel worksheet range like B2:B6
- The next line generates random numbers in the defined range of cells using a formula
- The second last line formats the font of the generated random numbers in RandomNums to red color 8.Press F5 or the run button ‘>’ to see the macro in action
Referring To Ranges In Your VBA Code