VBA development technology. (Lecture 6) презентация

Main steps during task solving
goal of the task;
mathematical model;
algorithm;
structure of the data;
GUI design;
code writing;
implementation with IDE;
application analysis;
testing;
performing of a program;
analysis of results.

Control names
Name of a control is set up with Name property.
Default names are:
UserForm1; TextBox1, TextBox2; Label1, Label2.
Prefix naming:
frmCheck; lblPrice; txtPrice; cmdCalculate

Common prefixes

Naming rules
No more than 40 symbols.
Names should be easy to read and understand
txtNewPrice, txtNew_Price
No spaces, dots and other special symbols instead underscore

Dot notation
.
txtPrice.Text = 0
txtPrice.BackColor = vbYellow
txtPrice.Visible = False
Each control has its own default property which may be used without dot notation, e.g.
txtCost.Text = txtPrice.Text*txtQuantity.Text
is the same as
txtCost = txtPrice * txtQuantity
in case if all controls are TextBoxes.

Variables and constants
Variables are used to store interim values
Dim operator is used to describe such variables
Constants are used for constant variables like PI (3.14) etc.
Const operator is used to describe such variables

Variable description
[Public|Private] Dim
As
Data type – set of possible values for this variable
Dim i As Integer
…
i = 0
…
i = i + 1
Dim operator reserves memory field of specific type for corresponding data type.

Typical data types

Constant description
[Public|Private] Const
= Value
Const PI = 3.14159265
Const Rate$ = 22.45
Const CompanyName= “Microsoft”
…
L = 2 * PI * R
…
Embedded into VBA constants start with vb prefix.
vbRed – red color
vbSunday – sunday
vbCrLf – new line
vbYesNo – Yes and No buttons

Boolean data type
The statement – is a sentence which can be true or false.

Logical operations
Operand – value that takes part in operations
Logical NOT is statement that is opposite to operand: 5 > 2 =True Not (5 > 2) = False
Logical AND is a statement when both its operands are true, e.g.
a < x < b is the same as (a < x) And (x < b)
Logical OR is a statement when at least one of its operands is true.
(i=5) Or (i = n)

Date and time datatype
8 bytes in memory
Default USA format:
#m/d/yy h:mm:ss#
#9/23/06 19:40#

Functions to work with data

Interval values for DateAdd and DateDiff
DateAdd("m",3,Date) – add 3 months to date,
DateDiff("ww",#1.01.2001#,Date) – amount of weeks between date and begin of century.

Transform to type functions

Priority of operations
1. Arithmetical,
2. Comparison,
3. Logical.
Logical:
1) Negation (Not);
2) Logical AND (And);
3) Logical OR (Or).
Arithmetical:
1) powering (^);
2) negation (-);
3) multiplication and division (*, /);
4) integer division (\);
5) modular summation (Mod);
6) addition and subtraction;
7) string concatenation (&).
Comparison:
=, <>, <, <=, >, >=

Variable assignment
variable = value
Firstly value on the right side is calculated, then result is assigned to variable.
txtCost = txtPrice * txtQuantity

Application with different data types
Data type is assigned using Dim operator.
You may use only variables those were described with Dim earlier.
Option Explicit operator allows VBA environment to look about this rule

Назначение условного оператора
Разветвляющийся процесс – из нескольких вариантов выбирают только один, причем выбор зависит от условия.

Conditional operator
One-line model is used when each branch contains no more than single operator
Block model is used when at least one branch has more than one operator

One-line model
If Condition Then Operator1 Else Operator2
If a > b Then max = a Else max = b
If txtPrice = "" Then MsgBox “Please input price", _ vbExclamation, “Attention"