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 ***

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

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

Recommended Posts

VBA user tried using Python / R: basic grammar
VBA user tried using Python / R: Iterative processing
VBA user tried using Python / R: conditional branching
VBA user tried using Python / R: string manipulation
VBA users tried using Python / R: basic arithmetic operations
VBA user tried using Python / R: String manipulation (continued)
VBA users tried using Python / R: logical operations and comparison operations
Python3 basic grammar
Python basic grammar / algorithm
Python basic grammar (miscellaneous)
Python basic grammar note (4)
Python basic grammar note (3)
Python basic grammar memo
Basic Python 3 grammar (some Python iterations)
[Python] Using OpenCV with Python (Basic)
Python installation and basic grammar
Python basic grammar (miscellaneous) Memo (3)
Python basic grammar (miscellaneous) Memo (2)
[Python] I tried using OpenPose
Basic Python grammar for beginners
I learned Python basic grammar
Python basic grammar (miscellaneous) Memo (4)
Python (Python 3.7.7) installation and basic grammar
Java and Python basic grammar comparison
I tried using Thonny (Python / IDE)
Basic grammar of Python3 system (dictionary)
[Python] I tried using YOLO v3
I tried using Bayesian Optimization in Python
Setting up Basic authentication using Python @Lambda
[Basic grammar] Differences between Ruby / Python / PHP
I tried using UnityCloudBuild API from Python
[Python] I personally summarized the basic grammar.
Basic grammar of Python3 system (character string)
Basic grammar of Python3 series (list, tuple)
I tried to touch Python (basic syntax)
Basic grammar of Python3 system (included notation)
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
RF Python Basic_01
python grammar check
Start using Python
Basic Python writing
Python grammar notes
RF Python Basic_02
Scraping using 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
[For beginners] Learn basic Python grammar for free in 5 hours!
I tried using Python (3) instead of a scientific calculator
I tried to access Google Spread Sheets using Python