Excel VBA の覚え書き　2018年度版

初版作成　　2018.2.10
最終更新　　2024.04.01

◆◆ プログラムを組むには ◆◆


ファイル --- オプション --- リボンのユーザー設定

右側の「開発」にチェックを入れる。

◆◆ Visual Basic Editor ◆◆


Excel でプログラムを開発するための環境。

開発 ---「コード：Visual Basic」で起動する。

挿入 --- 標準モジュール

で標準モジュールを挿入し、その中にプログラムを書く。
1 つのプログラムは以下の形式を持つ。

Sub program_name()


End Sub

Excel では「プログラム」のことを「マクロ」あるいは「プロシージャ」
と呼ぶ。

エディタの背景色やフォントは見やすいように変更する。
ツール --- オプション --- エディターの設定

フォント名はリストボックスから選ぶのは、フォント数が
多すぎて選びにくいので、フォント名のテキストボックスに
フォント名の最初の何文字かを入力し ▼ をクリックする。

「更紗」あるいは「ＭＳ ゴ」などと入れる。MS ゴシックは
M と S は全角でその後半角空白が 1 個入る。

（注意！）
標準モジュールを切り替えるときは「ダブルクリック」
1995 年頃に作成されたインターフェースであるため、
今とは感覚が異なることがある。

（注意！）
Sheet1(Sheet1) の中にプログラムを書くと、そのシートのセルしか
アクセスできない。
標準モジュールの中に書く。

◆◆ xlsm ファイル ◆◆


Excel ファイルの拡張子は以下の 2 つがある。

  xlsx：シートだけを含む
  xlsm：シートとマイクロの両方を含む。

xlsx でセーブするとプログラムがセーブされないので注意！！！

  xlsm：複数の標準モジュールを含むことができる
  標準モジュール：複数のプログラムを含むことができる

◆◆ コメント文 ◆◆


' より右側はコメント

◆◆ 継続行 ◆◆


行末に _ を置くと、続きを次の行に書くことができる。

◆◆ エラーなどのトラブルの対処 ◆◆


・無限ループで終わらない

  Esc キーで終了する。それでも終わらない場合は
  残念ながらタスクマネージャから Excel を終了させるしかない。

・For に対する Next がないというエラーが出るが Next はある

  End If を忘れたときにこのエラーが出る。なぜかは謎。

◆◆ セルに値を代入する ◆◆


Range("A1").Value = 10
Range("A" & i).Value = 10
Range("A" + CStr(i)).Value = 10

Cells(i, j).Value = "abc"
Cells(i, "B").Value = "abc"

ただし、.Value は省略可

◆◆ 変数 ◆◆


宣言なしに用いることができる。
そのとき Variant 型になる。

モジュールの先頭に

Option Explicit

と書くと、宣言しないとエラーになる。
宣言するときは

Dim a As Integer    2バイト整数　-32768 〜 +32767 までしか入らない
Dim b As Long       4バイト整数
Dim c As Double     8バイト実数
Dim d As String     文字列
Dim d As Variant    何でも入る。
Dim e               型を省略すると Variant

（注意！）

Dim a, b As Long

とすると、a は型を省略したので Variant になる。
両方とも Long にしたいときは、

Dim a As Long, b As Long

と書く。

◆◆ セルに代入できるもの ◆◆


・数値
・文字列

「セルの中に入っているものの形式」と「右クリック --- 表示形式」は関係ない。
「右クリック --- 表示形式」は表示するときの見え方を指定するもので、
セルの中の内容には影響を及ぼさない。

Range("A1") = abc

とするとどうなるか？
何も入らない。

abc という名前の変数の内容を入れようとしている。
デフォルトではバリアント型変数 abc の値は "" (空) なので
空が代入される。

"123" という文字列をセルに入れるのはバグの元
罠にはまる可能性のあることはしない。

Excel は自動型変換の機能を持っているので、

Range("A1") = "12"

としたとき、

Range("B1") = Range("A1") + 10

はエラーとならずに B1 に 22 が代入されるが、
たちの悪いバグを引き起こすおそれがある。

◆◆ 型変換 ◆◆


num は整数型、str が文字型のとき

   str = CStr(num)
   num = CLng(str)

文字型 ---> 数値への型変換の関数

   a = CInt(str)    ' 2バイト整数
   b = CLng(str)    ' 4バイト整数
   d = CDbl(str)    ' 8バイト実数
   e = Val(str)     ' 万能型　ただし使用は推奨されない

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


プログラム開発にはなくてはならないウィンドウ
閉じてしまったときは　表示 --- イミディエイトウィンドウ

使い道は 2 つある。

1. イミディエイトウィンドウに表示する

    Debug.Print "a = " & Range("A1")

のように書くとイミディエイトウィンドウに表示する。
+ あるいは & は文字列を接続する。+ は文字列同士しか接続できないが、
& は数値と文字列を接続すると、数値は文字列に自動的に変換される。

& と + 以外に ; や , も使えるが、古い Basic との互換性を
保つためにあるので（本当か？）使わない方が無難。

2. ダイレクトコマンドを入力する

イミディエイトウィンドウ内にカーソルを持って行き、
命令を入力すると、その命令を実行可能

ex. プログラムにバグがあり、実行中に止まった

イミディエイトウィンドウ内でコマンドを打つことで
その時点での変数の内容を表示させることができる。

? a　　あるいは print a で変数 a の内容を表示。

◆◆ For ループ ◆◆


For i = 1 To 10

Next i

For i = 10 To 1 Step -1

    Exit For

Next i

◆◆ If ◆◆


If 条件式 Then

Else

End If


If 条件式1 Then

ElseIf 条件式2 Then

Else

End If

複数の条件を書くときは

If 条件式1 And 条件式2 Then

If 条件式1 Or 条件式2 Then

うっかり、

If a = 3 And 4 Then

とすると、4 という条件式は常に成立するので、
a が何であっても条件が成立してしまう。

◆◆ while ループ ◆◆


Do While 条件式

   Exit Do

Loop

無限ループの場合は条件式として True を入れる。

◆◆ セルの色 ◆◆


Range("A1").Interior.Color = RGB(255,255,0)

RGB は B * 256 * 256 + B * 256 + R の値を返す関数

塗りつぶし無しのときは

Range("A1").Interior.Pattern = xlNone

◆◆ データが入っている最後の行を取り出す ◆◆


A 列のデータ入っている最後の行を取り出す

last_row = Range("A" & Rows.Count).End(xlUp).Row

Rows.Count はシートの行数を返す。
1048576 ( 2^20 ) である。

Range("A1047576").End(xlUp) は
「1047576 から上向きに検索し、最初に見つけた空でないセル」
を意味する。

A1 から下向きに検索し、最初に見つけた空セルの手前の行
は以下のように取得する。

last_row = Range("A1").End(xlDown).Row

（注意！！！）
A1 のみにデータが入っており、A2 以下は空白のとき、
last_row = 1 とはならず、last_row = 1048576 となる。
1 個しかデータ入っていない場合は使えない。
2 個以上のデータが必要。

◆◆ ワークシート関数の使用 ◆◆


Range("A1") = WorkSheetFunction.RandBetween(1, 20)

◆◆ オブジェクト変数の使い方 ◆◆


Dim a As Object, c As Object
Dim i

Set a = Range("A1:C5")

i = 1
For Each c In a
    c.value = i
    c.Interior.Color = RGB(255,200,255)
    i = i + 1
Next c

・オブジェクト変数への代入は Set が必要。
・Range 以外に  Set a = Selection  でも可
・a, c は Range オブジェクトなので以下でも可。
  Dim a As Range, c As Range

For Each c In a で Range a に含まれる個々のセルを
取り出すとき、セルの順序は

   A1 B1 C1 A2 B2 C3....

の順に並ぶ。

◆◆ セルのクリア ◆◆


Cells.ClearContents   セルの中を空にする
                      サイズ(高さ・幅)、塗りつぶし、罫線はそのまま
Cells.Clear           上に加えて塗りつぶしをなし、罫線を消す
Cells.Delete          全てデフォルトにする（シートの新規作成と同等）

◆◆ 変数名 ◆◆


たとえば name という変数名を使うと、1 文字目が大文字に
なり Name となる。row, column も同様である。

    Dim name

と宣言すると、name のままである。

標準モジュールではなく Sheet1(シート名) の中に
プログラムを書き、

    Name = "abc"

とすると、シート名が "abc" になる。1 文字目が大文字になる
変数名は、Excel があらかじめ定義している変数のようである。

使わない方が無難であると思われる。

Dim で宣言してから使うと、ユーザー定義変数として扱われる。

◆◆ 列、行の設定 ◆◆


Rows(3).Interior.color = RGB(255,255,220)
Rows("3:5").RowHeight = 50

Columns(3).ColumnWidth = 10
Columns("B").ColumnWidth = 10
Columns("A:C").ColumnWidth = 8

◆◆ 文字と数値の変換 ◆◆


str = CStr(num)
num = Val(str)

Val ではなく CInt, CLng, CDbl を使ってもよい。

◆◆ A, B, C <----> 1, 2, 3 の変換 ◆◆


cha = UCase(cha)
num = Asc(cha) - Asc("A") + 1

cha = Chr(Asc("A") + num - 1)

◆◆ 割り算の余り ◆◆


a Mod b