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
-Basic Grammar -Variable Declaration -Case Sensitive --Statement delimiter (#ステートメントの区切り) -Write multiple lines together on one line -Write one line in multiple lines -Space, etc. -Space -Indent -Comment -Substitution -Composite Assignment Operator -Control Syntax -Conditional branch -Repeat -Basic operation -Function -Directory operation, etc. -Current Directory -File List -Summary -List -Whole program
Python In Python, you can use it without declaring variables.
R In R, you can use it without declaring variables.
VBA
In VBA, variables are declared and used. (You can use it without declaring it.)
You can force the declaration of a variable by writing Option Explicit
at the beginning of the module (it will give an error when an undeclared variable is used).
Variables are declared in the form Dim variable name As variable type
, such as Dim x As Integer
, Dim s As String
.
VBA
Option Explicit
Sub test_grammar()
Dim x As Integer
Dim s As String
End Sub
It is about the distinction between uppercase and lowercase letters in variable names.
Python Python is case sensitive.
Python3
x = 1
X = 2
print(x) # 1
print(X) # 2
R R is case sensitive.
R
x <- 1
X <- 2
print(x) # 1
print(X) # 2
VBA VBA is not case sensitive.
VBA
x = 1
X = 2
VBE cannot write case-sensitive like this.
In Python, R, and VBA, instructions (processes) are basically written in line units.
This is when you really want to write multiple instructions (multiple processes) on one line.
Python
In Python, you can also write (multiple processes) on a single line separated by ;
(semicolons).
Python3
x = 1; y = 2
print(x) # 1
print(y) # 2
z = 3; print(z) # 3
R
In R, multiple lines (multiple processes) can be written on one line separated by ;
(semicolon).
R
x <- 1; y <- 2
x # 1
y # 2
z <- 3; print(z) # 3
VBA
In VBA, you can write multiple lines (multiple processes) on one line separated by :
(colon).
VBA
x = 1: y = 2
Debug.Print x ' 1
Debug.Print y ' 2
z = 3: Debug.Print z ' 3
On the contrary, it is about when you want to write in multiple lines because one line becomes long and difficult to see.
It is okay to write line breaks with ,
(comma) such as argument delimiters in any language. Otherwise:
Python
In Python, you can start a new line with \
(backslash).
Python3
x = 1 + 2 + \
3
print(x) # 6
x = 1 + 2 \
+ 3
print(x) # 6
s = "abc" + "def" + \
"ghi"
print(s) # abcdefghi
s = "abc" + "def" \
+ "ghi"
print(s) # abcdefghi
R With R, you can do this.
R
x <- 1 + 2 +
3
x # 6
VBA
In VBA, you can start a new line with _
(underscore).
VBA
x = 1 + 2 _
+ 3
Debug.Print x ' 6
x = 1 + 2 + _
3
Debug.Print x ' 6
s = "abc" & "def" & _
"ghi"
Debug.Print s ' abcdefghi
s = "abc" & "def" _
& "ghi"
Debug.Print s ' abcdefghi
About operators and spaces before and after parentheses.
Python In Python, the spaces before and after operators and parentheses are fairly free to write.
Python3
x=1+2+3
x = 1 + 2 + 3
abs(x)
abs ( x )
R In Python, the spaces before and after operators and parentheses are fairly free to write.
R
x<-1+2+3
x <- 1 + 2 + 3
abs(x)
abs ( x )
VBA With VBA, you can't write very freely. It will be adjusted automatically when writing in VBE.
VBA
x = 1 + 2
Debug.Print Abs(x)
About indentation.
Python Indentation makes sense in Python. Represents a block, such as in Control Syntax (#制御構文).
R In R, indentation has no meaning, so you can do it freely.
VBA In VBA, indentation has no meaning, so you can do it freely.
How to write a comment.
Python
In Python, you can write a comment after #
(pound). From #
to the end of the line is commented out.
Python3
#This is a comment.
x = 1 #Comments on x
print(x) # 1
In Jupyter Notebook, Ctrl + /
inserts #
at the beginning of the line (the entire line is commented out). Press Ctrl + /
again to uncomment.
R
In R, you can write a comment after #
(sharp). From #
to the end of the line is commented out.
R
#This is a comment.
x <- 1 #Comments on x
print(x) # 1
In RStudio, Ctrl + Shift + C
inserts#
at the beginning of the line (the entire line is commented out). Press Ctrl + Shift + C
again to uncomment.
You can also insert a comment line with Ctrl + Shift + R
.
VBA
In VBA, you can write a comment after '
(apostrophe). It is commented out after '
to the end of the line.
VBA
'This is a comment.
x = 1 'Comments on x
Debug.Print x ' 1
Python
In Python, you can use both single quotes '``'
and double quotes "
" `to enclose a string.
Python3
#String
s = 'abc'
print(s) # abc
s = "abc"
print(s) # abc
R
In R, you can use either single quotes '``'
or double quotes "
" `to enclose a string.
R
s <- 'abc'
print(s) # "abc"
s <- "abc"
print(s) # "abc"
VBA
VBA uses double quotes " `` "
to enclose a string.
VBA
s = "abc"
Debug.Print s
Python
Python3
x = 1
#Multiple simultaneous assignment
x, y = 1, 2
print(x, y) # 1 2
a, b, c = 1, 2, 3
print(a, b, c) # 1 2 3
a, b, c = 1, 2.345, "abc"
print(a, b, c) # 1 2.345 abc
In Python, you can assign to multiple variables at the same time (the data types can be different).
R
R
x <- 1
print(x) # 1
x = 2
print(x) # 2
3 -> x
print(x) # 3
1 + 2 * (3 - 4) / 5 -> x
print(x) # 0.6
In R, there are assignment operators <-
,=
,->
.
Substitute ->
from left to right. This is useful when writing complex expressions and assigning them at the end.
VBA
VBA
x = 1
Debug.Print x ' 1
VBA uses the same symbol =
for assignment and comparison operators.
In VBA, the Set
statement is used for assignment to an object.
About compound assignment operators (cumulative assignment operators, cumulative assignment statements) that perform operations and assignments together.
Python
Python3
n = 1
n += 1
print(n) # 2
n -= 1
print(n) # 1
n *= 2
print(n) # 2
n /= 2
print(n) # 1.0
s = "abc"
s *= 3
print(s) # abcabcabc
R
R
There seems to be no compound assignment operator in R.
VBA
VBA
There seems to be no compound assignment operator in VBA.
How to output the value of a variable.
Python In Python, use the'print'function.
Python3
x = 1
print(x) # 1
s = 'abc'
print(s) # abc
On the Jupyter Notebook, when you execute the cell in which the variable name is written, the value of that variable is displayed (if there are multiple in the cell, only the last written variable).
R
In R, if you write a variable name and execute it, the value of that variable will be displayed. You can also use the'print'function and the cat'function. Also, in R, if you enclose the assignment statement in
(``)`, it will be assigned and output.
R
x <- 1
x # 1
print(x) # 1
cat(x) # 1
s <- "abc"
s # "abc"
print(s) # "abc"
cat(s) # abc
(x <- 1) # 1
(s <- "abc") # abc"
VBA
In VBA, you can output to the immediate window with Debug.Print
and output to the message box with MsgBox
.
VBA
x = 1
Debug.Print x
MsgBox x
How to enter a value in a variable.
Python
In Python, you can use the input
function.
Python3
x = int(input("Please enter x."))
print(x)
Since the return value of the input
function is a character string (str type), it is converted to an int type by the int
function.
R
R
VBA
In VBA, you can use the InputBox
function and the Application.InputBox
method.
VBA
x = InputBox("Please enter x.")
Debug.Print x
x = Application.InputBox("Please enter x.")
Debug.Print x
For conditional branching (mainly if statements), click here (https://qiita.com/swathci/private/2dba6ebf683a11403f12).
For repetition (mainly for and while statements), click here (https://qiita.com/swathci/items/97de2111e70c10205521).
For arithmetic operations, click here (https://qiita.com/swathci/items/853b48ca75a0935378bb). For comparison operations, click here (https://qiita.com/swathci/items/dfd5e80060ff23416ce5). For logical operations, click here (https://qiita.com/swathci/items/dfd5e80060ff23416ce5).
Click here for functions.
Python
In Python, use the os
module to check the current working directory (import os
). You can check the current directory with os.getcwd ()
and change the current directory with os.chdir ()
.
Python3
import os
os.getcwd()
os.path.abspath(".")
os.chdir('C://Users/test')
R
In R, you can check the working directory with the getwd
function. You can also set the working directory with the setwd
function.
R
getwd()
setwd("C:/Users/test")
VBA
In VBA, you can get the path of the workbook (Excel file) running in ThisWorkbook.Path
.
VBA
Debug.Print ThisWorkbook.Path
ChDir "C:\Users\test"
How to display a list of files in a directory.
Python
In Python, use the os
module.
Python3
import os
os.listdir()
os.listdir('.')
os.listdir('./data')
os.listdir('..')
R
R
dir()
list.files(".")
list.files("./data")
list.files("..")
list.dirs(".")
VBA In VBA, it is not possible to display the file list as easily as Python and R.
VBA
The grammar of each language is summarized in a list.
*** Basic writing ***
Python | R | VBA | |
---|---|---|---|
Variable declaration | Do not declare | Do not declare | Dim variable name As variable type Declared in the form of |
Case sensitive | There is a distinction | There is a distinction | No distinction |
Write multiple lines together in one line | ; Separation |
; Separation |
: Separation |
Write one line in multiple lines | / Line break with |
_ Line break with |
|
space | freedom | freedom | Adjust automatically |
Indent | Representing a block with a control statement | freedom | freedom |
Comment out | # After |
# After |
' After |
String | '``' Ka"``" Surround with |
'``' Ka"``" Surround with |
"``" Surround with |
*** Substitution ***
Python | R | VBA | |
---|---|---|---|
Substitution | = |
<- = -> |
= |
Multiple simultaneous assignment | Possible | ||
Compound assignment operator | Yes |
*** I / O ***
Python | R | VBA | |
---|---|---|---|
output | print() |
print() cat() |
Debug.Print MsgBox |
input | input() |
InputBox() Application.InputBox() |
directory
Python | R | VBA | |
---|---|---|---|
Get current directory | import os os.getcwd() |
getwd() |
Debug.Print ThisWorkbook.Path |
Change current directory | import os os.chdir('C://Users/test') |
setwd("C:/Users/test") |
ChDir "C:\Users\test" |
Display file list | import os os.listdir('.') |
dir() |
The whole program used for reference is shown.
Python
Python3
#Case sensitive
x = 1
X = 2
print(x) # 1
print(X) # 2
#Write multiple lines together in one line
x = 1; y = 2
print(x) # 1
print(y) # 2
z = 3; print(z) # 3
#Write one line in multiple lines
x = 1 + 2 + \
3
print(x) # 6
x = 1 + 2 \
+ 3
print(x) # 6
s = "abc" + "def" + \
"ghi"
print(s) # abcdefghi
s = "abc" + "def" \
+ "ghi"
print(s) # abcdefghi
#space
x=1+2+3
x = 1 + 2 + 3
abs(x)
abs ( x )
#comment
#This is a comment.
x = 1 #Comments on x
print(x) # 1
#String
s = 'abc'
print(s) # abc
s = "abc"
print(s) # abc
#Substitution
x = 1
#Multiple simultaneous assignment
x, y = 1, 2
print(x, y) # 1 2
a, b, c = 1, 2, 3
print(a, b, c) # 1 2 3
a, b, c = 1, 2.345, "abc"
print(a, b, c) # 1 2.345 abc
#Compound assignment operator
n = 1
n += 1
print(n) # 2
n -= 1
print(n) # 1
n *= 2
print(n) # 2
n /= 2
print(n) # 1.0
s = "abc"
s *= 3
print(s) # abcabcabc
#output
x = 1
print(x) # 1
s = 'abc'
print(s) # abc
#input
x = int(input("Please enter x."))
print(x)
#Current directory
import os
os.getcwd()
os.path.abspath(".")
os.chdir('C://Users/test')
#File list
import os
os.listdir()
os.listdir('.')
os.listdir('./data')
os.listdir('..')
R
R
#Case sensitive
x <- 1
X <- 2
print(x) # 1
print(X) # 2
#Write multiple lines together in one line
x <- 1; y <- 2
x # 1
y # 2
z <- 3; print(z) # 3
#Write one line in multiple lines
x <- 1 + 2 +
3
x # 6
#space
x<-1+2+3
x <- 1 + 2 + 3
abs(x)
abs ( x )
#comment
#This is a comment.
x <- 1 #Comments on x
print(x) # 1
#String
s <- 'abc'
print(s) # "abc"
s <- "abc"
print(s) # "abc"
#Substitution
x <- 1
print(x) # 1
x = 2
print(x) # 2
3 -> x
print(x) # 3
1 + 2 * (3 - 4) / 5 -> x
print(x) # 0.6
#output
x <- 1
x # 1
print(x) # 1
cat(x) # 1
s <- "abc"
s # "abc"
print(s) # "abc"
cat(s) # abc
(x <- 1) # 1
(s <- "abc") # abc"
#input
#Current directory
getwd()
setwd("C:/Users/test")
#File list
dir()
list.files(".")
list.files("./data")
list.files("..")
list.dirs(".")
VBA
VBA
Option Explicit
Sub test_grammar()
'Variable declaration
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim s As String
'Case sensitive
x = 1
' X = 2
'Write multiple lines together in one line
x = 1: y = 2
Debug.Print x ' 1
Debug.Print y ' 2
z = 3: Debug.Print z ' 3
'Write one line in multiple lines
x = 1 + 2 _
+ 3
Debug.Print x ' 6
x = 1 + 2 + _
3
Debug.Print x ' 6
s = "abc" & "def" & _
"ghi"
Debug.Print s ' abcdefghi
s = "abc" & "def" _
& "ghi"
Debug.Print s ' abcdefghi
'space
x = 1 + 2
Debug.Print Abs(x)
'comment
'This is a comment.
x = 1 'Comments on x
Debug.Print x ' 1
'String
s = "abc"
Debug.Print s
'Substitution
x = 1
Debug.Print x ' 1
'output
x = 1
Debug.Print x
MsgBox x
'input
x = InputBox("Please enter x.")
Debug.Print x
x = Application.InputBox("Please enter x.")
Debug.Print x
'Current directory
Debug.Print ThisWorkbook.Path
'ChDir "C:\Users\test"
End Sub
Recommended Posts