VBA user tried using Python / R: Iterative processing

Introduction

I am a VBA user who started studying machine learning. As a memorandum, I would like to summarize the Python / R grammar while comparing it with VBA.

table of contents

-Repeat processing -for statement -Simple example -Other examples -More -while statement -End Loop -Skip Loop -Other -Summary -List -Whole program

Iterative processing

This time, I will summarize the iterative processing (loop processing).

for statement

First is the for statement.

Simple example

Here is an example of a simple for statement.

Python

Python3

#for statement
for i in range(5):
    print(i)
# 0
# 1
# 2
# 3
# 4
print(range(5))       # range(0, 5)
print(type(range(5))) # <class 'range'>
print(list(range(5))) # [0, 1, 2, 3, 4]

In Python, blocks of for statements are represented by indentation. range (5) is an iterable object (repeated object) that returns the integers 0,1,2,3,4 in order, and is a list [0] in list (range (5)) . , 1, 2, 3, 4] .

R

R

for (i in 0:4) {
  print(i)
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4
print(0:4)   # 0 1 2 3 4

0: 4 is a vector of integer values ​​from 0 to 4.

VBA

VBA

For i = 0 To 4
    Debug.Print i
Next i
' 0
' 1
' 2
' 3
' 4

Other examples

Python

Python3

Ns = [2, 3, 5, 7, 11]
for i in range(len(Ns)):
    print(Ns[i])
# 2
# 3
# 5
# 7
# 11

Ns = [2, 3, 5, 7, 11]
for n in Ns:
    print(n)
# 2
# 3
# 5
# 7
# 11

In Python, lists are also iterable objects, so you can specify them after in.

R

R

Ns <- c(2, 3, 5, 7, 11)
for (i in 1:length(Ns)) {
  print(Ns[i])
}
# [1] 2
# [1] 3
# [1] 5
# [1] 7
# [1] 11

Ns <- c(2, 3, 5, 7, 11)
for (n in Ns) {
  print(n)
}
# [1] 2
# [1] 3
# [1] 5
# [1] 7
# [1] 11

Even in R, you can specify a vector after in.

VBA

VBA

A = Array(2, 3, 5, 7, 11)
For i = LBound(A) To UBound(A)
    Debug.Print A(i)
Next i
' 2
' 3
' 5
' 7
' 11

In VBA, if you write A = Array (2, 3, 5, 7, 11), the index of array A will be an integer from 0 to 4 (by default). LBound (A) returns the smallest index of array A (here 0) and UBound (A) returns the largest index of array A (here 4).

further

You can also write it like this in Python.

*** enumerate function *** The enumerate function is a built-in function that returns an iterable object that returns tuples of the form (element number, element) one by one for the iterable object.

Python

Python3

Ns = [2, 3, 5, 7, 11]
for i, n in enumerate(Ns):
    print('Ns[' + str(i) + '] = ' + str(n))
# Ns[0] = 2
# Ns[1] = 3
# Ns[2] = 5
# Ns[3] = 7
# Ns[4] = 11

Ns = [2, 3, 5, 7, 11]
for i, n in enumerate(Ns):
    print('Ns[{}] = {}'.format(i, n))
# Ns[0] = 2
# Ns[1] = 3
# Ns[2] = 5
# Ns[3] = 7
# Ns[4] = 11

If you write the same thing in R and VBA, it will be like this. R

R

Ns <- c(2, 3, 5, 7, 11)
for (i in 1:length(Ns)) {
  n = Ns[i]
  cat(paste0("Ns[", i, "] = ", n, "\n"))
}
# Ns[1] = 2
# Ns[2] = 3
# Ns[3] = 5
# Ns[4] = 7
# Ns[5] = 11

VBA

VBA

A = Array(2, 3, 5, 7, 11)
For i = LBound(A) To UBound(A)
    Debug.Print "Ns[" & CStr(i) & "] = " & Str(A(i))
Next i
' Ns [0] = 2
' Ns [1] = 3
' Ns [2] = 5
' Ns [3] = 7
' Ns [4] = 11

*** zip function *** The zip function is a built-in function that returns an iterable object that returns one pair of elements from each of the multiple iterable objects.

Python

Python3

numbers = list(range(5))
alphabets = 'abcde'
hiraganas = 'AIUEO'
for n, s, h in zip(numbers, alphabets, hiraganas):
    print('{}-{}-{}'.format(n, s, h))
# 0-a-Ah
# 1-b-I
# 2-c-U
# 3-d-e
# 4-e-O

while statement

Next is the while statement.

Python

Python3

i = 0
while i < 5:
    print(i)
    i = i + 1
# 0
# 1
# 2
# 3
# 4

i = 0
while i < 5:
    print(i)
    i += 1
# 0
# 1
# 2
# 3
# 4

i = i + 1 can also be written as i + = 1 using the compound assignment operator + =.

R

R

i <- 0
while (i < 5) {
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4

VBA

VBA

i = 0
Do While i < 5
    Debug.Print i
    i = i + 1
Loop
' 0
' 1
' 2
' 3
' 4

In VBA, in addition to While, there is also Until.

VBA

i = 0
Do Until i = 5
    Debug.Print i
    i = i + 1
Loop
' 0
' 1
' 2
' 3
' 4

If you write the same thing in Python and R, it looks like this. Python

Python3

i = 0
while not i == 5:
    print(i)
    i = i + 1
# 0
# 1
# 2
# 3
# 4

R

R

i <- 0
while (!(i == 5)) {
  print(i)
  i = i + 1
  
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4

End of loop

This is a method to exit the loop (end the iterative process) on the way.

Python

Python3

for i in range(5):
    if i == 2:
        break
    print(i)
# 0
# 1

R

R

for (i in 0:4) {
  if (i == 2) {
    break
  }
  print(i)
}
# [1] 0
# [1] 1

VBA

VBA

For i = 0 To 4
    If i = 2 Then
        Exit For
    End If
    Debug.Print i
Next i
' 0
' 1

To exit a loop in VBA, use Exit For for a For loop and Exit Do for a Do loop.

Skip loop

This is a method of skipping (skipping) a loop in the middle and moving to the next loop (progressing to the next iteration).

Python

Python3

for i in range(5):
    if i == 2:
        continue
    print(i)
# 0
# 1
# 3
# 4

R

R

for (i in 0:4) {
  if (i == 2) {
    next
  }
  print(i)
}
# [1] 0
# [1] 1
# [1] 3
# [1] 4

VBA

VBA

For i = 0 To 4
    If i = 2 Then
        GoTo continue
    End If
    Debug.Print i
continue:
Next i
' 0
' 1
' 3
' 4

There are no loop-skipping statements in VBA like Python's continue statement or R's next statement. If you really want to use it, use GoTo.

Other

There is also a repeat statement in R, but this is the same aswhile (TRUE)(the conditional expression of the while statement with TRUE (always true)), so it is not very useful. There seems to be no.

R

R

i <- 0
repeat {
  if (i == 2) {
    break
  }
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1

i <- 0
while (TRUE) {
  if (i == 2) {
    break
  }
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1

If you write the same thing in Python and VBA, it will be like this.

Python

Python3

i = 0
while True:
    if i == 2:
        break
    print(i)
    i += 1
# 0
# 1

VBA

VBA

i = 0
Do While True
    If i = 2 Then
        Exit Do
    End If
    Debug.Print i
    i = i + 1
Loop
' 0
' 1

Summary

List

A list of writing styles used in each language is summarized.

Whole program

The whole program used for reference is shown.

Python

Python3

#for statement
for i in range(5):
    print(i)
# 0
# 1
# 2
# 3
# 4
print(range(5))       # range(0, 5)
print(type(range(5))) # <class 'range'>
print(list(range(5))) # [0, 1, 2, 3, 4]

Ns = [2, 3, 5, 7, 11]
for i in range(len(Ns)):
    print(Ns[i])
# 2
# 3
# 5
# 7
# 11

Ns = [2, 3, 5, 7, 11]
for n in Ns:
    print(n)
# 2
# 3
# 5
# 7
# 11

Ns = [2, 3, 5, 7, 11]
for i, n in enumerate(Ns):
    print('Ns[' + str(i) + '] = ' + str(n))
# Ns[0] = 2
# Ns[1] = 3
# Ns[2] = 5
# Ns[3] = 7
# Ns[4] = 11

Ns = [2, 3, 5, 7, 11]
for i, n in enumerate(Ns):
    print('Ns[{}] = {}'.format(i, n))
# Ns[0] = 2
# Ns[1] = 3
# Ns[2] = 5
# Ns[3] = 7
# Ns[4] = 11

numbers = list(range(5))
alphabets = 'abcde'
hiraganas = 'AIUEO'
for n, s, h in zip(numbers, alphabets, hiraganas):
    print('{}-{}-{}'.format(n, s, h))
# 0-a-Ah
# 1-b-I
# 2-c-U
# 3-d-e
# 4-e-O

#while statement
i = 0
while i < 5:
    print(i)
    i = i + 1
# 0
# 1
# 2
# 3
# 4

i = 0
while i < 5:
    print(i)
    i += 1
# 0
# 1
# 2
# 3
# 4

#Instead of until
i = 0
while not i == 5:
    print(i)
    i = i + 1
# 0
# 1
# 2
# 3
# 4

#break statement
for i in range(5):
    if i == 2:
        break
    print(i)
# 0
# 1

#continue statement
for i in range(5):
    if i == 2:
        continue
    print(i)
# 0
# 1
# 3
# 4

#Instead of repeat statement
i = 0
while True:
    if i == 2:
        break
    print(i)
    i += 1
# 0
# 1

R

R

#for statement
for (i in 0:4) {
  print(i)
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4
print(0:4)   # 0 1 2 3 4

Ns <- c(2, 3, 5, 7, 11)
for (i in 1:length(Ns)) {
  print(Ns[i])
}
# [1] 2
# [1] 3
# [1] 5
# [1] 7
# [1] 11

Ns <- c(2, 3, 5, 7, 11)
for (n in Ns) {
  print(n)
}
# [1] 2
# [1] 3
# [1] 5
# [1] 7
# [1] 11

Ns <- c(2, 3, 5, 7, 11)
for (i in 1:length(Ns)) {
  n = Ns[i]
  cat(paste0("Ns[", i, "] = ", n, "\n"))
}
# Ns[1] = 2
# Ns[2] = 3
# Ns[3] = 5
# Ns[4] = 7
# Ns[5] = 11

#while statement
i <- 0
while (i < 5) {
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4

#Instead of until
i <- 0
while (!(i == 5)) {
  print(i)
  i = i + 1
  
}
# [1] 0
# [1] 1
# [1] 2
# [1] 3
# [1] 4

#break statement
for (i in 0:4) {
  if (i == 2) {
    break
  }
  print(i)
}
# [1] 0
# [1] 1

#next statement
for (i in 0:4) {
  if (i == 2) {
    next
  }
  print(i)
}
# [1] 0
# [1] 1
# [1] 3
# [1] 4

#repeat statement
i <- 0
repeat {
  if (i == 2) {
    break
  }
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1

i <- 0
while (TRUE) {
  if (i == 2) {
    break
  }
  print(i)
  i = i + 1
}
# [1] 0
# [1] 1

VBA

VBA

Sub test_repeat()
Dim i As Integer
Dim A As Variant

'for statement
For i = 0 To 4
    Debug.Print i
Next i
' 0
' 1
' 2
' 3
' 4

A = Array(2, 3, 5, 7, 11)
For i = LBound(A) To UBound(A)
    Debug.Print A(i)
Next i
' 2
' 3
' 5
' 7
' 11

A = Array(2, 3, 5, 7, 11)
For i = LBound(A) To UBound(A)
    Debug.Print "Ns[" & CStr(i) & "] = " & Str(A(i))
Next i
' Ns [0] = 2
' Ns [1] = 3
' Ns [2] = 5
' Ns [3] = 7
' Ns [4] = 11

'while statement
i = 0
Do While i < 5
    Debug.Print i
    i = i + 1
Loop
' 0
' 1
' 2
' 3
' 4

'until statement
i = 0
Do Until i = 5
    Debug.Print i
    i = i + 1
Loop
' 0
' 1
' 2
' 3
' 4

'Exit from the loop
For i = 0 To 4
    If i = 2 Then
        Exit For
    End If
    Debug.Print i
Next i
' 0
' 1
'For Do loops, Exit Do

'Realize continue statement in VBA using GoTo
For i = 0 To 4
    If i = 2 Then
        GoTo continue
    End If
    Debug.Print i
continue:
Next i
' 0
' 1
' 3
' 4

'Instead of repeat statement
i = 0
Do While True
    If i = 2 Then
        Exit Do
    End If
    Debug.Print i
    i = i + 1
Loop
' 0
' 1

End Sub

reference

• Python -Python Tutorial -Chainer Tutorial 2. Introduction to Python
• R
  • RjpWiki
  • R-Tips