How to refer to cells with the Range object
What’s an object?
In programming, an object is a container for data. In Excel’s VBA, an object represents an Excel element.
We’ll be creating an object and then using its methods to do things with it.
Consider a car to be an Excel VBA range object.
The car is something that exists, and something we can do things to/with. Like, we can change the property of a car by painting it red. Or we can take it for a drive (which would be a method in this example).
Don’t worry too much about objects and methods yet. You’ll get used to them as you keep learning VBA.
We’ll start with the basics: how to refer to cells using VBA’s Range object.
If you’d like to follow along, open a blank workbook, go to the Developer tab, click Visual Basic, and click Insert > New Module.
In the blank module, type the below code:
This tells Excel to look at cell B1.
You’ll notice that if you now run this macro from the Macros menu (accessible via Alt + F11 or the Developer tab), you’ll get an error.
That’s because referring to a cell and not taking action doesn’t actually do anything.
There’s no property or method involved. Hence it can’t run.
Let’s tell Excel to do something with that cell.
Change your Excel VBA code to this:
Range("B1").Value = "Welcome to Spreadsheeto!"
Now run the macro from the Macros menu again:
Using “.Value”, the equals sign, and some text in quotation marks is a simple way to set the value of a range. In this case, a single cell.
You can also do this with only the equals sign, but it’s a good habit to start using “.Value” right away.