VBA入門
VBAの基礎・基本の要約

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
最終更新日:2022-06-12

VBAの基礎・基本の要約


マクロVBA入門は、全体で基本142回+デバッグ10回+参考ページ多数で構成されています。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
非常に内容が多いため、なかなか全体を順番に進めていくのは困難かもしれません。

以下では、VBA入門の前半の基本部分について、各回の詳細を省いて要点だけを抜粋しました。
より詳しく知りたい場合は、元のVBA入門のページを参照してください。
膨大なVBA入門学習のひとつの手助けになれば幸いです。


目次

1. VBAの基礎・基本:VBA入門

1-1. マクロとは、VBAとは

1-2. RangeとCells

1-3. 変数と繰り返し処理

1-4. If条件分岐

1-5. 関数とは、引数とは

1-6. ブック、シート、セル

1-7. セルの書式

1-8. 別シートにセルをコピー

1-9. VBA関数

1-10. オブジェクトとは、コレクションとは

1-11. ブック・シートを安全に扱う

これ以降については、本来のマクロVBA入門のページをお読みください。

第1回.マクロって何?VBAって何?

エクセルの操作を自動化する機能が「マクロ」
マクロの中身がプログラミング言語のVBA(Visual Basic for Applications)です。
Excel操作を自動化する「マクロ機能」の中身が、プログラミング言語である「VBA」で記述されています。


第2回.まずはマクロVBAを使う準備

リボンの「ファイル」
左メニューの「オプション」
左メニューの「リボンのユーザー設定」
右側にある、「開発」にチェック
これで「OK」


第3回.マクロの記録

エクセルの手動での操作が自動記録されるものです。
「開発」タブ→「コード」グループ→マクロの記録

マクロ記録の限界

・ステートメントや関数は記録されない
・不要な設定まで記録される
・全てが記録されるわけではない

個人用マクロブック

個人用マクロブックは、Excel起動と同時に開かれ、全てのブックで使用できます。
個人用マクロブックは、標準で非表示になっています。

マクロの記録は、VBAでの書き方が分からない時に調べる有効な方法


第4回.マクロはどこに書くの(VBEの起動)

「Visual Basic Editor」の表示方法

リボンの「開発」→「Visual Basic」 ・・・ Alt + F11

標準モジュールの挿入

メニューの「挿入」→「標準モジュール」 ・・・ Alt + I + M

「Module1」の名称変更

「Module1」という名称は、プロパティウインドウで行います。

「Module1」の削除

「Module1」を右クリック→「Module1の解放」


第5回.VBEのオプション設定(OptionExplicit)

必ず最初に設定しておかなければならないVBEのオプション設定です、

VBEの「ツール」→「オプション」
「変数の宣言を強制する」にチェックを付けて下さい。


第6回.とにかく書いてみよう(Sub,EndSub)

VBEで実際にマクロを書いてみましょう

VBE(Visual Basic Editor)に標準モジュールを追加したら、そこにマクロを書いていきます。

sub 練習1
と入力し、Enterして下さい、
subSubと先頭が大文字になり、後ろに()が付きます。
そして、1行空けてその下に、End Subが追加されます。
この、SubからEnd Subまでがマクロでこれをプロシージャーと言います。

Subの中は、「TAB」キーを使って1段さげて書きます、インデントと言います。

タイピングする時は、基本的には全て小文字で入力します。
range("A1")="おはよう!"
rangeと入れたものがRangeと、先頭が大文字に変換されます。
これは、Rangeが正しい記述であり、自動で変換されたという事です。

ただし、ダブルクォーテーションの中は、固定文字なので変換はされません。
"a1"と入力しても、"A1"とは変換されません。
"A1"はセル番地なので、シート関数と同じ見た目になるように大文字で入れる事をお勧めします。


プロシージャーについて

プロシージャーは、同じ標準モジュールに複数書くことができます。
プロシージャー名の付け方にはいくつかの制限があります。

✅英数字(全角半角)、漢字、記号はアンダーバー(_)だけ使える、空白(スペース)は使えない
✅先頭には、数字やアンダーバー(_)は使えない、英文字か漢字で始める
✅使えない予約語(VBAが特別に使っている単語)がある
✅長さは半角で255文字まで
✅1つの標準モジュール内では、同じ名前は付けられない

漢字を使って日本語で書けば上記の制限はほとんど気にならないはずです。
漢字を使う事p@vはありまが、VBAでは特に気にする必要はないでしょう。


第7回.マクロを実行するには(F5)

VBEの画面で実行

VBEの画面で、

このマクロVBAコードの、
SubからEnd Subの間のどこかに入力カーソル(縦棒の点滅カーソル)がある状態で、

マクロ VBA入門 画像

ショートカットのF5が簡単です。

ワークシートで実行

「開発」タブ→「マクロ」 ・・・ ショートカットはAlt+F8
一覧から実行したいマクロを選択して、
右側の「実行」ボタンをクリックします。


第8回.セルに文字を入れるとは(RangeオブジェクトのValueプロパティ)

セル番地ついて

Range("セルの番地") = "入れる文字"

セル番地は、列記号+行番号です、A列1行目のセルなのでA1になります。

= 記号について

この場合の「=」は、「」とでも考えて下さい。
=」の右側の文字・数値を左側に入れると言う事です。

Range("A1") = "おはよう!"
Range("A1") ← "おはよう!"

シートの関数と同じで、文字は"ダブルクォーテーションで囲みます。

値はValue

値は、Valueであり、本来は、

Range("セル番地").Value = "文字"
 ↑           ↑     ↑
セル        の 値  文字を入れる。

このように書きます。
ただし、.Valueは書かなくても良いので先のVBAのように.Valueは省略しても構いません。

オブジェクトとは、プロパティとは

セルオブジェクトと言います。
そして、Valueプロパティと言います。

オブジェクト   のプロパティを入れる。
 ↓           ↓     ↓
Range("セル番地").Value = "文字"
 ↑           ↑     ↑
セル       の  値  文字を入れる。

RangeオブジェクトのValueプロパティに文字を入れています。

オブジェクト.プロパティ = 値

オブジェクトの次に「.」を挟んで、プロパティを指定します。
そして、「=」で、値を入れます。

オブジェクトは対象物、プロパティはその属性になります

セルに文字を入れるとは

セルに文字を入れる・表示すると言う事は、
セル(Rangeオブジェクト)のValue(プロパティ)に、文字を入れると言う事です。
結果として、指定したセルに文字が入り表示されます。


第9回.Rangeでのセルの指定方法

複数のセル(矩形のセル範囲)

Range("A1:C5")

:(コロン)で区切って、始点と終点を指定します。

次のように指定しても同じになります。

Range("A1","C5")

,(カンマ)で区切って、始点と終点を指定します。
これで、A1セルからC5セルの四角いセル範囲(15個のセル)が指定できます。
Range(始点セル, 終点セル)


列全体

Range("A:A")
A列全体になります。

Range("A:C") または Range("A:A","C:C")
A列からC列までの3列の範囲指定になります。

行全体

Range("1:1")
1行目全体の指定になります。

Range("1:5") または Range("1:1","5:5")
1行から5行までの5行の範囲指定になります。


名前定義のセル範囲

Range("名前定義の名前")

特殊な指定方法

飛び飛びのセル
Range("A1,C5")
この指定では、A1セルとC5セル(2個のセル)のみ対象となります。
"(ダブルクォーテーション)の中で、,(カンマ)で区切って指定します。

交差セル
Range("A:C 1:5")
これは、A:Cと1:5の交差している範囲、
つまり、A1~C5の矩形セル範囲になります。

この指定は、
Range("A1","C5")と同じになりますので、ほとんど使うことは無いでしょう。

マクロVBAで良く使う書き方

Range("A1")
Range("A1:C5")
Range("A:A")
Range("1:1")
Range("名前定義の名前")



第10回.Range以外の指定方法(Cells,Rows,Columns)

Cells(行番号, 列番号)

1つのセル、単一セルの指定になります。

Cells(行番号, 列番号)
行番号は、縦方向の行位置で、1行目は1、10行目は10です。
列番号は、横方向の列位置で、A列は1、J列は10です。

列番号については、"A"や"J"といった列記号での指定も可能です。
Cells(1, 1) または Cells(1, "A")
A1セルになります。
Cells(5, 3) または Cells(5, "C")
C5セルになります。

Rows(行番号)

行全体を指定する場合です。

Rows(行番号)
行番号は、縦方向の行位置で、1行目は1、10行目は10です。

Rows(5)
これで、5行目の行全体になります。
"(ダブルクォーテーション)を使わずに数値をそのまま書きます。

Rows("1:5")
このように指定すれば、1~5行の5行分全体の範囲になります。
"(ダブルクォーテーション)で囲みます。

1行だけの場合は、数値をそのまま書きます。
複数行の場合は、:(コロン)で区切り"(ダブルクォーテーション)で囲みます。


Columns(列番号)

列全体を指定する場合です。

Columns(列番号)
列番号は、横方向の列位置で、A列は1、J列は10です。

Columns(3)
これで、3列目、つまりC列の全体になります。
"(ダブルクォーテーション)を使わずに数値をそのまま書きます。
数値指定の場合は複数列の指定はできません。

Cellsと同様に、
Columns("C")
複数列範囲の場合は、
Columns("A:C")
これで、A列~C列の3列の指定になります。

1列だけの場合は、数値をそのまま書くか、列記号を"(ダブルクォーテーション)で囲んで指定します。
複数列の場合は、:(コロン)で区切り"(ダブルクォーテーション)で囲みます。


第11回.RangeとCellsの使い方

Rangeを使うのは

固定位置のセルの場合
セル範囲(複数セル)の場合
名前定義を使う時

単一セル(1つの)セルの指定で変数を使う場合は、Cellsを使うようにします。

Cellsを使うのは

単一(1つの)セルを指定する場合

複数セルであるセル範囲や名前定義をCellsで指定することはできません。

固定セル(固定位置)の指定

固定位置で条件によって変化する事が無い場合はRangeでも良い。

Range("B2")
Range("A1:C5")
Range("1:5")
Range("A:C")

また、名前定義を使って、
Range("名前定義の名前")
名前定義を使う場合は、Rangeでしか記述できません。

1つの(VBAで位置を変化させる)セルを指定する場合

VBAで位置を変化させるような1つのセルを指定する場合の書き方の基本は、

Cells(行, 列)

列は、数字だけでなく列記号も使えます。
Cells(5, 3)
Cells(5, "C")
どちらで書いても同じC5セルになります。

変数 ijに数値が入っているとして、
Cells(i, 3)
Cells(i, j)

セル範囲(複数セル)を指定する場合

セル範囲(複数セル)の場合の書き方の基本は、

Range(始点セル, 終点セル)

この、始点セルと終点セルに、Cellsを指定します。

Range(Cells(行, 列), Cells(行, 列))

A1セルからA5セルなら
Range(Cells(1, 1), Cells(5, 1))

A1セルからC5セルなら
Range(Cells(1, 1), Cells(5, 3))

変数 i に数値が入っているとして、
Range(Cells(1, 1), Cells(i, 1))
Range(Cells(1, 1), Cells(1, i))

複数行全体、複数列全体の指定

複数行の場合、1行から5行なら、
Range(Rows(1), Rows(5))

複数列の場合、1列(A列)から3列(C列)なら、
Range(Columns(1), Columns(3))

変数 i に数値が入っているとして、
Range(Rows(1), Rows(i))
Range(Columns(1), Columns(i))

RangeとCellsの使い分け方のまとめ

セル範囲と名前定義の場合はRangeを使う、それ以外はCellsを使う。
変数を使う時はCells、Rows、Columnsを使用すると言う事です。


第38回.セルに計算式を設定(Formula)

計算式を設定できるプロパティ

Value
Formula
FormulaArray … これは配列数式でありVBAで使う事は滅多にありません
FormulaLocal
FormulaR1C1
FormulaR1C1Local

スピルにより追加されたプロパティもあります。
Formula2
Formula2Local
Formula2R1C1
Formula2R1C1Local

Valueプロパティ

計算式も入れる事が出来ます。

A1セル = B1セル + C1セル

Range("A1").Value = "=B1+C1"

これは、普通に計算式を指定すれば良いです。
関数を入れる場合でも同じです。
A1セル = B1:B10セルの合計

Range("A1").Value = "=SUM(B1:B10)"

Formulaプロパティ , FormulaLocalプロパティ

計算式を設定することに関しては、Valueと同じです。
違いは、計算式の入っているRangeを取得した時になります。

Valueプロパティは、計算結果の値が取得され、
Formulaプロパティは、計算式が取得されます。

FormulaR1C1プロパティ , FormulaR1C1Localプロパティ

Formulaプロパティとの違いは、
計算式をR1C1参照形式で指定するこということです。

R1C1参照形式

セルの相対位置、つまり、計算式を設定しようとしているセルからの移動量を、
行位置をRに続けて記述し、列位置をCに続けて記述します。

R ・・・ Rの後に何も記述しない場合は同一行
C ・・・ Cの後に何も記述しない場合は同一列
R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数
C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数
R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をずらした行
C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をずらした列

例.C5セルに計算式を設定する場合

RC1 ・・・ A5セル
R1C ・・・ C1セル
R[1]C[2] ・・・ E6セル
RC[-1] ・・・ B5セル

Localが付くプロパティについて

Localと付くものと付かないものの違いは、PC環境に依存するかどうかです。

書式の設定でもNumberformatLocalを使いますが、書式に比べて計算式ではそんなに多くはありません。

Localが必要な場合
JIS関数を設定するなら、Localを使わないとエラーとなり正しく設定できません。
Range("B1").Formula = "=JIS(A1)"
これは、#NAME?となってしまいます。

それぞれの違い(Localは除く)

Range("A1").Value = "=B1+C1"
Range("A2").Formula = "=B1+C1"
Range("A3").FormulaR1C1 = "=B1+C1"
Range("A4").Value = "=RC[1]+RC[2]"
Range("A5").Formula = "=RC[1]+RC[2]"
Range("A6").FormulaR1C1 = "=RC[1]+RC[2]"

上記では、A3セルのみ正しく設定されません。
Valueだけで良い事になりますが、コードの可読性を考慮して適宜使い分けます。

何故、こんなに多くのプロパティが存在しているのか

Range("A1").Value = "=B1+C1"

これを実行した後に、そのプロパティを参照すると、

Range("A1").Value ・・・ 計算結果の値
Range("A1").Formula ・・・ =B1+C1
Range("A1").FormulaR1C1 ・・・ =RC[1]

このように、取得されるものが違ってきます。

つまり、計算式を設定する場合は、
Value
だけでも良いが、
計算式を参照する場合は、
.Formula
.FormulaR1C1
これらを使う必要が出てくることがあります。

R1C1形式を使うメリット

2行目から10行目まで、B列にA列*100の計算式を設定するとしたとき、
通常の参照形式では、
2行目は、=A2*100
3行目は、=A3*100
というようになり、行ごとに計算式を変更しなければならなくなります。
しかし、R1C1参照形式を使えば、

Sub サンプル1()
  Dim i As Long
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Cells(i, 2).FormulaR1C1 = "=RC[-1]*100"
  Next i
End Sub
For…Nextは繰り返し処理です。
上記では2~10まで繰り返しています。
Cells(Rows.Count, 1).End(xlUp).Row
これは最終行を取得するVBAコードです。
これらについては今後詳しく解説していきます。

たった1行のVBAで複数のセルに計算式を入れる

たった1行のVBAで複数のセルに計算式を入れることもできます。
2行目から最終行までのB列に、A列*100の計算式を設定する場合、

Sub サンプル2()
  Range(Range("B1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value = "=A1*100"
End Sub

このように、1行でも書く事が出来るようになります。
(Offsetについては、後々説明します。)


第12回.変数宣言のDimとデータ型

データ型

データ型 名称 格納できる範囲
Integer 整数型 -32,768 ~ 32,767
Long 長整数型 -2,147,483,648 ~ 2,147,483,647
LongLong 64ビット符号付き数値 -9223372036854775808 ~ 9223372036854775807
※64ビットプラットフォームのみで有効な宣言型
Single 単精度浮動小数点数型 -3.402823E38 ~ -1.401298E-45(負の値)
1.401298E-45 ~ 3.402823E38(正の値)
Double 倍精度浮動小数点数型 -1.79769313486232E308 ~ -4.94065645841247E-324(負の値)
4.94065645841247E-324 ~ 1.79769313486232E308(正の値)
Currency 通貨型 -922,337,203,685,477.5808 ~ 922,337,203,685,477.00
String 文字列型 最大約20億文字まで
Date 日付型 西暦100 年1月1日~西暦9999年12月31日までの日付と時刻
Byte バイト型 0~255の範囲の単精度の正の数値。8 ビット(1 バイト)
Boolean ブール型 真 (True) または偽 (False)
Object オブジェクト型 オブジェクト
Variant バリアント型 すべてのデータ

変数の宣言

変数は「宣言」するという言い方をします。

Dim 変数名 [As データ型]

これがDimによる変数宣言です。
[As データ型]の[ ]の中は省略可能という意味です。
省略すれば、前記のVariant型になって、何でも入れる事が出来ます

変数名は、1行に複数書く事が出来ます。
Dim 変数名 [As データ型], 変数名 [As データ型], ・・・
このように、,(カンマ)で区切って、複数の変数を1行に書く事が出来ます。
このとき、変数一つ一つにデータ型を指定する必要があります、
Dim i, j, k As Long
これでは、iとjはデータ型を省略したことになりVariant、kはLongになってしまいます。

変数に値を入れる

Dim i As Long
i = 123

変数はiは長整数型 の変数で、その変数に数値の123を入れています。

Dim bookName As String
bookName = "Book1.xlsm"

変数はbookNameは文字列型 の変数で、その変数に文字列の"Book1.xlsm"を入れています。
シートの数式と同様に、文字列は"ダブルクォーテーションで囲みます。

変数名の規則

半角、全角の数字と文字を使えます
変数名の長さは、半角で255文字以内(全角なら127文字以内)です
記号はアンダーバーのみ使用可能(ピリオドやスペースは使用できない
1文字目は、数字、_アンダーバー、記号を使用することができない
VBAが意味を持たせている語句(予約語)は使用できない
大文字、小文字の区別がなく、同様に扱われる

以上は最低限の決まりですが、これは普通に書けば大抵は問題ありません。
なるべく入れるデータが容易に想像できる名称にする

良く使われる変数名

変数名は慣習的に良く使われる命名があります。

Dim i, j, k

これらは数値のみを扱い、特にセル行数や列数に使用されることが多いです。

Dim cnt As Long

件数をカウントする時に良く使われるのがcntです。
cntはcountの略になります。
このように英単語の母音を取った略文字は結構く使われています。
他には例えば年月日時分秒は、y,m,d,h,m,s、これらの文字が使われることが多いです。

慣用的に使われる場合以外では、使用目的が極力わかるような意味のある変数名を指定しましょう。

全角の日本語も使用できます。
Dim 行数 As Long

自動型変換、暗黙の型変換

VBAには、型を自動的に変換する仕様があります。
自動型変換暗黙の型変換

Dim i As String
i = 123
i = "123"

どちらも同じで、文字列として"123"が入ります。

Dim j As Long
j = "123"
j = 123

どちらも同じで、数値として123が入ります。

Dim i As String
Dim j As Long
i = "123"
j = i

変数jには、数値として正しく123が入ります。
代入(=)によりString型からLong型に自動変換されます。

Dim j As Long
j = "abc"

これはエラーになります。
VBAでは、代入(=)や演算においては、自動型変換されます。
しかし、型変換できない場合は、エラーになります。

変数宣言の必要性

変数はかならず宣言しましょう。

VBEのオプションで、「変数の宣言を強制する」にチェックを付けました。
マクロを書き始める前に、出来れば設定しておいた方が良いオプションがあります。いえ、絶対に設定しておかなければならないVBEのオプション設定です、必ず最初に設定しておいてください。VBEの「ツール」→「オプション」以下の画面が出ます。

Option Explicit

これが、標準モジュールの先頭に自動的に挿入されるようになります。
これは、「変数は必ず宣言してから使います。」とエクセルに伝えたことになります。


第13回.定数宣言のConstと型宣言文字

Const定数の基本

Const 定数名 [As データ型] = "定数の値"

変数との違いは、VBA実行の途中で値を変更できない事です。

Const strタイトル As String = "見出し"
Const int開始行 = 3

"見出し"という文字の代わりに、strタイトルを、
3という数値の代わりに、int開始行を使うようにします。

定数を使う事の利点を簡単に書くと以下になります。
意味のない数値や長い文字を、分かりやすい名称で使う事が出来ます。
同じ文字や数値を、VBA内で何度も記述しなくて済みます。
文字や数値を変更する場合、Const定数だけを修正すれば良く「保守性」が高まります。

変数でも同じ事が出来るが・・・

Dim int開始行
int開始行 = 3


確かに、マクロの動作としては同じことになります。
ですが、マクロを処理手順を記載した文章として考えて下さい。

int開始行は値を変えることがあるのか無いのか・・・
マクロを見たときに一目でわかる方が、手順書として優れていることは間違いないはずです。

ダブルクォーテーションについて

ここまでの説明で、
文字は、"(ダブルクォーテーション)で囲み、
数値は、そのまま書いていることは、気づきましたか。

数値型の変数・定数に入れるときは数値をそのまま書き、
文字列型の変数・定数に入れるときは"で囲むと分かり安いでしょう。

型宣言文字

型宣言文字 変数の型
% Integer(整数型)
& Long(長整数型)
! Single(単精度浮動小数点型)
# Double(倍精度浮動小数点型)
@ Currency(通貨型)
^ Longlong(64ビット符号付き数値)※64ビットExcelのみ有効
$ String(文字列型)

Dim i%
Const j& = 123456

これでデータ型を指定できます。

リテラル値(定数値)を指定する場合にも使います。
123%
123&


16進定数、8進定数
16進 : &H
8進 : &O


Const i1 As Long = &H11 '16進数の11なので10進数で17
Const i2 As Long = &O11 '8進数の11なので10進数で9

日付の定数に関する注意
Const 日付定数 As Date = 2011 / 12 / 28 '←ダメ!
2011 ÷ 12 ÷ 28 の計算結果の数値となってしまいます。

Const 日付定数 As Date = #12/28/2011#
このように、#で囲みます。
#12/28/2011#の部分は、#2011/12/28# と入力しても自動的に#12/28/2011#に変換されます。

その他の定数

VBAには、あらかじめ用意された定数もあります。

組み込み定数 ・・・ VBAに用意されている定数
ユーザー定義の定数 ・・・ 独自に設定する定数。ここまでに説明したものです。
条件付きコンパイル定数 ・・・ 条件によりコンパイルを変更できます。
条件付きコンパイルは、VBAの特定のコードブロックを選択してコンパイルします、VBAの他の部分は無視されます、条件付きコンパイルステートメントは、実行時ではなくコンパイル時に実行されます。条件に基づいてコンパイルするコードのブロックを指定するには #If...Then...#Elseディレクティブを使用します。

組み込み定数について
VBA共通の組み込み定数
vb○○○

mso○○○

xl○○○


第14回.文字の結合(&アンパサンド)と継続行(_アンダーバー)

文字結合&(アンパサンド)

Sub 練習1()
  Cells(1, 3).Value = Cells(1, 1).Value & Cells(1, 2).Value
End Sub

&(アンパサンド)でつなげます。
C1セルに、=A1&B1 これと同じです。

+(プラス)でつなげることもできます。
Cells(1, 3).Value = Cells(1, 1).Value + Cells(1, 2).Value
このように書く事も出来ますが、セルの値が数値同士の場合は足し算になってしまいます。

文字リテラルを結合することもできます。

Sub 練習2()
  Cells(1, 3).Value = Cells(1, 1).Value & "xyz"
End Sub


上記では、A1セルの文字の後ろに、"xyz"を結合して、C3セルに入れています。

継続行

Sub 練習3()
  Cells(1, 6).Value = Cells(1, 1).Value & Cells(1, 2).Value & Cells(1, 3).Value & Cells(1, 4).Value & Cells(1, 5).Value
End Sub


見易いように適当に改行を入れる事が出来ます。

Sub 練習3()
  Cells(1, 6).Value = Cells(1, 1).Value & _
            Cells(1, 2).Value & _
            Cells(1, 3).Value & _
            Cells(1, 4).Value & _
            Cells(1, 5).Value
End Sub


半角スペースに続けて_(アンダーバー)を書くと、次の行に続けて書く事が出来ます。


第15回.四則演算と注釈(コメント)

算術演算子

演算子 説明
+ 足し算
- 引き算(符号反転)
* 掛け算
/ 割り算
\ 割り算の商
Mod 割り算の余り
^ 指数(べき乗)

計算の優先順位が必要な場合は、必ず()でくくって計算順序を明示して下さい。
優先順位について簡単に記載しておきます。
指数(^) > 符号反転(-) > 乗算と除算(*、/) > 整数除算(\) > 剰余演算(Mod) > 加算と減算(+、-) > 文字列連結(&)

A1セルに、単価として300
B1セルに、数量として5
これらが入っているとして、
C1セルに、金額(単価×数量)として1500を入れる場合です。

Sub 練習1()
  Cells(1, 3).Value = Cells(1, 1).Value * Cells(1, 2).Value
End Sub

このマクロを見ても何の計算をしているかが不明です。
C1セル = A1セル * B1セル
そこで、コメント(注釈)を付けて、何をしているか分かるようにします。

注釈

Sub 練習1()
  '金額 = 単価 * 数量
  Cells(1, 3).Value = Cells(1, 1).Value * Cells(1, 2).Value
End Sub

このように、'(シングルクォーテーション)の後ろは、コメント(注釈)となります。

コメントは、VBA実行に何も影響を与えません。
コメントは行の途中からでも書く事が出来ます

Sub 練習1()
  Cells(1, 3).Value = Cells(1, 1).Value * Cells(1, 2).Value '金額 = 単価 * 数量
End Sub

'(シングルクォーテーション)から後ろだけがコメントになり、
その前までは、普通にマクロVBAを書く事が出来ますし、実際に実行されます。

Remステートメント

'(シングルクォーテーション)以外にコメントを書く方法としてRemステートメントがあります。

Sub 練習1()
  Rem 金額 = 単価 * 数量
  Cells(1, 3).Value = Cells(1, 1).Value * Cells(1, 2).Value: Rem 金額 = 単価 * 数量
End Sub


第16回.繰り返し処理(ForNext)

For Next ステートメント

指定した回数だけ、一連のステートメントを繰り返すフロー制御ステートメントです。

構文

For counter = start To end [Step step]
  [statements]
  [Exit For]
  [statements]

  
Next [counter]

counter 必ず指定します。
カウンタに使う数値変数を指定します。
start 必ず指定します。
引数 counter の初期値を指定します。
end 必ず指定します。
引数 counter の最終値を指定します。
step 省略可能です。
ループを繰り返すごとに引数 counter に加算される値を指定します。
引数stepを省略すると、ループを繰り返すごとに引数counterには1が加算されます。
statements 省略可能です。
ループ内で実行される一連のステートメントで、For と Next の間に記述します。

ステートメントとは、マクロの挙動を制御する構文、命令文です。
・キーワード ・予約語 ・演算子 ・識別子 ・ステートメント ・VBA用語の最後に
日本語に直してみましょう。

For 変数 = 開始数値 To 終了数値
  ・・・処理・・・
Next

変数開始数値から終了数値になるまで繰り返すということです。

Next [カウンター変数]
このカウンター変数は、省略可能です。

For Next 例文

A1セルからA10セルに、1を入れる場合です。

Sub 練習1()
  Dim i        '変数iを宣言


  For i = 1 To 10   '変数iを1から10まで1ずつカウントアップ
    Cells(i, 1) = 1 'A列のi行目のセルに1を入れる
  Next i        'Forの範囲はここまで
End Sub


For Next をステップ イン実行で目で見て確認しましょう。

マクロを1行ずつ実行する方法を、ステップ インと呼びます。
VBEの使い方:デバッグ
・実行、中断、リセット(停止) ・ステップ実行 ・呼び出し履歴 ・VBA実行途中で変数の状態を確認 ・Debug.Print ・Debug.Assert ・デバッグの最後に
コーディングとデバッグ
・効率的なコーディング ・デバッグ ・イミディエイト ウインドウ(Ctrl+G) ・ローカル ウインドウ ・「ツール」→「オプション」 ・陥りやすい間違い、よくやってしまう間違い ・サイト内の関連ページ
デバッグの基礎
・【ここでのポイント】 ・デバッグとは ・論理エラーと文法エラー ・イミディエイトウィンドウ ・ブレークポイント ・ステップ実行 ・その他のデバッグ用のウィンドウ ・【業務改善の実務】 ・【本サイト内の関連ページ】 ・VBAエキスパート公式テキスト

VBA Excel 画像

VBA Excel 画像

SubからEnd Subの範囲内に入力カーソルがある状態で、
ステップ イン実行、F8を押します。

VBA Excel 画像

黄色い行は、これからこの行を実行するという事です。
F8を押すごとに、黄色い行が進んでいきます。

VBA Excel 画像
VBA Excel 画像

マウスカーソルを変数の上に乗せると、変数の値がポップアップ表示されます。

VBA Excel 画像

F8を次々に押しながら、
その時々のシートの状態を確認してください。

VBA Excel 画像

1行置きに処理する場合

1行目、3行目、5行目・・・

Sub 練習2()
  Dim i
  For i = 1 To 10 Step 2
    Cells(i, 1) = 1
  Next i
End Sub

Step 2
これが1行置きの指定です。

iが2ずつ増えていきます
つまり、これを書かない時は、
Step 1

このStepは、マイナス数値も指定できます
Step -1
とすれば、iは1ずつ減っていきます。
セルの行を下から処理したい場合や、シートを後ろから処理したい場合です。

Exit For

Exit Forは、For~Nextのループを抜けます。

通常のFor~Nextは指定回数繰り返す為に使うので、Exit Forを使う事は少ないのですが、
特定の条件になった場合は、指定回数の処理を完了する前にループを抜けたい場合に使用します。

Sub 練習3()
  Dim i
  For i = 1 To 10
    If Cells(i, 1) <> "" Then
      Exit For
    End If
    Cells(i, 1) = 1
  Next
End Sub

For~Nextのネスト(入れ子)

縦10行、横10列に1を入れる場合です。

Sub 練習3()
  Dim i, j
  For i = 1 To 10
    For j = 1 To 10
      Cells(i, j) = 1
    Next j
  Next i
End Sub

ネストは、概ね3段階までが推奨されます。


第17回.繰り返し処理(DoLoop)

Do~Loopの構文

Do [{While | Until} condition]
  [statements]
  [Exit Do]
  [statements]

Loop

Do
  [statements]
  [Exit Do]
  [statements]

Loop [{While | Until} condition]

condition 省略可能です。
真 (True) または偽 (False) を評価する数式、あるいは文字列式を指定します。
statements 引数 condition が真 (True) である間、または引数 condition が真 (True) になるまで繰り返し実行される、
任意の行数のステートメントを記述します。

余分なものを消して、さらに日本語で書くと

Do [While 条件式]
  ・・・処理・・・
Loop


Do [Until 条件式]
  ・・・処理・・・
Loop

Whileは、条件を満たす間、・・・処理・・・を実行します。
Untilは、条件を満たす迄、・・・処理・・・を実行します。

条件式

真(True)か偽(False)を評価する数式または文字列式を指定します。

比較演算子 意味
= 等しい
<> 等しくない
> より大きい
>= 以上
< より小さい
<= 以下

論理演算子 意味
And 論理積
Or 論理和
Xor 排他論理和
Eqv 論理等価
Imp 論理包含

And、Or、Notが理解できていれば問題ありません。


Do Loop 例文

A1セルからA10セルに、1を入れる場合です。

Sub 練習1()
  Dim i
  i = 1
  Do While i <= 10
    Cells(i, 1) = 1
    i = i + 1
  Loop
End Sub

Sub 練習2()
  Dim i
  i = 1
  Do Until i > 10
    Cells(i, 1) = 1
    i = i + 1
  Loop
End Sub

Whileは、条件を満たす間
Untilは、条件を満たす迄

WhileUntilの条件は、補集合の関係になります。

Not(Whileの条件) = Untilの条件
Whileの条件 = Not(Untilの条件)

このような関係になります。

i = 1
これは、変数iに1を入れています。
i = i + 1
これは、変数iに1を足して、変数iに入れ直しています。
つまり、変数iは1増える事になります。

Sub 練習1()
  Dim i        '変数iを宣言
  i = 1         '変数iに1を入れる
  Do While i <= 10  '変数iが10以下の間処理を続ける
    Cells(i, 1) = 1 'A列のi行に1を入れる
    i = i + 1     '変数iを1増やす
  Loop        'Doの処理範囲はここまで
End Sub

Exit Do

Exit Doは、Do~Loopのループを抜けます。
指定の条件になる前に、
Do~Loopのループ処理を終了したい場合に使用します。

Do
 処理を終了したい時に、Exit Do
Loop

このように、Doに終了条件を書かずに、終了条件を判定し処理を終了できます。

Do~Loopのネスト(入れ子)

縦10行、横10列に1を入れる場合です。

Sub 練習3()
  Dim i, j
  i = 1
  Do Until i > 10
    j = 1
    Do Until j > 10
      Cells(i, j) = 1
      j = j + 1
    Loop
    i = i + 1
  Loop
End Sub


第18回.最終行の取得(End,Rows.Count)

.End(xlDown):Ctrl+↓

手操作で、A列の最終行に移動する場合は、
A1セルが選択されている状態で、
Ctrl + ↓
これで、A11に移動できます。

これをマクロVBAでは、
Cells(1, 1).End(xlDown)
このように書きます。

しかし、途中に空欄のセルがある場合、

マクロVBA 最終行

この場合は、Ctrl + ↓ではA6セルになってしまいます。

.End(xlUp):Ctrl+↑

表の途中に空欄が不特定に入っている場合は、
いったんシートの一番下(シートの最終行)のA列(A1048576)セルに移動してから、
Ctrl + ↑
とやれば、A11に移動できます。

これをマクロVBAでは、
Cells(Rows.Count, 1).End(xlUp)
このように書きます。
Rows.Countとは
Rows.Countは、シートの行数になります。
2007以降なら1048576です、2003なら65536です。
Rowsとはシートの全行で、Countなのでその数ということです。

シートのA列の最終行から、上に向かって、Ctrl+↑で移動している訳です。
このEndは、Rangeオブジェクトのプロパティで、Rangeオブジェクトを返します


Endプロパティの方向(↑↓←→)について

Endプロパティの構文は、
End(Direction)


Directionに指定するものは、
xlUp : ↑上へ
xlDown : ↓下へ
xlToLeft : ←左へ
xlToRight : →右へ

セルの行数を取得するRowプロパティ

Endプロパティで取得したままの、
Cells(1, 1).End(xlDown)
Cells(Rows.Count, 1).End(xlUp)
これらは、最期のセル(行数ではなく、セルそのもの)を表しています。
つまり最期のセルのRangeオブジェクトになります。
そのままでは行数ではなく、セルに入っている値(Value)が取得されてしまいます。

セルの行数(行位置)を取得するには、
Cells(1, 1).End(xlDown).Row
Cells(Rows.Count, 1).End(xlUp).Row
このように、.Rowを指定します。

最終行を取得する場合は、
Cells(Rows.Count, 1).End(xlUp).Row
これは、定型文として丸暗記して下さい。

Cells(Rows.Count, 1).End(xlUp).Rowを日本語に訳す

Cells(Rows.Count, 1).End(xlUp).Row
セルA1048576からCtrl+のセルの行数

この一文には多くのプロパティを含んでいて、今後の学習に必ず役に立ってきます。

EndプロパティがRangeオブジェクトを返す

プロパティは、オブジェクトの属性
そのプロパティがオブジェクトを返す

今は、プロパティには、値の設定・取得をするものと
オブジェクトを返すプロパティがあると言う事だけ知っておいて下さい。
EndはRangeオブジェクト返すプロパティでだということです。

Endプロパティの問題点

表の最下行が、非表示行またはフィルタで絞り込まれて非表示になっている場合
手動のCtrl+↑でも同じですが、可視セル(見えているセル)の最終行となってしまいます。


第20回.条件分岐(If)

Ifステートメントの構文

If 条件式 Then 真の処理 [Else 偽の処理]

If 条件式 Then
  真の処理 ・・・ 条件式を満たした場合の処理
[Else
  偽の処理] ・・・ 条件式を満たさない場合の処理
End If

真とは、条件式を満たす場合、
偽とは、条件式を満たさない場合

条件式に書かれている条件を判定し、
条件を満たしている(合致している)場合は真の処理、それ以外の(条件式を満たさない)場合は偽の処理を実行します。

IFステートメントの条件式

比較演算子
比較演算子 意味
= 等しい
<> 等しくない
> より大きい
>= 以上
< より小さい
<= 以下

論理演算子
論理演算子 意味 条件例 結果
And 論理積 True And True TRUE
True And False FALSE
False And True FALSE
False And False FALSE
Or 論理和 True Or True TRUE
True Or False TRUE
False Or True TRUE
False Or False FALSE
Xor 排他論理和 True Xor True FALSE
True Xor False TRUE
False Xor True TRUE
False Xor False FALSE
Not 論理否定 Not True FALSE
Not False TRUE
Eqv 論理等価
Imp 論理包含

And、Or、Not以外の論理演算子は当分は使用する必要はないでしょう。

Is演算子
2つのオブジェクト参照変数を比較するために使用されます。
If オブジェクト1 Is オブジェクト2 Then
オブジェクト1とオブジェクト2の両方が同じオブジェクトを参照する場合はTrueになります。

演算子の優先順位
算術演算子 > 比較演算子 > 論理演算子
各演算子グループの中では、より上が優先順位が高くなります。











↓ 
算術演算子 比較演算子 論理演算子
^(指数) すべて同じ
優先順位
=
<>
<
>
<=
>=
Like
Is
Not
-(符号反転 ) And
* / Or
\(割り算の商) Xor
Mod(割り算の余り) Eqv
+ - Imp
&(文字列連結)

同一優先順位の場合は、式の左から右へ順に評価されます。

Ifステートメントの使用例文

If Cells(1, 1) >= 100 Then Cells(1, 2) = "○"


A1セルが100以上の場合、B1セルに"○"

If Cells(1, 1) >= 100 Then
  Cells(1, 2) = "○"
End If


特段の事情(1行で書く事でプログラムの可読性を高めたい等の場合)が無い限り、こちらを使いましよう。

If Cells(1, 1) >= 100 Then
  Cells(1, 2) = "○"
Else
  Cells(1, 2) = "×"
End If


A1セルが100以上の場合、B1セルに"○"
A1セルが100未満の場合、B1セルに"×"

If Cells(1, 1) >= 100 Then Cells(1, 2) = "○" Else Cells(1, 2) = "×"


これは、例文3と同じですが、この使い方は余程の事情がない限り使わない方がよいです、

Ifステートメントのネスト(入れ子)

If Cells(1, 1) >= 100 Then
  If Cells(1, 1) >= 200 Then
    Cells(1, 2) = "A"  '>= 200
  Else
    Cells(1, 2) = "B"  '>=100 AND < 200
  End If
Else
  If Cells(1, 1) >= 50 Then
    Cells(1, 2) = "C"  '< 100 AND >= 50
  Else
    Cells(1, 2) = "D"  '< 50
  End If
End If


A1セルが
>= 200の場合、B1セルに"A"
>=100 AND < 200の場合、B1セルに"B"
< 100 AND >= 50の場合、B1セルに"C"
< 50の場合、B1セルに"D"

ネストのレベルに特に制限はありませんが、概ね3段階までにしましょう。


第21回.条件分岐(ElseIf)

IfステートメントのElseIfの構文

If 条件式1 Then
  条件式1が真の処理
[ElseIf 条件式2 Then
  条件式2が真の処理]
[ElseIf 条件式3 Then
  条件式3が真の処理]
・・・
[Else
  全ての条件式が偽の処理]
End If

[ ]は省略可能です。

条件式1に合致した場合(True)は、「条件式1が真の処理」が処理されます。
条件式1に合致しない場合(False)は、条件式2が判定されます。
条件式2に合致した場合(True)は、「条件式2が真の処理」が処理されます。
条件式2に合致しない場合(False)は、条件式3が判定されます。
条件式3に合致した場合(True)は、「条件式3が真の処理」が処理されます。
以下ElseIfが続く限り・・・

ElseIfの説明

ElseIf 条件式 Then
  条件式が真の処理

この部分は繰り返し何回も書く事が出来ます。

If 条件式1 Then
  条件式1が真の処理
ElseIf 条件式2 Then
  条件式2が真の処理
ElseIf 条件式3 Then
  条件式3が真の処理
Else
  全ての条件式が偽の処理
End If

ElseIfを使わずに書くと、

If 条件式1 Then
  条件式1が真の処理
Else
  If 条件式2 Then
    条件式2が真の処理
  Else
    If 条件式3 Then
      条件式3が真の処理
    Else
      全ての条件式が偽の処理
    End If
  End If
End If

この二つのVBAコードは同じになります。
多肢分岐の場合は、ElseIfを使う事で見易くなります。
ただし、できればこのElseIfより、Select Caseを使うことを考えましょう。


第22回.条件分岐(SelectCase)

Select Caseステートメントの構文

Select Case testexpression
    [Case expressionlist-n
        [statements-n]]
    ・・・
    [Case Else
        [elsestatements]]
End Select

testexpression 必ず指定します。
任意の数式または文字列式を指定します。
expressionlist-n

Case 節がある場合は、必ず指定します。
次の形式で指定します。
expression
expression To expressionIs
comparisonoperator expression
また、複数指定するときは、カンマ (,) で区切ります。
引数 expression には数式または文字列を、引数comparisonoperatorには比較演算子を指定します。
キーワード To は、値の範囲を指定するキーワードです。
キーワード To を使って値の範囲を指定するときは、小さい方の値を先 (左側) に指定してください。
キーワード Is は、値の範囲を指定するキーワードで、Is 演算子と Like 演算子以外の比較演算子と共に使われます。
キーワード Is は指定しなくても自動的に挿入されます。

statements-n 省略可能です。
引数 testexpression が引数 expressionlist-n のいずれかと一致するとき、一致した引数 statements-n のステートメントが実行されます。
elsestatements 省略可能です。
引数 testexpression が Case 節のいずれとも一致しないとき、引数 elsestatements のステートメントが実行されます。

Select Case の例文

まずは基本形
Select Case 変数
  Case 1
    '変数=1の処理
  Case 2
    '変数=2の処理
  Case Else
    '変数が上記以外の処理
End Select

Or条件の書き方

Select Case 変数
  Case 1, 2
    '変数=1 Or 変数=2の処理
  Case 3, 4, 5
    '変数=3 Or 変数=4 Or 変数=5の処理
  Case Else
    '変数が上記以外の処理
End Select


範囲指定

Select Case 変数
  Case 1 To 2
    '変数=1 ~ 2の処理
  Case 3 To 5
    '変数=3 ~ 5の処理
  Case Else
    '変数が上記以外の処理
End Select


不等号の使い方

Select Case 変数
  Case Is <= 2 'Is は書かなければ自動挿入されます。
    '変数<=2の処理
  Case Is <= 5
    '変数<= 5の処理
  Case Else
    '変数が上記以外の処理
End Select


Select Caseは、とても使い道が広い汎用的なステートメントです。
If~ElseIf~End Ifは、全てSelect Caseで書き直すことが出来ます。

ElseIfを使ってVBAを書いた場合

If 条件式1 Then
  '条件式1が真の処理
ElseIf 条件式2 Then
  '条件式2が真の処理
ElseIf 条件式3 Then
  '条件式3が真の処理
Else
  '全ての条件式が偽の処理
End If


Select CaseでVBAを書いた場合
Select Case True
  Case 条件式1
    '条件式1が真の処理
  Case 条件式2
    '条件式2が真の処理
  Case 条件式3
    '条件式3が真の処理
  Case Else
    '全ての条件式が偽の処理
End Select


第23回.メッセージボックス(MsgBox)

MsgBox関数の構文

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

prompt 必ず指定します。
ダイアログ ボックス内にメッセージとして表示する文字列を示す文字列式を指定します。
rompt に複数行を指定する場合は、キャリッジ リターン (Chr(13))、ライン フィード (Chr(10))、またはキャリッジ リターンとライン フィードの組み合わせ (Chr(13) & Chr(10)) を改行する位置に挿入し、行を区切ってください。
buttons 省略可能です。
表示されるボタンの種類と個数、使用するアイコンのスタイル、標準ボタン、メッセージ ボックスがモーダルかどうかなどを表す値の合計値を示す数式を指定します。
省略すると、名前付き引数 buttons の既定値 0 になります。
title 省略可能です。
ダイアログ ボックスのタイトル バーに表示する文字列を示す文字列式を指定します。
名前付き引数 title を省略すると、タイトル バーにはアプリケーション名が表示されます。
helpfile 省略可能です。
ダイアログ ボックスに状況依存のヘルプを設定するために、使用するヘルプ ファイルの名前を示す文字列式を指定します。
context 省略可能です。
ヘルプ トピックに指定したコンテキスト番号を表す数式を指定します。

名前付き引数

あらかじめ定義された名前を持つ引数。
名前付き引数を使用すると、構文どおりに指定された順序で各引数に値を指定しなくても、任意の順序で値を設定することができます。

MsgBox関数のbuttonsの定数

グループ 定数 内容
ボタンの種類 vbOKOnly 0 [OK] ボタンのみを表示します。
vbOKCancel 1 [OK] ボタンと [キャンセル] ボタンを表示します。
vbAbortRetryIgnore 2 [中止]、[再試行]、および [無視] の 3 つのボタンを表示します。
vbYesNoCancel 3 [はい]、[いいえ]、および [キャンセル] の 3 つのボタンを表示します。
vbYesNo 4 [はい] ボタンと [いいえ] ボタンを表示します。
vbRetryCancel 5 [再試行] ボタンと [キャンセル] ボタンを表示します。
アイコンの種類 vbCritical 16 警告メッセージ アイコンを表示します。
Windowsのシステムエラーの音が鳴ります。
vbQuestion 32 問い合わせメッセージ アイコンを表示します。
vbExclamation 48 注意メッセージ アイコンを表示します。
Windowsのメッセージ(警告)の音が鳴ります。
vbInformation 64 情報メッセージ アイコンを表示します。
Windowsのメッセージ(情報)の音が鳴ります。
標準ボタン vbDefaultButton1 0 第 1 ボタンを標準ボタンにします。
vbDefaultButton2 256 第 2 ボタンを標準ボタンにします。
vbDefaultButton3 512 第 3 ボタンを標準ボタンにします。
vbDefaultButton4 768 第 4 ボタンを標準ボタンにします。
その他 vbApplicationModal 0 アプリケーション モーダルに設定します。メッセージ ボックスに応答するまで、現在選択中のアプリケーションの実行を継続できません。
vbSystemModal 4096 システム モーダルに設定します。メッセージ ボックスに応答するまで、すべてのアプリケーションが中断されます。
vbMsgBoxHelpButton 16384 ヘルプ ボタンを追加します。
VbMsgBoxSetForeground 65536 最前面のウィンドウとして表示します。
vbMsgBoxRight 524288 テキストを右寄せで表示します。
vbMsgBoxRtlReading 1048576 テキストを、右から左の方向で表示します。

MsgBox関数の戻り値の定数

MsgBox関数は以下の戻り値を返します。

VbMsgBoxResult列挙
定数 説明
vbOK 1 [OK]
vbCancel 2 [キャンセル]
vbAbort 3 [中止]
vbRetry 4 [再試行]
vbIgnore 5 [無視]
vbYes 6 [はい]
vbNo 7 [いいえ]

MsgBox関数の例文

promptのみ
MsgBox "出来ました。"

マクロ VBA MsgBox関数

promptを複数行
MsgBox "出来ました。" & vbLf & "よかった"

改行は、VbLfだけで良いです。 

buttonsを指定
MsgBox "出来ました。", vbOKOnly, "タイトル"

MsgBoxの戻り値を使用
Dim rtn As Integer
rtn = MsgBox("出来ましたか?", vbYesNo + vbQuestion + vbDefaultButton2, "確認")
Select Case rtn '押されたボタンの確認
  Case vbYes
    MsgBox "「はい」が押されました。"
  Case vbNo
    MsgBox "「いいえ」が押されました。"
End Select

戻り値を取得する為、
変数 = MsgBox(・・・)
とした場合は、引数を()括弧でくくります。
VBAにおける括弧()の使い方
・基本文型 ・VBAにおける括弧() ・VBAにおける半角空白の意味 ・戻り値を他の用途に使う時 ・括弧()の使い方の基本文型 ・Callを省略しなければ全て括弧が必要になる ・最後に一言


第24回.インプットボックス(InputBox)

InputBox関数の構文

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

prompt 必ず指定します。
ダイアログ ボックス内にメッセージとして表示する文字列を示す文字列式を指定します。
名前付き引数 prompt に複数行を指定する場合は、キャリッジ リターン (Chr(13))、ライン フィード (Chr(10))、またはキャリッジ リターンとライン フィードの組み合わせ (Chr(13) & Chr(10)) を改行する位置に挿入し、行を区切ってください。
title 省略可能です。
ダイアログ ボックスのタイトル バーに表示する文字列式を指定します。
名前付き引数 title を省略すると、タイトル バーにはアプリケーション名が表示されます。
default 省略可能です。
ユーザーが何も入力しない場合に、テキスト ボックスに既定値として表示する文字列式を指定します。
名前付き引数 default を省略すると、テキスト ボックスには何も表示されません。
xpos 省略可能です。
画面の左端からダイアログ ボックスの左端までの水平方向の距離を、twip単位で示す数式を指定します。
省略すると、水平方向に対して画面の中央の位置に配置されます。
ypos 省略可能です。
画面の上端からダイアログ ボックスの上端までの垂直方向の距離を、twip単位で示す数式を指定します。
省略すると、ダイアログ ボックスは垂直方向に対して画面の上端から約 1/3 の位置に配置されます。
helpfile 省略可能です。
ダイアログ ボックスに状況依存のヘルプを設定するために、使用するヘルプ ファイルの名前を示す文字列式を指定します。
context 省略可能です。
ヘルプ トピックに指定したコンテキスト番号を表す数式を指定します。

InputBox関数の例文

promptのみ
Dim strIn As String
strIn = InputBox("何か入力して下さい。")

promptを複数行
Dim strIn As String
strIn = InputBox("何か入力して下さい。" & vbLf & vbLf & "入力サンプルをここに書きます。")

prompt、title、defaultを指定
Dim strIn As String
strIn = InputBox("何か入力して下さい。", "ユーザー入力", "既定文字")

何も入力されなかった場合
Dim strIn As String
strIn = InputBox("何か入力して下さい。", "ユーザー入力", "規定文字")
If strIn = "" Then
  MsgBox "何も入力されませんでした。"
End If

何も入力されなかったか、「キャンセル」ボタンが押された場合は、空白が返されます。
従って、受け取った変数を、""で判定すれば良いです。
何も入力されなかった場合と「キャンセル」の時を区別したい場合は、以下を参照してください。
空文字の扱い方と処理速度について
・String型変数のメモリ配置と取得する関数 ・空文字列について ・String変数に空文字列を入れる ・Stringが空文字列か判定 ・InputBox関数の戻り値が空文字列判定 ・空文字列の処理方法による速度比較


第25回.名前付き引数について

名前付き引数について

通常、複数の引数がある場合は、その順番通りに指定します。
先頭より、
第1引数, 第2引数, 第3引数, ・・・ 

しかし引数の数が多いと、何番目なのかを意識することは面倒になりますし混乱します。

引数に名前を付けて、その名前で指定できるようになっています
それが、
名前付き引数です。

名前付き引数を指定する場合は、
名前付き引数名:=値
このように、:=で値を指定します。

名前付き引数の例文

MsgBox関数での名前付き引数の使用例です。

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

MsgBox "出来ました。", vbOKOnly, "タイトル"

この引数は順番通りに指定する必要があります
名前付き引数を使って指定すると、

MsgBox prompt:="出来ました。", Buttons:=vbOKOnly, Title:="タイトル"

MsgBox Title:="タイトル", Buttons:=vbOKOnly, prompt:="出来ました。"

名前付き引数を使えば、順番を変更しても問題ありません
また、名前付き引数を使わない場合は、途中の引数を省略する場合は、

MsgBox "出来ました。", , "タイトル"

このように、, , として、引数の位置は正しく指定する必要があります
しかし名前付き引数を使えば、

MsgBox prompt:="出来ました。", Title:="タイトル"

名前付き引数を使えば、引数の位置を意識することなく指定できます


第27回.ブック・シートの選択(Select,Activate)

アクティブ

アクティブブック、アクティブシート、アクティブセル
これらは、今まさに操作対象となっていると言う事です。

文字を打ちEnterを押したときに、入力されるブックのシートのセル
それが、アクティブブックのアクティブシートのアクティブセルです。

単に選択している状態とは違います
選択は複数できます。
Ctrlを押しながら選択したり、Shiftを押しながら選択すれば、 複数の選択が可能です。
従って、「選択」は複数ありますが、「アクティブ」は常に1つです。

マクロVBA ブック シート

この場合、選択シートは、Sheet1とSheet2ですが、
アクティブシートは、Sheet1になります。

ActiveWorkbook
マクロVBAにおいて、アクティブブックを指定する場合の書き方です。

ActiveSheet
マクロVBAにおいて、アクティブシートを指定する場合の書き方です。

ブックをアクティブにする

マクロVBAで指定ブックをアクティブにするには、
Workbooks("ブック名").Activate
ブック名には、拡張子(.xls、.xlsx、.xlsm)も指定する必要があります。

マクロ記録の場合は、
Windows("ブック名").Activate

上記、2つは、1つのブックに対し複数のウインドウを開いている時に違いが発生します。
ブックに対し1つしかウインドウが存在しない場合は、
ウインドウ名 = ブック名

シートの選択、シートのアクティブ

シートの選択
マクロVBAで指定シートを選択にするには、

Sheets("シート名").Select
Sheets(インデックス).Select
Worksheets("シート名").Select
Worksheets(インデックス).Select

インデックスはシートの順番の数値です。
非表示シートが無ければ、一番左のシートから順に、1,2,3・・・となります。
Sheetsはすべてのシート(ワークシート、グラフシート等)が対象です。
Worksheetsはワークシートだけが対象となります。

シートをアクティブにする
マクロVBAで指定シートをアクティブにするには、

Sheets("シート名").Activate
Sheets(インデックス).Activate
Worksheets("シート名").Activate
Worksheets(インデックス).Activate

Activateでも、シートが選択されます。

SelectとActivateの違い
Sheets("シート名").SelectとSheets("シート名").Activateの違いは、
1つしかシートを選択にしない場合は全く同じ事です。

複数シートを選択(作業グループ化)している場合にだけ違いが出てきます。

複数シートの選択(作業グループ化)

Sheets(Array("シート名1", "シート名2", ・・・)).Select
これで、複数シートが選択されます。
Arrayは配列です。
この状態で、
Sheets("シート名2").Activate

この場合は、シートの複数選択がそのままで アクティブシートのみ移動します。
しかし、この状態で、
Sheets("シート名2").Select
とした場合は、作業グループが解除され"シート名2"が選択されアクティブになります。

複数ブックを開いている時のシート選択

Sheets("シート名").Select
これで選択できるのは、アクティブブックのシートだけです。
アクティブブック以外のシートを選択する場合は、
そのブックをアクティブにしてから、シートのSelectメソッドを実行します。

Workbooks("Book2.xlsx").Activate
Sheets("Sheet2").Select


ブック→シートの順にアクティブにします。

ただし、Activateメソッドを使って1行で書くこともできます。
Workbooks("Book2.xlsx").Sheets("Sheet2").Activate
これでも、Book2のSheet2を選択状態にできます。

VBAでの色々なシート指定方法

ワークシートの指定方法には、以下の3通りがあります。

✅シートのインデックス番号で指定
✅シートの名称で指定
✅シートのオブジェクト名で指定


第28回.セル・行・列の選択(Select,ActivateとCurrentRegion)

選択セルとアクティブセル

マクロ VBA セルの選択

この状態では、
選択セルは、B2~C6の10個のセルです。
アクティブセルは、B2セルになります。

選択セルは、1つの領域(連続した範囲)である必要はなく、
Ctrlを押しながら選択した場合は、複数の領域が選択できます。

しかし、アクティブセルは常に1つです
アクティブブックのアクティブシートにのみ存在します。

ActiveCell

このセルに"文字"を入れる場合は、

ActiveCell = "文字"

しかし、マクロにおいてはActiveCellはあまり使わない方が良いでしょう。

セルの選択

Range("セル番地").Select
Cells(行, 列).Select

セルをアクティブにする

Range("セル番地").Activate
Cells(行, 列).Activate

SelectとActivateは、複数セルを選択している場合のみ違ってきます。
Range("A1:B10,E11:F20").Select
これを実行した状態で、
Range("F18").Activate
とすれば、セルの選択状態は変わらず、アクティブセルのみF18に移動します。
しかし、
Range("F18").Select
とした場合は、
他のセルの選択状態は解除され、F18セルのみ選択されアクティブになります。

行の選択、列の選択

行の選択
Rows(行位置).Select
列の選択
Columns(列位置).Select

セル領域の選択

連続したセル領域の選択には、CurrentRegionプロパティを使います。

CurrentRegionプロパティ
選択範囲を自動的に拡大して、 現在の領域全体が含まれるようにします。
現在の選択範囲は、空白の行と空白の列の任意の組み合わせで囲まれた範囲です。

VBA マクロ CurrentRegion

Range("B2").CurrentRegion.Select

上記の"B2"はB2:D6の範囲であればどのセルを指定しても選択されるセル範囲は同じになります。
B4,C3,D5,C6のように部分的な空欄はセル領域として認識されます。
7行目は空行となっているので、8行目以降は範囲に含まれません。

メソッドとはプロパティとは

Select
Activate

これらは、Rangeオブジェクトメソッドです。
メソッドとは方法・方式で、
オブジェクトに対する動作・操作を指定します

オブジェクトは対象物、プロパティはその属性、メソッドは動作を与えます。


第29回.セル・行・列の削除・挿入(Delete,Insert)

セルの削除

Cells(行, 列).Delete

手動でセルを削除した場合は以下のダイアログが表示されます。

マクロ VBA セルの削除

左方向にシフト
Cells(行, 列).Delete Shift:=xlToLeft

上方向にシフト
Cells(行, 列).Delete Shift:=xlUp

行全体
Cells(行, 列).EntireRow.Delete

EntireRowは、指定セルが含まれる行全体になります。
EntireRowは、RangeオブジェクトのプロパティでRangeオブジェクトを返します。

列全体
Cells(行, 列).EntireColumn.Delete

EntireColumnは、指定セルが含まれる列全体になります。
EntireColumnは、RangeオブジェクトのプロパティでRangeオブジェクトを返します。

セルの挿入

Cells(行, 列).Insert

マクロ VBA セルの挿入

右方向にシフト
Cells(行, 列).Insert Shift:=xlToRight

下方向にシフト
Cells(行, 列).Insert Shift:=xlDown

行全体
Cells(行, 列).EntireRow.Insert

EntireRowは、指定セルが含まれる行全体になります。

列全体
Cells(行, 列).EntireColumn.Insert

EntireColumnは、指定セルが含まれる列全体になります。

セルの削除・挿入時は、Shift:=は必ず指定

Shift:=は省略可能なのですが、
セルの挿入削除時には、Shift:=は必ず指定しましょう。

省略した場合は、どちらにシフトされるかは選択セル範囲の形によって自動で判定されます。
縦長か、横長かによって、自動的に上下左右が決定されます。

行・列の削除・挿入

行の削除
Rows(行位置).Delete

行の挿入
Rows(行位置).Insert

列の削除
Columns(列位置).Delete

列の挿入
Columns(列位置).Insert

行・列の削除/行・列の挿入で、Shift:=は必要か

マクロの記録で作成される行削除した時のVBAコードは、

Rows("6:6").Select
Selection.Delete Shift:=xlUp

このShift:=xlUpが必要なのか、との質問が時々あります。
結論としては、不要です。

セル範囲の挿入・削除では、Shift:=は必須
行・列の挿入・削除では、Shift:=は不要


第31回.セルの書式(表示形式,NumberFormatLocal)

マクロでの表示書式の指定

Range.NumberFormatLocal = "表示書式指定文字"

Range.は、Range・Cells・Rows・Columns等、Rangeオブジェクトになります。

例.
Range.NumberFormatLocal = "@" '文字列
Range.NumberFormatLocal = "#,###"'カンマ区切りで0サプレス
Range.NumberFormatLocal = "0.00" '小数点2桁固定
Range.NumberFormatLocal = "yyyy/mm/dd" '日付、月・日を2桁

表示書式指定文字

ワークシートで、「セルの書式設定」→「表示形式」
ここで、「ユーザー定義」で指定する、書式指定文字と同じです。

書式指定文字

説明
G/標準 標準の表示形式です。
_(アンダーバー) _(アンダーバー)に続く文字の幅だけ文字間隔を空けることができます。
" 「"」で囲まれた文字列を表示します。
入力値を文字列としてそのまま表示します。
# 小数点のいずれの側でも表示形式の # 記号の数より少ない場合に 0 は表示されません。
0 数値の桁数が表示形式の 0 の桁数より少ない場合に、表示形式の桁まで 0 が付加されます。
? 有効桁以外の 0 にスペースが挿入されるため、列内で小数点の位置が整列されます。
. (ピリオド) 数値に小数点を表示します。
,(カンマ) 数値に桁区切り記号を表示します。
yy 年を2桁の数値で表示します。
yyyy 年を4桁の数値で表示します。
g 元号のアルファベット1文字が表示されます。M,T,S,Hが表示できます。
gg 元号の1文字が表示されます。明,大,昭,平
ggg 元号が表示できます。明治,大正,昭和,平成
e 年数が1桁で表示されます。
m 月数を表示します。1~12が表示できます。
mm 01~12が表示できます。1~9は01~09と0付で表示されます。
mmm Jan~Dec
mmmm January~December
mmmmm J~D頭文字が表示されます。
d 日数を表示します。1~31が表示できます。
dd 01~31が表示できます。
ddd Sun~Sat
dddd Sunday~Saturday
aaa 日~土
aaaa 日曜日~土曜日
h 時を表示します。0~23が表示できます。
hh 00~23が表示できます。
m 分を表示します。0~59が表示できます。
mm 00~59が表示できます。
s 秒を表示します。0~59が表示できます。
ss 00~59が表示できます。
[h] 24時を超える時間を表示します。
[m] 60分を超える分を表示します。
[s] 60秒を超える秒を表示します。

表示書式指定文字の調べ方

正の数の書式;負の数の書式;ゼロの書式;文字列の書式

これはエクセルの基本として押さえて置いてください。

マクロで書式を設定する時に、書式文字が分からない場合は、
「ユーザー定義」で表示を確認して使えば良いでしょう。

Range.NumberFormatについて

Localが付かない、NumberFormatというプロパティもあります。
Localと付くものと付かないものの違いは、PC環境に依存するかどうかです。
Localと付くものはPC環境に依存する設定を有効にする場合に使います。

12345 → \12,345 と表示したい時、
Range("A1").NumberFormat = "\#,##0"
これでは期待した形式になりません。
"\"は、次に続く特殊文字をそのまま表示する記号として扱われてしまいます。
Range("A1").NumberFormatLocal = "\#,##0"
これであれば、期待通りに表示されます。


第32回.セルの書式(配置,Alignment)

マクロVBAでの配置の指定

Range.プロパティ = 設定値

プロパティ プロパティの説明 設定値 設定値の説明
HorizontalAlignment 横位置 xlCenter 中央
xlDistributed 均等割り付け
xlJustify 両端揃え
xlLeft
xlRight
VerticalAlignment 縦位置 xlBottom
xlCenter 中央
xlDistributed 均等割り付け
xlJustify 両端揃え
xlTop
WrapText 折り返し True する
False しない
Orientation 文字の向き -90 ~ 90 角度 (°)
xlDownward 下向き
xlHorizontal 水平方向
xlUpward 上向き
xlVertical 文字列は下向きでセルの中央に配置されます。
AddIndent 自動的にインデント True する
False しない
IndentLevel インデントのレベル 0 ~ 15
ShrinkToFit 自動的に縮小 True する
False しない
ReadingOrder 読む順序 xlRTL 右から左
xlLTR 左から右
xlContext 最初の文字に依存
MergeCells セルの結合 True する
False しない

マクロVBAでの配置の使用例

セルを結合して中央揃え

Range("セル範囲").HorizontalAlignment = xlCenter
Range("セル範囲").VerticalAlignment = xlCenter
Range("セル範囲").MergeCells = True


MergeCellsはRangeのプロパティですが、セルの結合に関しては、メソッドもあります。
Range("セル範囲").Merge


第33回.セルの書式(フォント,Font)

マクロでの指定

Range.Font.プロパティ = 設定値

プロパティ プロパティの説明 プロパティの設定値と説明
Name フォント名
FontStyle フォント スタイル 下のBoldとItalicを使用して下さい。
Bold 太字 True、False
Italic 斜体 True、False
Size フォントのサイズ
Strikethrough 水平な取り消し線 True、False
Superscript 上付き文字 True、False
Subscript 下付き文字 True、False
OutlineFont アウトライン フォント True、False
Shadow 影付きフォント True、False
Underline 下線の種類 xlUnderlineStyleNone
xlUnderlineStyleSingle
xlUnderlineStyleDouble
xlUnderlineStyleDoubleAccounting
Color フォントの色 RGB値を表す長整数、色定数またはRGB関数
ColorIndex フォントの色 2003までの56色カラーパレットのインデックス
何色か分かりづらいのでColorを使ったほうが良い
ThemeColor 配色のテーマ カラー 2007以降のみ
TintAndShade 色を明るく、または暗く 2007以降のみ
ThemeFont テーマのフォント 2007以降のみ

色定数

vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta マゼンタ
vbCyan シアン
vbWhite

Range("セル範囲").Font.Color = vbRed '赤
Range("セル範囲").Font.Color = vbWhite '白
Range("セル範囲").Font.Color = vbBlack '黒

RGB関数

RGB(red, green, blue)

red, green, blue、それぞれを0~255で指定します。

Range("セル範囲").Font.Color = RGB(255, 0, 0) '赤
Range("セル範囲").Font.Color = RGB(255, 255, 255) '白
Range("セル範囲").Font.Color = RGB(0, 0, 0) '黒

色の指定を解除(自動)

色の指定を自動に戻す場合は、

Range("セル範囲").Font.ColorIndex = xlAutomatic 

見た目の結果は黒文字になりますが、「自動」と「黒」は違うということを理解しておいてください。


第34回.セルの書式(塗りつぶし,Interior)

マクロVBAでのInterior指定

Range.Interior.プロパティ = 設定値

プロパティ プロパティの説明 プロパティの設定値と説明
Color 塗りつぶす色 RGB値を表す長整数、色定数またはRGB関数
ColorIndex 塗りつぶす色 2003までの56色カラーパレットのインデックス
何色か分かりづらいのでColorを使ったほうが良い
Pattern 塗りつぶしのパターン XlPattern 列挙
PatternColor パターンの色 RGB値を表す長整数※1
PatternColorIndex パターンの色 2003までの56色カラーパレットのインデックス
何色か分かりづらいのでColorを使ったほうが良い

色の指定方法定数

前節の「セルの書式」を参照してください。

塗りつぶしなし

塗りつぶしを解除して、塗りつぶしなしにする場合は、

Range("セル範囲").Interior.ColorIndex = xlNone

見た目の結果は、白の塗りつぶしと同じように見えますが、
「白の塗りつぶし」と「塗りつぶしなし」は違うということを理解しておいてください。

条件付き書式との使い分け

条件付き書式で、データに応じて色を変更することができます。
多くの場合、マクロVBAで塗りつぶすより簡単なので良く使われていると思います。

ただし、条件付き書式は行列の挿入削除により分断が起こり、
シートが重くなる一因になることがあります。


第35回.セルの書式(罫線,Border)

A1セル~B5セルに格子線を引いた時のマクロの記録

うんざりするほど長いマクロガム記録されます。
しかし、以下のマクロで済んでしまいます

Sub Macro2()
  Range("A1:B5").Borders.LineStyle = xlContinuous
End Sub

マクロVBAでの罫線指定

セル(Rangeオブジェクト)の罫線は、Bordersコレクションになります。
Bordersコレクションは、Borderオブシェクトの集まりです。

RangeのBordersプロパティで、Bordersコレクション内のBorderオブジェクトを扱います。

Range.Borders.プロパティ = 設定値
Range.Borders(index).プロパティ = 設定値

Indexには、以下のXlBordersIndex列挙を指定します。

設定値 説明
xlDiagonalDown 範囲内の各セルの左上隅から右下への罫線
xlDiagonalUp 範囲内の各セルの左下隅から右上への罫線
xlEdgeBottom 範囲内の下側の罫線
xlEdgeLeft 範囲内の左端の罫線
xlEdgeRight 範囲内の右端の罫線
xlEdgeTop 範囲内の上側の罫線
xlInsideHorizontal 範囲外の罫線を除く、範囲内のすべてのセルの水平罫線
xlInsideVertical 範囲外の罫線を除く、範囲内のすべてのセルの垂直罫線

括弧()を省略して、
Range.Borders.プロパティ
とした場合は、
xlEdgeBottom、xlEdgeLeft、xlEdgeRight、xlEdgeTop、xlInsideHorizontal、xlInsideVertical
これら全てに同一の設定をしたことになります。

Bordersのプロパティ

プロパティ プロパティの説明 プロパティの設定値 設定値の説明
LineStyle 罫線の種類 xlNone または xlLineStyleNone なし
xlContinuous 実線
xlDash 破線
xlDashDot 一点鎖線
xlDashDotDot ニ点鎖線
xlDot 点線
xlDouble 2 本線
xlLineStyleNone 線なし
xlSlantDashDot 斜破線
Color 罫線の色 RGB値を表す長整数
ColorIndex 罫線の色 1~56 2003までの56色カラーパレットのインデックス
何色か分かりづらいのでColorを使ったほうが良い
Weight 罫線の太さ xlHairline 非常に細い線 (最も細い罫線)
xlMedium 普通
xlThick 太線 (最も太い罫線)
xlThin 細線
TintAndShade 罫線の色色明るく、または暗く 2007以降

規定値
LineStyleまたはWeightの片方だけを指定した場合は、規定値で罫線が引かれます。
LineStyleは、xlContinuous
Weightは、xlThin

プロパティの設定値には、組み合わせの制限があります
ワークシートの「セルの書式設定」で指定可能な組み合わせしか出来ません。

マクロ VBA 罫線

Range.BorderAroundメソッド

罫線を扱う方法が、もう一つあります。

Range.BorderAround LineStyle, Weight, {ColorIndex | Color | ThemeColor}

{ColorIndex | Color | ThemeColor}は、いずれか1つのみ指定可能。

LineStyle, Weight, ColorIndex, Color, ThemeColor

これらは、メソッドの引数になります。
以下は名前付き引数で指定した場合の使用例です。

使用例
Range("セル範囲").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, Color:=vbRed

セル範囲の外枠に、実線、太さ普通、赤色の罫線を引いています。

マクロVBAでの罫線の注意点

効率的かつ簡潔な罫線の引き方をするように工夫してください。
同じ罫線のセル範囲はまとめて罫線を引くようにします。

マクロ VBA 罫線

Range("セル範囲").Borders.LineStyle = xlContinuous
Range("セル範囲").BorderAround Weight:=xlThick

外枠の罫線は2度引いていることになりますが、
一か所ずつ罫線を引くよりマクロVBAの記述も短く簡潔になりますし、処理速度も若干は速くなります。


第37回.ブック・シートの指定

マクロVBAでのブック・シート指定の具体例

ブック:Book1.xlsx
シート:Sheet1
セル:A1

このA1セルに"文字"を入れる場合、

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1") = "文字"

ブック:アクティブブック
シート:Sheet1
セル:A1~B10

ここに、格子線を引く場合、

Worksheets("Sheet1").Range("A1:B10").Borders.LineStyle = xlContinuous

マクロVBAでのブック・シート指定の必要性

WorkbookやWorksheetをSelectしたり、Activateしたりせずに、
ブック・シートを指定して、操作するようにします。

アクティブブックのアクティブシートでなければ出来ない事もあります。
例えば、RangeのSelectやActivate等は、アクティブブックのアクティブシートのみで有効です。
しかし、ほとんどの(通常必要とされる)処理は、ブック.・シートを指定することで操作可能です。

ブックが1つしか開いてない場合や、アクティブブックのみ操作する場合は、
ブックの指定は省略して良いでしょう。
しかし、
シートの指定は、基本的には省略せずに指定しましょう。

VBAでの色々なシート指定方法

ワークシートの指定方法には、以下の3通りがあります。

✅シートのインデックス番号で指定
✅シートの名称で指定
✅シートのオブジェクト名で指定


第39回.セルのクリア(Clear,ClearContents)

セル(Rangeオブジェクト)のクリア関係のメソッド(動作を与える)

以下のメソッドは、Rangeは、Cells、Rows、Columns、Rangeのいずれでも使用可能です。

メソッド 説明
Clear 全体(数式・文字・書式・コメント全て)をクリアします。
ClearComments コメントを消去します。
ClearContents 数式と文字を削除します。
ClearFormats 書式設定を削除します。
ClearNotes コメントを削除します。
ClearOutline アウトラインを消去します。
ClearHyperlinks 2010以降のみ、ハイパーリンクを削除します。

Range.Clear

Clearメソッドを実行すると、セルの値も書式もコメントも全てが消去され、未使用状態(初期状態)になります。

Cells(2, 2).Clear
Range("B2").Clear
Range("B2:C10").Clear
Rows(2).Clear
Columns(2).Clear

Range.ClearContents

ClearContentsを実行すると、セルの数式と文字だけが消去されます。
定数としての文字・数値、そして計算式(関数)が消去されます。
書式(フォント、背景色、罫線等)は残ります。

Cells(2, 2).ClearContents
Range("B2").ClearContents
Range("B2:C10").ClearContents
Rows(2).ClearContents
Columns(2).ClearContents


第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)

セルをコピー(複写)する場合

A1セルをB1セルにコピー貼り付けする場合

Range("A1").Copy
Range("B1").Select
ActiveSheet.Paste
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Copy
これはA1セルをクリップボードにコピーします。

Range("B1").Select
ActiveSheet.Paste
アクティブシートのB1セルに貼り付けます。

Range("C1").Select
ActiveSheet.Paste
アクティブシートのC1セルに貼り付けます。
この記述方法ではアクティブシートのアクティブセル以外には、貼り付けできません。

Application.CutCopyMode = False
コピーモード(セルの周りが点々の状態)を解除します。

セル範囲を指定できます。
例."A1:C5"

セルを切り取る(移動する)場合

A1セルをB1セルにカット貼り付けする場合

Range("A1").Cut
Range("B1").Select
ActiveSheet.Paste

Range("A1").Cut
A1セルを切り取りします。

Range("B1").Select
ActiveSheet.Paste
アクティブシートのB1セルに貼り付けます。
A1セルは、クリアされた(初期)状態になります。

Cutの場合はCopyと違い、Paste(貼り付け)した時点で、コピーモード(セルの周りが点々の状態)が解除されます。
つまり、繰り返し貼り付けはできないという事になります。

セル範囲を指定できます。
例."A1:C5"

セル範囲のコピーについて

Range("A1:B10").Copy ・・・ ○
Range("A1,B5").Copy ・・・ ×
Range("A1,B5,C10").Copy ・・・ ×
Range("A1:B10,C11:D20").Copy ・・・ ×

上記の×は実行できません。
複数のセル範囲(離れた場所にあるセル範囲)はCopyはできません。

※行範囲が同じであったり列範囲が同じ場合は、複数範囲でもコピーできます。
Range("A1:B10,D1:E10").Copy
しかし、VBAで使う事はほぼありません。

別のシートにコピーする場合

Range("A1").Copy
Sheets("シート名").Select
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False 

シート選択すれば良い事は直ぐに理解できる事と思います。
しかし、アクティブシートにしか貼り付けできないのは不便です。
以下の書き方ならアクティブシート以外にもコピーできます。

アクティブシート以外へのコピー

Sheets("シート名1").Range("セル番地").Copy Destination:=Sheets("シート名2").Range("セル番地")

Destination:=
これは、名前付き引数です。
引数が1つなので、引数名は省略してもよいでしょう。

Copy Destinationの使用例

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("A1")

また、.Cutで切り取りも使用できます。

Sheets("Sheet1").Range("A1").Cut Sheets("Sheet2").Range("A1")

コピー方法は、アクティブシートへのコピーでも使えます。
従って、マクロVBAではアクティブシート以外へのコピー

Sheets("シート名1").Range("セル番地").Copy Destination:=Sheets("シート名2").Range("セル番地")

これだけを覚えて使用するようにすれば良いでしょう。


第41回.セルのコピー&値の貼り付け(PasteSpecial)

PasteSpecialメソッド

Range.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

名前 指定値 説明
Paste xlPasteAll すべてを貼り付けます。
xlPasteAllExceptBorders 輪郭以外のすべてを貼り付けます。
xlPasteAllMergingConditionalFormats すべてを貼り付け、条件付き書式をマージします。
xlPasteAllUsingSourceTheme ソースのテーマを使用してすべてを貼り付けます。
xlPasteColumnWidths コピーした列の幅を貼り付けます。
xlPasteComments コメントを貼り付けます。
xlPasteFormats 書式を貼り付けます。
xlPasteFormulas 数式を貼り付けます。
xlPasteFormulasAndNumberFormats 数式と数値の書式を貼り付けます。
xlPasteValidation 入力規則を貼り付けます。
xlPasteValues 値を貼り付けます。
xlPasteValuesAndNumberFormats 値と数値の書式を貼り付けます。
Operation xlPasteSpecialOperationAdd コピーしたデータは、対象セルの値に加算されます。
xlPasteSpecialOperationDivide コピーしたデータは、対象セルの値によって割り算されます。
xlPasteSpecialOperationMultiply コピーしたデータには、対象セルの値に掛け算されます。
xlPasteSpecialOperationSubtract コピーしたデータは、対象セルの値に引き算されます。
xlPasteSpecialOperationNone 貼り付け操作で計算は行われません。
SkipBlanks True / False クリップボードに含まれる空白のセルを貼り付けの対象にしないようにするには、True を指定します。
既定値は False です。
Transpose True / False 貼り付けのときにデータの行と列を入れ替えるには、True を指定します。
既定値は False です。

「形式を選択して貼り付け」の画面と対応させてみるようにしてください。

マクロ VBA コピー 値の貼り付け

値の貼り付け

Worksheets("Sheet1").Range("A1:B10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial _
                 Paste:=xlPasteValues, _
                 Operation:=xlNone, _
                 SkipBlanks:=False, _
                 Transpose:=False

前回のPasteとは違い、アクティブシート以外にも貼り付けが可能です
Paste:=xlPasteAll
これなら、セルの全てがコピーされるので、Pasteと同じ機能で別シートに貼り付け可能になります。

いろいろなコピーのVBAの書き方

Pasteを使って
Sheets("Sheet1").Select
Range("A1:B10").Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Destinationを使って
Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("A1")

PasteSpecialを使って

Sheets("Sheet1").Range("A1:B10").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll


PasteSpecialの使用例

Sheet1のA1:B10をコピーして、Sheet2のA1に行列を入れ替えて値を貼り付けします。

Worksheets("Sheet1").Range("A1:B10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues, Transpose:=True

Paste:=xlPasteValues ・・・ 値を貼り付け
Transpose:=True ・・・ 行と列を入れ替え


第42回.セルをコピーするとは

セルをコピーするとは

セルは、Rangeオブジェクトです、
オブジェクトそのものは、コピーできません。
では、何をコピーしているのでしょぅか。

オブジェクトのプロパティの値をコピーしているのです
.Copyで.Pasteなら、Rangeの全てのプロパティをコピーしているのです。
値の貼り付けなら、.Valueをコピーしているに過ぎないのです

Range("セル番地1").Value = Range("セル番地2").Value

Range("セル番地2").Valueの値を、Range("セル番地1").Valueに入れているのです
これで、値のコピーが出来る事が理解できると思います。

Range("セル番地2").Interior.Color = Range("セル番地1").Interior.Color
このようにすれば、背景色がコピーされます。
シートを指定すれば、別シート間でも可能です。

上記方法ではコピーできないプロパティ

Range.Borders.○○○
Range("B2").Borders.LineStyle
Range("B2").Borders.Weight
これらのプロパティは、上記の方法では正しくコピー出来ません。
罫線(Borders)には、上下左右斜めを指定する引数があります。
Bordersだけの指定では、どの罫線かが特定できません。
(Bordersは引数を省略して設定すると、上下左右に同じ罫線が設定されます。)

この他にもオブジェクトを返すプロパティ等もありますので、単純にコピーできないプロパティがある事に注意してください。

.Valueのセル範囲間のコピー

.Valueであれば、セル範囲でも正しく動作します。

Sheets("Sheet1").Range("A1:B10").Value = Sheets("Sheet2").Range("A1:B10").Value

これで値のコピーが出来てしまいます、実に簡単です。
注意点としては、
このセル範囲の場合は、.Valueを省略できません、省略してしまうと正しくコピーされません。
(正確には右辺のValueが省略できない)

.Value以外の場合は、セル範囲をセル範囲にコピーは出来ません

コピー先にはセル範囲を指定可能ですが、コピー元にはセル範囲は指定できません
正確には、正しくコピーされないと言う事です。

Sheets("Sheet2").Range("A1:B10").Interior.Color = Sheets("Sheet1").Range("A1").Interior.Color
これはOKですが、
Sheets("Sheet2").Range("A1:B10").Interior.Color = Sheets("Sheet1").Range("A1:B10").Interior.Color
これですと、全てが同一のColorならコピーされたように見えますが、
セルによってColorが違う場合は、正しくコピーされません。
セルによってColorが違う場合に使えないのでは意味がありませんので、あまり使用する機会は無いと思います。

コピー方法の使い分け

・セルを全て同じ状態でコピーしたい場合は、
コビー元Range.Copy Destination:=コピー先Range

・罫線のように複数のプロパティが関係している場合や、セル範囲で値以外のコピーの場合
コビー元Range.Copy
コピー先Range.PasteSpecial Paste:=xlPasteFormats

・上記以外なら、特に値のコピーなら
コピー先Range.プロパティ = コビー元Range.プロパティ


第44回.VBA関数について

VBA関数の書き方

戻り値を他の変数やセルに代入
変数orセル = 関数名(第1引数, 第2引数, 第3引数, ・・・)
関数の戻り値を他の変数やセルに入れて使います。

関数の戻り値をIf文で判定
If 関数名(第1引数, 第2引数, 第3引数, ・・・) = ○○○ Then
関数の戻り値をIf文等で判定します。

VBA関数のネスト
変数orセル = 関数名(関数名(第1引数, 第2引数, 第3引数, ・・・), 第2引数, 第3引数, ・・・)
関数をネストして使う事も出来ます。
関数の戻り値を、別の関数の引数として指定しています。

関数の戻り値を使わない場合

MsgBox "メッセージ"
このように、戻り値を使用しない事も稀にあります。

関数名 第1引数, 第2引数, 第3引数, ・・・

関数は、ほとんどの場合は戻り値を使うので、通常は、

変数 = 関数名(第1引数, 第2引数, 第3引数, ・・・)

このように、()で引数を囲いますので、戻り値を使わない場合の記述については要注意です。

名前付き引数

関数の引数は、名前付き引数が利用できますが、
引数自体の数が、そんなに多くないので記述を簡略化して省略する事が多いようです。
オブジェクトのメソッドの場合は引数が多いものがあるので、名前付き引数を使うようにします。

○○○B関数

文字列をバイト データとして扱う場合の関数です。
AscB、ChrB、LeftB、LenB、RightB

○○○$関数

文字列型 (String) の値を返します。

Chr$、ChrB$、Command$、CurDir$、Date$、Dir$、Error$、Format$、Hex$、Input$、InputB$、LCase$、Left$、LeftB$、LTrim$、Mid$、MidB$、Oct$、Right$ RightB$ RTrim$、Space$、Str$、String$、Time$、Trim$、UCase$

多くありますが、実際に使用する事は少ないです。

ワークシート関数との関係

VBA関数には、ワークシート関数と(ほぼ)同一名称の関数も多くあります。
従って、同様機能の関数はワークシート関数とVBA関数をセットで覚えるようにすると良いでしょう。
しかし、その引数に違いがあったり、機能の細部が違っていたりするので、ある程度の注意は必要です。

また、WorkSheetFunctionを使う事でワークシート関数も使用できます。


自動メンバ表示

VBEにおいては、引数が自動的に表示(自動メンバ表示)されますので、これを活用して下さい。
関数名を入力すると、

マクロVBA サンプル画像

このように、引数が表示されます。
そして、引数が決められた内容の場合は、そのメンバが自動表示されます。

マクロVBA サンプル画像

上下矢印キーで選択し、TABキーで決定して下さい。

習得すべき関数

VBA関数は、全部で140以上あり(ワークシート関数は400以上ありますが)、
全てを覚えるのは大変ですし、その必要もないでしょう。

マクロを書く上で、どうしても覚える必要のある関数もあります。
日付や文字列操作の関数は、どのような業務においても必須となりますので、必ず習得して下さい。


第45回.VBA関数(Format)

Format関数

第1引数に指定した値を、第2引数以降に指定した書式に変換した文字列の値を返します。
Format(expression[,format[,firstdayofweek[,firstweekofyear]]])

Expression 必須。必ず指定します。
任意の有効な式。
Format 省略可能。
有効な名前を持つ、またはユーザー定義の書式指定式。
FirstDayOfWeek 省略可能。
週の最初の曜日を指定する定数です。
vbUseSystem 0 NLSAPI設定を使用します。
vbSunday 1 日曜日(既定)
vbMonday 2 月曜日
vbTuesday 3 火曜日
vbWednesday 4 水曜日
vbThursday 5 木曜日
vbFriday 6 金曜日
vbSaturday 7 土曜日
FirstWeekOfYear 省略可能。
年内で、最初の週を指定する定数です。
vbUseSystem 0 NLSAPI設定を使用します。
vbFirstJan1 1 1月1日が含まれる週から開始します(既定)。
vbFirstFourDays 2 4日以上が含まれる最初の週が年の第1週目になります。
vbFirstFullWeek 3 1週間全体が含まれる最初の週がその年の第1週目になります。

日付/時刻表示書式指定文字

文字 内容
(:) 時刻の区切り記号です。
オペレーティングシステムの国別情報の設定によっては、時刻の区切り記号として他の記号が使用されることがあります。
時刻を時間、分、および秒で区切ることができます。
変換後の時刻の区切り記号は、コントロールパネルの設定によって決まります。
(/) 日付の区切り記号です。
オペレーティングシステムの国別情報の設定によっては、他の記号が使用されることがあります。
日付を年、月、および日で区切ることができます。
変換後の区切り記号は、コントロールパネルの設定によって決まります。
c dddddおよびtttttの書式で表した日付と時刻を、日付、時刻の順序で返します。
指定された値に小数部がない場合は日付のみ、整数部がない場合は時刻のみを表す文字列を返します。
d 日付を返します。1桁の場合、先頭に0が付きません(1~31)。
dd 日付を返します。1桁の場合、先頭に0が付きます(01~31)。
ddd 曜日を英語(省略形)で返します(Sun~Sat)。
aaa 曜日を日本語(省略形)で返します(日~土)。
dddd 曜日を英語で返します(Sunday~Saturday)。
aaaa 曜日を日本語で返します(日曜日~土曜日)。
ddddd 年、月、日を含む短い形式(コントロールパネルで設定)で表した日付を返します。
既定の短い日付形式は、m/d/yyです。
dddddd 年、月、日を含む長い形式(コントロールパネルで設定)で表した日付を返します。
既定の長い日付形式はmmmmdd,yyyyです。
w 曜日を表す数値を返します(日曜日が1、土曜日が7となります)。
ww その日が一年のうちで何週目に当たるかを表す数値を返します(1~54)。
m 月を表す数値を返します。1桁の場合、先頭に0が付きません(1~12)。
ただし、hやhhの直後にmを指定した場合、月ではなく分と解釈されます。
mm 月を表す数値を返します。1桁の場合、先頭に0が付きます(01~12)。
ただし、hやhhの直後にmmを指定した場合、月ではなく分と解釈されます。
mmm 月の名前を英語(省略形)の文字列に変換して返します(Jan~Dec)。
mmmm 月の名前を英語で返します(January~December)。
oooo 月の名前を日本語で返します(1月~12月)。
q 1年のうちで何番目の四半期に当たるかを表す数値を返します(1~4)。
g 年号の頭文字を返します(M、T、S、H)。
gg 年号の先頭の1文字を漢字で返します(明、大、昭、平)。
ggg 年号を返します(明治、大正、昭和、平成)。
e 年号に基づく和暦の年を返します。1桁の場合、先頭に0が付きません。
ee 年号に基づく和暦の年を2桁の数値を使って返します。
1桁の場合、先頭に0が付きます。
y 1年のうちで何日目に当たるかを数値で返します(1~366)。
yy 西暦の年を下2桁の数値で返します(00~99)。
yyyy 西暦の年を4桁の数値で返します(100~9999)。
h 時間を返します。1桁の場合、先頭に0が付きません(0~23)。
hh 時間を返します。1桁の場合、先頭に0が付きます(00~23)。
n 分を返します。1桁の場合、先頭に0が付きません(0~59)。
nn 分を返します。1桁の場合、先頭に0が付きます(00~59)。
s 秒を返します。1桁の場合、先頭に0が付きません(0~59)。
ss 秒を返します。1桁の場合、先頭に0が付きます(00~59)。
ttttt コントロールパネルで設定されている形式で時刻を返します。
先頭に0を付けるオプションが選択されていて、時刻が午前または午後10時以前の場合、先頭に0が付きます。
既定の形式は、h:mm:ssです。
AM/PM 時刻が正午以前の場合は大文字でAMを返し、正午~午後11時59分の間は大文字でPMを返します。
am/pm 時刻が正午以前の場合は小文字でamを返し、正午~午後11時59分の間は小文字でpmを返します。
A/P 時刻が正午以前の場合は大文字でAを返し、正午~午後11時59分の間は大文字でPを返します。
a/p 時刻が正午以前の場合は小文字でaを返し、正午~午後11時59分の間は小文字でpを返します。
AMPM 12時間制が選択されていて、時刻が正午以前の場合は午前を表すリテラル文字列を、正午~午後11時59分の間は午後を表すリテラル文字列を返します。
これらの文字列の設定および"12時間制"の選択は、コントロールパネルで行います。
AMPMは大文字、小文字のどちらでも指定できます。
既定の形式は、AM/PMです。

数値表示書式指定文字

文字 内容
なし 指定した数値をそのまま返します。
0 桁位置や桁数を指定するときに使います。
引数formatに指定した書式文字列内の表示書式指定文字"0"1つで、数値の1桁を表します。変換対象の数値(式)が、"0"で指定された桁位置を使っている場合は、その桁に該当する値が入ります。
変換対象の数値の桁数が少なく、指定された桁位置に該当する値がない場合は、その桁には0が入ります。
引数expressionに指定した数値の整数部または小数部の桁数が、指定書式内の"0"の桁位置に満たない場合は、その桁位置には0が付加されます。
また、数値の小数部の桁数が小数部に指定した"0"の桁位置を超える場合には、数値の小数部は指定の桁位置に合わせて四捨五入されます。
逆に、整数部の桁数が整数部に指定した"0"の桁位置を超える場合には、整数部は変更されることなく、すべて表示されます。
(#) 桁位置や桁数を指定するときに使います。
引数formatに指定した書式文字列内の表示書式指定文字"#"1つで、数値の1桁を表します。変換対象の数値(expression)が"#"で指定された桁位置を使っている場合は、その桁に該当する値が入ります。
変換対象の数値の桁数が少なく、指定された桁位置に該当する値がない場合は、その桁には何も入りません。
この記号は表示書式指定文字の"0"と同じような働きをしますが、数値の小数部や整数部の桁数が"#"で指定された桁位置に満たない場合に0は挿入されず、その桁には何も入りません。
(.) 表示書式指定文字("0"または"#")と組み合わせて、小数点の位置を指定するときに使います。
表示する桁数を指定するとき、この表示書式指定文字の位置によって、整数部と小数部が区別されます。
指定書式内で"."の左側に"#"だけが指定されている場合は、1未満の数値は小数点記号から始まります。
数値が1未満の場合に小数点記号の左側に常に0が付くようにするには、指定書式内で""の左側に"#"ではなく"0"を指定します。
小数点記号は、オペレーティングシステムの国別情報の設定によって決まります。
(%) 数値を100倍し、パーセント記号(%)を付けるときに指定します。
(,) 1000単位の区切り記号を挿入するときに指定します。
整数部が4桁以上ある数値については、1000単位の区切り記号が付きます。
1000単位の区切り記号は、オペレーティングシステムの国別情報の設定によって決まります。
通常、この表示書式指定文字","の前後に"0"または"#"を指定して使います。

この表示書式指定文字","の右側に"0"も"#"も指定しない場合、つまり、整数部の右端にこの表示書式指定文字","を1つ、または2つ以上続けて指定した場合(小数部の表示指定の有無は任意)、変換対象の数値は1000単位で割った値に変換されます。
このとき、値は桁位置の指定に応じて丸められます。
たとえば、書式指定文字列として"##0,,"と指定すると、数値100000000(1億)は、100に変換されます。100万未満の数値は0となります。
整数部の右端以外でこの表示書式指定文字","を2つ以上続けて指定した場合は、","を1つ指定したときと同じになります。
(:) 時刻の区切り記号を挿入するときに指定します。
時刻を時間、分、秒で区切ることができます。
時刻の区切り記号は、オペレーティングシステムの国別情報の設定によって決まります。
(/) 日付の区切り記号を挿入するときに指定します。
日付を年、月、日で区切ることができます。
区切り記号は、オペレーティングシステムの国別情報の設定によって決まります。
(E-E+e-e+) 指数表記で表すときに指定します。
"E-"、"E+"、"e-"、"e+"のいずれかの右側に"0"または"#"を1つ以上指定すると、数値は指数表記で表され、整数部と指数部の間にeまたはEが挿入されます。
これらの表示書式指定文字の右側に指定する"0"または"#"の数は、指数部の桁数を示します。
"E-"や"e-"を使うと、指数が負の場合にはマイナス記号が付きます。
"E+"や"e+"の場合は、指数の正負に合わせてプラス記号かマイナス記号が付きます。
-+$()スペース 指定する文字をそのまま挿入します。
これら以外の表示書式指定文字を挿入するには、その前に円記号(\)を付けるか、ダブルクォーテーション("")で囲みます。
(\) すぐ後に続く1文字をそのまま表示します。
書式指定の中で、特別な意味を持っている"#"または"E"などの文字を文字としてそのまま表示するには、その文字の前に円記号(\)を付けます。
この円記号(\)は表示されません。
文字をダブルクォーテーション("")で囲んでも、同じです。円記号(\)を挿入するには、円記号(\)を2つ続けて記述します(\\)。
そのままでは挿入できない文字としては、日付や時刻の表示書式指定文字(a、c、d、h、m、n、p、q、s、t、w、y、/、:)、数値の表示書式指定文字(#、0、%、E、e、カンマ、ピリオド)、文字列の表示書式指定文字(@、&、<、>、!)などがあります。
("ABC") ダブルクォーテーション("")で囲まれた文字列は、そのまま挿入されます。
ダブルクォーテーションの文字コードはChr(34) をです。

文字列表示書式指定文字

文字 内容
@ 1つの文字またはスペースを表します。
変換対象expressionの中で@(アットマーク)に対応する位置に文字が存在する場合は、その文字が表示されます。
文字がなければスペースが表示されます。
@は、引数formatに指定した書式の中に表示書式指定文字の!(感嘆符)がない限り、右から左の順に埋められます。
& 1つの文字を表します。
変換対象expressionの中で&(アンパサンド)に対応する位置に文字が存在する場合は、その文字が表示されます。
文字がなければ何も表示せず、詰められて表示されます。
&は、引数formatに指定した書式の中に表示書式指定文字の!(感嘆符)がない限り、右から左の順に埋められます。
< 小文字にします。すべての文字は小文字に変換されます。
> 大文字にします。すべての文字は大文字に変換されます。
! 文字を右から左ではなく、左から右の順に埋めていくように指定します。
この文字を指定しない場合は、右から左の順に埋められます。

※セルに出力する場合

マクロVBAでの結果をセルに出力する場合は、セルの表示形式を設定する必要があります。
セルの表示形式が文字列になっていない場合においては、
数値・日付の場合は、これは意味の無い処理となってしまいます。
セルに値を入れた時点で、セルの表示形式になってしまいます。


第46回.VBA関数(日付,DateAdd)

日付時刻に関するVBA関数の一覧

関数 説明 対応ワークシート関数
Date 現在のシステムの日付の値を返す ワークシート関数のTODAYと同じ
Now 現在の日付と時刻の値を返す ワークシート関数と同じ
Time 現在のシステムの時刻の値を返す  
Timer 午前0時から経過した秒数を単精度浮動小数点数型で返す
1/100まで計測する場合に使用します。
 
Year 年の値を返します ワークシート関数と同じ
Month 1 年の何月かを表す 0~12 の範囲の値を返す ワークシート関数と同じ
Day 月の何日かを表す 1~31 の範囲の値を返す ワークシート関数と同じ
Hour 1 日の時刻を表す 0~23 の範囲の値を返す ワークシート関数と同じ
Minute 時刻の分を表す 0~59 の範囲の値を返す ワークシート関数と同じ
Second 時間の秒を表す 0~59 の範囲の値を返ます ワークシート関数と同じ
Weekday 何曜日であるかの値を返す ワークシート関数と同じ
WeekdayName 指定された曜日を表す文字列を返す  
MonthName 指定された月を表す文字列を返す  
DatePart 日付の指定した部分を含む値を返す  
DateSerial 引数に指定した年、月、日に対応する日付の値を返す ワークシート関数のDATEと同じ
DateValue 日付を表す値を返す ワークシート関数と同じです。
TimeSerial 引数で指定した時、分、秒に対応する時刻の値を返す ワークシート関数のTIMEと同じ
TimeValue 時刻を表す値を返す ワークシート関数と同じです。
DateAdd

指定された時間間隔を加算した日付の値を返す

 
DateDiff 2つの指定した日付の時間間隔の値を返す ワークシート関数のDATEDIFと似てますが大きな違いがあります。
シートのDATEDIF関数は年齢計算に使用されますが、
VBAのDateDiff関数は直接的に年齢計算を行えません。


第47回.VBA関数(文字列操作,Replace,InStr,StrConv)

文字列操作に関するVBA関数の一覧

関数 説明 対応ワークシート関数
Asc,AscB,AscW 指定した文字列内にある先頭の文字の文字コードを返す変換関数です ワークシート関数のCODE,UNICODEと同様ですが、文字コードが違います。
Chr,ChrB,ChrW 指定した文字コードに対応する文字を示す文字列型 (String) の値を返します ワークシート関数のCHAR,UNICHARと同様ですが、文字コードが違います。
LCase アルファベットの大文字を小文字に変換します ワークシート関数のLOWERと同じ
UCase アルファベットの小文字を大文字に変換します ワークシート関数のUPPERと同じ
Len,LenB 指定した文字列の文字数を表す値を返します
LenBはバイト数です
ワークシート関数と同じ
Left,LeftB 文字列の左端から指定した文字数分の文字列を返します
LeftBはバイト数です
ワークシート関数と同じ
Mid,MidB 文字列から指定した文字数分の文字列を返します
MidBはバイト数です
ワークシート関数と同じ、文字数は省略可能
Right,RightB 文字列の右端から指定した文字数分の文字列を返します
RightBはバイト数です
ワークシート関数と同じ
Replace 指定された文字列の一部を、別の文字列で指定された回数分で置換した文字列を返します ワークシート関数のSUBSTITUTEと同じ機能
InStr ある文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す ワークシート関数のFINDと同じ機能
ただし大文字小文字の区別はcompareで指定可能。
InStrRev ある文字列の中から指定された文字列を最後の文字位置から検索を開始し、最初に見つかった文字位置を返す文字列処理関数です  
Space 指定した数のスペースからなる文字列を返す文字列処理関数です  
String 指定した文字コードの示す文字、または文字列の先頭文字を、指定した文字数だけ並べた文字列を返す文字列処理関数です ワークシート関数のREPTと似ているが、REPTが文字列(複数文字)を繰り返すのに対し、Stringは先頭1文字のみ繰り返します。また引数の順序が違います。
Str 数式の値を文字列で表した値 (数字) で返す文字列処理関数です
StrConv 指定した変換方式で変換した文字列をバリアント型 (内部処理形式 String の Variant) で返します  
StrReverse 指定された文字列の文字の並びを逆にした文字列を返します  
Trim 指定した文字列から先頭と末尾の両方のスペースを削除した文字列を表す値を返します ワークシート関数とほぼ同じだが、中間スペースは削除されない
LTrim 指定した文字列から先頭のスペースを削除した文字列を表す値を返します  
RTrim 指定した文字列から末尾のスペース (RTrim)を削除した文字列を表す値を返します  
Val 指定した文字列に含まれる数値を適切なデータ型に変換して返します ワークシート関数のVALUEに近いが、先頭の数値部分のみ取り出す


第48回.VBA関数(その他,Fix,Int,Rnd,Round,IsEmpty)

数学VBA関数の一覧

関数 説明
Fix 指定した数値の整数部分を返します。負の場合、その数値以下の最大の値を返します。
Int 指定した数値の整数部分を返します。負の場合、その数値以上の最小の値を返します。
Rnd 単精度浮動小数点数型 (Single) の乱数を返します。
Round 指定された小数点位置で丸めた数値を返します。

データ型確認のVBA関数一覧

関数 説明
IsArray 変数が配列であるかどうかを調べ、結果をブール型 (Boolean) で返します。
IsDate 式を日付に変換できるかどうかを調べ、結果をブール型 (Boolean) で返します。
IsEmpty 変数が Empty 値かどうかを調べ、結果をブール型 (Boolean) で返します。
IsError 式がエラー値かどうかを調べ、結果をブール型 (Boolean) で返します。
IsMissing プロシージャに省略可能なバリアント型 (Variant) の引数が渡されたかどうかを調べ、結果をブール型 (Boolean) で返します。
IsNull 式に Null 値が含まれているかどうかを調べ、結果をブール型 (Boolean) で返します。
IsNumeric 式が数値として評価できるかどうかを調べ、結果をブール型 (Boolean) で返します。
IsObject 識別子がオブジェクト変数を表しているかどうかを示すブール型 (Boolean) の値を返します。

データ型変換のVBA関数一覧

関数 説明
CBool ブール型 (Boolean)のデータ型に変換します。
CByte バイト型 (Byte)のデータ型に変換します。
CCur 通貨型 (Currency)のデータ型に変換します
CDate 日付型 (Date)のデータ型に変換します。任意の有効な日付
CDbl 倍精度浮動小数点数型 (Double)のデータ型に変換します。
CDec 10進型 (Decimal)のデータ型に変換します
CInt 整数型 (Integer)のデータ型に変換します。
CLng 長整数型 (Long)のデータ型に変換します。
CLngLng LongLong型に変換します。
CLngPtr LongPtr型に変換します。
CSng 単精度浮動小数点数型 (Single)のデータ型に変換します。
CStr 文字列型 (String)のデータ型に変換します。
CVar バリアント型 (Variant)のデータ型に変換します。
CVerr 指定した数値(エラー番号)を、バリアント型のエラー値に変換します。


第49回.Like演算子とワイルドカード

Like演算子

result=stringLikepattern

result 任意の数値変数を指定します。
string 任意の文字列式を指定します。
pattern パターンマッチング規則に従った任意の文字列式を指定します。

文字列式stringと文字列式patternが一致していると、演算結果resultは真(True)になり、
一致していないと、演算結果resultは偽(False)になります。

If string Like pattern Then
  真(True)の場合の処理
EndIf

ワイルドカード、文字リスト、文字範囲などを組み合わせて指定できます。

パターン文字列式(ワイルドカード、文字リスト、文字範囲)

文字パターン 引数stringの中の一致する文字
? 任意の1文字
* 任意の数の文字
# 任意の1文字の数字(0-9) 1バイト(半角)の数字
半角全角どちらの数字にも一致します
[charlist] 文字リストcharlistに指定した文字の中の任意の1文字
[!charlist] 文字リストcharlistに指定した文字以外の任意の1文字

特殊文字の指定
特殊文字の左角かっこ([)、疑問符(?)、数値記号(#)、およびアスタリスク(*)を文字列比較するには、
これらの文字を角かっこで囲み、[*]のように指定します。

文字リスト
ハイフン (-) を使用して範囲の上限と下限を分離することで、charlistで文字の範囲を指定できます。
たとえば、
[A-Z]では、string内の対応する文字位置にAからZの範囲内の大文字が含まれる場合に一致します。

Like演算子の使用例

データ 文字列式 結果 説明
aBBBa a*a TRUE aで始まりaで終わる
F [A-Z] TRUE AからZまでの文字
F [!A-Z] FALSE AからZ以外の文字
a2a a#a TRUE a 数字1桁 a
aM5b a[L-P]#[!c-e] TRUE a LからP 数字1桁 cからe
BAT123khg B?T* TRUE B 任意1文字 T 何でも良い
CAT123khg B?T* FALSE B 任意1文字 T 何でも良い
ab a*b TRUE a 何でも良い b
a*b a[*]b TRUE a *1文字 b
axxb a[*]b FALSE a *1文字 b
a[xyz a[[]* TRUE a [1文字 何でも良い

正規表現について

一般に言われる、正規表現は使用できません。
マクロVBAで正規表現を扱う為には、VBScriptを使う事になります。
VBAで正規表現を利用する(RegExp)
・メタ文字 ・正規表現 ・正規表現RegExpの使い方 ・RegExpオブジェクト ・RegExpの使用例 ・RegExp関連のオブジェクト ・Execute(Matches,Match,SubMatches)の使用例 ・Replaceの使用例 ・先読み:肯定先読み、否定先読み ・正規表現の実践例


第87回.WorksheetFunction(ワークシート関数を使う)

ワークシート関数の使い方

[Application.]WorksheetFunction.関数名(引数・・・)

Application.は省略できます。

Range("B1") = WorksheetFunction.CountA(Columns(1))

引数は、ワークシート上での関数入力と同一となります。

WorksheetFunctionで使用できる関数

VBA関数として同等機能の関数が存在するものは使用できません
Left、Mid、Right、Year、Month、Day・・・等々は使えません。
文字列操作、日付/時刻、これらの関数はほとんど使えません

OFFSET関数も使えませんが、
VBAでは関数ではなく、セル(Rangeオブジェクト)のOffsetプロパティが使えます。
・Offsetプロパティの構文 ・Offsetの使用例 ・Offsetの注意点 ・Offsetのまとめ

データベース関数は使用できません
DGET、DSUM等、データベース関数は使えません。

VBAでは、繰り返し処理等の他の方法で実装することが基本です。

最近のバージョンで追加されたピリオド付きの関数の関数名
CEILING.MATH
このように、
○○○.△△△
.ピリオドでつながった関数は、
○○○_△△△
このように、.ピリオドが_アンダーバーに変換されています。

WorksheetFunctionで使える関数を確認する方法
VBEで、
WorksheetFunction.
ピリオドまで入力した時に候補表示されます。

個別の関数の使い方

WorksheetFunctionの個別の関数の使い方は、ワークシート関数と同様になります。
ただし、VBEでは、
引数名が、Arg1, Arg2, ・・・
これだけしか表示されません。
引数が不明の場合は、ワークシートで関数を入力して確認してください。

関数の結果(戻り値)

ワークシート関数と同一になります。
ただし、
ワークシート関数の場合に、結果が「#N/A」のようなエラー値になる場合は、
マクロの実行がエラーとなりストップしてしまいます。
エラーになる代表的なものとして、Vlookupで検索値が検索範囲に無い場合になります。

WorksheetFunctionの使用例.

Countif
変数・セル = WorksheetFunction.Countif(範囲,検索条件)
変数・セル = WorksheetFunction.Countif(Range("A:A"), "abc")

Vlookup
変数・セル = WorksheetFunction.VLookup(検査値, 範囲, 列番号, 検索方法)
変数・セル = WorksheetFunction.VLookup(Cells(1, 4), Range("A:C"), 3, 0)

Match
変数・セル = WorksheetFunction.Match(検査値, 範囲, 照合の種類)
変数・セル = WorksheetFunction.Match(Cells(1, 4), Range("A:A"), 0)

検索系の関数での日付の扱い

検査値にセルを指定する場合、.Valueを指定しない事を基本として覚えておいてください。

Cells(1, 4)に日付が入っているとして、
変数・セル = WorksheetFunction.VLookup(Cells(1, 4).Value, Range("A:C"), 3, 0)
このように.Valueを指定した場合、日付としては正しく検索されません。

変数・セル = WorksheetFunction.VLookup(Cells(1, 4).Value2, Range("A:C"), 3, 0)
このように、数値で検索することも可能ですが、.Valueを付けないほうが簡単で確実です。

WorksheetFunctionのエラー対処

WorksheetFunction.VLookupにおいて検索値が無い場合はエラーとなります。
マクロが停止しないようにするための対処が必要になります。

対応方法1.On Error Resume Next
On Error Resume Next
変数 = WorksheetFunction.VLookup(Range("D1"), Range("A:B"), 2, 0)
If Err.Number <> 0 Then
  変数 = "なし"
  Err.Clear
End If

対応方法2.別の関数で確認
件数 = WorksheetFunction.CountIf(Range("A:B"), Range("D1"))
If 件数 > 0 Then
  変数 = WorksheetFunction.VLookup(Range("D1"), Range("A:B"), 2, 0)
Else
  変数 = "なし"
End If

対応方法3.WorksheetFunctionではなくApplicationを使う
変数 = Application.VLookup(Range("D1"), Range("A:B"), 2, 0)

Applicationに続けて関数を指定すると、エラーでVBAが停止することは無くなります。
変数には「エラー 2042」が入ります、セルであれば「#N/A」となります。


第51回.Withステートメント

Withに指定したオブジェクトに対してオブジェクト名を再度記述することなく、プロパティやメソッドを記述することができます。
同じオブジェクトに対して様々な処理を行う時に、Withで1度だけ書いて、その後はオブジェクトを省略して書くことができます。


Withの構文

With object
  [statements]
  ・・・
End With

With~End Withの間では、
ピリオドから書き始めることでオブシェクト名を省略した書き方ができます。

Withを使った時と使わない時の比較

Withステートメントを使わない通常の記述では、

オブジェクト.プロパティ = 値
オブジェクト.メソッド

このように記述するところを、

With オブジェクト
  .プロパティ = 値
  .メソッド
End With

このようにオブジェクトの記述を省略し、.から書き始めることが出来るということです。

Withの使用例

Worksheets(1).Cells(1, 1) = 1
Worksheets(1).Cells(2, 1) = 2


これを、Withを使って書くと、

With Worksheets(1)
  .Cells(1, 1) = 1
  .Cells(2, 1) = 2
End With


このようになります。
With~End Withの間では、
.で書き始めれば、.の前のWithのオブジェクトが省略できることになります。

Range("A1").Font.Bold = True
Range("A1").Font.Color = vbRed
Range("A1").Font.Size = 12


これを、Range("A1") をWithに指定して書くと、

With Range("A1")
  .Font.Bold = True
  .Font.Color = vbRed
  .Font.Size = 12
End With


さらに、Range("A1").Font までをWithに指定して書くと、

With Range("A1").Font
  .Bold = True
  .Color = vbRed
  .Size = 12
End With


Withのネスト

With Range("A1")
  With .Font
    .Bold = True
    .Color = vbRed
    .Size = 12
  End With
End With


Withがネストされている場合に、.の前の省略されているオブジェクトは、
直前(そのステートメントが含まれる最も内側)のWithステートメントに指定したオブジェクトになります。

Withを使ったときに気を付けるべき書き方

Sub sample1()
  With Worksheets(1)
    Debug.Print .Name
    Worksheets.Add Before:=Worksheets(1)
    Debug.Print .Name
  End With
End Sub

イミディエイト ウインドウには、もともと先頭にあったシートのシート名が2回出力されます。
Withの時点で捕まえたオブジェクトは、Wnd Withまで保持され、Withの中では常に同じワークシートを参照します。
つまり、
WithでWorksheets(1)と書かれていても、必ずしも1番目のシートを参照しているとは限らないという事です。

Withの中で、
Withで指定したオブジェクトの位置をずらすようなVBAコードは書いてはいけません。
このようなコードを書いてしまうと、後々判読不能なVBAとなってしまいます。

Withの使いどころ

VBAを書く時は、まずは主に扱うシートをWith指定して、

With ワークシート
  ・・・
End With

習い始めにおいては、このような形を意識して書き始めてみると良いでしょう。


第52回.オブジェクト変数とSetステートメント

オブジェクト変数

変数のデータ型
Object ・・・ オブジェクト型

Object型変数は、オブジェクトなら何でも入れられるデータ型になります。
総称オブジェクト型とも言います。

しかし、さらに固有のオブジェクトに対応したデータ型もあります。

Workbook
Worksheet
Range

その他にも、
Font
これは、Fontオブジェクトのデータ型になります。

オブジェクト型は沢山ありますが、良く使うものはそんなに多くありません。
とりあえず最低限として、Workbook、Worksheet、Range、この3つだけは覚えておいてください。

Variant(バリアント型) > Object(総称オブジェクト型) > 固有オブジェクト型

Variantは、何でも入る
Objectは、オブジェクトなら何でも入る
個別のオブジェクト型は、そのオブジェクトのみ

Setステートメント

オブジェクト変数を使うには、Setステートメントが必要になります
オブジェクトへの参照を変数に代入します。

オブジェクトへの参照、それはつまり、オブジェクトのアドレスを変数に入れるのです。
オブジェクトは単なる値ではなく、プロパティやメソッドを複数含んだ集合体になります。
オブジェクトの実態を変数に入れていたのでは大変です。
そこで、オブジェクトのある場所(アドレス)を変数に入れておくという事です。
アドレスの入った変数を見ればオブジェクトのある場所が分かり、
結果としてオブジェクトにアクセスできるという仕組みです。

Set オブジェクト変数 = オブジェクト

Setステートメントの使用例

Dim ws As Worksheet
Set ws = Worksheets("シート名")
ws.Cells(1, 1) = 1

これは、

Worksheets("シート名").Cells(1, 1) = 1

これと同じ事になります。
Set ws = Worksheets("シート名")
これ以降は、
Worksheets("シート名")別名として、wsという名称を使えると考えてもらって結構です。

Worksheets("シート名").Range("A1").Font.Bold = True
Worksheets("シート名").Range("A1").Font.Color = vbRed
Worksheets("シート名").Range("A1").Font.Size = 12


これは、

Dim MyRange as Range
Set MyRange = Worksheets("シート名").Range("A1")
MyRange.Font.Bold = True
MyRange.Font.Color = vbRed
MyRange.Font.Size = 12

WithとSetの使い分け方

Setステートメントは、使い方としてはWithステートメントと似ている部分があります。
どちらも、記述の簡略化になります、
そして、なにより、処理速度も速くなるのです。
ワークシートは、プロシージャーの先頭でオブジェクト変数に入れてから使う事をお勧めします。
さらに、Withと組み合わせることで、可読性・保守性の高いマクロVBAにすることができます。

Setステートメントの実践的な使い方

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

With ws1
  .Cells(1, 1) = ws2.Cells(1, 1)
  ・・・
  .Cells(1, 2) = ws3.Cells(1, 1)
  ・・・
End With

上記の考え方は、
扱うワークシートは、とりあえず全てオブジェクト変数に入れる
主なワークシートは、Withにする

Is演算子によるオブジェクトの比較

Is演算子は、2つのオブジェクト参照変数を比較するために使用されます。

If オブジェクト1 Is オブジェクト2 Then

Object1とobject2の両方が同じオブジェクトを参照する場合にはTrueになります。
オブジェクト変数(As Object または、As 固有オブジェクト型)の初期値はNothingです。

Dim 変数A As Worksheet
Debug.Print 変数A Is Nothing '→ True
Set 変数A = Worksheets(1)

Dim 変数B As Worksheet
Set 変数B = 変数A

Dim 変数C As Worksheet
Set 変数C = Worksheets(1)

Debug.Print 変数A Is 変数B '→ True
Debug.Print 変数A Is 変数C '→ True

Set 変数C = Worksheets(2)
Debug.Print 変数A Is 変数C '→ False

Rangeブジェクトの場合は注意が必要です。
Dim 変数A As Range
Set 変数A = Range("A1")

Dim 変数B As Range
Set 変数B = 変数A

Dim 変数C As Range
Set 変数C = Range("A1")

Debug.Print 変数A Is 変数B '→ True
Debug.Print 変数A Is 変数C '→ False

Rangeオブジェクトでは、上記VBAの最後がFalse判定になる点に注意してください。
同じセルを参照しているかの判定には、
.Address(External:=True)
この値(文字列)を比較してください。


第53回.Workbookオブジェクト

WorkBookの指定方法

Workbooks("ブック名")
ブック名で指定されるブックです。

Workbooks(インデックス)
ブックを開いた順番の数値で指定しますが、これは使う事はほぼないでしょう。

ActiveWorkbook
アクティブなブック、これは常に一つだけです。

ThisWorkbook
動作しているマクロが書いてあるブックです。

WorkBookのデータ型

データ型は、
Workbook

Dim 変数 As Workbook

気を付けてもらいたいのは、
Workbooks
ではないと言う事です。
データ型のWorkbookは複数形ではないので、最後のsは付きません。

Workbooksは、Workbookの集まりで、コレクションと言います。
Workbooksコレクションの中から、特定のブックを指定したものが、Workbookオブジェクトになります。

WorkBookのプロパティとメソッド

WorkBookオブジェクトに含まれるプロパティ・メソッドは、非常にたくさんあります。
WorkBookのプロパティ、メソッド、イベントの一覧
・WorkBookオブジェクトのプロパティ一覧 ・WorkBookオブジェクトのメソッド一覧 ・WorkBookオブジェクトのイベント一覧
良く使うものを以下に紹介します。

プロパティ Name 名前
Names 名前定義
Path 保存パス
FullName 保存パス+名前
Saved 最後の保存から変更されたかを取得・設定
ReadOnly 読み取り専用かを取得・設定
Sheets 全てのシート
Worksheets 全てのワークシート
メソッド ActiVate アクティブにします
Close 閉じる
Save 上書き保存
SaveAs 名前を付けて保存
SaveCopyAs コピーを保存
PrintOut 印刷
PrintPreview 印刷プレビュー
Protect 保護
Unprotect 保護を解除

Workbookオブジェクトの使用例

Dim wb As Workbook
Set wb = Workbooks("Book1.xlsx")
MsgBox wb.Name

メッセージボックスには、「Book1.xlsx」と表示されます。
Workbookの集まりWorkbooksコレクションの中から"Book1.xls"を指定することでWorkbookオブジェクトを取得し、
その参照を、オブジェクト変数wbに入れています。
そして、Nameプロパティで名前を取得してメッセージ出力しています。


第54回.Windowオブジェクト

エクセルのリボンでは「新しいウィンドウ」や「ウィンドウ枠」といった言葉で表現されているものです。
スクロールバー、枠線などの多くのワークシート関連の見た目は、実際にはウィンドウのプロパティです。


Windowの指定方法

Windows("ウィンドウ名")
ウィンドウ名で指定されるウィンドウ

Windows(インデックス)
インデックス(ウィンドウの順番)で指定されるウィンドウ

ActiveWindow
アクティブ ウィンドウ

ウィンドウ名は、ワークブックに1つだけウィンドウが存在している場合は、
ウィンドウ名 = ワークブック名
複数ウィンドウが存在している場合は、
ウィンドウ名 = ワークブック名:ウィンドウ番号

Windowオブジェクトデータ型

Dim 変数 As Window

ただし、Windowオブジェクトを変数に入れて使うようなマクロVBAを書く機会は少ないでしょう。

Windowオブジェクトのプロパティとメソッド

Windowのプロパティ、メソッドの一覧
エクセルの基本である、Windowオブジェクトのプロパティ、メソッドの一覧です。覚えるというより、必要に応じて調べて使うものになります。Windowオブジェクトのプロパティ一覧 Windowオブジェクトのメソッド一覧 名前 説明 Activate ウィンドウをZオーダーの前面に移動します。
良く使うプロパティとメソッドを紹介します。

プロパティ DisplayGridlines 枠線を表示
DisplayHeadings 行と列の両方の見出しを表示
DisplayHorizontalScrollBar 水平スクロール バー
DisplayVerticalScrollBar 垂直スクロール バー
DisplayWorkbookTabs シート見出しを表示
FreezePanes 分割ウィンドウ枠を固定します
Zoom 表示サイズを、パーセント単位
メソッド Activate アクティブにする
Close 閉じる
SmallScroll 行または列数分だけウィンドウをスクロールします
LargeScroll ページ数分だけウィンドウをスクロールします
NewWindow 新しいウィンドウ、またはウィンドウのコピーを作成

Windowオブジェクトの解説

恐らく、WorkbookオブジェクトやWorkSheetオブジェクトとの違いに戸惑うのではないかと思われます。
上で紹介した、プロパティ・メソッドは、Windowオブジェクトのメンバーであり、
WorkbookオブジェクトやWorkSheetオブジェクトには存在していません。

スクロールバーや枠線は、ブックやシートに設定するものではなく、
Windowオブジェクトに設定するものだということです。
DisplayGridlines ・・・ 枠線を表示

これは、ワークシートでもなければ、ワークブックの情報でもないのです。
従って、「新しいウィンドウを開く」では、その情報は引き継がれません。

Windowオブジェクトの使用例

Dim wd As Window
Set wd = ActiveWindow
With wd
  .DisplayGridlines = False '枠線を非表示
  .DisplayHeadings = False '行と列の両方の見出しを非表示
  .DisplayHorizontalScrollBar = False '水平スクロールバーを非表示
  .DisplayVerticalScrollBar = False '垂直スクロール バーを非表示
  .DisplayWorkbookTabs = False 'シート見出しを表示
  .FreezePanes = False '分割ウィンドウ枠を固定解除
  Range("C3").Select
  .FreezePanes = True 'C3セルでウィンドウ枠を固定
  .Zoom = 75 '表示サイズを75%
End With

アクティブシート以外のWindowの設定

枠線や行列見出しは、どのVBAサンプルを見ても大抵はActiveWindowに対して設定しているはずです。
以下では、シートをアクティブにすることなく表示(Window)に関する設定を行う方法と、設定可能なプロパィを紹介しています。

アクティブシート以外の表示(Window)に関する設定
・Windowオブジェクトのメソッドとプロパティ ・アクティブシート以外の表示(Window)に関する設定 ・アクティブシート以外の表示(Window)を設定するVBA ・全ウィンドウの全シートのWorksheetViewを設定するVBA


第55回.Worksheetオブジェクト

WorkSheetオブジェクトの指定方法

Worksheets(インデックス)
インデックスで指定されるワークシート

Worksheets("シート名")
シート名で指定されるワークシート

Activesheet
アクティブなシート
※Activesheetは、正確にはワークシートオブジェクトではありません、詳細は後述。

Worksheetsコレクションの中から、特定のシートを指定したものが、Worksheetオブジェクトになります。

Worksheetオブジェクトデータ型

Dim 変数 As Worksheet

気を付けてもらいたいのは、
Worksheets
ではないと言う事です。
Worksheetsは、Worksheetの集まりで、コレクションと言います。

WorkSheetのプロパティとメソッド

WorkSheetのプロパティ、メソッド、イベントの一覧
・WorkSheetオブジェクトのプロパティ一覧 ・WorkSheetオブジェクトのメソッド一覧 ・WorkSheetオブジェクトのイベント一覧
良く使うものを紹介します。

プロパティ Name 名前
Names 名前定義
Cells Rangeオブジェクト
Range Rangeオブジェクト
Columns Rangeオブジェクト
Rows Rangeオブジェクト
FilterMode フィルタ モード
Hyperlinks ハイパーリンク
PageSetup ページ設定
ScrollArea スクロールが可能な領域
Shapes すべての図形
Visible 表示するかどうか
メソッド Activate アクティブ
Select 選択
Copy コピー
Move 移動
Paste 貼り付け
PasteSpecial 形式を選択して貼り付け
AutoFilter フィルター
PivotTables ピボットテーブル
PrintOut 印刷
PrintPreview 印刷プレビュー
Protect 保護
Unprotect 保護解除

Worksheetオブジェクトの使用方法

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
MsgBox ws.Name

メッセージボックスには、「Sheet1」と表示されます。
Worksheetの集まりWorksheetsの中から、"Sheet1"を指定し、
その参照を、オブジェクト変数のwsに入れています。

Activesheet、Sheetsコレクションについて

Sheetオブジェクトというものはありません
Sheetsコレクションには、そのブックの全てのシートが入っています。
シートには、ワークシート・グラフ等々の種類がありますので、Activesheetもワークシートとは限りません。

Sheetsコレクションは、ワークシート以外のシートも含まれています。
Activesheetも、ワークシート以外のシートの事もあります。

この為、ActivesheetSheets()で参照されるデータ型Objectになっています。
従って、例えばグラフシートが存在するブックでは、
Sheets.CountとWorksheets.Countの数は一致しません。


第56回.Rangeオブジェクト(RangeとCells)

Rangeオブジェクトを参照するためのプロパティ

Rangeオブジェクトを参照するにはRangeオブジェクトを返すプロパティの戻り値を使います。
下表は、Rangeオブジェクトを参照するためのプロパティです。

オブジェクト プロパティ 説明
Worksheet Range セルまたはセル範囲を表す
Cells セルを表す
Rows 行を表す
Columns 列を表す
Range Range セルまたはセル範囲を表す
Cells セルを表す
Rows 行を表す
Columns 列を表す
EntireRow セル範囲を含む 1 行または複数の行全体
EntireColumn セル範囲を含む 1 列または複数の列全体
Offset オフセットの範囲
Resize サイズを変更
End 領域の終端のセル。Ctrl+方向キーに相当
SpecialCells 指定された条件を満たしているすべてのセル
MergeArea 結合セル範囲を表す
Next 次のセルを表す

Rangeオブジェクトに、Rangeプロパティがあるあたりが、少し理解しづらいかもしれません。

Rangeオブジェクトを返すRangeプロパティ

Worksheet.Range
これは、WorksheetのRangeプロパティであり、このRangeプロパティを通して、Rangeオブジェクトを参照しています。
そして、Rangeオブジェクトにも、Rangeプロパティがあり、同じくRangeオブジェクトを参照できるのです。

ここは、少々難しいと感じるかもしれません。
Rangeプロパティを介してRangeオブジェクトを参照しているのだと、まずはそのままに理解してください。

また、Offset、Resize等、まだ説明していないプロパティについては、このマクロVBA入門シリーズで今後説明していきます。

Rangeオブジェクトのデータ型

Dim 変数 As Range

このように変数定義します。

Rangeの変数定義の使用例
Dim MyRange As Range
Set MyRange = Worksheets(1).Range("B2:D10")
MyRange.Value = "文字列"

これは、以下と同じです。

Worksheets(1).Range("B2:D10").Value = "文字列"

全てのセルを表すCells

シートの全セル

シートの全セルを扱う場合は、

Worksheets(1).Cells

このように、Cellsに引数を指定しないとシートの全セルになります。、
WorksheetオブジェクトのCellsは、シートの全セルを参照することになります。

複数セルを含むRangeオブジェクトの全セル

Rangeオブジェクト.Cells

これは、そのRangeオブジェクト内の全セルを指しています。

Rangeオブジェクトの使用例

Dim MyRange As Range
Set MyRange = Worksheets(1).Range("B2:D10")
MyRange.Cells(3, 2) = "C4セル"

これは、C3セルに文字が入ることになります。
RangeオブジェクトであるMyRangeの先頭セルはB2セルです。
Cells(...)は、Rangeオブジェクト内の行列の位置を引数に指定します。

B2セルがMyRange.Cells(1, 1) になりますので、
MyRange.Cells(3, 2) はB2セルから3行目の2列目
つまりB2セルの2つ下1つ右のC4セルになります。

マクロ VBA RangeとCells

RangeオブジェクトのCells(...)とItem(...)

以下のItemプロパティについては、直ぐに使いこなせなくて構いません。

MyRange.Cells(3, 2)
これは、
MyRange.Item(3, 2)
このようにItemプロパティを使っても同じ指定になります。
さらに、
MyRange.Item(8)
このようにItemに引数を1つだけ指定する書き方もあります。

Itemの引数
2つ指定した場合は行列の指定。
1つだけ指定した場合はセルの順番(列→行)の位置のセルになります。

RangeとCellsの使い分け方

RangeとCellsの使い分け方については、
複数セル範囲と名前定義の場合はRangeを使う、それ以外はCellsを使う。
別の言い方をすれば、
変数を使う時はCells、Rows、Columnsを使用すると言う事です。

VBEの自動メンバ表示(インテリセンス)

VBE(Visual Basic Editot)では、次に入力できるものが自動メンバ表示されます。
この自動メンバ表示の事をインテリセンスと呼びます。
range("A1").
と、「.」を打った時点で、メンバが自動表示されます。

VBA マクロ Range Cells

しかし、
Cells(1, 1)
これでは表示されません。

VBA マクロ Cells Range

このような場合は、
cells.
このように、引数の()を省略するか、
cells().
と、引数を空っぽにするとメンバが自動表示されるようになります。

VBA マクロ Range Cells

これらは先に説明した、シートの全セルを表していることになります。

RowsとColumns

Rowsで取得されるRangeオブジェクトは行の集まりになります。
Columnsで取得されるRangeオブジェクトは列の集まりになります。
これらは、単一セルの集まりではなく、行全体や列全体の集まりになります。

括弧の引数は、Itemプロパティを使った書き方でも同じになります。
Rows(n) → Rows.Item(n)
Columns(n) → Columns.Item(n)

Rangeオブジェクトは難しい

Rangeオブジェクトは、とても奥深く、難しいものです。
いきなり全てを理解することは困難です。
マクロVBAを書くときは、常にRangeオブジェクトを意識して書くようにして下さい。
そうすることで、少しづつ理解が深まっていきます。


第57回.Applicationのプロパティ(マクロ高速化と警告停止等)

Applicationは、Excel全体をあらわすオブジェクトです
WorkbooksもApplicationのプロパティですが、
このようなApplicationを省略できるものは省略して書くことが多いので意識せずに使っているものが多くあります。


Applicationのメソッドは、その使い方が難しいものが多いので、今後、少しずつ個別に紹介します。
Applicationオブジェクトに含まれるプロパティ・メソッドは、非常にたくさんあります。

Applicationのプロパティ、メソッド、イベントの一覧
・Applicationオブジェクトのプロパティ一覧 ・Applicationオブジェクトのメソッド一覧 ・Applicationオブジェクトのイベント一覧

Applicationの主要プロパティ

プロパティ 説明
Calculation 計算方法のモード、自動・手動の切り替え
Caller Visual Basic を呼び出した方法についての情報
Cursor ポインターの形状を設定
Dialogs すべての組み込みダイアログ ボックス
DisplayAlerts マクロの実行中に特定の警告やメッセージの表示を制御
EnableEvents 指定されたオブジェクトに対してイベントの発生を制御
FileDialog ファイル ダイアログ
Interactive キーボードやマウスからの入力を受け付けるかどうか制御
ScreenUpdating マクロの実行中に画面表示の更新を制御
StatusBar ステータス バーの文字列を設定

ScreenUpdating(マクロVBAの高速化)

マクロVBAの実行中に画面描画の更新を止めて、マクロが高速に処理されるようにします。

Application.ScreenUpdating = False

マクロ実行中の画面描画が更新されないので、
その画面更新にかかる時間が不要になり、それだけ早く処理が終了します。

マクロが終了すると、自動的に画面表示が更新されますが、マクロの最期で、
Application.ScreenUpdating = True
このように明示的に書いておく方が良いでしょう。

DisplayAlerts(警告停止)

マクロVBAの実行中に特定の警告やメッセージが表示される事があります。
例えば、シート削除するときは、確認メッセージが表示されます。
この応答が出ると、マクロはそこで応答待ちになり、ボタンのクリックが必要になります。
応答が必要なメッセージの表示を止める為に使用します。

Application.DisplayAlerts = False

これで応答メッセージは表示されず、マクロは自動的に次に進むようになります。
マクロが終了すると、自動的に表示する状態になりますが、
マクロの最期で、
Application.DisplayAlerts = True
このように明示的に書いておく方が良いでしょう。

Interactive(ユーザー操作の禁止)

キーボードやマウスからの入力の受け付けを停止できます。

Application.Interactive = False

とすると、Falseの間はキーボードやマウスの入力を受け付けなくなります。
ただし、マクロの終了で、自動的には戻りません。

必ず
Application.Interactive = True
として下さい。
忘れると、マクロ終了後もキーボード操作が効かなくなってしまいます

Calculation(計算方法)

計算モードを変更します。

設定値はXlCalculation列挙です。

xlCalculationAutomatic 自動
xlCalculationManual 手動
xlCalculationSemiautomatic データテーブル以外の自動

Application.Calculation = xlCalculationManual

これ以降、自動計算は行われなくなります。
この設定は、マクロ終了後も引き続き有効となりますので、

Application.Calculation = xlCalculationAutomatic

通常は自動計算に戻しておけば良いでしょう。
もし、マクロ実行前の状態に戻す必要がある場合は、
事前に変数に退避しておいて、その変数を戻すようにしてください。

Dim appCalc As XlCalculation '型はVariantでも構いません。
appCalc = Application.Calculation
Application.Calculation = xlCalculationManual
'各種処理・・・
Application.Calculation = appCalc

StatusBar

ステータス バーに文字列を表示します。

Application.StatusBar = "文字列"

高速にVBAが動作している最中は画面の再描画は必ず行われるわけではありません。
適宜、DoEvents をいれて再描画されるようにしてください。

Application.StatusBar = "実行中"
DoEvents

ステータス バーの文字列を消し既定値に戻すには、

Application.StatusBar = False
または、
Application.StatusBar = vbNullString

Cursor

マウスポインターの形状を設定します。
マクロVBA実行中にカーソルが頻繁にちらつくような場合は、この設定により処理速度が向上します。

設定値
xlDefault 標準のポインター
xlIBeam I 字型ポインター
xlNorthwestArrow 矢印型ポインター
xlWait 砂時計型ポインター

Application.Cursor = xlWait ← この時点でウエイトカーソルになります。
Application.Cursor = xlDefault ← 標準のカーソルに戻ります。

マクロが終了しても元に戻りませんので、必ず標準(xlDefault)に戻しておきましょう。

その他

上記以外では、
Dialogs
・Application.Dialogsコレクション ・Application.Dialogオブジェクト ・印刷ダイアログの使用例 ・組み込みダイアログの最後に
FileDialog
・FileDialogオブジェクト ・FileDialogの実践例 ・FileDialogの最後に
Caller
メニューのシートを作成して、ボタンを配置、そしてボタンにより指定シートに移動する。よくありますが、ボタン一つずつに別々(移動先のシート毎に)のマクロを作成するのは面倒です、そこで、一つのマクロで済ませる方法の紹介です。Application.Caller Application.Callerは、VBAを呼び出した方…
EnableEvents
・マクロVBA開始時 ・マクロVBA終了時 ・Applicationのプロパティ解説 ・マクロが途中で終了した場合


第58回.コレクションとは(Collection)

同種のオブジェクトを複数まとめたものを「コレクション」と呼びます


Workbookオブジェクトが複数まとまったものは「Workbooksコレクション」
Worksheetオブジェクトが複数まとまったものは「Worksheetsコレクション」
オブジェクト名が単数形であるのに対し、
コレクション名が複数形であることがポイントです。
多くの場合、○○○オブジェクトに対応する、○○○sコレクションがあります。

コレクションの中から単一オブジェクトを指定する場合

コレクション.Item(インデックス)
コレクション.Item(オブジェクト名)

普通は、.Itemを省略して、

コレクション(インデックス)
コレクション("オブジェクト名")

具体的には、

Workbooks("Book1.xls")
Worksheets("Sheet1")
Worksheets(1)

セルであるRangeオブジェクトのコレクションは?

セルであるRangeオブジェクトのコレクションは、どうなっているのでしょうか。
少なくとも、Rangesコレクションというものはありません。

セル(単一セル)のコレクションが、Rangeオブジェクトなのです。
つまり、単一セルを表すオブジェクトが存在していないのです。
Cellsが複数形で、Cellオブジェクトのコレクションのような感じを受けるにもかかわらず、
Cellオブジェクトなんて存在していない事が、余計に混乱を招いているのかもしれません。

Rangeオブジェクトだけが特別なものだと考えてください。
Rangeは、オブジェクトでもあり、コレクションでもあります

コレクションの要素数

コレクションの要素数、つまりコレクションに入っているオブジェクトの数を取得するには、
コレクション.Count

コレクション(1)
コレクション(2)
・・・
コレクション(コレクション.Count)

これらは単一のオブジェクトであり、その集合がコレクションとなっています。
コレクションは、コレクション.Count個のオブジェクトの集まりだという事です。

ワークシートの場合なら、
例えば、「Sheet1」「Sheet2」「Sheet3」の3つのシートがある場合
Worksheets.Count → 3
Worksheets(1) → Sheet1
Worksheets(2) → Sheet2
Worksheets(3) → Sheet3

Collectionオブジェクト

上記までのコレクションの説明は、VBAとしてのコレクションと言う用語の説明です。
VBAには、独自のコレクションを作成するためのオブジェクトが用意されています。
それがCollectionオブジェクトになります。

Collectionオブジェクトは、1つのオブジェクトとして参照できる複数の要素の集合です。
Collectionオブジェクを利用すると、互いに関連付けられた複数の要素を 1 つのオブジェクトとして参照できます。
文字列、数値、オブジェクトを要素とした独自のコレクション(オブジェクト)を作成できるという事です。

コレクションのメンバーは、同じデータ型を共有する必要はありません。
つまり、データ型の違うデータを1つのコレクションに入れられるという事になります。

Collection オブジェクトのプロパティ
Count コレクション内のオブジェクトの数を含む長整数型 (Long) の値を返します。
値の取得のみ可能です。

Collection オブジェクトのメソッド
Add Collection オブジェクトにメンバを追加します。
Item 指定した位置または文字列に対応する特定の Collection オブジェクトのメンバを返します。
Remove Collection オブジェクトからメンバを削除します。

Addメソッド
object.Add item, key, before, after

object 必ず指定します。
Collectionオブジェクトを指定します。
item 必ず指定します。
追加するメンバを表す式を指定します。
key 省略可能です。
各メンバの位置を表す数値の代わりに使用できる重複しない文字列を指定します。
before 省略可能です。
コレクションの中の相対的な位置を表す式を指定します。
追加対象のメンバは、このbeforeで指定したメンバの前に追加されます。
after 省略可能です。
コレクションの中の相対的な位置を表す式を指定します。
コレクション内のafterで指定したメンバの後ろに追加されます。

引数Keyには重複する値を指定できずエラーとなります。
引数Keyは省略可能ですが、その場合は当然キーによる要素へのアクセスは出来なくなります。

Itemメソッド
指定した位置または文字列に対応する特定の Collectionオブジェクトのメンバを返します。

Removeメソッド
Collectionオブジェクトからメンバを削除します。
object.Remove index

object 必ず指定します。
Collectionオブジェクトを指定します。
index 必ず指定します。
削除するメンバの位置を式で指定します。
数式を使って指定する場合、1~Countプロパティの値までの範囲の整数を指定します。
文字列式を使って指定する場合、メンバをコレクションに追加したときに指定したkeyと一致する文字列を指定します。

Collectionの使用例

Dim colls As New Collection
'または、以下のように、DimとSetを使う
'Dim colls As Collection
'Set colls = New Collection

With colls
  .Add Item:="アイテム1", Key:="key1"
  .Add Item:="アイテム2", Key:="key2"
  .Add Item:="アイテム3", Key:="key3"
  .Remove ("key3")
  .Add Item:="アイテム4", Key:="key4"
End With

MsgBox colls.Count '3と表示
MsgBox colls(1) 'アイテム1と表示
MsgBox colls.Item(2) 'アイテム2と表示
MsgBox colls.Item(3) 'アイテム4と表示
MsgBox colls("key4") 'アイテム4と表示

Collectionに対して以下のように処理すると、
後ろの要素に行くにしたがってより処理時間がかかってしまうようになります。

Dim i As Long
For i = 1 To colls.Count
  Debug.Print colls(1)
Next

コレクションに対して、インデックス指定してアクセスすると、後ろの要素に行くにしたがってとても遅くなります。
コレクション対して全件処理する場合は、次回説明するFor Eachを使って以下のように処理してください。

Dim v As Variant
For Each v In colls
  Debug.Print v
Next


第59回.コレクション処理(ForEach)

For Each は、コレクションの各要素に対して繰り返し処理を実行します。
For Each は、
コレクションの中から、個々のオブジェクトを取り出して処理する場合に使用します。
コレクションの全ての要素に対しての処理が終わるとループは終了します。


For Each の構文

For Each element In group
  [statements]
  [Exit For]
  [statements]

Next [element]

element 必ず指定します。
コレクションの各要素を繰り返す変数を指定します。
引数 element にはバリアント型変数、総称オブジェクト型変数、または固有オブジェクト型のオブジェクト変数を指定できます。
group 必ず指定します。
オブジェクト コレクション名または配列名を指定します。
statements 省略可能です。
引数 group の各メンバに対して実行するステートメントを指定します。

elementのデータ型について
elementに指定するオブジェクトについては、「第52回.オブジェクト変数」で説明した、
Variant(バリアント型) > Object(総称オブジェクト型) > 固有オブジェクト型

固有オブジェクト型が不明な場合は、総称オブジェクト型やバリアント型でも良いです。
コレクション名の最後のsを取れば、多くの場合はそれで固有オブジェクト型になります。

Dim 変数 As ○○○
For Each 変数 In ○○○s
 ・・・
Next 変数


○○○がオブジェクト、○○○sがコレクションです。

Exit For

コレクションの全ての要素に対し処理が終了する前に、ループを抜けます。

For Each 変数 In コレクション
  ・・・
  If 終了すべき条件 Then
    Exit For
  End If
  ・・・
Next

For Each の使用例

Dim ws As Worksheet
For Each ws In Worksheets
  ws.PrintPreview
Next ws

これで、全シートが印刷プレビューされます。
これは、For Eachを使わずに書くと、

Dim i As Integer
For i = 1 To Worksheets.Count
  Worksheets(i).PrintPreview
Next i

.Countはコレクションの要素数です。

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  Debug.Print ws.Name
Next

マクロの書かれているブックのすべてのワークシートをイミィディエイト ウインドウに出力しています。

RangeオブジェクトのFor Each

RangeをFor Eachで処理する場合、少々注意が必要です。

Dim MyRange As Range
Dim i As Long
i = 1
For Each MyRange In Range("A1:B5")
  MyRange = i
  i = i + 1
Next

A1,B1,A2,B2,・・・
このような順に処理されます。
列→行
この順で取得されます。


第60回.エラー処理(OnError)

マクロVBAのエラー発生例

マクロVBA実行において、エラーメッセージが表示される事があります。
マクロの記述の中に、実行不能な事が書かれていてエラーが発生し、VBAが停止してしまう事があります。

しかし、時にはエラーが発生しても処理を続けたいとか、
エラー発生したら、それに対応する為の処理をしたいとか、
そのような事が必要な場合もVBAでは結構あるものです。

エラー処理のステートメント

エラーに対処するステートメントは以下の3つになります。

On Error GoTo 行ラベル
On Error Resume Next
On Error GoTo 0

On Error GoTo 行ラベル

 エラーが発生すると、行ラベルの位置に制御が移ります。
行ラベルは、On Errorステートメントと同じプロシージャ内にある必要があります。

On Error Resume Next

エラーが発生しても、エラーが発生したステートメントの次のステートメントから実行を継続します。

On Error GoTo 0

現在のプロシージャに含まれる使用可能なエラー処理ルーチンを無効にします。
On Error GoTo 0 ステートメントを指定していない場合は、プロシージャの終了時に自動的に無効になります。

On Error の有効範囲

On Error GoTo 行ラベル
On Error Resume Next
これらは、このステートメント以降、
On Error GoTo 0が出てくるか、プロシージャが終了するまでが有効範囲となります。


第61回.「OnError GoTo」と「Exit Sub」

「On Error GoTo 行ラベル」
このステートメントは、実行時エラーが発生した時に制御を指定の行ラベルに移動させるものです。


エラー発生時にも、マクロVBAが停止せずに指定の行ラベルの位置に移動し処理を継続したい場合に、
On Error Go To ステートメントを使用します。

On Error GoTo 行ラベル

On Error GoTo 行ラベル
これが実行された後にエラーが発生すると、行ラベルの位置に制御が移ります。

このステートメント以降、
・On Error GoTo 0 を実行・・・ 同一プロシージャー内で記述されている場合のみ
・プロシージャーの終了
上記いずれかになるまでOn Errorは有効となります。

On Errorが実行された時点で、それ以前のエラー情報は消去さます。

Sub Sample
  On Error GoTo Label01
  '・・・
  'ここでエラー発生した場合はLabel01に移動
  '・・・
  On Error GoTo 0
  '・・・
  'ここでエラー発生した場合はVBA停止します
  '・・・
  Exit Sub
Label01:
  エラー時の処理
End Sub

行ラベル
Label01:
これが、行ラベルです。
ラベル名:
このようにラベル名の後ろに:(コロン)を付けて書きます。

行番号
On Error GoTo 行番号
行ラベルではなく、行番号(数字)を指定することもできますす。

On Error GoTo 10
'・・・
10:

このように数字の後ろに:(コロン)を付けて書きます。

Exit Sub

Subプロシージャーを抜けます。
先に掲示したVBAコードの場合、Exit Subが無いと常にエラー時の処理が必ず実行されてしまいます。

On Error の有効範囲とその動作について

Sub sample1()
  On Error GoTo Label01
  Call sample2
  Call sample3
  Exit Sub
Label01:
  MsgBox "Label01"
End Sub

Sub sample2()
  On Error GoTo Label02
  Dim i As Long
  i = "abc"
  On Error GoTo 0
  Exit Sub
Label02:
  MsgBox "Label02"
End Sub

Sub sample3()
  Dim i As Long
  i = "abc"
  Exit Sub
Label03:
  MsgBox "Label03"
End Sub

sample02には、On Error GoTo Label02、があるのでエラー発生時はLabel02に移ります。
sample03には、On Error がないので、エラー発生した時点でプロシージャーを抜けます。

上記のVBAでsample1を実行した結果は、
「Label02」→「Label01」の順でメッセージ表示されます。

On Error がないプロシージャーでのエラー時の動作
・呼び出し元が無い場合はエラー停止します。
・呼び出し元にOn Error が無い場合はエラー停止します。
・呼び出し元にOn Error がある場合はその時点でプロシージャーを抜けます。


第62回.「OnError Resume Next」とErrオブジェクト

「On Error Resume Next」
このステートメントは、実行時エラーが発生してもマクロVBAを中断せずに、エラーが発生したステートメントの次のステートメントから実行を継続します。


On Error Resume Next

エラーが発生しても、エラーが発生したステートメントの次のステートメントから実行を継続します。
このステートメント以降、On Error GoTo 0 が無い限り、プロシージャーの終わりまで有効となります。

On Errorの有効範囲については、「On Error Go To」と「Exit Sub」を参照してください。

エラー発生の情報は、Errオブジェクトに入っています。
On Errorが実行された時点で、それ以前のエラー情報は消去さます。

Errオブジェクト

Err オブジェクトは、実行時エラーに関する情報を保有しています。

Errオブジェクトのプロパティとメソッド
Err.Clear すべてのプロパティの設定値をクリア
Err.Description エラーに関する簡単な説明
Err.HelpContext ヘルプ ファイルのトピックに対応するコンテキスト番号
Err.HelpFile ヘルプ ファイルへの絶対バス
Err.LastDllError 最後にダイナミック リンク ライブラリ (DLL) を呼び出したときのエラー コード
Err.Number エラーを指定する数値。エラーではない場合は0
Err.Source エラーの発生元のオブジェクトまたはアプリケーションの名前

Errオブジェクトは、プロジェクト内で一つしか存在しません。
つまり、エラーの情報を複数保持しておくことはできません。

On Error Resume Next の使用例

Dim i As Long
On Error Resume Next
i = "ABC"
If Err.Number <> 0 Then
  MsgBox Err.Description
  Err.Clear
End If
MsgBox Err.Number

この場合、メッセージボックスには、
「型が一致しません」と表示されます。
そして、Err.Clearの後のメッセージ表示は「0」となります。

Err.Clearこれでエラー情報をクリアしています。
Err.Clearを実行すると、Errオブジェクトがクリアされエラー情報は消去されます。


第63回.ブックを開く(Open)

Workbooks.Openメソッド

既存ブックを開くには、WorkbooksコレクションOpenメソッドを使います。

Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

名前 説明
FileName 開くブックのファイル名。
UpdateLinks ファイル内の外部参照 (リンク) の更新方法を指定します。
ReadOnly ブックを読み取り専用モードで開くには、True を指定します。
Format テキスト ファイルを開く場合は、この引数で区切り文字を指定します。
Password パスワード保護されたブックを開くのに必要なパスワードを指定します。
WriteResPassword 書き込み保護されたブックに書き込みをするために必要なパスワードを指定します。
IgnoreReadOnlyRecommended [読み取り専用を推奨する] チェック ボックスをオンにして保存されたブックを開くときでも、読み取り専用を推奨するメッセージを非表示にするには、True を指定します。
Origin 開こうとしているファイルがテキスト ファイルの場合、それがどのような形式のテキスト ファイルかを指定します。
Delimiter 開こうとしているファイルがテキスト ファイルで、引数 Format が 6 の場合は、この引数で区切り文字を使用します。
Editable 開こうとしているファイルが Excel 4.0 のアドインの場合、この引数に True を指定すると、アドインがウィンドウとして表示されます。
Notify ファイルが読み取り/書き込みモードで開けない場合に、ファイルを通知リストに追加するには、True を指定します。
Converter ファイルを開くときに最初に使用するファイル コンバーターのインデックス番号を指定します。
AddToMru 最近使用したファイルの一覧にブックを追加するには、True を指定します。既定値は False です。
Local Excel の言語設定 に合わせてファイルを保存するには、True を指定します。
CorruptLoad 開くモードを、XlCorruptLoad列挙で指定します。
抽出(xlExtractData)、正常(xlnormalload)、修復(xlRepairFile)から指定します。
既定の動作はxlNormalLoadになり、回復は行われません。

戻り値
開いたブックのWorkbookオブジェクトになります。

主要な太字の引数のみ説明します。

FileName
通常は、エクセルファイルをフルパスで指定します。

FileName:="C:\User\sample.xls"

パスが指定されていないと、カレントディレクトリーからになりますが、フルパスで指定するようにして下さい。

マクロを書いているブックと同一フォルダなら、
FileName:=Thisworkbook.Path & "\sample.xls"
Thisworkbook.Path、これでブック自身のパスが取得できます

UpdateLinks
外部参照 (リンク) のあるブックを開こうとすると、リンクの更新方法を確認するメッセージがユーザーに表示されます。

0 : 外部参照 (リンク) は更新されません。
3 : 外部参照 (リンク) が更新されます。

(1,2の指定もありますが、特に使う事はないでしょう)

UpdateLinks:=0
これで、リンクが更新されずに、ブックが開かれます。
0Falseと記す事もできます、VBAではFalse=0です。

ReadOnly

ReadOnly:=True
これで、読み取り専用で開かれます。

Password、WriteResPassword
パスワード保護されているブックを開く場合には、
Password:="読取りパスワード"
WriteResPassword:="書込みパスワード"
このようにすることで、パスワード入力のダイアメグが出なくなります。

CorruptLoad
開くモードを、XlCorruptLoad列挙で指定します。
エラーのあるエクセルファイルを開くと、
「'○○'の一部の内容に問題が見つかりました。・・・」とメッセージが出ます。
「はい」をクリックすると、
修復されたメッセージが表示され、「閉じる」でブックを開くことができます。
しかし、
このファイルをVBAでOpenすると、マクロがエラー停止してしまいます。

手動で開く時と同様に、
回復して開く場合はCorruptLoadにxlRepairFileを指定します。

さらに、修復されたメッセージを止めるために、
Application.DisplayAlerts = False
これも同時に指定してください。

Application.DisplayAlerts = False
Workbooks.Open Filename:=ファイルパス, CorruptLoad:=xlRepairFile

Openメソッドの使用例

Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Test\sample.xls", UpdateLinks:=0, ReadOnly:=True, CorruptLoad:=xlRepairFile

・外部参照 (リンク) は更新しない
・読み取り専用
・修復モード
このように、引数が複数になる場合は、名前付き引数を使用した方が良いでしょう。

Openメソッドの戻り値の使い方

Setステートメント

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\User\sample.xls")

戻り値が、Workbookオブジェクトなので、同時にSetステートメントでオブジェクト変数に入れています。
これは、
Workbooks.Open(Filename:="C:\User\sample.xls")
これと
Set wb = Activeworkbook
または、
Set wb = Workbooks("sample.xls")
これらを1行で指定している事と同じ意味になります。

Withステートメント

With Workbooks.Open(Filename:=str)
  '・・・
.  Close SaveChanges:=False
End With

このように、Withで戻り値のブックオブジェクトを受け取る事が出来ます。
開いたブックをVBA内で使用する場所が限定されている場合は、簡単になります。

Workbooks.Addメソッド

既存ブックを開くのではなく、新規にブックを追加する場合には、WorkbooksコレクションAddメソッドを使います。

Workbooks.Add(Template)

戻り値
新しいブックを表すWorkbookオブジェクト。

Template
この引数に既存のExcelファイルを指定すると、指定したファイルをテンプレートとして新しいブックが作成されます。
この引数にXlWBATemplate定数を指定しすると、新しいブックには指定した種類のシートが1つだけ含まれます。
名前 説明
xlWBATChart -4109 グラフ
xlWBATExcel4IntlMacroSheet 4 Excel バージョン 4 のマクロ
xlWBATExcel4MacroSheet 3 Excel バージョン 4 のインターナショナル マクロ
xlWBATWorksheet -4167 ワークシート

この引数を省略すると、空白のシートを含む新しいブックが作成されます


第64回.ブックを閉じる・保存(Close,Save,SaveAs)

ブックを閉じる

Workbook.Close SaveChanges, Filename, RouteWorkbook

SaveChanges ブックに変更がない場合、この引数は無視されます。ブックに変更がある場合、この引数で変更を保存するかどうかを指定します。
Filename 変更後のブックのファイル名。
RouteWorkbook ブックの回覧。

全ての引数を省略
Workbook.Close
とだけ指定した場合は、ブックに変更が無ければそのまま閉じられ、
ブックに変更がある場合は、保存確認のメッセージが表示されます。

Application.DisplayAlerts = False
これが指定されている場合は、確認のメッセージが表示されずに保存もされません。

SaveChanges
ブックに変更がある場合に、保存するかどうかを指定します。
True:ブックに変更がある場合は保存します。
False:ブックに変更があっても保存せずに閉じます。

保存しないで閉じる
Workbook.Close SaveChanges:=False

保存して閉じる
Workbook.Close SaveChanges:=True

ブックを上書き保存

ブックを上書き保存するには、WorkbookのSaveメソッドを使用します。

Workbook.Save

ブックに名前を付けて保存

Workbook.SaveAs FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local

Filename 保存するファイルの名前を表す文字列を指定します。
FileFormat ファイルを保存するときのファイル形式を指定します。
Password ファイルを保護するためのパスワードを表す15 文字以内の文字列を指定します。
WriteResPassword ファイルの書き込みパスワードを表す文字列を指定します。
ReadOnlyRecommended 読み取り専用で開くことを推奨するメッセージを表示するには、True を指定します。
CreateBackup バックアップ ファイルを作成するには、True を指定します。
AccessMode ブックのアクセス モードを指定します。
ConflictResolution ブックを保存するときの競合の解決方法を指定します。
AddToMru 最近使用したファイルの一覧にブックを追加するにはTrueを指定。既定値はFalseです。
TextCodepage 使用しない。
TextVisualLayout 使用しない。
Local 通常は使用しない。
※csv出力時
 日付がm/d/yyyyになってしまう場合に、yyyy/m/dにする場合にはTrueを指定

全ての引数がオプション(任意の指定)となっていますが、通常は、FileNameは必ず指定します。

FileName
フルパスで指定して下さい。
パスを指定しない場合は、カレントフォルダーに保存されます。

FileFormat
xlExcel8 : Excel2007以降の、xlsxまたはxlsm
xlExcel9795 : Excel2003以前のxls

Excel2003以前では、xlExcel8、この定数は使用できません、コンパイルエラーとなります。

SaveAsメソッドの使用例
Thisworkbook.SaveAs "C:\User\sample.xls"

ブックのコピーを保存

ブックのコピー(別ファイルとして)を保存するには、WorkbookのSaveCopyAsメソッドを使用します。

Workbook.SaveCopyAs Filename

ブックのコピーを別ファイルとして保存します。
メモリ上のブック(開かれているブック)に対しては、変更は行われません。
バックアップの作成等で使用します。

ブックを閉じる・保存の実践例

他のブックが開かれている場合は、自身のブックを閉じ、
他のブックが開かれていない場合は、Excelを終了する場合。

Sub 保存して終了()
  If Workbooks.Count > 1 Then
    ThisWorkbook.Close SaveChanges:=True
  Else
    ThisWorkbook.Save
    Application.Quit
  End If
End Sub

Sub 保存しないで終了()
  If Workbooks.Count > 1 Then
    ThisWorkbook.Close SaveChanges:=False
  Else
    ThisWorkbook.Saved = True
    Application.Quit
  End If
End Sub

Application.Quit
Applicationオブジェクトのメソッドで、Excelを終了します。

ThisWorkbook.Saved = True
.Savedプロパティには、ブックが保存後に変更されたかの情報が入っていて、値の設定も可能です。
.SavedをTrueにすることで保存後に変更が無い状態にすることが出来ます。


第65回.シートの挿入、名前の変更(Add,Name)

シートの挿入

VBAでシートを挿入するには、Sheets.AddメソッドWorksheets.Addメソッドを使います。

Sheets.Add Before, After, Count, Type
Worksheets.Add Before, After, Count, Type

Before 指定したシートの直前に、新しい シートを追加します。
After 指定したシートの直後に、新しいシートを追加します。
Count 追加するシートの数を指定します。
既定値は 1 です。
Type ワークシートの種類を指定します。
既定値は xlWorksheet です。
Worksheets.Addの場合は、xlWorksheet以外は指定できません。

戻り値
挿入された、ワークシート、グラフ シート、またはマクロ シートを表すオブジェクト型 (Object) の値

Before、After
ともに省略すると、アクティブシートの直前に挿入されます。

Count
追加するシートの数を指定します。
既定値は選択したシートの数です。
複数シートを同時に追加した場合、戻り値は最後に追加されたシートのオブジェクトになります。

Type
ワークシートの種類を指定します。

xlChart グラフ
xlDialogSheet ダイアログ シート
xlExcel4IntlMacroSheet Excel バージョン 4 International Macro シート
xlExcel4MacroSheet Excel バージョン 4 マクロ シート
xlWorksheet ワークシート

既定値は xlWorksheet 、つまりワークシートです。
Worksheets.Addの場合は、xlWorksheet以外を指定するとエラーになります。

Addメソッドの使用例.
Sheets.Add
アクティブシートの直前にワークシートが追加されます。

Sheets.Add After:=Sheets(Sheets.Count)
シートの最後にワークシートが追加されます。

シートの名前変更

VBAでシート名を変更するには、
シートのオブジェクト.Nameプロパティ
このプロパティの値を変更します。

シートのオブジェクト.Name = "名前"

シート挿入、名前変更の実践例

Dim ws As Worksheet
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
ws.Name = "新規シート"

With Sheets.Add(After:=Sheets(Sheets.Count))
  .Name = "新規シート"
End With

シートの最後に、"新規シート"というワークシートを追加しています。
Sheets.Addの戻り値がオブジェクトなので、
前者は、戻り値をそのままオブジェクト変数に入れて使用しています。
後者は、戻り値をWithで受けとって使用しています。


第66回.シートのコピー・移動・削除Copy,Move,Delete)

シートのコピー・移動

シートのコピー
シート.Copy Before, Afte

シートの移動
シート.Move Before, After

Before コピーしたシートを特定のシートの直前の位置に挿入するときに、そのシートを指定します。
ただし、引数 After を指定すると、引数 Before を指定することはできません。
After コピーしたシートを特定のシートの直後の位置に挿入するときに、そのシートを指定します。
ただし、引数 Before を指定すると、引数 After を指定することはできません。

備考
引数 Before と引数 After の両方を省略した場合は、
新規ブックが自動的に作成され、シートはその新規ブック内にコピー・移動されます。

Copyメソッドの使用例
Worksheets(1).Copy

Worksheets(1)だけの、新規ブックが作成されます。

Worksheets(1).Copy After:=Worksheets(Worksheets.Count)

1番目のシートを最後のシートの後ろにコピーしています。

Moveメソッドの使用例
Sheets("シート名").Move After:=Sheets(Sheets.Count)

Sheets("シート名")をシートの最後に移動しています。

シートの削除

シート.Delete

Deleteメソッドの使用例
Worksheets(1).Delete

先頭のシートが削除されます。
シートを削除すると、メッセージが表示され、マクロが中断します。
Application.DisplayAlerts = False
これを入れる事で、このメッセージは出なくなります。

Application.DisplayAlerts = False
Worksheets(1).Delete




同じテーマ「マクロVBA入門」の記事

VBAの基礎・基本の要約
第1回.マクロって何?VBAって何?
第2回.まずはマクロVBAを使う準備
第3回.マクロの記録
第4回.マクロはどこに書くの(VBEの起動)
第5回.VBEのオプション設定(Option Explicit)
第6回.とにかく書いてみよう(Sub,End Sub)
第7回.マクロを実行するには(F5)
第8回.セルに文字を入れるとは(RangeオブジェクトのValueプロパティ)
第9回.Rangeでのセルの指定方法
第10回.Range以外の指定方法(Cells,Rows,Columns)


新着記事NEW ・・・新着記事一覧を見る

ピッボットテーブルって便利だよね|還暦のVBA(2021-10-18)
還暦のVBA:VBAまでたどりつけるか… (2021-09-29)
VLOOKUPを使うことを基本としてシートを設計すべきか|エクセル雑感(2021-08-17)
コンピューターはブラックボックスで良い|エクセル雑感(2021-08-14)
小文字"abc"を大文字"ABC"に変換する方法|エクセル雑感(2021-08-13)
ADOでテキストデータを集計する|VBAサンプル集(2021-08-04)
VBA学習のお勧めコース|エクセル雑感(2021-08-01)
エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.Excelショートカットキー一覧|Excelリファレンス
6.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
9.並べ替え(Sort)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ