カテゴリー: ExcelVBA

ExcelVBAに関する記事

CreateObject関数について

CreateObject関数はExcelVBAとは別の機能を利用するために使用する関数です

'Excelの新規作成
Dim zzExApp As Object
Set zzExApp = CreateObject("Excel.Application")
'新規作成したExcelの表示
zzExApp.Visible = True
'新規作成したExcelの終了と変数の開放
zzExApp.Quit
Set zzExApp = Nothing

CreateObject関数は、アプリのインスタンスを作成する関数です
この作成したインスタンスは必ず変数に代入して使用します
その時に使用するのは、基本的にはObject型を指定します

例コードではExcelを追加で新規起動します
コードのあるExcelとは別のアプリとして起動しますので、Excel関係の設定であったりを使用中のExcelとは別に指定することが出来ます

VisibleプロパティにTrueを指定することで、表示されるようになります
これを行わないとバックグランド実行となり、変数を開放してしまったり処理が強制終了するとVBAから終了させることが不可能となり起動したままになってしまいます

何らかのブックを開いた状態で、そうなってしまうとそのブックが別のユーザーが使用中となり編集が出来なくなります

この場合にはタスクマネージャーからプロセスの終了を行う必要があります

そのため、この関数を使用してアプリを使用する場合はおおよそVisibleプロパティを最初に設定して表示状態にしておく方が無難です
Visibleプロパティに関しては以下の記事で解説しています

Excelを非表示にする

ここまでの話であれば、それほど使用場面は無さそうな関数と感じるかもしれません
ですがこの関数の最大の利用場面は、Excel以外のアプリを使用できることにあります

ExcelVBAの標準機能で使用できるExcelファイル以外のデータが扱えるのはせいぜいテキストデータ程度です
しかし、この関数を使用すると同じOfficeソフトであるWord・PowerPoint・Outlook・AccessそれぞれのアプリやファイルをExcelVBAから操作することもできます
またInternetExplorerの操作によるWeb操作も可能になります

それ以外にもFileSystemObjectやDictionaryの利用時にも使用します

VBAをやり込んでいけば、必ずVBAだけで実現できない場面に遭遇します
その時にVBAの限界と諦めるか、VBAから操作できるものは全て使い切ると考えるかはこの関数が使えるかによると言っても過言ではありません

アプリのインスタンスを作成するというだけの関数ですが、この関数からの広がる世界はExcelVBAの世界を遥かに超えた世界になります

どうせならその世界を経験して欲しいなと思います
出来ることが劇的に増えて、とんでもなくおもろいねん、これが

関連の記事

バインディングについて

警告音を鳴らす

Beepステートメントはシステム警告音を鳴らします

'警告音を鳴らす
Beep

警告音を鳴らすには、「Beep」ステートメントを使用します
このコードを実行するとシステム設定された警告音を鳴らすことが出来ます
この音の設定はシステム設定になるのでVBAから任意に変更することは出来ません

あまり落とを鳴らすというような処理は好まれない場面が多いとは思いますが、例えば何らかの資料を見ながら入力処理を行う場合に、画面上で注意表示をしてもユーザーに認知されない可能性があります

またメッセージボックスでも音を鳴らすことが出来ますが、処理が停止するため入力処理の作業がそこで停止してしまいます
メッセージを確認しないと次の入力も行えません

入力値が制限外のデータであれば、それで構わないのですが、例えば数字を入力する際に前回入力値との差が10％以上あったら注意を促したい場合には有効です

その差が織り込み済みであれば、無視して操作を続けられるし、音が鳴ればユーザーは一度は画面を確認しますので入力時点で異常の検知や間違いに気づくことができるかもしれません

何らかの注意を促す場合に最低限で使用するようにしましょう

シートの内部名を取得する

シートの内部名を取得できるCodeNameプロパティについて

'シートの内部名を取得
Worksheets("Sheet1").CodeName

シートを操作する際に使用する、「Worksheets(“Sheet1”)」というオブジェクトはシート名を名称で直接指定しています

これとは別に、内部名としてシートごとにオブジェクト名が存在します
こちらもシート名同様に同じ名前を付けることが出来ないためユニーク名になります
その内部名を取得するのが「CodeName」プロパティです

この内部名を使用することにより、シート名を変更しても処理に影響が出なくなるため、状況によってはシート名より有効に使用できます

内部名とはプロパティでいうオブジェクト名のことになる
プロパティウィンドウ

今回のプロパティは、赤枠内の（オブジェクト名）という部分の文字列です
通常初期値はシート名と同じようにつけられています

またその下のほうにあるNameプロパティはシートのタブに表示されている文字列になります
通常、シート名を変更するとこちらが変更されます

シート名自体も同じ名前を付けることが出来ないので、オブジェクト名を使ったほうがいい場面の方が多いということはないと思います
状況によって使い分けることが重要です

また、この内部名をオブジェクトとして使用することが可能です

シート名を取得するときの動き
シート名の取得

この画像のコードでは、NameプロパティとCodeNameプロパティの両方を使用して、それぞれの名称文字列を取得しています

やっていることは同じですが、指定の仕方が違っています

オブジェクト名の方が、コードを比較的少なくすることが出来ると思います
またシート名をユーザーに変更されても問題が発生しないのは大きな利点です

ただ、Nameプロパティを使用しない場合、シートをコピー作成して同じシート名にして既存のシートを削除する、というようなことをされると使用できなくなります
また、何らかの理由でCodeNameプロパティに相違が出た際にユーザーが簡単に修正することが出来なくなります

シート名は「～～」にしてください、というようなメッセージでシート名を統一させることは出来ても、内部名を編集してもらうのはVBAを知らない人からすれば困難です

なので、シート名の変更にも対応できて、コードを短くすることもできる、この素晴らしいプロパティは状況によってはユーザーに迷惑になる可能性がありますので注意が必要です

やはり、一番相互にいいのはNameプロパティを使用して、それを最初にオブジェクト変数に代入しておくことかもしれません
そうしておけば、もしシート名が変更になっても1か所の修正で済みます

セルが結合されているか調べる

MergeCellsプロパティではセルの結合状態を取得できます

'選択範囲が結合セルかを調べる
Debug.Print Selection.MergeCells
'シート内に結合セルがあるかを調べる（Nullなら存在有）
Debug.Print Cells.MergeCells

指定したセルが結合セルかどうかを調べるには「MergeCells」プロパティを使用します
このプロパティの返し値によって判定を行います

指定したセルが結合されていれば「True」、結合されていなければ「False」をそれぞれ返します
さらに、その両方が含まれていれば「Null」が返されます

なお、このプロパティは設定も可能です
Trueを代入すると結合セルとなり、Falseを代入すると非結合セルとなります
実際の書き方は以下の記事にありますので確認してください

セル範囲を結合する

コード解説

結合セルの存在を確認するプロパティの動き
プロパティ取得の動き

この画像の前提として、A2とB2が結合されたセルです

Debug.Print Selection.MergeCells

結合されたセルが選択状態になっていますので、このコードを実行するとTrueが返されます
つまり、選択している範囲は結合されて1セルとなってい状態と判定できます

このプロパティは、単体での判定でしかTrueを返しません
例え指定範囲が全て結合されたセルだとしてもNullが返されてしまいます

逆にすべてが結合されていないセルであればFalseが返されます

そしてその仕様から、シート全体などの範囲で使用することで結合セルの存在を調べられます

Debug.Print Cells.MergeCells

このコードを実行するとNullが返されます
結合セルとされていないセルが範囲内に含まれているからです

画像の最後の動きの部分を確認してください
再度取得する前に、すべての結合を解除しています
その状態でプロパティを取得させると、Falseが返されます
つまり、シート内に結合セルが存在しないことを意味します

どのセルが、という所まではこのプロパティで調べられませんが、存在するかどうかは調べられます

基本的に結合セルとは、見栄えなどの書式的な使い方が多く、VBA泣かせのセルです
あったら何とか解除してしまえないかと思ってしまいますが、この判定や解除をうまく活用して乗り越えてください

セルの結合を解除する

セルの結合を解除するにはUnMergeメソッドを使用します

'選択範囲内セルの結合を解除
Selection.UnMerge
'選択範囲内セルの結合を解除
Selection.MergeCells = False

結合されたセルの結合を解除するには「UnMerge」メソッドを使用します
このメソッドは指定した範囲内にある結合セル全てを一括で解除します

また、本来は結合セルの存在確認を行うMergeCellsプロパティにFalseを代入することでも実行することが出来ます

コード実行時の動き

セル結合の解除の動き
セル結合解除の動き

画像の確認をしてください
前提として、2行目はすでに結合済みのセルです
結合解除を行った際に、そちらは解除されていないことを確認してください

Selection.UnMerge

選択範囲内にあるすべての結合セルの結合を解除します
指定範囲を限定することで、シート内に複数の結合セルがあったとしても限定して解除することが可能です

Cells.UnMerge

なので、逆に指定範囲を広げてシート全てのセル範囲を指定するとシート内にあるすべての結合セルの結合を解除することが出来ます
書式統一のために使用された結合セルが多いので、あまりこういった使い方はしないかもしれませんが、結合セルがシート内に1つしか無いのであれば、そのセル範囲を検索する必要がなく楽に解除することが出来ます

MergeCellsプロパティに関しては別の記事で解説していますので、ここでは割愛します

セル範囲を結合する

セルを結合するにはMergeメソッドを使用します

'選択範囲の結合
Selection.Merge
'選択範囲の横方向への結合
Selection.Merge True

セル範囲を結合するには「Merge」メソッドを使用します
このコードを使用すると指定したセル範囲が結合セルとなります

またメソッドの引数に「True」を指定することで、横方向のみの結合を行うことが出来ます

Selection.MergeCells = True

本来は結合セルの存在確認に使用するMergeCellsプロパティを使用することでも、例コードの1つ目と同じ動きの結合を行うことが出来ます

コードの動きの解説

セル結合時のコードの動き
セル結合時の動き

各結合の後にある「Cells.UnMerge」は結合セルを解除するコードです
結合の動きを確認するために書いています

Selection.Merge

これを実行すると、選択範囲が1つのセルに結合されます
行列ともに1つのセルとして結合されます

Selection.Merge True

このメソッドの引数にTrueを指定することで横方向のみの結合を行うことが出来ます
画像の動きを確認してください
選択範囲が複数行である場合に、横にのみ結合が実行され、縦方向には結合が実行されていないことが確認できます

これは、通常操作の「横方向に結合」と同じ動きをします
複数行で縦には結合しない場合に一括で指定することが出来る方法です

1つ目では省略されていましたが、この引数を省略するとFalseが指定されていることになります

Selection.MergeCells = True

このコードは、通常は選択範囲に結合セルが存在するかを調べて、存在すればTrueを返すプロパティですが、Trueを代入することで例コードの1つ目と同じ動きを実行できます

ただ、このコードでは横方向の指定が出来ません
ですが、解除する場合も使用でき、Falseを代入するだけで可能です

結合するにはTrueを、解除するにはFalseを代入するというのがイメージしやすいので使い勝手はいいと思います
蛇足ではありますが便利なので紹介します

コード実行時の注意点として、指定範囲に複数のデータが存在する場合はExcelの警告が発生します

結合時のデータ欠損の警告メッセージ
データ欠損の警告

結合セルを使用したことがある人なら見たことあると思いますが、複数のデータがある場合にデータの欠損が発生するため警告が表示されます
ここでキャンセルを選択すると、実行時エラーが発生します

動的にセル結合を行う場合にはこの点のみ注意が必要です

セル範囲の配列データを出力する

セル範囲の配列データを使用する際の使用方法と有効な利用場面について

'配列データを出力する
Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select
Selection = zz配列

配列データの中でも、セル範囲を取得した場合の出力にはResizeプロパティを使用することで比較的簡単に配列データのデータ数に合わせたセル範囲指定を行うことが出来ます

例コードでは、動きを分かり易くするために一旦選択を行っていますが、実際の使用時は選択は行わず直接データを出力すればいいです

実際の使用例

Dim zz配列 As Variant

zz配列 = Range("A1").CurrentRegion

Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select

Selection = zz配列

このコードを使用して解説を行います
後半2行は例コードをそのまま使用しています

セル範囲の配列データを一括出力する動き
コード実行時の動き

実際の動きを先に確認しておきます
このコードを実行すると、A1セルを含む表範囲を配列として取得して、データをD1セルから同じ範囲サイズに出力します

Dim zz配列 As Variant

最初に変数の宣言を行っています
セル範囲を直接配列データに代入する場合は、Valiant型の変数を宣言します
またこの際、静的配列は指定できませんので例の様に特に配列指定まで行わなくても問題ありません

zz配列 = Range("A1").CurrentRegion

次に、宣言した変数にA1セルを含む表範囲のデータを代入させます
表範囲の取得にはCurrentRegionプロパティを使用すると簡単に取得できます
また、この時点で配列データが作成され要素数も全て自動的に定義されています

Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select

配列データを出力する場合は、セル範囲が配列データの要素数と一致する必要があります
要素数を超える範囲選択をするとはみ出した部分はエラー値が入力されてしまいます
少ない場合は当然、データが全て出力されません

そこで配列データに合わせたセル範囲選択を行います
その際にResizeプロパティを使用するのが便利です

Resizeプロパティの引数の行数・列数にはUBound関数を使用して要素数の最大値を取得することで判定できます
通常、要素数の最大値は要素数と一致しない場合が多いです
それは下限値の違いによるものです

しかし、セル範囲を代入させた場合は下限値が1から始まるので関数で返される数値をそのまま使用することができます

セル範囲の配列データは2次元配列なので、その2次元目までの要素数を取得させます
2次元目の要素数はUBound関数の引数の2つめに次元数を指定することで取得できます
省略すると1次元が指定されますので、1つ目の関数では省略していますが2つめは省略せず「2」を指定しています
これで行数・列数を配列データと同じサイズのセル範囲を選択できます

Selection = zz配列

選択範囲に配列データを出力します
ここは、上記にも書いたように選択は行わずに出力するようにするほうが処理速度が速いので動きの理解が出来たら、その様に変更してもいいです

このコードでは配列データを加工していないのでセルデータのコピーを行っている形になります
しかし、配列データに書式データは含まれませんので値の貼り付けを行ったような動きになります

ちなみに、この方法はコピーして値の貼り付けをするより処理速度の向上が図れます
クリップボードを使用しないし、データ以外の情報も扱わないためです

なので、値貼り付けを実行したい場合にセル範囲が大きい場合はこのコードで配列としてデータを扱うと良いです

見出しを除外したデータ範囲選択

Resizeプロパティ等を使用してデータ範囲のみを選択するコード

'見出し1行を除外した範囲選択
Range("A1").CurrentRegion.Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count).Offset(1, 0).Select

表のデータを取得する際によく使用するのが、見出しの範囲を除外してデータの範囲のみだけを取得する処理です

コードを使用して取得したセル範囲
コードを使用して取得したセル範囲

このコードを使用すると、画像の様に見出しが1行の場合にその行以外のデータ範囲を選択状態にします
プロパティを複数使用する為、1行のコードが長くなってしまうので少しぱっと見わかりにくいかもしれませんが分割して確認すれば簡単な内容で取得出来る事が分かります

コードの解説

コードの解説を行うにあたって、このままでは少し分かりづらいので分割してコードを書いてみます
やっていることは全く同じですが、アクティブセル等の動的なセル指定を行っている点は少し違うことを認識しておいてください

分割して取得を行った時の動き
コードを分割して取得した時の動き

分割して実行していくと、コードは画像のように分割することができます
このコードを1行ずつ解説を行います
なお、最初のA1選択は便宜上のモノなので解説は割愛します

ActiveCell.CurrentRegion.Select

最初に全てのデータ範囲を取得しておきたいので、CurrentRegionプロパティを使用して表範囲全体を取得します
この状態では当然、見出しも含まれています

Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select

次にResizeプロパティを使用して範囲サイズを変更します
この例での表では見出しが1行なので、行数を1減らすことでデータ範囲の行数を取得する形になります

なので、ここの数値を変更すれば見出しの行数の変動にも対応出来ます

ただ、Resizeプロパティは範囲の左上からのサイズ変更になりますので、このままだとデータ範囲の最終行が除外されてしまいます

Selection.Offset(1, 0).Select

選択範囲を1行下に移動させることで見出し分を除外させます

ここまでの動きを記事コードでは一括で実行しています
コードが長くなり可読性が低いと思いますので、変数に代入させることで少しわかりやすくなるかもしれません

Dim zzデータ範囲 As Range
Set zzデータ範囲 = Range("A1").CurrentRegion

zzデータ範囲.Resize(zzデータ範囲.Rows.Count - 1, zzデータ範囲.Columns.Count).Offset(1, 0).Select

Set zzデータ範囲 = Nothing

このコードの様に表範囲をセル変数に代入させておけば、少しコードを短くすることができます

範囲を取得したいだけならクドくなりますが、この取得した範囲に対して処理を行う場合にはとても有効な方法です

セル範囲サイズを変更する

セル範囲を指定の行数・列数の範囲に変更するにはResizeプロパティを使用します

'A1から1行3列の範囲を選択
Range("A1").Resize(1, 3).Select

セル範囲サイズを減らしたり増やしたりする場合には「Resize」プロパティを使用します

このプロパティを使用すると、指定セル範囲の左上のセルから指定した行・列数のセル範囲に変更できます

例コードではA1セルを起点にセル範囲を変更しています
プロパティの引数1つ目が行数、2つめが列数になりますので、ここでは1行3列を指定することになります

つまり、「A1,B1,C1」の3つのセルが選択状態になります

使用時の動き

セル範囲サイズの変更の動き
コード実行時の動き

実際にコードを実行したときの動きの画像です
この動きを元に解説を行います

最初にA1セルを選択状態にしています

次に記事コードを実行します
例コードと同じものを実行してますので、解説した通りA1~C1セルが選択状態になります

言うなればこのコードは「Range(“A1:C1”).Select」と同じ動きをすることになります

そして上記でも解説したように、このプロパティで指定される起点となるのはセル範囲の左上のセルです
そのため、アクティブセルをC1に移動させてから実行したとしても範囲が移動することはありません

また、その際にセル範囲選択が再設定されるためアクティブセルは左上に移動してしまいます
この点については、アクティブセルを参照する様な処理作成を行っている場合には注意が必要です

このプロパティの大きな使い道は、特定の列だけに選択をさせたり、データ表範囲の見出しだけを除いて選択状態にする様な場合に有効なプロパティです

またこのプロパティを有効に使用すれば、配列データを出力する際の範囲選択も簡単に行うことが出来ます

セルのデータ取得時の情報について

Value・Text・Formula・Value2の各プロパティを使用するセルの情報取得時の種類の解説

Debug.Print Range("A1")

セルに入力されたデータを取得する際には通常上記のコードで対応します
このコードを実行すると、アクティブシートのA1セルの内容がイミディエイトに出力されます

A1セルに「100」と入力されていれば、出力されるのも「100」です
これは当然の話ですが、このコードには実際にはセルの入力値を指定するプロパティが省略されています

Debug.Print Range("A1").Value

省略しない形の場合は、このコードの様になります
省略した場合の既定のプロパティは「Value」プロパティです

これは入力されている値を取得するプロパティです
通常値の取得には、なんの問題もありませんので処理中ではほぼ省略する形で作成することが多いプロパティです

ただ、セルにあるデータをイメージしてください
セルには数値や文字列以外にも入力されるものがあります、日付やエラー値、関数などです
このあたりを取得したい場合にはValueプロパティでは対応できなくなります
その場合にはそれぞれ別々のプロパティがあります

取得情報によって切り替えるプロパティ

取得を行うセルの入力状態
取得を行うセル

このプロパティを説明するうえで一番分かり易い例が、関数で求められた日付データです
この画像のA2セルを対象に取得を行ってみます

同じセルを違うプロパティで取得した状態
別々のプロパティで取得した状態

先にこの画像を確認してください
これらの他にも取得プロパティは有りますが、とりあえずこれだけあればおおよそ問題ありません

Debug.Print Range("A2")
Debug.Print Range("A2").Value
→→→　#2020/05/19#

これはすでに解説したようにValueプロパティが省略されているので値が取得されます
なので、この2行のコードは同じものを意味しています
日付データを取得しているので、日付の両側に#が付与されています

Debug.Print Range("A2").Text
→→→　"2020年5月19日"

セルに表示されている状態のデータを取得するには、「Text」プロパティを使用します
これは基本的には、表示形式が適用された後のデータを取得します
Textなので、例え数値であっても文字列として取得されます

そして、このプロパティは「表示されている状態」の、と言ったように画面に見えている状態を取得します

例えば、入力値がセルの列幅より大きく表示できない場合Excelでは「#」の連続文字列で表現されることがありますが、それもしっかりその文字列を取得します

表示されているものを取得している状態
「#」が取得されている

この画像の様に「#」がイミディエイトに出力されます
実際は上記の内容と全く同じデータで「2020年5月19日」が入力されていますが、列幅を狭めた結果、表示できない表現として画像の様になります

Textプロパティはこの表現さえも素直に取得を行います
画面に表示されている状態というのは、こういうことになります

Textプロパティは表示形式が適用されたデータではない、という点に注意が必要です

ちなみに、この内容を反対に応用すれば入力したデータが列幅に収まっているかどうかを判定することも出来ますね

Debug.Print Range("A2").Formula
→→→　=A1+B1

数式を取得する場合は「Formula」プロパティを使用します
このプロパティであれば、関数の結果の値ではなく数式自体を取得することが可能です

VBAでセルに入力された数式を取得して作業を行うことは少ないのですが、配列への代入時などでは重宝するプロパティです

また、このプロパティはセル入力値が数値や文字列であれば、それをそのまま取得するので数式以外が取得できない、というものではありません

Debug.Print Range("A2").Value2
→→→　43970

日付データが実際には内部数値で扱われている、という点はある程度Excelを勉強した方ならご存じかとは思います
その内部数値を取得することが出来るのが「Value2」プロパティです

このプロパティは、なんの加工も行われていない純粋なデータを取得するプロパティです
リファレンスでも書いてあることなのですが、その性質上セルのプロパティで処理速度が最高のプロパティです

処理速度は間違いなく速いです、そこでValueプロパティと比較してみました
リファレンスにもあり、結果が明らかですのでコードは記載しませんが、内容としては変数にセルの入力値を代入させるだけの処理をループ処理で実行しました

自分の環境になりますが、100万セルで0.1秒ほどの差でした
Value2プロパティの方がやはり速かったです

ですが、100万セルで0.1秒ほどなので相当量のビッグデータを扱う際に気にしてみる
程度で十分な差でしかありませんでした

ちなみに、これも明らかですがTextプロパティは圧倒的に遅かったです
これは文字列を扱っているので想定の範囲内ではあります
まあ、Textプロパティは目的があって使用する場面の方が多いのであまり気にしなくても良いかなとは思います

処理速度の優位性はありますが、内部数値が欲しい、という場面でValue2プロパティを使用すれば良いです