VBA Language

The code that compiles the macro can be read, corrected, modified, deleted, or created from scratch using the Visual Basic Editor. The purpose of this article is to get to know the basic rules of a particular code and to allow readers to create macros that go beyond what can be produced with a macro recorder.

VBA is one of the new (former) programming languages, known as "Object Oriented" (19459006) or ". An object-oriented programming language applies to objects on the computer screen as we discussed it For example, Visual Basic worksheets of and buttons etc. Thus reflects our natural intention to talk about things about the world: chairs, shoes, cars, cakes, etc. This feature makes visual languages, such as VBA, relatively straightforward for beginners

This article explains Visual Basic for Applications programming language but do not forget that the code required for any operation in the object can be generated incorrectly by fixing the given operation . m needs to become an expert in language syntax, but this article seeks to give a general sense of the standard structure of the modules.

The macro always starts with the following word: & # 39; Sub & # 39; followed by the name of the macro; always the & # 39; End Sub & # 39; ends.

Text after the apostrophe is green and does not run as part of the macro (comments).

The remaining text is the programming code and executed strictly (executed) in the following order. The first line is fully executed before the second starts and so on. The post-apostrophic text on the module turns green and ignores the macro. Thus, your work can be freely formulated with comments that explain the function of a line or section without obstructing the smooth running of the macro. Notes: The macro writer is a way to make code readable and troubleshoot problems (use a lot).

Blue text – Keywords

Certain words are blue in color; These are referred to as Visual Basic Keywords. Generally, these keywords identify lines that modify the normal, sequential transition through the macro; this type of instruction that works on the table instead of the code is called the "Control structure". The control structures are discussed in more detail in a future article. Other keywords are the names given in constant constant values ​​ such as & # 39; True & # 39; or & # 39; False & # 39;

Black text – code

The remaining text of the module is black and the standard code. This code can be used to modify Excel, calculate values, or perform a spreadsheet procedure.

There are only a few rules on how to use and capture the language, it is important to know how to refer to different parts of the app. These different parts are called objects and include things like cells / ranges, worksheets, drawing objects, scrollbars, cell backgrounds, and so on. The objects that make up the app can be modified in different ways:

  • There are some actions that can be passed on to them, such as selecting or deleting them. These operations are called "methods" and do not require information beyond the name of the method in question.
  • Or they may change one of their properties for example their color, height, or name. To change a property, you always have to enter the value that the property changes, for example. Color = Red or Height = 10cm .

But before using any method or changing properties, it is necessary to be able to refer effectively to the subject. This is addressed in the next section.

Although Excel can easily record operations and get the application; write more often. In addition, a fixed code generated by a macro recorder is often the most effective way to reach the desired target. It is useful to understand the code in the macro and to arrange it if necessary.

Most of the code in the macro includes the modification of specific objects in Excel; so it is a useful starting point for at least one basic understanding of different modes in the objects.

Addressing an Object

A typical example of one of Excel's objects is a domain. One way to identify a domain is to use a domain name such as Range ("A1: A7") that describes the range of the current tab in the active workbook. If you want to describe a domain on another tab, you need to use an address, for example, Worksheets ("Sheet3"). Range ("A1: A7") . Just as if you were a person, if the person is in the immediate vicinity, their names are sufficient to identify them, but if they are in another city, they should use an address and a name. You can write a different workbook range as follows: Workbook ("Data.xls"). Worksheets ("Sheet3"). Range ("A1: A7") .

Another similar method that you can call an object is the fact that it is part of a larger object, for example, the entrance to 10 Downing Street. This identifies the front door of 10 Downing Street. So I can refer to the range ("A1"). Interior that is, the Range ["19459007"] object or Range ("A1") inside (background color). "A1". Methods and Properties

There are two types of changes to objects that can change one of its properties (color, size, pattern), or action (method) to change its status. Such a type of code appears on a module with the name of the object (and with its address where needed), then the name of the property and its new value or the desired method.

Object.Property = NewValue



Genuine example to obtain the door object; I could change its color properties (by painting), but I would always change it to a certain color from the possible colors. The properties are always associated with a specific value or set (paint the door blue) or read (what color is the door?); as such, the property code typically contains a & # 39; equivalent & # 39; (=). Door.Color = Blue.

To change the state of my door, I would use one method; For example, I could use the Open method. Do not open the door: 6, or blue, or 15 kg; the methods do not relate to specific values, so the method code simply names the object and then names the method. Door opener.

The fixed code created by selecting a cell, say A1 – and entering a text … begins with a method that is followed by a property change in the second line. The first line of the macro is as follows:

Domain ("A1")

Applies the Select method to the domain element A1. If you apply to a domain, the Select method highlights the selected range. The next line in the macro.

ActiveCell.FormulaR1C1 = "BBC Worldwide"

Modifies the property of ActiveSell Object FormulaR1C1 set to "BBC wordwide." ActiveCell is a domain object, a movable domain, and identifies a white cell in a priority domain or domains.

FormulaR1C1 property and formula FormulaR1C1 is only required if a formula is introduced using the R1C1 reference style. For any other purpose, it is easier to use the formula General Methods and Properties

Here are some examples of how to use the methods and properties:

Worksheets ("S Select

From Select on the worksheet object "Sheet1", and then use the active tab.

Range ("G5: G7"). Formula = "Hello Mum"

Set the property of G5: G7 to "Hello Mum", so all cells in the range display the text "Hello Mum". Object property ActiveSheet object property property of the G3 domain property , that is, change the name of the active tab to any of the G3.

It is also useful to know the property of the cell . The value of is similar to the input to cells in or the readout of cell contents. Unlike in the sense that when reading the contents of a cell containing a formula, the property of formula returns the text of the formula, i.e., "= Sum (A1: A7)", while Value property returns the output of the formula, i.e., the 69 (or whatever it is).

If a cell does not contain a formula, then formula (19459007) and FormulaR1C1 all return the same, although some functions that expect an input to not accept the cell even though the cell contains a number. This is because the formula property returns the number as a series of text characters (eg 91.5 instead of 91.5). Therefore, you should use the Value for returning numeric values ​​and the formula .

For help with development or any training, please contact: http://www.theSpreadsheetCompany.co.uk

Supported by sbobet

Leave a Reply

Your email address will not be published. Required fields are marked *