Visual Basic for Excel Review Type of variables

Visual Basic for Excel : Review Type of variables, expressions, giving value to a variable Basic types: Integer(%) Double(#) Single String($) Boolean Definition of types is not obligatory! (but very useful…) Basic operations: + - * / ^ (arithmetical) And Or Not (logical) Relations: = < > <= >= <> Arithmetical expression: a*a*a*a - 81 Logical expression: fa*fm<0 Giving value: variable = expression like: fa = a^4 - 81 Structure of a VBA program: Sub <name> ( ) <definition of types (optional)> <statements> End Sub

Visual Basic for Excel – conditional statements If – Then and If – Then – Else statements The values of X are (in both cases): 2 , -4 , 3 , -1 give: X , write: X yes Y = X*X give: X , write: X X is even ? no yes Y = X*X no Y=X+8 write: Y IF <log. expr. > THEN <statement> Results: (2, 4) ; (-4, 16 ) ; (3, 16 ) ; (– 1, 16 ) write: Y IF <log. expr. > THEN <statement 1> ELSE <statement 2> Results: (2, 4 ) ; (-4, 16 ) ; (3, 11 ) ; (-1, 7 )

Loops Example: the average of two exams for n students start give: n k=1 give: Name, E 1 , E 2 k<=n ? entrance-testing loop no yes give: Name, E 1 , E 2 Ave=(E 1+E 2)/2 write: Name , Ave k=k+1 yes k<=n ? k=k+1 no stop exit-testing loop What about if n=0 ? vége

Loops Example: the average of two exams for n students Do - Loop While start give: n k=1 VBA code give: Name, E 1 , E 2 Ave=(E 1+E 2)/2 write: Name , Ave k=k+1 yes k<=n ? no stop exit-testing loop n=Input. Box(“n=? ”): k=1 Do Name=Input. Box(“Name=? ”) E 1=Input. Box(“E 1=? ”) E 2=Input. Box(“E 2=? ”) Ave=(E 1+E 2)/2 : Cells(k, 1)=Name Cells(k, 2)=Ave : k=k+1 Loop While k<=n

Loops Example: the average of two exams for n students Do While - Loop start entrance testing loop give: n k=1 k<=n ? Visual Basic code no yes give: Name, E 1 , E 2 Ave=(E 1+E 2)/2 write: Name , Ave k=k+1 vége n=Input. Box(“n=? ”): k=1 Do While k<=n Name=Input. Box(“Name=? ”) E 1=Input. Box(“E 1=? ”) E 2=Input. Box(“E 2=? ”) Ave=(E 1+E 2)/2 : Cells(k, 1)=Name Cells(k, 2)=Ave : k=k+1 Loop

Loops Example: the average of two exams for n students For To - Next start Visual Basic code n=Input. Box(“n=? ”) FOR k=1 TO n Name=Input. Box(“Name=? ”) E 1=Input. Box(“E 1=? ”) E 2=Input. Box(“E 2=? ”): Ave=(E 1+E 2)/2 Cells(k, 1)=Name : Cells(k, 2)=Ave NEXT k entrance testing loop give: n k=1 k<=n ? no yes give: Name, E 1 , E 2 Ave=(E 1+E 2)/2 write: Name , Ave k=k+1 vége

VBA – summary of the statements we know Giving value, input, output a=1: b= Input. Box(„b? ”, , 2) : c=cells(1, 1) D=b^2 -4*a*c : cells(2, 2)=(-b+sqr(D))/(2*a) ? Sequential statements yes ? no no Conditional statements ? Loops yes ? no yes For – Next Do - Loop Go To <label> no yes