Programming in the VBA environment

 
 
 
 
 
 
K. F. Gabdrakhmanova, L. Z. Samigullina 
 
 
 
 
 
 
PROGRAMMING IN THE VBA ENVIRONMENT  
 
 
Study manual 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Moscow    Vologda 
Infra-lngeneria 
2024 
1 

 
UDC 004.432 
BBK 32.973 
G12 
 
 
 
Reviewers: 
Doctor of Technical Sciences, Professor  
Magnitogorsk State Technical University named after G. I. Nosov Rif Mugalimov; 
Associate Professor, Candidate of Mathematical Sciences, Ufa State Petroleum  
Technological University Gulnara Izmailova 
 
 
 
Gabdrakhmanova, K. F. 
G12   
Programming in the VBA environment : study manual / K. F. Gabdrakhmanova, L. Z. Samigullina. – Moscow ; Vologda : Infra-lngeneria, 2024. – 
168 p. 
ISBN 978-5-9729-2096-9 
 
 
The tutorial discusses the basic provisions of programming in the Visual Basic for Applications (VBA) environment. Examples are given for solving programming and algorithmization problems in the discipline "Information Technology". The manual is intended for students 
in the field of training 21.05.06 Oil and gas engineering and technology and can be used by 
students of other fields of training, mastering VBA programming. The basic principles of programming, the stages of problem solving, the programs designing procedure are considered 
as well and the basics of programming in the VBA language are outlined. A large number of 
examples are given. The manual can be used for translation of technical and scientific texts 
within the framework of the discipline “Foreign language for professional communicatiɨn”. 
 
ȼ ɭɱɟɛɧɨɦ ɩɨɫɨɛɢɢ ɪɚɫɫɦɨɬɪɟɧɵ ɨɫɧɨɜɧɵɟ ɩɨɥɨɠɟɧɢɹ ɩɪɨɝɪɚɦɦɢɪɨɜɚɧɢɹ ɜ ɫɪɟɞɟ 
Visual Basic for Applications (VBA). ɉɪɢɜɟɞɟɧɵ ɩɪɢɦɟɪɵ ɞɥɹ ɪɟɲɟɧɢɹ ɡɚɞɚɱ ɩɪɨɝɪɚɦɦɢɪɨɜɚɧɢɹ ɢ ɚɥɝɨɪɢɬɦɢɡɚɰɢɢ ɩɨ ɞɢɫɰɢɩɥɢɧɟ «ɂɧɮɨɪɦɚɰɢɨɧɧɵɟ ɬɟɯɧɨɥɨɝɢɢ». ɉɨɫɨɛɢɟ 
ɩɪɟɞɧɚɡɧɚɱɟɧɨ ɞɥɹ ɫɬɭɞɟɧɬɨɜ ɩɨ ɧɚɩɪɚɜɥɟɧɢɸ ɩɨɞɝɨɬɨɜɤɢ 21.05.06 «ɇɟɮɬɟɝɚɡɨɜɵɟ ɬɟɯɧɢɤɚ ɢ ɬɟɯɧɨɥɨɝɢɢ» ɢ ɦɨɠɟɬ ɛɵɬɶ ɢɫɩɨɥɶɡɨɜɚɧɨ ɫɬɭɞɟɧɬɚɦɢ ɞɪɭɝɢɯ ɧɚɩɪɚɜɥɟɧɢɣ ɩɨɞɝɨɬɨɜɤɢ, ɨɫɜɚɢɜɚɸɳɢɦɢ ɩɪɨɝɪɚɦɦɢɪɨɜɚɧɢɟ ɜ ɫɪɟɞɟ VBA. Ɍɚɤɠɟ ɪɚɫɫɦɨɬɪɟɧɵ ɨɫɧɨɜɧɵɟ ɩɪɢɧɰɢɩɵ ɩɪɨɝɪɚɦɦɢɪɨɜɚɧɢɹ, ɷɬɚɩɵ ɪɟɲɟɧɢɹ ɡɚɞɚɱ, ɩɨɪɹɞɨɤ ɩɪɨɟɤɬɢɪɨɜɚɧɢɹ ɩɪɨɝɪɚɦɦ ɢ ɢɡɥɨɠɟɧɵ ɨɫɧɨɜɵ ɩɪɨɝɪɚɦɦɢɪɨɜɚɧɢɹ ɧɚ ɹɡɵɤɟ VBA. ɉɪɢɜɟɞɟɧɨ ɛɨɥɶɲɨɟ ɤɨɥɢɱɟɫɬɜɨ ɩɪɢɦɟɪɨɜ. ɉɨɫɨɛɢɟ ɦɨɠɟɬ ɛɵɬɶ ɢɫɩɨɥɶɡɨɜɚɧɨ ɞɥɹ ɩɟɪɟɜɨɞɚ ɬɟɯɧɢɱɟɫɤɢɯ ɢ 
ɧɚɭɱɧɵɯ ɬɟɤɫɬɨɜ ɜ ɪɚɦɤɚɯ ɞɢɫɰɢɩɥɢɧɵ «ɂɧɨɫɬɪɚɧɧɵɣ ɹɡɵɤ ɜ ɩɪɨɮɟɫɫɢɨɧɚɥɶɧɨɣ ɞɟɹɬɟɥɶɧɨɫɬɢ». 
 
UDC 004.432 
BBK 32.973 
 
ISBN 978-5-9729-2096-9 
© Gabdrakhmanova K. F., Samigullina L. Z., 2024 
 
© Infra-lngeneria, 2024 
 
2 

 
 
CONTENTS 
 
Introduction ............................................................................................................... 6 
1. Algorithm development. Block diagram. Algorithm structures ........................... 7 
1.1. The concept of the algorithm. Algorithm structures ........................................ 7 
2. Representation of data in memory, data types, identifiers, 
................................. 17 
variables, arrays 
....................................................................................................... 17 
2.1. Software method for recording algorithms ................................................... 17 
2.2. Data representation in memory ..................................................................... 19 
2.3. Data types ...................................................................................................... 20 
2.4. Identifiers, variables, arrays .......................................................................... 22 
2.5. Operations, expressions, operators 
................................................................ 30 
3. The VBA program structure, description operators, assignments ...................... 34 
3.1. The VBA program structure 
.......................................................................... 34 
3.2. Description operators .................................................................................... 37 
3.3. Assignment operators 
.................................................................................... 38 
4. The concept of a macro, creating, debugging a program 
.................................... 40 
4.1. The concept of a macro. Creating a macro ................................................... 40 
4.2.  Debugging, using the environment to debug programs ................................ 44 
4.3. Debugging tools ............................................................................................ 45 
4.4. Runtime Errors Handling .............................................................................. 48 
4.5. Error catching 
................................................................................................ 50 
4.6. Understanding Error Trapping ....................................................................... 51 
5. VBA control structures 
........................................................................................ 56 
5.1. If ... Then construction .................................................................................. 56 
5.2. If ...Then ...Else construction ........................................................................ 59 
5.3. Select Case construction ............................................................................... 62 
6. Loop operators. Nested Loops ............................................................................ 65 
6.1. Loop operators 
............................................................................................... 65 
6.2. Nested Loops 
................................................................................................. 69 
3 

 
7. Character data. Operations and functions for operating with character data 
...... 75 
7.1. Character data 
................................................................................................ 75 
7.2. Operations and functions for operating with character data ......................... 75 
8. Boolean vectors and operations for operating with them ................................... 85 
9. Subroutine-procedure .......................................................................................... 88 
9.1. General procedures 
........................................................................................ 89 
9.2. Creation of new procedures .......................................................................... 89 
9.3. Viewing Existing Procedures 
........................................................................ 90 
9.4. Calling Sub Procedures .................................................................................. 91 
9.5. Procedures in Standard Modules 
................................................................... 92 
10. Subroutine-function 
........................................................................................... 93 
10.1. Calling Function Procedures ........................................................................ 94 
10.2. Parameters passing mechanisms. Parameter data types.  
Passing parameters by value ................................................................................. 94 
10.3. Passing parameters by reference .................................................................. 95 
10.4. Unknown number of parameters .................................................................. 97 
10.5. Recursion 
...................................................................................................... 98 
11. Use of objects, their properties and methods. Forms 
...................................... 103 
11.1. Working with objects ................................................................................ 103 
11.2. Origin of objects. Object Basics ............................................................... 103 
11.3. Setting property values 
.............................................................................. 106 
11.4. Getting Property Values 
............................................................................ 106 
11.6. Object relationship .................................................................................... 108 
11.7. Applying Properties and Methods to Set Members .................................. 108 
11.8. Shared Sets in VBA .................................................................................. 109 
11.9. Form development. Form Events and Methods ........................................ 110 
11.10. Communication between objects. Creation of objects.  
Using and declaring variables for objects ........................................................... 112 
11.11. Forms as objects. FSO object 
.................................................................. 116 
12. Controls in VBA Applications ........................................................................ 124 
4 

 
12.1. Control buttons ........................................................................................... 124 
12.2. Labels ......................................................................................................... 125 
12.3. Text fields 
................................................................................................... 126 
12.4. Working with text in a text field ................................................................ 127 
12.5. Checkboxes ................................................................................................ 128 
12.6. Creating groups of radio buttons. Selecting switches ................................ 129 
12.7. Combined windows and lists 
...................................................................... 130 
12.8. Scrollbars as input devices ......................................................................... 131 
12.9. Graphic window as a container .................................................................. 132 
12.10. Image Control 
........................................................................................... 132 
12.11. Other controls ........................................................................................... 133 
Bibliography .......................................................................................................... 140 
Appendix 1 ............................................................................................................ 141 
 
 
 
5 

 
INTRODUCTION 
 
The tutorial discusses the issues of visual programming. To describe the theory 
and practice of visual programming, the VBA language is used, the appendix provides 
examples of problem solving and some variants of tasks. 
As a result of discipline mastering the, the student must know the basic provisions of computer science, the concept of information; be able to use standard application software packages to solve practical problems on a personal computer (PC); possess the basic methods of operating on a PC with application software, computer 
graphics. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6 

 
1. ALGORITHM DEVELOPMENT. BLOCK DIAGRAM.  
ALGORITHM STRUCTURES 
 
1.1. The concept of the algorithm. Algorithm structures 
Algorithm is 
a. description of the sequence of actions to solve the problem or achieve the goal 
[2, 3]; 
b. rules for basic data processing operations performing [4]; 
c. description of calculations done by mathematical formulas [1, 3]. 
Before starting the algorithm development, it is necessary to clearly understand 
the task: what is required to be obtained as a result, what initial data is needed and what 
is available, what restrictions exist on this data. Next, you need to write down what 
actions you need to take to get the desired result from the source data. 
In practice, the following forms of algorithms representation are most common 
[3, 4]: 
− verbal (natural language recordings); 
− graphic (images from graphic symbols); 
− pseudocodes (semi-formalized algorithms descriptions in a modern algorithmic language, including both programming language elements the and natural 
language phrases, generally accepted mathematical notation, etc.); 
− software (texts in programming languages). 
The verbal method of writing algorithms is a successive stages of data processing 
description. The algorithm is given in an arbitrary presentation in natural language. 
Example. Write down an algorithm for finding the greatest common divisor 
(GCD) of two natural numbers. 
The algorithm can be as follows: 
1. Set two numbers. 
2. If the numbers are equal, then take any of them as an answer and stop, otherwise continue executing the algorithm. 
7 

 
3. Determine the larger of the numbers. 
4. Replace the larger of the numbers with the difference of the larger and smaller 
of the numbers. 
5. Repeat the algorithm from step 2. 
The described algorithm is applicable to any natural numbers and should lead to 
the problem solution. Make sure of this yourself by using this algorithm to determine 
the largest common divisor of the numbers 125 and 75. 
The verbal method is not widely used for the following reasons: 
− such descriptions are not strictly formalizable; 
− verbosity of entries; 
− allow interpretation of individual prescriptions ambiguity. 
The graphical way of algorithms representing is more compact and visual compared to the verbal one. 
In graphical representation, the algorithm is depicted as a sequence of interconnected functional blocks, each of which corresponds to one or more actions performance. 
Such a graphical representation is called an algorithm diagram or a flowchart. 
Pseudocode is a system of notation and rules designed for uniform algorithms 
recording. 
It occupies an intermediate place between natural and formal languages. 
On the one hand, it is close to an ordinary natural language, so algorithms can 
be written and read in it as plain text. On the other hand, some formal constructions 
and mathematical symbols are used in pseudocode, which brings the algorithm record 
closer to the generally accepted mathematical record. 
The pseudocode does not adopt strict syntactic rules for writing commands inherent in formal languages, which makes it easier to write an algorithm at the design 
stage and makes it possible to use a wider set of commands designed for an abstract 
performer. However, pseudocode usually has some constructions inherent in formal 
languages, which facilitates the transition from writing in pseudocode to writing an 
algorithm in a formal language. In particular, in pseudocode, as well as in formal 
8 

 
languages, there are service words, the meaning of which is determined once and for 
all. There is no single or formal pseudocode definition, so various pseudocodes are 
possible, differing in a set of service words and basic constructions. 
A flowchart is a graphical algorithm representation in which it is depicted as a 
sequence of interconnected functional blocks, each of which corresponds to the performance of one or more actions. 
In the flowchart, each type of action (input of input data, calculation of expression values, verification of conditions, control of repetition of actions, completion of 
processing, etc.) corresponds to a geometric figure represented as a block symbol. 
Block symbols are connected by transition lines that determine the sequence of actions. 
Algorithms can be represented as some structures consisting of separate basic 
elements. The logical structure of an algorithm can be represented by a combination of 
three basic structures: following, branching, and looping. 
A characteristic feature of the basic structures is the presence of one input and 
one output in them. 
1. The basic structure of following. It is formed from a sequence of actions following one after another (Fig. 1): 
 
 
action 1 
 
 
action 1 
 
action 1 
 
 
Fig. 1. Diagram of the basic structure of following 
 
2. The basic structure of branching. Provides, depending on the result of checking the condition (yes or no), the choice of one of the alternative ways of the algorithm. 
Each of the paths leads to a common exit, so the algorithm will continue to work regardless of what path is chosen. 
9 

 
The branching structure exists in four main variants (Fig. 2a, 2b(1,2), 2c, 2d): 
− if-then; 
yes 
− if-then-otherwise; 
condition 
− choice; 
− choice-otherwise. 
actions 
no 
a) if-then 
if the condition 
then actions 
end if 
 
 
 
Fig. 2a. “If – then” algorithm control flow chart 
 
yes 
b) if-then-otherwise 
condition 
if the condition 
then actions 1 otherwise 
actions 
no 
actions 2 end if 
 
Fig. 2b (1, 2). “If – then –otherwise” algorithm control flow chart 
c) selection                                      
under condition 1: actions 1 
yes 
no 
under condition 2: actions 2 
condition 
.......... 
actions 1 
actions 2 
 
 
 
under condition N: actions N  
yes 
end of selection 
condition 
actions 1 
 
no 
 
 
yes 
actions 2 
condition 
 
 
 
 
 
no 
Fig. 2c. "Choice" algorithm control flow chart 
10