Before we manipulate the VBA code, we should have a preliminary understanding of some concepts in Excel VBA, which will help us learn Excel VBA well.
If we count the concepts in VBA, it is estimated that we will not be able to finish it in 10 articles. Therefore, only the most basic concepts will be introduced today. These concepts will appear repeatedly in future tutorials and are a necessary foundation for learning VBA. The rest will be introduced gradually as the learning progresses.
1. Basic Principles of Excel VBA
Excel is an object, this object contains many attributes and sub-objects, and VBA is a tool that can manipulate these objects to achieve various effects. Excel includes Range objects, which are cell objects. For example, using VBA can change the fill color property of a cell object. The code is as follows.
Range(“H3”).Interior.Color = 49407
This line of code means that the fill color of cell H3 is set to color number 49407.
2. Basic Concepts in VBA Language
1.Comment
A comment is a piece of code in the code that will not be executed. Commenting is a feature of almost all programming languages, and VBA is no exception. Comments in VBA start with English single quotation mark (‘), followed by the content of the comment. The part starting with single quotes will not be executed.
In the process of writing code, it is a good habit to comment on the code, which helps yourself or others to better understand an existing piece of code.
‘This is a code that will not be executed
2.Variable
Variables are an expression of stored data. At the beginning of the program, you can declare a variable, specify its type (number, text, logical value, etc.), and assign a value to the variable. In other places in the program, you can use this variable to make its stored value participate in calculations.
‘Declare a variable of text type
Dim val As String
‘Assign a value to the val variable, which is “Hello Excel”
val = “Hello Excel”
‘’Write the data stored in the val variable in cell A1
Range(“A1”).Value = val
3.Program Structure
The program structure shows how the program runs. It is precisely because of the existence of multiple code structures that VBA can realize various complex data calculations. There are three commonly used code structures:
Sequence structure: The program is executed in sequence. In VBA, it is executed line by line from top to bottom.
Conditional structure: The specified part of the code is selectively executed according to a certain condition. That is, when the condition is true, the specified code is executed; otherwise, this part of the code is skipped and not executed.
Cyclic structure: The specified part of the code is executed in a loop for the specified number of times. This is one of the reasons why VBA is highly efficient, because it can automatically repeat the same operation in any number.
4.Procedures and Functions
Procedures or functions contain multiple lines of code, which are two ways to organize code. In general, a procedure or function only contains the relevant code to realize a function. If a procedure or function implements multiple functions, it is recommended to divide the code into multiple procedures or functions.
Both procedures and functions can execute a piece of code. The main difference is that after executing the code, the function can return a value, but the procedure cannot.
5.Array
An array represents a collection of data of the same type, and is one of the most important concepts in VBA.
6.Object
The object is a thing, it can be a thing, an object, a concept, a noun. Objects contain properties that describe static information and methods that can be manipulated on the object.
Take the object in life as an example, the car is an object. The car’s license plate number, fuel volume, mileage, etc. are the attributes of the car; driving, refueling, and changing the license plate are the methods of the car.
It is similar in VBA. Worksheet is an object, which has attributes such as name, label color, etc. There are methods for adding and deleting.
7. Excel Object Model
In fact, Excel itself is an object, the largest object in Excel. The Application object contains the Workbook object, the Workbook object contains the Worksheet object, and the Worksheet object contains other sub-objects.
The Application object represents an Excel application.
The Workbook object represents a workbook.
Worksheet object, representing a worksheet
Range object, which represents a cell range.
8. Module
A module is an internal component that contains one or more procedures or functions. There is no limit to the number of modules contained in a workbook, and there is no limit to the number of procedures or functions contained in a module. Modules are used as containers for storing procedures or functions, which are usually applied to the entire workbook.
9. User Form
The user form is the user interface for VBA code to interact with the user. Excel VBA provides many basic form controls, which can make complex user interfaces. The most typical window interface for setting cell format in Excel is a user form.
The most basic form controls include:
- Text control
- Button control
- List control
- Input control
VBA Editor
The VBA editor is where the VBA code is written in Excel. The following operations can be performed in the editor:
- Write code
- Modify the existing code
- Insert a new module, edit the code in the module
- Insert user form, design form interface
- Run code
- Debug code
The above is the basic concepts of Excel VBA to be introduced today. The author hopes that you can gain from this.
Leave a Reply