カテゴリー: 関数

'全角に変換する
Debug.Print StrConv(ActiveCell, vbWide)

'半角に変換する
Debug.Print StrConv(ActiveCell, vbNarrow)

文字列の変換には「StrConv」関数を使用します
引数の設定により、全角と半角の切り替えができます

引数「String」は、変換前の文字列を指定します
引数「Conversion」は、変換するモードの指定します

関数の解説

StrConv("aＢ１23", vbWide)

→→→　ａＢ１２３

「vbWide」を指定すると、半角を全て全角に変換します

StrConv("aＢ１23", vbNarrow)

→→→　aB123

「vbNarrow」を指定すると、全角を全て半角に変換します

数字やアルファベット、カタカナの変換を行います。スペースも変換可能です
全角と半角の変換のためアルファベットの大文字小文字には対応しませんので注意してください

'文字列を数値で取得する
Debug.Print Val(ActiveCell.Text)

文字列になっている数字を数値として取得するには「Val」関数を使用します
引数には文字列を指定します、取得が出来なかった場合は「0」が返されます

文字列になっている数値

文字列となっている数値は、そのままでは通常の計算を行えませんので変換する必要があります

画像を確認してください
画像のアクティブセルにはSUM関数が入力されています
引数には上のセル5つが指定されています

SUM関数の答えは「1123.456」になっています
A5はともかく、A1からA4までの合計値とは一致しません
これは、A3とA4が文字列で指定されているためです

SUM関数だけではありませんが、文字列での計算は行えません
この場合に変換が必要となり、この関数の出番がやってきます

また、この関数は「適切な型で返す」とリファレンスにあるように整数だけでなく小数点数値も対象にすることが出来ます

この関数は1文字目から読み込んでいき、数字以外の文字列が出てきた時点で読み込みを終了します

Val("10本")

→→→　10

つまり上記のコードの様になります
A5セルの「10本」をVal関数で取得すると「本」で読み込みが終了して「10」が返されます

逆に先に数字以外の文字列が出てきた場合は数値に変換は出来ません
よくあるのが、和暦の年数取得ですが、これには使用できません

Val("令和2年")

→→→　0

「令和2年」をVal関数で取得しても1文字目の「令」の時点で読み込みを終了するため「2」という数値が返されることはありません

Val("123.4 5　6")

→→→　123.456

A4セルのような小数点の「.」やスペースは無視して読み込みが継続されるので、そういった文字列には対応可能です

使用場面は、画像の様に文字列入力された数字や数字の後に単位が入力されているような時に使用します

特にこの関数を知らないと、後に単位がある文字列を扱う際にMid関数で単位を除外して数字だけにして・・・と、非常に手間になるのでこの場面では非常に便利です

'本月の1日を取得
Debug.Print DateSerial(Year(Date), Month(Date), 1)

'本月の最終日を取得
Debug.Print DateSerial(Year(Date), Month(Date) + 1, 0)

月末の日にちを取得するには「DateSerial」関数を使用します
この関数は引数に数値で指定した年月日の日付データを返す関数です

引数の「year」は年の数値、「month」は月の数値、「day」は日にちの数値を指定します

関数の解説

この関数は引数に指定した数値の日付データを返しますが、指定する数値に関しては通常の日付数値範囲内でなくても大丈夫です

範囲内を超えた場合は、その上位の範囲に自動的に変換されます
引数「day」に「50」を指定すると、引数「month」に1が加算されて、残りの数値が日にちとして返されます

Debug.Print DateSerial(2020, 1, 50)

このコードを実行すると「2020/02/19」が取得されます
「50 – 31（1月の日数） = 19（2月の日数）」となり、月が加算されて結果の日付になります

これは引数「month」も同様で、「13」以上を指定すると引数「year」に繰り上げされます
引数「year」の繰り上げは無いので関係ありません

コード解説

Debug.Print DateSerial(Year(Date), Month(Date), 1)

本月の1日を取得します

年月はDate関数を使用してコード実行日を取得します
日にちは「1」を固定で指定することで、「○年○月1日」を取得できます

Debug.Print DateSerial(Year(Date), Month(Date) + 1, 0)

本月の最終日を取得します
関数の解説でもあるように、「Month(Date) + 1」が「12 + 1」でmonthの指定値が「13」になるので、yearに繰り上げされるので問題なく取得できます

また、この関数の特別な使い方で、「0以下の数値も指定可能」です
dayに「0」を指定すると、1日の前日が指定されるため、前月の最終日が指定されます

「前月の」なので、月に1を加算する必要があります
月に1を加算することで、来月の0日を指定することになり、来月の前月の最終日
つまり、今月の最終日を取得することが出来ました

また、マイナス数値も可能と言いましたが、その場合は指定数値分前の日にちが返されます

ワークシート関数にあるEOMONTH関数がVBAには無いので、こうした工夫をして最終日は取得します

'OSに処理を渡す
DoEvents

処理中にOSに処理を渡す関数が「DoEvents」関数です
引数は無く、この関数名のみで使用可能です

関数の解説と使用場面

このコードは単純に処理の中で実行しても何も起きていないように感じる関数です

この関数は、ループ処理で使用します
ループ処理はCPUへの負荷が強く、大量なループ処理や無限ループではExcelが応答なしの状態になり、描画処理も停止するため、いわゆるフリーズしたように見える状態になります

VBAでは処理実行中に「Ctrl+Break」か「Esc」で処理の一時停止させることができます
しかし、処理の内容によってはその操作が効かなくなる場合がありますし、そもそも本当にフリーズしていれば当然停止不能になります

そういった状況を防ぐために使用するのが、この関数です

関数の使用について

この関数をループ処理の内側どこでもいいので入れてあげると、その瞬間だけVBAの処理の為に使用している制御をOS（Windows）に渡しますので、VBAの処理は停止しますがそのタイミングでOSの操作が可能になるためVBEの画面もExcelも操作が可能になります

ただ、上記の停止操作はOSに制御が移っているせいで停止が効きません
ですが、VBEの操作ができるのでループ処理内のどこかでブレイクポイントを設置してあげるとそこで停止します

関数を入れずにループさせた場合、どこかの時点でExcelの描画処理が停止します
その状態でExcelをクリックすると、応答なしの表示がされてしまいフリーズのような画面になります

この関数を使用すると、この描画処理がOS制御でしっかり行ってくれるので描画処理が停止しませんので、VBAが頑張っているところを見せつけられます

ただ、この関数を使用するとOSに制御を渡すため処理時間は大幅に延長してしまいます、まあ別の制御も行ってるんですから当然です

なので、この関数での目的は処理速度ではなく
処理実行中の待ち時間を無駄にしないために使用することになります

また、IE制御などの待機用無限ループでは必須の関数になります

全ての操作が可能になる点について

Excelの操作が可能になるため、アクティブセルを操作するような処理では、動作不良を起こす可能性が非常に高いのでそういった処理で使用する場合であったり、Excel以外の全てのアプリも操作が可能になるため、IE制御であれば制御中のIEを終了されてしまったり、WinAPIでアクティブウィンドウのハンドル値を取得しているのが思惑と違うウィンドウになってしまったり、とさまざまなバグを発生させる可能性があります

ユーザーの為に操作を可能にするのであれば、そういった可能性を考慮した処理作成を行いましょう

'前日の日付を取得
Debug.Print DateAdd("d", -1, Date)

'翌日の日付を取得
Debug.Print DateAdd("d", 1, Date)

日付データを計算するには「DateAdd」関数を使用します

その他の計算範囲は以下になります

'1年後の日付を取得
Debug.Print DateAdd("yyyy", 1, Date)

'1月後の日付を取得
Debug.Print DateAdd("m", 1, Date)

'1週後の日付を取得
Debug.Print DateAdd("ww", 1, Date)

'3時間後の日付を取得
Debug.Print DateAdd("h", 3, Now)

'30分後の日付を取得
Debug.Print DateAdd("n", 30, Now)

'300秒後の日付を取得
Debug.Print DateAdd("s", 300, Now)

関数の解説

1つ目の引数「interval」に計算したい範囲を文字列で指定します
ここはDatePart関数の記事を確認してください
（DatePart関数の記事はこちらから）

3つ目の引数「date」は、計算前の日付データです

2つ目の引数「number」は、引数「date」に計算する数値を指定します
一応小数点数値も指定できますが、整数値に丸め処理が行われるため、使用する意味はありません
ここで「-」を数値に付けることで、減算を行うことが出来ます
付けなければ加算になります

日数であれば、シリアル値は整数値になっているので普通に四則演算で事足りますがその結果が数値として返されるか日付データとして返されるかは計算の状況によります
対して、この関数は結果をdate型で返すので安心設計です

また、時刻データは小数点数値であるため正確に1分や13秒などの時刻を計算するのは困難です

この関数を使用すれば、そこを整数値で計算させることができるので時刻の計算にはまずこの関数を使用するのがいいです

また、時刻の計算に関して例コードではNow関数を使用していますが、Time関数という現在時刻を取得する関数でも計算は可能です
ですが、日付をまたぐ計算を行った場合には日付が初期値が入ってしまい、うまく計算ができない可能性がありますので時刻データではなく日付データで計算したほうが分かり易いと思います
ただ、時刻のみのデータに日付データも付与される形になるのでセルに入力する際は注意が必要です

この関数を使用して計算を行うと上位の日付がしっかり繰り上がっていく点にも利点があります
月は12進法、日にちは28から31進法、時は24進法、分・秒は60進法と、書いてみると日付データの繰り上げがいかに面倒かがよくわかります
（進法とは、その数値で桁上がりをすることをいいます。日常で使う「…8,9,10,11…」の様に9から10で、桁が上がるものを10進法といいます）
もちろん減算時の繰り下げも同様に行われます

'本日の曜日数値を取得（日曜始点）
Debug.Print DatePart("w", Date)

'本日の曜日数値を取得（月曜始点）
Debug.Print DatePart("w", Date, vbMonday)

'本日の週数を取得
Debug.Print DatePart("ww", Date)

'本日の週数を取得（月曜始点）
Debug.Print DatePart("ww", Date, vbMonday)

週数と曜日数値を取得するには「DatePart」関数の引数「interval」でそれぞれ「”w”」と「”ww”」を使用します

関数の解説

関数の引数の1つ目と2つ目の解説は別の記事で行っています
（解説記事はこちらから）

引数の3つ目「firstDayOfWeek」は週の始まりを何曜日にするかの設定です
既定では日曜日となっています

指定する際はVBAの定数を使用すると分かり易いです
ワークシート関数と同じように数値での指定をする場合は、日曜日=1でそこから順番に数値と並んでいますので、もし月曜日を始点にする場合は「2」もしくは「vbMonday」（定数）を指定します

ここに関しては計算する際の環境に合わせて任意に合わせてください
曜日数値を取得する際はここで設定した数値で返されるので、設定次第で返される曜日数値も変化する形になります

次に4つ目の引数「firstWeekOfYear」ですが、これは週数を取得する際に年の最初の週をどこにするかの設定です

まず、指定できる内容が3種類あります（「vbUseSystem」はどれかに当てはまるため割愛します）

「vbFirstJan1」は1月1日が週の始まりになります
「vbFirstFourDays」は4日以上ある週を始まりとします
「vbFirstFullWeek」は全ての曜日がある週を始まりとします

文章では少しイメージしづらいので、取得したものを画像にしました

B列が1月1日から週の計算が始まる設定です
10行目の1月1日から1週目になっています、その前日は53週目ですね
これが既定の設定になりますので、引数を省略した場合の動きになります
ただ、これは1週間=7日にはならないことに注意が必要です
53週目は3日で、1週目は4日になっています
また2020年の第1週目の日～火曜日は存在しないことになりますので、週で業務を管理する際にはこれではうまくいかない可能性が高いですね

C列は後述します

D列は全ての曜日が含まれる週を第1週とします
その為、既定の始まりである最初の日曜日から週を計算する形になります
年が変わっても、週が変わるわけではないので週管理で考えると非常に分かり易いと思います

ただ、これでは年に対する考え方が全くなくなります
そこで使用するのが、C列の設定です
週の4日以上含む週を第1週目とする計算方法です

「vbFirstFourDays」のバグについて

C列の内容になりますが、ここでバグがあるため要注意です
新年に4日以上ある週を第1週とする場合、12/29（日）が本来であれば1週目1日目となります

画像の赤く塗られたセルの箇所になります
画像を確認してもらうと分かるように、「53」となり次の月曜日が「1」となっています
1月5日を確認して分かるように、ちゃんと週の始点は日曜日に指定されています

このバグはこの関数だけでなくFormat関数でも発生するようなので、そちらでも注意してください

この処理に対する対応策は、条件分岐により53を1に強制的に変更して取得する方法しかないようです

以下のページにこのバグの詳細が記載されています
「Format 関数または DatePart 関数で、先週の最後の月曜日の週番号を返すことができません」-MicrosoftOfficeトラブルシューティング

'現在の和暦を取得
Debug.Print Format(Now, "ggge")

'指定日付の和暦を取得
Debug.Print Format(#1/1/2019#, "ggge")

日付データから和暦を取得するには「Format」関数を使用します

関数の解説

Format関数は、ワークシート関数のText関数と同じような目的で使用します

引数「expression」は変換元のデータを指定します、ここで指定したデータを次の引数で指定した形式に変換して返します

引数「format」が、変換後の表示形式になります
表示形式の指定は、「ggg」で元号を表示して「e」で和暦年数を表示しますのであわせて指定することで、元号と年数を指定することが出来ます

3つ目と4つ目の引数は週の設定のため、今回は使用しません

2つ目の例コードでは、指定日付を指定することで特定の年月日から和暦を取得します、使用例でいうと「平成31」が取得されます

なお、「ggg」の文字数で指定内容が変更されます
下記にコードと一緒に記載しておきますので、必要に合わせて利用してください
基本的には文字数だけの違いなので、トップのものからgを削ってもいいです

'現在の元号を取得
Debug.Print Format(Now, "ggg")

「令和」が取得されます

'現在の元号の1文字目のみ取得
Debug.Print Format(Now, "gg")

「令」が取得されます

'現在の元号のアルファベットを取得
Debug.Print Format(Now, "g")

「R」が取得されます

'現在の和暦年数を取得
Debug.Print Format(Now, "e")

「1」が取得されます

全て記事作成日の2019年12月12日時点で取得される内容です

元年表示について

また、もうあと半月で2019年も終わってしまいますが2019年の間は「令和1年」であり、元号を使用する場合は「令和元年」と「元年」表示をするのが一般的です

ただこれは表示形式の指定がありませんので、日付で条件分岐をさせて直接「1」を「元」に変換する必要があります

VBAであれば、この取得処理の前段階で指定日付を元に表示形式を分岐させればいいのですが、なんせあと半月だけなのでここにこそっとコードは置いておきます

'元年表示の分岐処理
If Date >= #1/1/2020# Then
Debug.Print Format(Date, "ggge")
Else
Debug.Print Format(Date, "ggg元")
End If

2020年の1月1日より前であれば元号の年数は取得せずに「元」表示としているだけです、2020年以前なので2018年もそれ以前も全て元年表示となるので使えるタイミングはそれほどありませんね
ちなみに、このコードは年月日のみを対象としているのでNow関数ではなくDate関数を使用して日付を取得しています

一応表示形式の条件分岐を行って、それぞれの形式に変換する表示形式の書き方がここのWebページにあります
「日本の年号変更と元号」-MicrosoftOffice　サポートページ

'現在の西暦年を取得
Debug.Print DatePart("yyyy", Now)

'現在の月を取得
Debug.Print DatePart("m", Now)

'現在の日を取得
Debug.Print DatePart("d", Now)

'現在の時刻を取得
Debug.Print DatePart("h", Now)

'現在の分数を取得
Debug.Print DatePart("n", Now)

'現在の秒数を取得
Debug.Print DatePart("s", Now)

日付データから年月日時分秒のそれぞれ取り出すには「DatePart」関数を使用します

関数の書式

引数（太字は必須引数）
(interval, date, firstdayofweek, firstweekofyear)
戻り値の型　variant型（integer）

関数の解説

1つ目の引数「interval」に指定の文字列を入力することで指定の範囲のデータを取得することが出来ます

西暦年数を取得するには「”yyyy”」を指定します
和暦年数の取得は出来ないので、必要な場合はFormat関数を使用して取得します
（和暦に関してはこちらの記事を確認してください）

それ以外の範囲はコードを確認してください
これら以外にも指定できる範囲がありますが、使いどころが少し変わってくるので別の記事にします
（曜日と週数の取得はこちら）

また、文字列で指定するため「”」で囲む必要があります
そしてこの文字は実際には全角半角・大文字小文字全てどれで入力しても対応して返してくれますが、そんなことはせずに半角小文字で入力しましょう

2つ目の引数「date」に取得する元となる日付のデータを指定します
今回は全て現在の日付データを取得する「Now」関数を使用しています
（Now関数についてはこちらの記事です）

3つ目と4つ目の引数は省略可能で、計算の起点を指定するものです
「firstdayofweek」は週の始まりを指定できます、省略した場合は日曜日が指定されます
「firstweekofyear」は年の最初の週をどこに設定するかを指定します、省略した場合は1月1日のある週が第1週目として指定されます
これら2つの引数は週数や曜日の計算に関係する項目のため、今回の場合では必要ないので省略してもらっていいです

戻り値はvariant型の整数型です
Excelでは時刻データは小数点数値で表現されているので、この関数を使う事で実際目に見えているそれぞれの数値を整数値として取得することが出来ます

DatePart関数を使わずに取得する

この関数はそれぞれの範囲を指定することで取り出す関数ですが、例えば年を取り出す関数として「Year」関数があります
他にも全てそれぞれを取得する関数があります

あえてDatePart関数をトップにもってきた理由は、この関数は日付を取り出す専用の関数のため、Year関数など全ての関数を覚えていなくてもこの関数の解説を見ればすべてを網羅できるためです
あと、日付データの計算を行う「DateAdd」関数でも同じものを使用するのでそれとセットで分かり易いかなと思いました
（DateAdd関数の記事はこちらから）

ですが、年を取り出すYear関数であれば引数はdateのみでいいので記載は簡潔です
それに英単語からイメージもしやすいです

どちらにも利点があるので、ここから下にDatePart関数で行ったものと同じ結果を返すそれぞれの関数のコードも載せておきます

'現在の西暦年を取得
Debug.Print Year(Now)

'現在の月を取得
Debug.Print Month(Now)

'現在の日を取得
Debug.Print Day(Now)

'現在の時刻を取得
Debug.Print Hour(Now)

'現在の分数を取得
Debug.Print Minute(Now)

'現在の秒数を取得
Debug.Print Second(Now)

全ての関数の引数は1つで必須です、日付データを指定します
今回はNow関数でシステム日付を取得させています

また日付データを指定日付にする際に、VBA特有の日付データの書き方があります
その内容はこちらの記事の中にあります

'システム年月日を取得
Debug.Print Date

'システム時分秒を取得
Debug.Print Time

'システム年月日時分秒を取得
Debug.Print Now

ＰＣのシステム日付および時刻データを取得するには、それぞれの必要な範囲で関数が複数あります

年月日だけの取得には「Date」、時分秒だけの取得には「Time」、年月日時分秒の全てを取得するのが「Now」です
全て関数で戻り値を取得しますが、引数がないため「()」は使用しません
入力しても消えます

ExcelVBAで主に使用するのはDateで、ワークシート関数でいうところのToday関数と同じ役割です
ユーザーフォームの日付入力ボックスに初期値として、本日日付を取得させたり
処理の実行を日付限定で行う際の判断基準となったりします

「Format」関数と組み合わせることで、和暦の表示も可能です
（和暦の取得方法はこちらの記事へ）

時分秒に関しては、操作履歴を記入する際に添付したり
新しいブックの保存名に使用することで、確実にかぶらないブック名にしたりすることもできます

秒まで取得できるので、ユーザーフォームに秒がリアルタイムで更新されて表示されるデジタル時計を作ってみたくなります
実際作りました、「DoEvents」関数と「Wait」メソッドを併用してCPUに負荷をかけずに無限ループさせてみました

問題なく時計になりましたが、やはりVBAは無限ループによる並行処理が苦手です
システム作成上、かならずどこかで無理がたたります

結果、やっぱり思ったのはデジタル時計って自己満足でしかありませんでした
だってPCに時計あるしね、普通に
時間もそこから取得してるので、別に高精度の時計になるわけでもなかった
でもやりたなるよね～、デジタル時計って

'「中止・再試行・無視」ボタンで分岐
Select Case MsgBox(Prompt:="データ欠損！（無視で強制起動）", Buttons:=vbAbortRetryIgnore)
Case vbAbort
'中止をクリックしたときの処理
Case vbRetry
'再試行をクリックしたときの処理
Case Else
'それ以外（無視）をクリックしたときの処理
End Select

'「はい・いいえ・キャンセル」ボタンで分岐
Select Case MsgBox(Prompt:="データを上書きします（いいえで新規追加）", Buttons:=vbYesNoCancel)
Case vbYes
'はいをクリックしたときの処理
Case vbNo
'いいえをクリックしたときの処理
Case Else
'それ以外（×ボタンかキャンセル）をクリックしたときの処理
End Select

「MsgBox」関数では最大3択のボタンを表示することが出来ます
そのボタンによって対話型のより複雑な処理分岐が可能になります
（MsgBoxの基本の使い方についてはこちら）

また2択ではIf分岐を使用しましたが、ここではSelect分岐を使用します
それぞれのボタン名で分岐の処理を行います
（Select分岐の解説はこちら）

3択には2種類あります

引数「Buttons」に「vbAbortRetryIgnore」を指定すると「中止・再試行・無視」の3択になります
それぞれの各大文字部分からがボタンの名前です
なので戻り値の定数もその名前で指定すればいいだけです
また、キャンセルボタンが無いので×ボタンが使用不可になっています（グレー表示になり無効状態）

「vbYesNoCancel」を指定すると「はい・いいえ・キャンセル」の3択になります

単純に2択にあった「はい・いいえ」にキャンセルが追加されただけです

2択の時には無かったキャンセルボタンが追加されたことで、ここでは×ボタンが使用可能になっています
×ボタンとキャンセルは同じ操作とみなされます

数値などの分岐で多数分岐はいくらでもありますが、対話型分岐の3択処理分岐というのはなかなか無いのでシチュエーションが難しかったです
まあ要は2通りの処理と、処理自体のキャンセルという流れになりますがなかなかないで～