Menu
homeInternet

Logical operators in vba. VBA Logical Operators. Syntax and semantics of the VBA programming language

The VBA language was designed to be fairly easy and quick to learn and then use on a daily basis. The VBA language is useful for both professional programmers and ordinary users to automate everyday routine work. Whether your goal is to simplify your daily work or increase productivity, VBA is certainly the best language for these purposes, since it is designed specifically to work with Microsoft Office applications.

Knowledge of operators is a necessary basis for learning any topic in VBA, i.e. Before you start learning a language, you should spend enough time on operators and functions.

VBA Operators

  • AppActivate title, wait- Activates an open application by the title or task identifier specified by the title argument
  • Beep- sound signal
  • Call name, argumentist- Calling a procedure called name. (Because the Call keyword is optional and you can call a procedure using only its name, this statement is rarely used in VBA programming)
  • ChDir path- Replaces the current folder with the folder specified by the path argument
  • Close filenumberlist- Changes the current drive to the drive specified by the drive argument
  • Const CONSTNAME- Declares a variable named CONSTNAME as a constant
  • Date = date- Changes the system date to the date specified by the date argument
  • Declare name- Declares a procedure from a dynamic link library (DLL)
  • DefBool letterrange- A module-level operator that sets Boolean as the default data type for all variables that begin with the letters specified by the letterrange argument (for example, DefBool A-F)
  • DefByte letterrange- Sets Byte as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DefCur letterrange- Sets Currency as the default data type for all variables that begin with the letters specified by letterrange
  • DefDate letterrange- Sets Date as the default data type for all variables that begin with the letters specified by letterrange
  • DefDbl letterrange- Sets Double as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DefInt letterrange- Sets Integer as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DefLng letterrange- Sets Long as the default data type for all variables that begin with the letters specified by letterrange
  • DefObj letterrange- Sets Object as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DefSng letterrange- Sets Single as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DefStr letterrange- Sets String as the default data type for all variables that begin with the letters specified by letterrange
  • DefVar letterrange- Sets the Variant type as the default data type for all variables that begin with the letters specified by the letterrange argument
  • DeleteStting appname, section, key- Removes the section section or key from the Windows registry
  • Dim varname- Declares a variable named varname
  • Do:Loop- Loops through multiple statements until the Boolean condition is True
  • End Keyword- Terminates a procedure, function, or control structure
  • Enum name- A module-level operator that declares a variable of an enumerated type
  • Erase arraylist- Clears memory from a dynamically allocated array or reinitializes a fixed-size array
  • Error errornumber- Simulates an error by setting the Err object to errornumber
  • Event procedurename(arglist)- An operator can only be present in a class module. It declares a user defined event
  • Exit keyword- Exit from a procedure, function or control structure
  • FileCopy source, destination- Copies the source file to the destination folder (you can change the name of the source file)
  • For Each:Next- Loop through each member of the collection
  • For:Next- Loop through several statements until the loop counter reaches a specified value
  • Function- Declares a custom function procedure
  • Get #filenumber, varname- Reads into a variable from the I/O file opened with the Open statement
  • GoSub:Return- Enters and exits the subroutine described in the procedure. (Using this statement is not recommended because creating a separate procedure will make the code easier to understand)
  • GoTo line- Transfers control to the line of code pointed to by the line label
  • If:Then:Else- Executes one of two pieces of code based on the result of a logical test
  • Imlements InterfaceName, Class- Defines the name of the interface or class that will be implemented in the class module
  • Input #filenumber, varlist- Reads from file I/O into variables
  • Kill pathname- Removes file pathname from disk
  • Let varname = expression- Sets the variable varname to expression. The Let keyword is optional and almost never used
  • Line Input #filenumber, var- Reads one line from the I/O file and stores it in a string variable
  • Load- Loads the form into memory, but does not display it on the screen
  • Lock #filenumber, recordrange- Controls file I/O access
  • LSet stringvar = string- Places the string, starting with the leftmost character, in a variable of type String. In this case, the size of the variable does not change
  • LSet var1 = var2- Copies a variable of one user-defined type to a variable of a different, but compatible, user-defined type
  • Mid- Replaces a specified number of characters in a string variable with characters from another string
  • MidB- Replaces characters in a string variable byte-by-byte with characters from another string
  • MkDir path- Creates a folder named path
  • Name oldpathname as newpathname- Changes the name of a file or folder
  • On Error- Sets how errors are handled
  • On:GoSub, On:GoTo- Transfers control to a given string based on the result of an expression
  • Open pathname, :- Opens a file for I/O
  • Option Base 0|1- Sets (at the module level) for arrays a default lower bound (number of the first element)
  • Option Compare Text|Binary- Defines (at the module level) the default string comparison mode
  • Option Explicit- Forces the programmer to explicitly declare variables. Enter this statement at module level
  • Option Private- Indicates that the module is closed and cannot be accessed from procedures located in other modules. Enter a module-level operator
  • Print #filenumber- Writes to the I/O file
  • Private varname- Declares that the variable varname will be private and can only be used in the module in which it is declared. Enter a module-level operator
  • Property Get- Declares a property procedure that will accept the values ​​of this property
  • Property Let- Assigns a value to a property in a property procedure
  • Property Set- Sets a reference to an object in a property procedure
  • Public varname- Gives access to the varname variable for all procedures contained in the module
  • Put #filenumber,varname- Writes data from variable varname to I/O file
  • RaiseEvent eventname, arguments- Raises an event named eventname
  • Randomize number- Initializes the random number generator. Skip the number argument to initialize it with a different seed each time
  • ReDim varname- Re-allocates memory for a dynamic array
  • Rem comment- Tells VBA that the text following the statement is a comment. The apostrophe symbol is often used instead of this operator
  • Reset- Closes all I/O files that were previously opened by the Open statement
  • Resume- After an error occurs, resumes program execution from the line where the error occurred
  • RmDir path- Deletes a folder
  • RSet stringvar = string- Writes a string to a string variable, aligning characters to the right
  • SaveSetting appname, :- Creates or saves settings in the Windows registry
  • Seek #filenumber, position- Sets the current position in the I/O file
  • Select Case- Executes a group of statements based on the value of an expression
  • SendKeys string, wait- Transmits keystrokes specified by string to the active application as if they were entered from the keyboard
  • Set objectvar = object- Assigns the object object to an object variable named objectvar
  • SetAttr pathname, attr- Sets the access attributes specified by the attr argument (for example, vbReadOnly) for the file specified by the pathname argument
  • Static varname- Declares (at the module level) that the variable varname will retain its value as long as the module code is executed
  • Stop- Puts VBA into pause mode
  • Sub- Declares a procedure
  • Time = time- Sets the system time to the time argument
  • Type varname- Declares a custom data type. (Used at module level only)
  • Unload- Removes the form from memory
  • Unlock #filenumber, recordrange- Removes access attributes for an I/O file
  • While:Wend- Loops through a group of statements until the logical condition is true
  • Width #filenumber, width- Sets the line width for the I/O file
  • With:End With- Executes a group of statements for a given object
  • Write #filenumber- Writes data to the I/O file

A VBA program is a sequence of statements.

There are a number of conventions that should be followed when writing programs. Thus, several statements can be placed on one line. A colon is placed between statements on the same line.

Any line can be split into two by placing the characters “Space” + “Underscore” (_) at the end of the first, in which case the second line will be considered a continuation of the first.

Comments are used to make the program easy to read. There are two ways to enter comments in VBA: using an apostrophe (‘), which can be placed anywhere on a line, and using the reserved word Rem instead of an apostrophe.

1. Dim operator is intended for declaring variable types.

1. Dim A As Integer – variable A is declared as an integer, i.e. it will store only integer values .

2. Dim D As Date – variable D is declared to store dates.

3. Dim Last Name, Name As String – variables are declared. Last name and Name, intended for storing text.

4. Dim B(12) As Integer – a one-dimensional array (vector) consisting of 12 integers is declared, and by default the first element of the array will be B(0), and the last B(12).

5. Dim B(3,3) As Single – a two-dimensional 3x3 array (matrix) consisting of real numbers is declared.

If the variable type is not specified, the Variant type is used by default. However, specifying a specific type of variable makes the program more reliable and speeds up its operation, because VBA doesn't have to waste time recognizing an undeclared variable every time it is accessed.

If the size of the array M is not known in advance and is determined during the program, then when describing the array the number of elements is not indicated, and the array is defined as follows:

Dim M() As Integer

After determining the number of array elements, for example, N, you need to write the operator

2. Assignment operator is intended to assign a value to a variable.

Syntax:

Variable (or object property) = expression.

1. a=5 – assign variable A the value 5 ;

2. b=“Manager” – variable b assign meaning "Manager";

· Address=Sheets("Organizations").Cells(2,2) – assign the Address variable the contents of cell B2, which is located on the Organizations sheet in the current workbook;

Last Name=UserForm1.TextBox1.Text – variable Last name is assigned to the contents of the TextBox1 field of the user form UserForm1.

o With/End with statement saves the programmer from a large number of repetitions of the name of the same object.

Syntax:

With object

operator1

operator2

operatorN

For example, instead of a sequence of statements

UserForm1.TextBox1.Text = Date

UserForm1.TextBox2.Text = “ “

UserForm1.ComboBox1.Text = “ “

can be written like this

TextBox1.Text = Date

. TextBox2.Text = “ “

. ComboBox1.Text = “ “

REM On sheet Sheet1 in column A, starting from the second line, ‘employee tariffs’ are written down. Fill in the combo box ComboBox1 in the user form UserForm1

‘The first line of the program – on the sheet Sheet1 in column A ‘the number of filled cells is counted, the result ‘is assigned to the variable N

N=Application.CountA(Sheets(“Sheet1”).Range(“A:A”)).

D=”A2:A”&Cint(N)

Sheets(“Sheet1”).Range(D).Name=”Tariffs”

TextBox1.Text = Date

. TextBox2.Text = “ “

. ComboBox1.Text = “ “

. ComboBox1.Rowsource = “Tariffs”

1. Conditional statement If/Then/Else– allows you to check a certain condition and, depending on the results of the check, perform one or another action

Syntax:

If condition Then operators1 [ Else operators2]

If the condition is true, then statements1 are executed, otherwise statements2 are executed.

It is also possible to use a complex conditional operator, which is written as a block:

If condition1 Then

operators1

ElseIf condition2 Then

operators2

operators3

Last Name=.TextBox1.Text

If Last Name =”“ Then MsgBox”You have not entered a last name”

REM When checkbox number 1 is checked, the client is given a 5% discount

‘ Amount – amount of money paid by the client

‘ The cost of the product is stored in the Cost variable

If UserForm1.CheckBox1.Value = True Then

Amount=Cost-Cost*0.05

Else Amount=Cost

1) REM Assume that Tariff is the established tariff, and Time is the time worked. Time and tariff are entered into the input fields ‘in TextBox1 and TextBox2, respectively. Salary is calculated using the formula Tariff*Time. Let's write a program to display the calculated salary in the form. Label4 – inscription prepared for ‘displaying the salary value’

If IsNumeric(TextBox1.Text)=True And _

IsNumeric(TextBox2.Text)=True Then

Tariff=TextBox1.Text

Time=TextBox2.Text

Label4.Caption=Tariff*Time

In the first example, we work with user form number one. Variable Surname the contents of input field number one are assigned. It then checks to see if anything has been entered into the input field (whether the variable is empty Surname). If the variable Surname is empty, a message box is displayed on the screen.

In the second example, the first lines starting with the word REM and apostrophes are comments explaining the purpose of the variables. The conditional operator determines the amount paid by the client.

The third example checks whether numbers are entered in the input fields. If numbers are entered into two fields, then the variables are assigned the values ​​of the input fields and the label is assigned the value of the result of multiplying the tariff by time.

1) Unconditional jump operator GoTo is intended for specifying a transition to a specified line within a program.

Syntax:

GoTo String

Required argument String can be any line label or line number.

If IsNumeric(TextBox1.Text)=False Then GoTo Error

Error: MsgBox “Error entering numbers!”

The example above checks to see if a number has been entered in input field one. If the entered number is not a number, the line is moved to the line labeled Error and a message is displayed on the screen.

2) Loop operator For/To/Next designed for programming repeating fragments, i.e. to describe cyclic algorithms.

Syntax:

For variable=M1 To M2 [ Step M3]

operators

M1, M2, M3 – expressions. A loop statement repeats the execution of a group of statements while the variable (counter) changes from the initial value M1 to the final value M2 with the specified step M3. If the step is not specified, then it is assumed to be 1.

Last Name = Sheets(“Employees”).Cells(I,1)

UserForm1.ComboBox1.AddItem Last Name

In the example given, it is assumed that on the “Employees” sheet, the first column contains the names of employees who need to be included in the list of combo box number one of user form number one. Program operation algorithm:

1) Variable I is assigned the value 2.

2) The Last Name variable is assigned the value of cell A(I,1), located on the “Employees” sheet. (When the loop is executed for the first time, this is cell A(2,1))

3) An element from the Last Name variable is added to the list of the ComboBox1 combo box.

4) 1 is added to the value of the variable I (if the step is not specified, then it is assumed to be equal to 1). A check is performed to see if the value of I has exceeded 10 (the final value of the variable I, in the example M3=10)? If the value of I is even less than or equal to 10, then steps 2-4 are performed, otherwise the cycle ends.

Thus, the above program allows you to fill the ComboBox1 combo box with data.

Basic algorithms used in solving
economic tasks

Let's look at the basic algorithms using the following example. Employees from different departments participated in the execution of work under the contract. The table on sheet “Sheet1” presents data on the number of hours worked by each employee (Fig. 8).


Fig. 8. Data on the number of hours worked

employees of the company

signNameDescription
= EqualsReturns True if the left and right operands are equal. Note that this is an overload of the assignment operator.
<> Not equalReturns True if the left and right operands are not equal.
> Better thanReturns True if the left operand is greater than the right operand.
< Less thanReturns True if the left operand is less than the right operand.
>= More or equalReturns True if the left operand is greater than or equal to the right operand.
<= Less or equalReturns True if the left operand is less than or equal to the right operand.
Is Reference capitalReturns True if the left object reference is the same instance as the right object reference. It can also be used with Nothing (null object reference) on both sides. Note. The Is operator will try to coerce both operands to Object before performing the comparison. If either side is a primitive type or A Variant that does not contain an object (either a non-object subtype or a vtEmpty) comparison will result in a runtime error 424 - "Object required". If any operand belongs to another interface the same object, the comparison will return True . If you need to check the fairness of both an instance, then And interface, ObjPtr(left) = ObjPtr(right) use ObjPtr(left) = ObjPtr(right) .

Notes

VBA syntax allows for "chaining" of comparison operators, but in general these constructs should be avoided. The comparison is always done from left to right on only 2 operands at a time, and each comparison results in a Boolean. For example, the expression...

A = 2: b = 1: c = 0 expr = a > b > c

Can be read in some contexts as testing whether b is between a and c. In VBA this is evaluated as follows:

A = 2: b = 1: c = 0 expr = a > b > c expr = (2 > 1) > 0 expr = True > 0 expr = -1 > 0 "CInt(True) = -1 expr = False

Any comparison operator other than Is used with Object as an operand will perform on the return value of Object's default member. If the object does not have a default member, the comparison will result in runtime error 438 - "The object does not support its property or method."

If Object is not initialized, the comparison will result in runtime error 91 - "Object variable or With block variable locked."

If the Nothing literal is used with any comparison operator other than Is, it will result in a compilation error - "Illegal use of object".

If Object's default Object is another Object, VBA will continually call the default element of each subsequent return value until a primitive type is returned or an error is raised. For example, suppose SomeClass has a default member Value , which is an instance of ChildClass with a ChildValue member of ChildValue . Comparison...

Set x = New SomeClass Debug.Print x > 42

Will be assessed as:

Set x = New SomeClass Debug.Print x.Value.ChildValue > 42

If either the operand is numeric and another the operand is a String or a Variant of a String subtype, a numeric comparison will be performed. In this case, if String cannot be mapped to a number, the result of the comparison will be runtime error 13 - "Type mismatch".

If both operands are a String or Variant of the String subtype, the string comparison will be performed based on the comparison parameter of the code module parameters. These comparisons are made on a character-by-character basis. note that symbolic representation String containing the number Not is the same as matching numeric values:

Public Sub Example() Dim left As Variant Dim right As Variant left = "42" right = "5" Debug.Print left > right "Prints False Debug.Print Val(left) > Val(right) "Prints True End Sub

For this reason, make sure that String or Variant variables are passed to numbers before performing numerical inequality comparisons.

If one of the operands is a Date, then a numeric comparison on the underlying double will be performed if the other operand is numeric or can be converted to a numeric type.

If the other operand is a String or a Variant of a subtype of String that can be cast to Date using the current locale, the String will be cast to Date . If it cannot be applied to Date in the current locale, the result of the comparison will be runtime error 13 - "Type mismatch".

Care should be taken when comparing Double or Single values ​​and Booleans. Unlike other numeric types, non-zero values ​​cannot be considered True due to VBA's behavior in promoting a comparison data type using a floating point number in Double:

Public Sub Example() Dim Test As Double Test = 42 Debug.Print CBool(Test) "Prints True." True is promoted to Double - Test is not cast to Boolean Debug.Print Test = True "Prints False "With explicit casts: Debug.Print CBool(Test) = True "Prints True Debug.Print CDbl(-1) = CDbl(True) "Prints True End Sub


There are five basic and important logical operators in Visual Basic: And, Or, Not, Xor, and Eqv. I will now try to explain the role of each of them!

So, And!
This is probably the simplest and most necessary logical operator among others.

Help:
Returns the result of a conjunction (logical AND) of two expressions.

Syntax:
result = expression1Andexpression2

Those. result will be true only if true expression1ANDexpression2. The values ​​returned by the operator are presented in the following table:

expression1 expression2 result
True True True
True False False
True Null Null
False True False
False False False
False Null False
Null True Null
Null False False
Null Null Null

And now a small example:

Let's imagine that the variables User_Has_Computer and User_Has_Car are declared as Boolean values ​​and in the process of some action they take on the values ​​either True or False. You set a condition that checks these values:

We emulate this ourselves :) - enter the following lines somewhere:


Dim User_Has_Car As Boolean
User_Has_Computer = True
User_Has_Car = True

If User_Has_Computer And User_Has_Car Then
MsgBox "You are a happy person!"
End If

When checking this condition, a message will pop up on the screen notifying that the user is a happy person, because. he has a car AND computer:-)! And now before the third or fourth line put an apostrophe """ - comment out the line. Now one of the variables will be False by default. Now the message will NOT pop up, because the user is not very happy with having a car OR computer...

The next operator is no less simple - Or.

Help:
Performs a logical OR (addition) operation on two expressions.

Syntax:
result = expression1Orexpression2

The result will be true only if only one of the expressions is true. The values ​​returned by the operator are presented in the following table:

expression1 expression1 result
True True True
True False True
True Null True
False True True
False False False
False Null Null
Null True True
Null False Null
Null Null Null

And again a small example with the same Boolean variables User_Has_Computer and User_Has_Car, which in the process of some action took on the values ​​either True or False.

Dim User_Has_Computer As Boolean
Dim User_Has_Car As Boolean
User_Has_Computer = False
User_Has_Car = True

If User_Has_Computer Or User_Has_Car Then
MsgBox "You are almost a happy person!"
End If

When checking this condition, a message will pop up on the screen notifying that the user is almost a happy person, because he has a car, but no computer:-|...

Not operator

Help:
Performs a logical negation operation on an expression.

Syntax:
result = Not expression

Those. result becomes the opposite expression(excluding Null). The values ​​returned by the operator are presented in the following table:

Example:

Dim User_Has_Computer As Boolean

User_Has_Computer = False

If Not User_Has_Computer Then
MsgBox "I sympathize...:)"
End If

Let me explain: the If ... Then condition is executed when some expression (in place ...) is True. Let's imagine that the variable User_Has_Computer = False. Then the line
Not User_Has_Computer gives the result True and the condition is true!

Xor operator.

Help:
Performs an exclusive OR operation on two expressions.

Syntax:
[result =] expression1 Xor expression2

In principle, this operator is similar to Or, but it is not simple OR, and exclusive OR. If one and only one of expressions true (has the value True), result is True. And the operator Or allows two true expressions. Result

Example:

Dim User_Has_Computer As Boolean
Dim User_Has_Car As Boolean
User_Has_Computer = False
User_Has_Car = True

If User_Has_Computer Xor User_Has_Car Then
MsgBox "You need a car AND a computer!"
End If

Here, just like in the previous example, the condition is met only when the line User_Has_Computer Xor User_Has_Car is true (= True). And it is true if and only if only one of the conditions is true. If both, then the condition will no longer be fulfilled.

Eqv operator.

Help:
Used to test the logical equivalence of two expressions.

Syntax:
result = expression1 Eqv expression2

Everything is quite simple here: the result will be true if both expressions are either true or false. Result is defined as follows:

Example:

Dim User_Has_Computer As Boolean
Dim User_Has_Car As Boolean
User_Has_Computer = True
User_Has_Car = True

If User_Has_Computer Eqv User_Has_Car Then
MsgBox "You are either a happy or unhappy person!"
End If

Here the computer will call you a “happy or unhappy” person, because... the condition is satisfied only when the user has both a car and a computer or neither.

Brief theoretical information

1. Syntax and semantics of the VBA programming language

The syntax of a programming language is a set of rules that describe combinations of alphabetic characters that are considered a correctly structured program (document) or its fragment.

The basic syntactic principles of this language are as follows:

  • VBA is case insensitive;
  • To comment out code to the end of the line, use a single quote (") or the REM command;
  • character values ​​must be enclosed in double quotes (");
  • the maximum length of any name in VBA (variables, constants, procedures) is 255 characters;
  • beginning of a new statement - transfer to a new line (semicolon, as in C, Java, JavaScript, is not used for this);
  • There are no restrictions on the maximum line length (although in the editor only 308 characters can fit in a line). Multiple statements on the same line are separated by colons:
    MsgBox "Check 1" : MsgBox "Check 2"
  • For ease of reading, you can combine several physical lines into one logical one using a space and an underscore after it:
    MsgBox "Message to user" _
    & vUserName

The semantics of a language is the semantic meaning of words. In programming - the initial semantic meaning of operators, basic language constructs, etc.

Operator is the smallest unit of VBA code that can be executed. A statement can declare or define a variable, set a VBA compiler option, or perform some action in a program.

Arithmetic There are only 7 operators in VBA.

Four standard ones: addition (+), subtraction (−), multiplication (*), division (/), and three more:

  • exponentiation (^). For example, 2^3 = 8;
  • integer division (\). Divides the first number by the second, discarding (without rounding) the fractional part. For example, 5\2 = 2;
  • modulo division (Mod). Divides the first number by the second, returning only the remainder of the division. For example, 5 Mod 2 = 1.

The assignment operator in VBA is the equals sign. You can write it like this:

Let nVar = 10
or even simpler:
nVar = 10

Here, do not confuse the equals sign with the equals operator. The last expression means "set the value of nVar to 10", and if the line looks like this: If (nVar = 10) , then it means "if the value of nVar is 10".

Comparison operators in VBA there are only 8:

  • equality (=). For example, If (nVar = 10);
  • greater than (>) and less than (10);
  • greater than or equal to (>=) and less than or equal to (= 10);
  • not equal (). For example, If(nVar10);
  • comparison of objects (Is). Determines whether object variables refer to the same object or to different ones. For example, If (obj1 is obj2);
  • similarity (Like). Compares a string object to a pattern and determines whether the pattern matches.

Comparison operators always return True(if the statement is true) or False(if the statement is false).

Very often, when checking several conditions, they use logical operators:

  • And - logical AND. Both conditions must be true;
  • Or - logical OR. At least one of the conditions must be true;
  • Not - logical negation. Returns True if the condition is false;
  • Xor is a logical exception. In the expression E1 Xor E2 returns True if only E1 = True or only E2 = True, otherwise False;
  • Eqv - equivalence of two expressions, returns True if they have the same value;
  • Imp - implication, E1 Imp E2 returns False if E1 = True and E2 = False, otherwise True.

You need to remember about And, Or, Not, other logical operators are rarely used.

Variables- These are containers for storing changeable data. Almost no program can do without them. For simplicity, a variable can be compared to a number in a wardrobe - you give some data to the wardrobe, and in response you are given a number. When you need this data again, you “present the number” and receive it.

Each variable has a name. A variable is accessed by name. The rules for choosing names in VBA are the same for many elements (variables, constants, functions, procedures, etc.):

  • the name must begin with a letter;
  • must not contain spaces or punctuation symbols (exception is the underscore);
  • maximum length - 255 characters;
  • must be unique in the current scope
  • reserved words (those that are highlighted in blue in the code editor window) cannot be used.

When creating VBA programs, it is recommended to decide on the rules by which objects will be named - a naming convention. The most commonly used is the so-called Hungarian agreement (in honor of one of the Microsoft programmers, Charles Simonyi, a Hungarian by nationality):

  • The variable name must begin with a prefix, written in lowercase letters. The prefix indicates what exactly will be stored in this variable:
    str (or s) - String, character value;
    fn (or f) - function;
    sub - procedure;
    c (or all letters of the name are capitalized) - constant(a container for storing data that, unlike variables, does not change during the execution of a VBA program);
    b - Boolean, logical value (True or False);
    d - date;
    obj (or o) - object reference;
    n - numeric value;
  • names of functions, methods, and each word in a compound word must begin with a capital letter:
    MsgBox objMyDocument.Name
    Sub CheckDateSub()

Data type are considered to be the most fundamental concepts of any programming language. A data type defines the set of valid values ​​that a value (variable or constant) can take and the set of actions that can be performed on that value.

2. Basic VBA Data Types

VBA provides the following data types:

  • numeric:
    Byte- an integer from 0 to 255, needed for storage 1 byte memory;
    Integer- an integer from −32,768 to 32,767, 2 bytes ;
    Long- a large integer from −2 147 483 648 to 2 147 483 647, 4 bytes ;
    Currency(monetary) - a large decimal number with 19 places, including 4 decimal places
    (from –922337203685477.5808 to 922337203685477.5807), 4 bytes,
    used to store numbers when accuracy is extremely important, which happens when calculating with monetary units;
    Decimal- an even larger decimal number with 29 positions (after the decimal point you can use from 0 to 28 positions), 8 bytes;
    Single And Double- floating point values ​​( 4 and 8 bytes)
    (from -3.402823·10 38 to -1.401298·10 -45 for negative values ​​and
    from 1.401298·10 -45 to 3.402823·10 38 for positive values ​​for Single, and
    from -1.79769313486232·10 308 to -4.94065645841247·10 -324 for negative values ​​and
    from 4.94065645841247·10 -324 to 1.79769313486232·10 308 for positive values ​​for Double);
  • string( String variable length (up to approximately 2 billion characters) and fixed length (up to approximately 65,400 characters));
  • date and time ( Date- from 01.01.100 to 31.12.9999), 8 bytes;
  • boolean ( Boolean- can only store True and False values), 2 bytes;
  • object ( Object- stores a reference to any object in memory);
  • Variant- a special data type that can store any type of data, 16 bytes +1
Data type designations are language keywords (and are highlighted when typed in the VBA editor). Different operations are allowed on different data types. There are three main types of operations in VBA:
  • mathematical, performed on numbers, their result is numbers;
  • relational operations can be applied not only to numbers; their result is a value of the logical type;
  • logical are used in logical expressions and their result is logical values.

3. Operation priorities

A priority Operation
1
2
3
4
5
6
7
8
9
10
11
12 		Function call and parentheses
^
- (change of sign)
*, /
\
Mod
+, –
>, <, >=, <=, <>, =
Not
And
Or
Xor

4. Mathematical functions

Function Return value
Abs(<число>) Modulus (absolute value) of a number
Atn (<число>) Arctangent
Cos (<число>) Cosine
Exp (<число>) Exponent, i.e. the result of raising the base of the natural logarithm to the specified power
Log(<число>) Natural logarithm
Rnd (<число>) Random number from the interval)