Category: Excel VBA

コメントと何が違うんじゃー

というと、コメントではなく、入力規則のメッセージから設定する

なんだよ、下手に手が込んでるな。。

Select: Whenのようにcaseを書いていく構文です。

Sub SelectTest()
    Dim signal As String
    signal = Range("a1").Value
    
    Dim result As Range
    Set result = Range("a2")
    
    Select Case signal
    
    Case "red"
        result.Value = "Stop!"
    Case "green"
        result.Value = "Go"
    Case "yellow"
        result.Value = "Caution"
    Case Else
        result.Value = "n.a"
        
    End Select
    
End Sub

while文

Sub WhileTest()
    Dim i As Integer
    i = 1
    
    Do While i < 10
        Cells(i, 1).Value = i
        i = i + 1
    Loop
End Sub

For文

Sub ForTest()
    Dim i As Integer
    
    For i = 1 To 9
        Cells(i, 2).Value = i
    Next i

End Subl

ForEachによるループ

Sub EachTest()
    Dim names As Variant
    names = Array("yamada", "taro", "sakamoto")
    
    For Each Name In names
        Debug.Print Name
    Next Name
End Sub

プロシージャの呼び出し

Sub CallTest()
    Dim names As Variant
    names = Array("okamoto", "nakamura", "hayashi")
    
    For Each name In names
        Call SayHi(name)
    Next name
End Sub

Sub SayHi(ByVal name As String)
 Debug.Print "hi, " & name
End Sub

返り値を返すfunctionプロシージャ

Sub CallTest()
    Dim names As Variant
    names = Array("okamoto", "nakamura", "hayashi")
    
    For Each name In names
        Debug.Print SayHi(name)
    Next name
End Sub

Function SayHi(ByVal name As String)
  SayHi = "hi, " & name
End Function

成績表 :一定数以下の数字をカウントして、メッセージ表示

Sub FindLowScores()
    Dim i As Long
    Dim n As Long
    i = 2
    n = 0
    
    Do While Cells(i, 1).Value <> ""
        If Cells(i, 2).Value < 40 Then
            Cells(i, 2).Interior.Color = vbRed
            n = n + 1
        End If
        i = i + 1
        Loop
    
    MsgBox (n & "件該当しました!")

End Sub

Excel VBAの開発には、Visual Basci Editorを使います。
ツール => マクロ => VBE

プログラムを書いていくには、標準モジュールを使います。プロシージャーはSubから書き始めていきましょう。

Sub HelloWorld()
    MsgBox ("hello wolrd")
End Sub

セルにデータを書き込む

Sub CellChange()
    Worksheets("Sheet1").Range("A1").Value = "hello"
    Range("A2").Value = "hello2"
    Cells(3, 1).Value = "hello3"
End Sub

Sub CellChange()
    Range("A1", "B3").Value = "hello"
    Range("A5:C7").Value = "hello2"
    Range("4:4").Value = "row 4"
    Range("C:C").Value = "Column C"
End Sub

withで複数の命令を重ねる

Sub WithTest()
    With Range("A1")
    .Value = "hello"
    With .Font
      .Bold = True
      .Size = 16
    End With
     .Interior.Color = vbRed
    End With
End Sub

値の取得

Sub GetTest()
    MsgBox (Range("A1").Value)
End Sub

メソッドで処理を呼び出し

Sub MethodTest()
    'Range("B3").Delete shift:=xlShiftUp
    Worksheets.Add after:=Worksheets("sheet1"), Count:=3
End Sub

変数計算

Sub VariableTest()
    Dim x As Integer
    x = 10 + 5
    x = x + 1
    'Range("A1").Value = x
    Debug.Print x
End Sub

配列

Sub VariableTest()
    Dim sales As Variant
    sales = Array(200, 150, 300)
    Debug.Print sales(2)
    
End Sub

条件分岐

Sub IfTest()
    If Range("A1").Value > 15 Then
        Range("A2").Value = "OK"
    Else
        Range("A2").Value = "NG!"
    End If
    
End Sub