プログラムの初心者の方にエクセルVBAを教える機会がありました。
ここではエクセルの機能をガリガリ鍛えるのではなくて、プログラミングの基礎をVBAで学ぶ的なノリにしています。

せっかくですので、教材をまとめておきます。普段はPython, Shell, C言語をあたりを使用しVBAはほとんど触らないため間違いがあるかもしれません。もし間違いがあればご指摘ください。

Hello World
===================

1.1 セルに "Hello World" を出力

1.1.1 Rangeを利用

VBA

Sub HelloWorld()
    Range("A1").Value = "Hello World"
End Sub

以下のような結果が得られます

1.1.2 セルを利用

VBA

Sub HelloWorld()
    Cells(1,1).Value = "Hello World"
End Sub

結果は上記と同様です。

1.2 メッセージボックスに出力する

VBA

Sub HelloWorld3()
    MsgBox "Hello World"
End Sub

以下のような結果が得られます。

1.3 Debug.Pringを使ってイミディエイトウィンドウに出力する

表示 > イミディエイトウィンドウ を選択し、予めイミディエイトウィンドウを表示しておきましょう。

VBA

Sub ifTest()
    Debug.Print ("Hello World")
End Sub

以下のような出力が得られます。

変数とその値の更新
========================

2.1 a = a + 1 で混乱しない

a = a + 1 見て混乱することを無くしましょう。これは分かりやすく言えば、以下のように aの値を更新することを意味します。

a(New) = a(Old) + 1

「数学的に変だ！」ではなく、数学の表記を借りているだけで、意味はまったく違うことにまずはなれましょう。

2.2 使い方の例

2.2.1 サンプルプログラムの作成

では実際にVBAを触って確かめてみます。aの値を1つずつ増やしています。インクリメントと呼びます。

コード

VBA

Sub TestDataType1()
    Dim a As Integer
    a = 1
    Range("A1").Value = a
    a = a + 1
    Range("A2").Value = a
    a = a + 1
    Range("A3").Value = a
End Sub

動作確認

プログラムを実行し、動作を確認します。以下のような結果が得られていることを確認してください。

データの型
===================

3.1 型の種類

Boolean: ブール型
- True または False
Byte: バイト型
- 0～255までの整数
Integer: 整数型
- -32,768～32,767の整数
Long: 長整数型
- -2,147,483,648～2,147,483,647の整数
Currency: 通貨型
- -922,337,203,685,477.5808 ～ 922,337,203,685,477.5807の固定小数点数
Single: 単精度浮動小数点数型
- 負の値：約-3.4×10(38乗)～-1.4×10(-45乗)正の値：約1.4×10(-45乗)～1.8×10(38乗)
Double: 倍精度浮動小数点数型
- 負の値：約-1.8×10(308乗)～-4.0×10(-324乗)正の値：約4.9×10(-324乗)～1.8×10(308乗)
Date: 日付型
- 日付：西暦100年1月1日～西暦9999年12月31日時刻：0:00:00 ～ 23:59:59
String: 文字列型
- 任意の長さの文字列
Object: オブジェクト型
- オブジェクト
Variant: バリアント型
- すべてのデータ

3.2 基本的な使い方

3.2.1 定義方法

データの型にはとり得ることができる範囲もあることに注意して、よく扱うであろう型を触ってみます。
型の定義は以下のように表現します。

Dim 変数 As 型

複数の変数も定義可能です。

Dim 変数1 as 変数1, 変数2 As 型

ここからはサンプルプログラムで、よく使う Integer, Long, Single を実際に見ていきます。

3.2.2 サンプルプログラム: Integer (整数型)

Integer 型は, -32,768(=-2^15) to 32,767(= 2^15 - 1) の整数を扱うことができます。
全部で65535個(=2^16個)存在することになります。

ご存知のとおり、コンピュータはOn/Offの組み合わせ、つまり0と1の2通りの組み合わせでできています。つまり2進法ですね。なので、このように2の階乗で範囲も設定されています。

コードの作成

サンプルプログラムを見てみましょう。注意点としては

10/4 = 2.5 ですが、整数型をとると答えは 2 になります。
"a" とすることで、それは変数ではなく a という文字列(型)になります。

VBA

Sub TestDataType()
    Dim a As Integer
    a = 10 / 4
    Range("A1").Value = "a"
    Range("A2").Value = a
End Sub

動作確認

実行すると以下のような結果が得られます。

3.2.3 サンプルプログラム: Integerのオーバーフローエラー

Integer型の数値の範囲を超えるとエラーになります。

a = 2 ^ 15 として、Integer で扱える範囲を超えるとどうなるかみてみましょう。

コードの作成

VBA

Sub TestDataType()
    Dim a As Integer
    a = 2 ^ 15
    Range("A1").Value = "a"
    Range("A2").Value = a
End Sub

動作確認

デバッグをクリックすることでどこが悪いか分かります。

3.2.4 サンプルプログラム: Long (長整数型)

先程のコードのIntegerをLongに置き換えてみます。これにより、Integer型のオーバーフローをLongでDebugしてみます。

コードの作成

VBA

Sub TestDataType()
    Dim a As Long
    a = 2 ^ 15
    Range("A1").Value = "a"
    Range("A2").Value = a
End Sub

動作確認

今度はエラーが発生せず、下記のように正しく数値が入りました。

3.2.5 サンプルプログラム: Single (単精度浮動小数点型)

扱える範囲は以下のとおりです。

負の値: 約, -3.4x10^38 〜 -1.4×10^(-45)
正の値: 約, 1.4x10^(-45) ～ 1.8×10^38

先程と似たコードですが、Integer をSingleに変更しています。これで、2.5が出力できます。
ちなみに、'Dim a As Integer コメントアウトしておりプログラムには影響を与えません。

コードの作成

VBA

Sub TestDataType()
    'Dim a As Integer
    Dim a As Single
    a = 10 / 4
    Range("A1").Value = "a"
    Range("A2").Value = a
End Sub

動作確認

下記のように2.5が出力されたことを確認しましょう。

3.3 参考

下記を参考にさせていただきました。ありがとうございます。

Office Tanaka "型は覚えなくていい"

If文
================================

4.1 If文の基本的な使い方

4.1.1 サンプルプログラム: 数値の大小

データの作成

エクセルに下記のようなデータを作成してみましょう。

以下のプログラムを作成してみましょう。

コードの作成

VBA

Sub ifTest()
'セルの読み込み
    a_key = Cells(1, 1).Value
    a = Cells(1, 2).Value
    b_key = Cells(2, 1).Value
    b = Cells(2, 2).Value
    
'読み込んだセルの値の確認
    Debug.Print "------"
    Debug.Print a_key; a
    Debug.Print b_key; b

'条件分岐   
    If a > b Then
        tmp = a_key & ">" & b_key
    ElseIf a = b Then
        tmp = a_key & "=" & b_key
    Else
        tmp = a_key & "<" & b_key
    End If

'結果の出力
    Debug.Print tmp
    Cells(3, 1).Value = tmp
End Sub

動作確認

セルA3にa>bと出力されていれば正しく動作しています。

またDebugように出力したイミディエイトウィンドウには以下のように出力されています。

イミディエイトウィンドウ

------
a 100 
b 1 
a>b

4.1.2 練習

a<bになるようにセルB1,B2の値を変えてテストしてみましょう。
a=bになるようにセルB1,B2の値を変えてテストしてみましょう。

4.2 AndとOr

4.2.1 AndとOrとは

And: AかつBの条件
Or : A またはBの条件

このサンプルではOrの条件を使って、実際の動作をみてみます。

4.2.2 サンプルプログラム: Orの使用例

検証用データの作成

以下のようにB1のセルに、数値以外の文字列をいれてください。ここでは100aとしました。aを追加することで、B1のセルは数値ではなくなっています。

サンプルプログラムの作成

このサンプルプログラムでは先程のプログラムに、A1とA2に入力したセルの文字列が同じ、またはB1が数値ではない、またはB2が数値でないときは、プログラムを終了するというような機能を追加してみましょう。

サンプルプログラムを実際に作成してみましょう。

VBA

Sub ifTest()
    
    a_key = Cells(1, 1).Value
    a = Cells(1, 2).Value
    b_key = Cells(2, 1).Value
    b = Cells(2, 2).Value
    
    Debug.Print "------"
    Debug.Print a_key; a
    Debug.Print b_key; b
    Debug.Print IsNumeric(a)
    Debug.Print IsNumeric(b)
    
    If a_key = b_key Or IsNumeric(a) = False Or IsNumeric(b) = False Then
        MsgBox "A1とB1は異なる文字列、B1とB2は数値を入力してください。"
        End
    End If
    
    If a > b Then
        tmp = a_key & ">" & b_key
    ElseIf a = b Then
        tmp = a_key & "=" & b_key
    Else
        tmp = a_key & "<" & b_key
    End If
    Debug.Print tmp
    Cells(3, 1).Value = tmp
End Sub

追加されたプログラムを抜き出すと以下になります。

変更点

Debug.Print IsNumeric(a)
Debug.Print IsNumeric(b)

If a_key = b_key Or IsNumeric(a) = False Or IsNumeric(b) = False Then
    MsgBox "A1とB1は異なる文字列、B1とB2は数値を入力してください。"
    End
End If

IsNumeric(<変数>)は、数値かどうかを判断し、数値ならTrue、そうでないならFalseを返します。
if文はA1とA2に入力したセルの文字列が同じ、またはB1が数値ではない、またはB2が数値でないときは、プログラムを終了するという命令になっています。
Endはこのプログラムを終了する命令です。

プログラムの動作確認

ここではB1が、数値ではないため、以下のエラーがでます。

イミディエイトウィンドウ

------
a100a
b 1 
False
True

4.2.3 練習

上記サンプルプログラムの不便な点

今、A1とA2の文字列が同じでも、もしくはB1かB2が数値ではなく文字列でも同じメッセージ A1とB1は異なる文字列、B1とB2は数値を入力してください。 が出力されてしまうため、ユーザにとって不親切です。

改善してみよう

そのため、If a_key = b_key Or IsNumeric(a) = False Or IsNumeric(b) = False Then の文をElseIfを使って分解し、下記の要件を満たすようにプログラムを変更してください。

B1もしくはB2が数値でなければ、メッセージボックスに、B1とB2は必ず数値を入力してください と表示し、プログラムを終了
A1とA2の文字列が同じなら、メッセージボックスに、A1とA2は異なる文字列にしてください と表示し、プログラムを終了

プログラム作成後、データを修正してその動作を確認してください。

Forによる繰り返し処理
=======================

5.1 For文の基本的な使い方

繰り返し処理を自動で実行したいときに便利です。実際にサンプルプログラムをみてみましょう。

5.1.1 簡単な繰り返し処理例

コードの作成

VBA

Sub ForTest()
    Dim i As Long, imax As Long
    imax = 10
    For i = 1 To imax
        Cells(i, 1) = i
    Next i
End Sub

動作確認

下記のようにA1からA10までセルに、数値が1ずつインクリメントされていることを確認してください。

5.1.2 練習

下記の図のように、上記で作成したA1からA10までのデータを読み込み用データとして利用してください。
かつ、B1からB10にその2乗の値が表示されるようにしてください。

回答はこちら

5.2 For文のネスト

5.2.1 ネストとは

ネストとは入れ子構造を意味します。

例

For j = 1 To jmax
  For i = 1 To imax
    (何か処理)
　　　　Next i
Next j

具体例としては下記のようなマトリックスのデータを扱うのに非常に便利です。

また、x,y,zの3次元の空間データを扱うときなども便利です。

例

For k = 1 To kmax
  For j = 1 To jmax
    For i = 1 To imax
      r = f(i,j,k) みたいな処理・・・
　　　　  Next i
  Next j
Next k

コードの作成

10行10列の1から100まで、順番に数値が入るサンプルプログラムを作ってみましょう。結果のイメージがつかない場合は下記の動作確認の画像を見ると良いでしょう。

VBA

Sub ForNestTest()
    Dim i As Long, j As Long
    Dim imax As Long, jmax As Long
    imax = 10
    jmax = 10
    For i = 1 To imax
        For j = 1 To jmax
            Cells(i, j) = (i - 1) * imax + j
        Next j
    Next i
End Sub

動作確認

5.2.2 練習

下記のように先程のマトリックスの行と列を入れ替えたマトリックスを作ってみましょう。

回答例はこちら

5.2.3 練習

下記のように１から９が並ぶ3行3列のマトリックスを縦に3回繰り返すプログラムを作成してみましょう。
図では太線で囲っていますが、これは無くてかまいません。

少しむずかしいので、ヒントを出しておきます。

ヒント

    For n = 1 To nmax
        idelta = imax * (n - 1)
        For i = 1 To imax
            irow = idelta + i
            For j = 1 To jmax

回答はこちら

配列
======================

6.1 配列の基本的な使い方

6.1.1 エクセルのセルと配列

エクセルのセル自体が配列の概念に近いため、初心者の型は使い所を悩まれるかもしれませんが、逐次セルに値を格納せず、コンピュータのメモリー上に配列として値を記憶しておき、いろいろ配列上で処理をして、最後にセルに書き出すほうが高速になります。

6.1.2 静的配列と動的配列

静的配列とはa(10)など、予め配列サイズが決められている場合に用いることができます。この()の中に書く添字はVBAコードに直接書き込むことになり、変数にすることはできません。
動的配列とはa(imax)のように添え字部分を変数にしたいときに使用します。この場合状況に応じて、imaxは変化することになり、バスの搭乗人数など状況に応じて可変にできます。

6.1.3 静的配列の定義方法

Integer型, 0から10の配列数11の固定長の配列を定義するときは以下のようになります。

固定長

Dim my_array(10) As Integer

注意点として my_array(10) した場合、その配列数は他のプログラミング言語と異なることに注意してください。

0から9までの10個: C, Python
0から10までの11個: VBA

6.1.4 サンプルプログラム: 静的配列の開始番号,終了番号,配列数

配列の開始番号,終了番号,配列数について見てみます

コードの作成

VBA

Sub arrayTest()
    Dim a(10) As Integer
    Dim b(1 To 11) As Integer
    
    amin = LBound(a)
    amax = UBound(a)
    asize = amax - amin + 1
    
    bmin = LBound(b)
    bmax = UBound(b)
    bsize = bmax - bmin + 1
    
    Debug.Print " amin ="; amin
    Debug.Print " amax ="; amax
    Debug.Print "asize ="; asize
    Debug.Print " bmin ="; bmin
    Debug.Print " bmax ="; bmax
    Debug.Print "bsize ="; bsize
End Sub

動作確認

イミディエイトウィンドウに以下のような結果が得られます。配列数を求めるときは,UBound(a) - UBound(a) + 1のように、1を加えることに注意しましょう。

イミディエイトウィンドウ

 amin = 0 
 amax = 10 
asize = 11 
 bmin = 1 
 bmax = 11 
bsize = 11

6.1.5 サンプルプログラム: `y =a * x^2`

次の２つのサンプルはy =a * x^2を計算しており、どちらも同様の結果を得ます。
エクセルのセル始まりはCells(1,1)であり(0,0)ではありません。そのため配列の定義の違いと、セルアドレス指定方法の違いに注目し動作させてください。

コードの作成1

VBA

Sub arrayTest()
    Dim i As Integer, imin As Integer, imax As Integer
    Dim alpha As Single
'配列の要素番号は1から11まであり、配列の要素数は合計11個
    Dim a(1 To 11) As Single, b(1 To 11) As Single

    imin = LBound(a)
    imax = UBound(a)

    alpha = 0.1
    For i = imin To imax
        a(i) = alpha * (i - 1)
        b(i) = a(i) * a(i)
    Next i

    For i = imin To imax
'配列番号は1から始まるため、セルの指定もそのまま行える。
        Cells(i, 1).Value = a(i)
        Cells(i, 2).Value = b(i)
    Next i
End Sub

コードの作成2

VBA

Sub arrayTest()
    Dim i As Integer, imin As Integer, imax As Integer
    Dim alpha As Single
'配列の要素番号は0から10まであり、配列の要素数は合計11個 
    Dim a(10) As Single, b(10) As Single
  
    imin = LBound(a)
    imax = UBound(a)
     
    alpha = 0.1
    For i = imin To imax
        a(i) = alpha * i
        b(i) = a(i) * a(i)
    Next i
    
    For i = imin To imax
'配列番号は0から始まるが、０番地のセルは無いため、`+1`と指定している。
        Cells(i + 1, 1).Value = a(i)
        Cells(i + 1, 2).Value = b(i)
    Next i
End Sub

動作確認

6.1.6 サンプルプログラム: 2次元静的配列の開始番号,終了番号,配列数

同様に2次元配列のときの開始番号,終了番号,配列数について見てみます

コードの作成

VBA

Sub arrayTest()
    Dim a(10, 5) As Integer
    Dim b(1 To 11, 1 To 6) As Integer
   
    amin1 = LBound(a, 1)
    amax1 = UBound(a, 1)
    asize1 = amax1 - amin1 + 1
    
    amin2 = LBound(a, 2)
    amax2 = UBound(a, 2)
    asize2 = amax2 - amin2 + 1
    
    bmin1 = LBound(b, 1)
    bmax1 = UBound(b, 1)
    bsize1 = bmax1 - bmin1 + 1
    
    bmin2 = LBound(b, 2)
    bmax2 = UBound(b, 2)
    bsize2 = bmax2 - bmin2 + 1
    
    Debug.Print " amin1 ="; amin1
    Debug.Print " amax1 ="; amax1
    Debug.Print "asize1 ="; asize1
    Debug.Print " amin2 ="; amin2
    Debug.Print " amax2 ="; amax2
    Debug.Print "asize2 ="; asize2
    Debug.Print " bmin1 ="; bmin1
    Debug.Print " bmax1 ="; bmax1
    Debug.Print "bsize1 ="; bsize1
    Debug.Print " bmin2 ="; bmin2
    Debug.Print " bmax2 ="; bmax2
    Debug.Print "bsize2 ="; bsize2
End Sub

動作確認

イミディエイトウィンドウ

 amin1 = 0 
 amax1 = 10 
asize1 = 11 
 amin2 = 0 
 amax2 = 5 
asize2 = 6 
 bmin1 = 1 
 bmax1 = 11 
bsize1 = 11 
 bmin2 = 1 
 bmax2 = 6 
bsize2 = 6

6.1.7 練習

以下のような10から90まで並ぶデータをエクセルに作成します。

このデータを3x3のa(2,2)の配列に読み込み、Debug.Printを使って以下のような a(0,0) = 10 のフォーマットで出力させてください。

イミディエイトウィンドウ

a(0,0) = 10 
a(1,0) = 20 
a(2,0) = 30 
a(0,1) = 40 
a(1,1) = 50 
a(2,1) = 60 
a(0,2) = 70 
a(1,2) = 80 
a(2,2) = 90

ヒント: 以下のプログラムの ここから ここまで の間に必要なプログラムを挿入してください。

ヒント

Sub arrayTest()
    Dim a(2, 2) As Integer
    Dim i As Integer, j As Integer, k As Integer
    
    imin = LBound(a, 1)
    imax = UBound(a, 1)
    isize = imax - imin + 1
    
    jmin = LBound(a, 2)
    jmax = UBound(a, 2)
    
'---- ここからプログラムを挿入 ----


'---- ここまで ----
    
    For j = jmin To jmax
        For i = imin To imax
            Debug.Print "a(" & i & "," & j & ") ="; a(i, j)
        Next
    Next
End Sub

回答はこちら

6.1.8 動的配列の定義方法

配列数を変数にしたいときは動的配列を使用します。
基本的な動的配列の定義方法ですが、例えばString型の動的配列は以下のようになります。 my_array()のカッコの中身を指定しないことで動的配列を指定していることに注意してください。

動的配列の定義方法

'定義方法: Dim 配列名() As 型で定義
Dim my_array() As String

'何かここで処理をし配列数が判明。例えばA列のセル数など。そのためここで配列数を定義する
isize = 2
ReDim my_array(isize)

'配列に対する処理
my_array(0) = "Tanaka"
my_array(1) = "Yamada"
my_array(2) = "Suzuki"

これはVariant型の変数を使って以下のようにも書けます。

Variant型を使う場合

Dim my_array As Variant
'Array関数はVariant型に使用可能。
'ここではString型のみをいれているが、Booleanや数値も入れられることに注意
my_array = Array("Tanaka", "Suzuki", "Yamada")

コメント文にも書いていますが、動的配列で型を正しく定義し、ReDim で配列数を定義することで、プログラムとして問題が起こりにくく、設計の意図を反映できると思います。

個人的にはReDimの再定義はC言語のallocな使い型に近いイメージがあります。そこから想像するにVBAのVariant側は便利だけど、どんな型でも許容できるので危険なイメージがあります。数値だけで計算したいのに、文字列が入り込んでBUGが起こるとか・・・

6.1.9 サンプルプログラム: 動的配列1

まずはわざと、エラーを発生させてみましょう。

コードの作成

VBA

Sub arrayTest()
    Dim a() As Integer
    Dim i As Integer, isize As Integer
    
    a(0) = 1
    a(1) = 2
    a(2) = 3
    
    For i = LBound(a) To UBound(a)
        Debug.Print a(i)
    Next
End Sub

動作確認

以下のようなエラーが得られます。デバッグ ボタンを押して次のサンプルプログラムで修正してみましょう。

6.1.10 サンプルプログラム: 動的配列2

以下のように ReDim を使ってバグを修正してみましょう。配列数が変数にできるよう、わざとisizeを入れています。

コードの作成

VBA

Sub arrayTest()
    Dim a() As Integer
    Dim i As Integer, isize As Integer
    
    isize = 2
    ReDim a(isize)
    
    a(0) = 1
    a(1) = 2
    a(2) = 3
    
    For i = LBound(a) To UBound(a)
        Debug.Print a(i)
    Next
End Sub

動作確認

イミディエイトウィンドウに以下のように表示されていれば正しく動作しています。

イミディエイトウィンドウ

 1 
 2 
 3

6.1.11 サンプルプログラム: Variant型による動的配列

上記と同じ動作をさせてみましょう。

コードの作成

Sub arrayTest()
    Dim a As Variant
    a = Array(1, 2, 3)

    For i = LBound(a) To UBound(a)
        Debug.Print a(i)
    Next
End Sub

動作確認

イミディエイトウィンドウ

 1 
 2 
 3

6.1.12 練習1

型定義による動的配列とVariat型による動的配列の動作の違いを見てみます。どちらも、配列の3つ目を"Tanaka"に変更します。

"サンプルプログラム: 動的配列2" のプログラムを利用し a(2) = "Tanaka" に変更しどのように動作するか確認しましょう(エラーがでます)
"サンプルプログラム: Variant型による動的配列" のプログラムを利用し Arrayの中身を Array(1, 2, "Tanak")に変更しどのように動作するか確認しましょう。

6.2 Variat型の配列適用例

6.2.1 セルの値を配列に格納

データの作成

コードの作成

VBA

Sub MyVariant()
    Dim arr As Variant
    Dim x1 As Integer, y1 As Integer
    Dim x2 As Integer, y2 As Integer
    
    x1 = 1 'column
    y1 = 1 'row
    x2 = 2 'column
    y2 = 5 'row
    arr = WorksheetFunction.Transpose(Range(Cells(y1, x1), Cells(y2, x2)))
    
    Dim i As Integer, j As Integer
    For j = y1 To y2
        For i = x1 To x2
            Debug.Print i; j; arr(i, j)
        Next i
    Next j
End Sub

動作確認

イミディエイトウィンドウ

6.2.2 CSVデータをSplit関数で要素に分割する

コードの作成

VBA

Sub MyVariant()
    Dim member As Variant
    Dim i As Integer
    member = Split("tanaka,suzuki,yamada", ",")
    For i = LBound(member) To UBound(member)
        Debug.Print i; member(i)
    Next i
End Sub

動作確認

イミディエイトウィンドウ

 0 tanaka
 1 suzuki
 2 yamada

6.3 参考

ユーザ定義関数
=======================

　7.1 ユーザ定義関数の基本的な使い方

7.1.1 ユーザ定義関数とは

エクセルではaverage, if, sum など予め用意された関数が使えますが、これらと同じように、VBAでユーザが定義する関数を作ることができます。必ず何か返り値があるように記述します。その例をみてみます。

特にVBプログラミングにおいて同様の処理を複数回行う場合など、ユーザ定義関数にまとめておけば便利です。
以下にその例を見てみましょう。

7.1.2 サンプルプログラム1

以下は100円の税を関数にて計算する例です。

VBA

Sub MyTax()
    Dim price As Long
    price = 100
    ans = tax(price)
    Debug.Print ans
End Sub

Function tax(a As Long)
    tax = 1.1 * a
End Function

イミディエイトウィンドウ

7.1.3 サンプルプログラム2