VBA's cheat sheet （ VBAの簡単なメモ ）

Summary

個人的 VBA のチートシート

Environment

( OS )
$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.13.6
BuildVersion:   17G65

( VBA )
Microsoft Visual Basic for Applications 7.1

( Excel )
Microsoft Excel for Mac version 16.16.2

Grammar

Option Explicit

Sub Comment()

    ' line comment

    ' block comment is nothing

End Sub

Sub Operator()

    ' assign ( = )
    Dim Foo As String: Foo = "hello"
    Debug.Print Foo ' hello

    ' equal  ( = )
    Debug.Print (1 = 1) ' True

    ' not equal ( <> )
    Debug.Print (1 <> 1) ' False

    ' same instance ( is )
    Debug.Print (New Collection Is Nothing) ' False

    ' and ( And )
    If True And False Then
        Debug.Print "foo"
    Else
        Debug.Print "bar" ' bar
    End If

    ' or ( Or )
    If True Or False Then
        Debug.Print "foo" ' foo
    Else
        Debug.Print "bar"
    End If

    ' not ( Not )
    Debug.Print (Not True) ' False

    ' ternary op ( IIf )
    Debug.Print IIf(True, "foo", "bar") ' foo

End Sub

Sub Strings()

    ' string concatenation
    Debug.Print "foo" & "bar"     ' foobar

    ' verbatim strings
    Debug.Print "<book title=""foo bar"">"    ' <book title=""foo bar"">

    ' strings indent
    Debug.Print "foo" & vbNewLine & "bar"
    ' foo
    ' bar

End Sub

Sub Variables()

    ' mutable
    Dim Foo As Long: Foo = 123

    ' immutable
    ' VBA has nothing

End Sub

Sub CheckType()

    Debug.Print TypeName("foo")     ' String

    Debug.Print VarType("foo")      ' 8 ( vbString )

End Sub

Sub Conditions()

    ' IF
    If True Or False Then
        Debug.Print "foo"                ' foo
    Else
        Debug.Print "bar"
    End If


    ' Switch
    Select Case "bar"
        Case "foo": Debug.Print "foo"
        Case "bar": Debug.Print "bar"    ' bar
        Case Else: Debug.Print "hoge"
    End Select

End Sub

Sub Loops()

    ' for
    Dim i As Long
    For i = 1 To 3
        Debug.Print i
    Next i

    ' for each ( non order )
    Dim v
    For Each v In Array(1, 2, 3)
        Debug.Print v
    Next v

    ' while
    Dim n As Long ' n is zero
    Do While n < 5
        Debug.Print n
        n = n + 1
    Loop

End Sub

Sub Functions()

    Dim x As Long: x = 1
    Dim y As Long: y = 2

    ' byVal ( pass values )
    Debug.Print Foo(x, y)    ' 203
    Debug.Print x, y         ' 1 2

    ' byRef ( pass address )
    Debug.Print bar(x, y)    ' 203
    Debug.Print x, y         ' 101 102

End Sub
Private Function Foo(ByVal a As Long, ByVal b As Long) As Long
    a = a + 100
    b = b + 100
    Foo = a + b
End Function
Private Function bar(ByRef a As Long, ByRef b As Long) As Long
    a = a + 100
    b = b + 100
    bar = a + b
End Function


Sub ExeptionHandlings()

    On Error GoTo Err11
    Debug.Print (1 / 0)

    GoTo Escape

Err11:
    Dim e As ErrObject: Set e = Err
    Debug.Print e.Number, e.Source, e.Description, e.HelpFile, e.HelpContext
    ' 11
    ' VBAProject
    ' Division by zero
    ' /Applications/Microsoft Excel.app/Contents/Frameworks/Visual Basic for Applications.framework/Office VBA Core Help
    ' 1000011

Escape:
End Sub

Sub Class()

    ' VBAs class code must be in Class Module file.

    Dim f As Foo: Set f = New Foo

    Debug.Print f.x    ' 100
    Debug.Print f.y    ' 200
    Debug.Print f.bar  ' 300

    f.x = 1000
    f.y = 2000

    Debug.Print f.x    ' 1000
    Debug.Print f.y    ' 2000
    Debug.Print f.bar  ' 3000

    ' Explicit GC
    Set f = Nothing

End Sub

' file name is Foo in class module
Option Explicit

' class's member variables
Private x_ As Long
Private y_ As Long

' initial constructor ( VBAs initial constructor can not receive paramaters )
Private Sub Class_Initialize()
    x_ = 100
    y_ = 200
End Sub

' terminater
Private Sub Class_Terminate()
    x_ = 0
    y_ = 0
End Sub

' add method
Public Function bar(Optional z As Long = 0) As Long
    bar = x_ + y_ + z
End Function

' Setter
Public Property Let x(ByVal x As Long)
    x_ = x
End Property

Public Property Let y(ByVal y As Long)
    y_ = y
End Property

' Getter
Public Property Get x() As Long
    x = x_
End Property

Public Property Get y() As Long
    y = y_
End Property

Excel sheet とのからみ

あとで書く（かも）

その他

あとで書く（かも）