|
For Loop Example |
While Loop Example |
Sub
test_for_loop()
For i = 1 To 50
ActiveCell.Formula = Round(ActiveCell.Value, 4)
ActiveCell.Offset(1, 0).Select
Next
End Sub |
Sub
test_while_loop()
Do While Not IsEmpty(ActiveCell.Offset(1,0))
ActiveCell.Formula =
Round(ActiveCell.Value, 4)
ActiveCell.Offset(1, 0).Select
Loop
End Sub |
| This loop round the each
value in a column of 50 rows to 4 decimal places. To see how
it works fill in a column of numbers in excel. then copy and past
the code into the excel VBA editor. Select the first row in the
column and run the macro "test_for_loop" |
This loop
round the each value in a column with any number of rows to
4 decimal places. To see how it works fill in a column of numbers
in excel. then copy and past the code into the excel VBA editor.
Select the first row in the column and run the macro "test_while_loop"
|
| |
|
| |
|
| |
|
|
if statement Example |
Function example |
Sub if_state()
Do While Not IsEmpty(ActiveCell.Offset(1, 0))
If ActiveCell.Value > 0.5 Then
ActiveCell.Offset(0, 1).Formula = "greater than .5"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub |
Function sum_matrix(matrix As
Range)
Dim number_of_rows As Integer
Dim number_of_columns As Integer
Dim sum
number_of_rows = matrix.Rows.Count
number_of_columns = matrix.Columns.Count
sum = 0
For i = 1 To number_of_rows
For j = 1 To number_of_columns
sum = sum +
matrix(i, j)
Next
Next
sum_matrix = sum
'return sum to excel
End Function |
| this macro
runs though a column of numbers and prints "greater than .5" next
to any number in the column greater than .5 |
This function takes in a range
(block of cells form excel) the range is named matrix. it sums all the
values in the range (matrix) and returns sum to the excel cell where the
function was written. |
|
|
|
|
|
|
Simple Function Example |
|
Function Area(height As Double,
width As Double)
Area = height * width
End Function |
|
| this function takes in two values
height and width and returns the area. Note that to return a value form the
function you set the name of the function equal to what you want to return
ie Area = height*width |
|
International Finance