In this section, we will consider what VBA is, since it is precisely on it that macros are written.
VBA - programming language (stands for Visual Basic for Applications) was developed by Microsoft. This language is not independent, but intended only for automating processes in the MS Office package. VBA is widely used in Excel, I also in Access, Word and other package programs.
VBA is a simple programming language that anyone can learn. Once you learn it, you'll be able to give Excel commands, what to do with columns, rows, cell values, move/add/sort worksheets, display pre-programmed messages, write your own formulas and functions, and more. The essence of the language is to operate objects.
To work with VBA code, we need an editor (Visual Basic Editor), which is already installed in Excel by default. You can open it by pressing the key combination "ALT+F11".
Let's understand what an object is. Object is an element, a structural particle of Excel, namely: book, sheet, range, cell. These objects have a hierarchy, that is, they are subordinate to each other. Schematically, the structure of the Excel hierarchy can be represented as follows:
The most important object is Application, which corresponds to the Excel program itself. Next comes Workbooks (book), Worksheets (sheet), Range (range or individual cell).
For example, to refer to cell "A1" in the worksheet, we would need to write the following path, taking into account the hierarchy:
Application.Workbooks("Archive").Worksheets("Sheet1").Range("A1").
Thus, we learned to refer to the smallest object in Excel - a cell.
In turn, objects have "collections". A Collection is a group of objects of the same class. Individual elements of the collection are also objects. Yes, Worksheets objects are collection elements of a Worksheet object, which also contains other collections and Objects:
Each object has properties. For example, a Range object has a Value or Formula.
Worksheets(“Sheet1”).Range(“A1”).Value or Worksheets(“Sheet1”).Range(“A1”).Formula
In this example, the property displays the value entered into the cell or entered formula.
Also, through the Formula property, you can not only get the formula, but also write it down:
MsgBox Range(“A1”).Formula - we will receive a message with a formula in cell "A1" ;;
Range(“B12”).Formula = “=2+6*100” - enter the formula =2+6*100 in cell B12.
Now let's look at how we can control the contents of a range or cell. For this, there are so-called methods ("what to do" commands) in VBA. When writing code, methods are separated from object with a dot, for example:
Range("A1").Select or Cells(1, 1).Select
This method specifies to select (Select) cell "A1". Next, let's delete the value in this cell. For this, we will write the following code.
Selection.ClearContents
Here the program "takes" what we selected (Selection) and removes its contents (ClearContents ).