VBA. Лабораторна робота 1
Ознайомлення із VBA.
Код, написаний на VBA компілюється у проміжну мову, так званий p-code (packed code), який зберігається на стороні аплікації (Access, Excel, Word) як окремий потік у структурних файлах (.doc or .xls). Цей код запускається у віртуальній машині.
1.1 variables, constants und types
Variables and constants are located in reserved places of memory. They store numbers, letters or even whole types. Variables are declared with the Dim statement:
Dim VariablesName As VariablesType
Dim iColumn As Integer
allocates a 2 bytes of dynamic memory large area named iColumn for storing values of integer numbers (-32.768 to 32.767). At this point you can assign values to the variable iColumn.
iColumn = 12345
Global variables are declared on top of the module.
Public g_iColumn As Integer
Now, any function or procedure can access the variable g_iColumn. On top of the module user-defined types can be declared by using the statement Type.
Type TTesttyp
Variablesname1 As Integer
Variablesname2 As String
End Type
Now you can assign variables to this type (look for chapter variables). This variables behave like objects, Variablesname1 and Variablesname2 are the "properties" of the type TTesttyp. Important keywords for declaring variables are Public, Private and Static. The usage of one of these describe, how other procedures or functions can access these variables. "Public" declared variables can be accessed program-wide, any procedure is allowed to access this variable. "Private" declared variables are visible only in the module, they were declared in. If a variable is declared "Static" in a procedure or function, it is accessible after the procedure has finished.
1.2 Procedures and functions
Procedures and functions are sub-programs, which can take parameters. Procedures and functions are used if equal "jobs" are done at different places in the source code, for example the printing of "done" or calculating the square root of a number. Procedures in VBA are defined with the Sub-statement followed by the name and an optional list of parameters. This procedure sets the values of the cells in row Var1 in the columns 2 and 3 to zero
Sub Test (Var1 As Integer)
Cells(Var1, 2).Value = 0
Cells(Var1, 3).Value = 0
End Sub
Functions are able to, in difference to procedures, return values. They are inevitable, if complex algorithms are used to calculate values. Functions are declared with the Function-statement followed by the name of the function, the (optional) list of parameters and the type of return. This function computes the product of the square root of Var1 and the sine of Var1 and adds the cosine of Var1.
Function Test2(Var1 As Single) As Single
Test2 = Sqrt(Var1) * Sin(Var1) + Cos(Var1)
End Function
Procedures and functions are called with its name and the (optional) list of parameters.
Sub mainprogram ()
  Test (5) 'sets the cells in row 5 an columns 2 and 3 to zero
  xyz = test2 (7) 'the variable xyz now contains the square root of 7 *sine of 7 + cosine of 7
End Sub
The mainprogram sets the values of the cells (5,2) and (5,3) to zero and stores the result of the multiplication of square root of 7 and sine of 7 added to cosine of 7 in the variable xyz. Parameters are passed in different ways. ByRef ahead of the variable's name says, that the variable is referenced, the procedure accesses the variable directly and is able to change the value of it. ByVal facing the variables name says, that a copy of the variable is made, changes doesn't result in changes outside of the sub-program. If the statement Optional is used, every parameter declared after this one must be declared Optional. Optional says, that this parameter doesn't need to be passed.
1.3 Loops
Loops are used to define repeated actions. The different types of loops supported by VBA variate in the type of stopping-condition and the minimal number of runs.
1.3.1 For ...To ... Next
A For-To-Next-Loop runs until the counter (a variable of a countable type) exceeds the stopping value (in this example the value of the variable endvariable). Counting starts at the beginning value (in this example the value of the variable startvariable). If the loop need to be counting backwards, the variable stepvariable needs to be negative. In this case the loop stops after the counter falls below the endvariable. In "normal" cases, that means if the counter isn't changed in the loop, For-To-Next-Loops run "stopping value minus beginning value"-times. The optional stepping value divides the number of go-throughs. For counter = startvariable To endvariable [Step stepvariable]  [statements]Next [counter]The loop can be exited at any time with the following statement: Exit For
1.3.2 For Each ... Next
A For-Each-Next-Loop runs till every member of a group was accessed. For Each Element In Group  [statements]Next [Element]For-Each-Next-Loops can be left, like the For-To-Next-Loop, with this statement: Exit For
1.3.3 Do While/Until ... Loop
A Do-While-Loop loop runs if the condition set at the beginning of the loop is true. The minimal number of runs is zero, in this case the condition was false at the time of entering the loop. A Do-Until-Loop loop runs till the condition set at the beginning of the loop is true. Do [{While | Until} condition]  [statements]LoopAn early exit is done with: Exit Do
1.3.4 Do Loop ... While/Until
A Do-Loop-While loop runs if the condition set at the end of the loop is true. The minimal number of runs is 1. A Do-Loop-Until loop proceeds till the condition set at the end of the loop is true. Do  [statements]Loop [{While | Until} condition]The command for early exit is: Exit Do
1.4 Branches
Branches allow the execution of different statements belonging to different conditions.
1.4.1 If ... Then
If, for example, statement "a" need to be performed if the variable b is 5, following instructions will work: If b = 5 Then statement aFor all other cases (b <> 5), this code need to be implemented:If b = 5 Then  statement aElse  statement cEnd IfThe universal syntax: If condition_1 Then  [statements_1][ElseIf condition_n Then  [statements_n] ...[Else  [statements_0]]End IfThe statements_1 are executed if condition_1 is true, in all other cases the program continues checking the condition_2 and to stements_n.
1.4.2 Select Case
If there's only one variable, which needs to be checked for different values the "Select-Case" branch is very efficient. An If-Then construction can be used everytime you can use a "Select-Case" but the source code is easier to read and the program runs a little faster if you use "Select-Case". Select Case Testvariable[Case conditions  [statements]] ...[Case Else  [statements]]End SelectSelect Case z  Case 1    statement a  Case 2    statement b  Case 3    statement c  Case Else    statement dEnd SelectExecutes statement a if z = 1, if z = 3 statement c will be run and so on. If z = 1 Then  statement aElse  If z = 2 Then    statement b  Else    If z = 3 Then      statement c    Else      statement d    End If  End IfEnd ifImagining a lot of source code for each statement a "Select-Case" will do better.
1.5 Objects
Objects are very important in VisualBasic for Applications. They have attributes (comparable to variables) and methods (similar to procedures). The Excel-Object "cells" has the attribute column and the method select. Attributes can, but not need to, be objects too. Using objects, structures and dependencies will come much clearer.
1.5.1 Attributes
Attributes are accessed like variables. Some attributes are read-only, assigning values to such variables leads to errors. Usually this attributes can be changed by methods. The advantage of such a construction comes clear while thinking about a variables value affecting other variables. One application's inside counter, pointer or variables can be set, for instance. Application.Workbooks.Count is a read-only attribute. Application.Workbooks.Count = Application.Workbooks.Count + 1directly leads to an error-message, while set Variable = Application.Workbooks.Addprocesses without failure. Repeatedly accessing attributes of an object can be simplified by using the With-statement
With Application
.ScreenUpdating = False
.Statusbar = "Working..."
.Height = 400
End With
instead of
Application.ScreenUpdating = False
Application.Statusbar = "Working..."
Application.Height = 400
increases readability.
1.5.2 Methods
Methods access attributes of objects, without letting the programmer know, which special attributes the method changes.
Assistant.Move 100, 100
for instant, moves the assistant to the coordinates 100, 100 of the screen. Additionally the attributes "left" and "top" of the assistant-object are changed. It is absolutely possible to set these attributes by hand to 100, the effect stays the same