VBA-Lesson 5. Properties

In this article, we will learn to write VBA code to control the contents of cells, the cells themselves, and letters.

1. Write values in cell

To begin, open the editor, add a module, copy this macro there:

Sub Properties() 'moonexcel.com.ua
    Range ("A1")
End Sub

We turned to cell A1. Now let's try to control this cell. To see what we can do, let's add a period after Range ("A1").

Select Value and press Tab. We will get the following code:

Sub Properties() 'moonexcel.com.ua
         Range("A1").Value
End Sub

The value of Value displays the contents of the cell.

Now let's write the value 35 in cell A1:

Sub properties() 'moonexcel.com.ua
    Range("A1").Value = 35
    'The value of cell A1 is 35
End Sub

Let's now try to write the text in the cell (when assigning a text value, it must be enclosed in double quotes ""):

Sub properties() 'moonexcel.com.ua
    Range("A1").Value = "There is some text here"
End Sub

Note that the macro will display the values in the sheet you last opened. Therefore, in order to control the contents of a cell on any sheet of the book, we will need to write the full path to the cell, namely, add the name of the sheet to the front of the code, for example:

Option 1. We apply by the name of the letter - Sheets("Sheet2").

Sub properties() 'moonexcel.com.ua
    Sheets("Sheet2").Range("A1").Value = "There is some text here"
    'Means: open sheet 2, select cell A1 and write text in its value
End Sub

Option 2. We apply not by the name of the letter, but by its serial number - Sheets(2).

Sub properties() 'moonexcel.com.ua
    Sheets(2).Range("A1").Value = "There is some text here"
End Sub

Similarly, if we want to refer to a cell in another workbook, we need to write the name of the workbook at the beginning of the code:

Sub properties() 'moonexcel.com.ua
    Workbooks("Book2.xlsx").Sheets("Sheet2").Range("A1").Value = "There is some text here"
End Sub

Although we specify the Value parameter in our examples, it can actually be omitted as it is the default. That is, these two lines of code will be equivalent:

Sub properties() 'moonexcel.com.ua
    Range("A1").Value = 35
    Range("A1") = 35
End Sub

2. Deleting values

Let's delete the value 35 from cell A1, which we recorded at the beginning of the lesson:

Sub properties() 'moonexcel.com.ua
    Range("A1").Clear
    'Means: select cell A1 and clear it
End Sub

3. Formatting values

If you select a Font value, a list of properties will appear that you can apply to the cell:

Formatting: resizing text

Let's set the cell to 35 and reduce the font size to 8:

Sub properties() 'moonexcel.com.ua
    Range("A1") = 35
    Range("A1").Font.Size = 8
End Sub

Formatting: make text bold

Sub properties() 'moonexcel.com.ua
    Range("A1").Font.Bold = True
End Sub

Remove highlights with bold:

Sub properties() 'moonexcel.com.ua
    Range("A1").Font.Bold = False
End Sub

Formatting: make text italic

Sub properties() 'moonexcel.com.ua
    Range("A1").Font.Italic = True
End Sub

Formatting: underline text

Sub properties() 'moonexcel.com.ua
    Range("A1").Font.Underline = True
End Sub

Formatting: set font type

Sub properties() 'moonexcel.com.ua
    Range("A1").Font.Name = "Arial"
End Sub

Formatting: fill in the cell

Sub properties() 'moonexcel.com.ua
    Range("A1").Interior.ColorIndex = 6
End Sub

Articles on the topic: