VBA user tried using Python / R: basic grammar

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

-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

Basic grammar

Variable declaration

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

Case sensitive

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.

Statement delimiter

In Python, R, and VBA, instructions (processes) are basically written in line units.

Write multiple lines together in one line

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

Write one line in multiple lines

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

Space etc.

space

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)

Indent

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.

comment

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

String

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

Substitution

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.

Compound assignment operator

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.

output

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

input

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

Control syntax

Conditional branch

For conditional branching (mainly if statements), click here (https://qiita.com/swathci/private/2dba6ebf683a11403f12).

repetition

For repetition (mainly for and while statements), click here (https://qiita.com/swathci/items/97de2111e70c10205521).

Basic calculation

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).

function

Click here for functions.

Directory operation, etc.

Current directory

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"

File list

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

Summary

List

The grammar of each language is summarized in a list.

*** Basic writing ***

*** Substitution ***

*** I / O ***

directory

Whole program

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

reference

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