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.

Python R VBA
for statement for i in ...:
 ...
for (i in ...) {
 ...
}
For i = ... To ...
 ...
Next i
while statement while ...:
 ...
while (...) {
 ...
}
Do While ...
 ...
Loop
until statement while not ...:
 ...
while (!(...)) {
 ...
}
Do Until ...
 ...
Loop
repeat statement while True:
 ...
repeat {
 ...
}
Do While True
 ...
Loop
break statement break break Exit For
Exit Do
continue statement continue next

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

Recommended Posts

VBA user tried using Python / R: Iterative processing
VBA user tried using Python / R: Matrix
VBA user tried using Python / R: conditional branching
VBA user tried using Python / R: string manipulation
VBA user tried using Python / R: basic grammar
VBA user tried using Python / R: String manipulation (continued)
VBA users tried using Python / R: basic arithmetic operations
VBA users tried using Python / R: logical operations and comparison operations
[Python] I tried using OpenPose
Using Python mode in Processing
[Python] Iterative processing (for, while)
I tried using Thonny (Python / IDE)
Python beginner tried 100 language processing knock 2015 (05 ~ 09)
[Python] I tried using YOLO v3
I tried asynchronous processing using asyncio
Python beginner tried 100 language processing knock 2015 (00 ~ 04)
Periodic execution processing when using tkinter [Python3]
[Python] Speeding up processing using cache tools
[Python] Matrix multiplication processing time using NumPy
Python iterative
I tried using Bayesian Optimization in Python
I tried using UnityCloudBuild API from Python
[Python] Various data processing using Numpy arrays
Video processing using Python + OpenCV on Mac
vprof --I tried using the profiler for Python
I tried web scraping using python and selenium
I tried object detection using Python and OpenCV
I tried using mecab with python2.7, ruby2.3, php7
I tried reading a CSV file using Python
I tried using the Datetime module by Python
python image processing
Start using Python
Python file processing
Scraping using Python
I tried to compare the processing speed with dplyr of R and pandas of Python
[Python] I immediately tried using Pylance's VS Code extension.
I tried using TradeWave (BitCoin system trading in Python)
[Python] I tried running a local server using flask
I tried drawing a pseudo fractal figure using Python
Process csv data with python (count processing using pandas)
I tried using Python (3) instead of a scientific calculator
I tried to access Google Spread Sheets using Python