「Excel Tips」ではExcelを使用していて気付いたことを取り上げて行きます。
今回はVBAコーディングをマクロ有効ブックではないXLSX形式ファイルで実行するやり方をご紹介いたします。
普通のExcelファイルで入力された内容をVBAコーディンクでチェックや情報を取得する時にわざわざマクロ形式ファイルに保存せずに実行したいケースがあることと存じます。
なお今回は普段あまりVBAコーディングをされていない人でも解るようにご説明できればと思っています。
Excel 2016バージョン2010(ビルド 13328.20292)を使用しています。
フォーカスの当たっているワークシートからVisual Basic for Applicationsを起動させる
普通のExcelファイルでもVBAコーディングをする事は可能ですが、XLSX形式では作成した成果物を保存する事ができません。
従いまして、マクロやVBAコーディングを最初から作成する場合はマクロ形式ファイルにする必要があります。
ただし既に作られているマクロやVBAコーディングを例えばテキストファイルからコピーして実行させて内容を取得したりチェックするだけであれば、XLSX形式ファイルのままでも実行することができます。
この場合はリボンのメインタブに開発タブが表示されていなくても大丈夫です。
ではどのようにしてVisual Basic for Applicationsを起動させるか?なのですが、次のように操作します。
- シートタブでマウスの右ボタンをクリックしてショートカットメニューを表示します。
- ショートカットメニューの「コードの表示」を選択
- クリックしたシートのコードウインドウが開かれた状態でVisual Basic for Applicationsが起動します。
イミディエイトウィンドウを表示させる
マクロやVBAコーディングを普段使われていない方には煙たく思われている事と存じますが、もう少しお付き合いをお願いします。
今回ご説明するVBAコーディングは結果をイミディエイトウィンドウに出力します。
そのためイミディエイトウィンドウの表示の仕方と閉じ方が必要になります。
Visual Basic for Applicationsのメニュー→表示→イミディエイトウィンドウをクリックするとコードウインドウの画面が上下に分割されたように見えますが、上半分はコードウインドウが短縮されて表示されていて、空いた下半分がイミディエイトウィンドウになります。
イミディエイトウィンドウを閉じるときは上記右図の赤枠内の×印をクリックします。
なおイミディエイトウィンドウは結果を表示するたけではなく、「?」の後に式などを入力して計算結果を表示させる事ができます。
コードの実行の仕方は後述いたしますが、左図のように「?17*19」と入力した後改行キーをクリックすると結果が次の行に表示されます。
コードウインドウに貼り付けたVBAコーディングを実行する
前章で使用したイミディエイトウィンドウに結果を表示する簡単な下記のVBAコーディングをコードウインドウに貼り付けて実行してみたいと思います。
Sub check()
Dim a As Integer, b As Integer
a = 17: b = 19
Debug.Print a * b
End Sub
上記の5行をドラッグで選択してショートカットキーでコピー(ctrl + C)するか、下図赤枠のCopy to Clipboradのボタンをクリックしてください。
ショートカットキーの「ctrl + C」はCTRLキーを押しながら英文字「C」を押す操作になります。
その後で、コードウインドウをクリックしてショートカットキーでの貼り付け(ctrl + V)もしくはメニューバーの編集→貼り付けをクリックします。
これでVBAコーディングを実行するための準備が整いましたので、ツールバーの「sub/ユーザーフォームの実行(F5)」ボタン(左図赤枠)をクリックします。
この操作によりイミディエイトウィンドウに計算結果が表示されます。
なおコードウインドウにフォーカスがない状態で上記操作をすると左図のようなマクロ画面が表示されます。
恐らくマクロは1つだけ設定されているはずですので、そのまま「実行(R)」ボタンをクリックすれば同じようにVBAコーティングが実行されます。
なお1点注意すべき点がございます。
この方法でコードウインドウに設定したVBAコーティングは、開いたワークシートのみを対象に処理を実行します。
今回ご紹介しているサンプルコーディングはワークシートのデータを処理対象にしていませんが、ワークシートのデータを処理するVBAコーディングの場合は注意が必要です。
なお複数のワークシートで実行できるようにするには「ThisWorkbook」オブジェクトにVBAコーディングを貼り付けて、実行する時に処理対象となるワークシートをアクティブにする必要がありますが、本記事ではこの辺りの詳しい説明は省略していますのでお含み置きいただければ幸いです。
コードウインドウの内容を消去する
Visual Basic for Applicationsは右上の×マークをクリックする、もしくはメニューバーのファイル(F)→終了してMicrosoft Excelへ戻る(C)をクリックすることで閉じることができます。
ただしコードウインドウにVBAコーディングが残っている状態で上記操作をすると、最終的に「Excelファイルを閉じる」時に次のような2段階でメッセージが表示される状況になります。
- Excelのワークシートで変更作業をしている場合は、「最初のメッセージ(上左図)」は必ず「保存(S)」をクリックしてください。なおこのメッセージはファイル更新時に保存していないと表示されますので見たことがあるメッセージであると思います。
- 変更作業をしていない時は「保存しない」をクリックすると、隣の「次のメッセージ」は表示されず、VBAコーディングは消去されます。
- 最初のメッセージで保存をクリックした時は、普段見慣れない「次のメッセージ(上右図)」が表示されます。今回はVBAコーティングは保存しないので、「はい(Y)」をクリックしてください。
- マクロ有効ファイルでの保存につきましては本記事ではご説明しておりませんので、お含み置きいただければ幸いです。
もしも普段見慣れない「次のメッセージ」は表示させたくないという時は、コードウインドウに入力されているすべてのコードを選択して削除をする必要があります。
なお全コードを選択するには次のようなやり方があります。
- 最初の行の先頭から最後の行までドラッグで選択する。
- Visual Basic for Applicationsのメニューで編集→すべてを選択(A)
- コードウインドウのどこかをクリックしショートカットキーの「ctrl + A」をキー入力する。
全コードが選択されたら削除キーまたはメニュの編集→クリア(L)でVBAコーティングを消去してください。なおイミディエイトウィンドウに表示が残ったままExcelを閉じても「次のメッセージ」は表示されませんので消去しなくても大丈夫です。
サンプルVBAコーディング
XLSXファイルでVBAコーディングが動かせる事のご説明は以上となりますが、「やり方は解ったけれど何を動かすものはあるのですか?」という疑問が沸いてくる事と存じます。
一つは「Excel VBAでオートシェープ図形のサイズを書式サイズに合わせて計算する」でご紹介している、シートに配置されたオートシェープ図形のサイズを出力するVBAコーディングになります。
ただ上記のVBAコーティングはExcelを使われている人の中でも一部の人しかお使いにならないと思います。
そこで二つ目として「ワークシートに入力された数値データに問題がないか確認する」ためのVBAコーディングをご紹介いたします。
ただしVBAコーディングの行数が70行近くあり「そんなに大量に毎回コピペするのか?」と面倒臭く感じられるかもしれませんが、ショートカットキーの「ctrl + A」をキー入力であれば全選択するのは1回の操作になりますので、ご負担は少ないことと存じます。
Option Explicit
Sub NumericCheck()
Dim i As Integer, rw As Long, cl As Long, r As Integer, rMax As Integer, c As Integer, cMax As Integer, cnt As Integer: cnt = 0
Dim hyplnk As String, cladrs As String, strwk As String
Dim NowCell As Range, cellrefer As String: cellrefer = ""
Dim 行除外 As String: 行除外 = "" '【①】行でチェックを除外する時、行番号(数字)をカンマ区切りでセット。無ければ""(空文字)
Dim 列除外 As String: 列除外 = "" '【②】列でチェックを除外する時、列番号(英半角大文字)をカンマ区切りでセット。無ければ""(空文字)
Dim judge As Boolean, 区切字 As String: 区切字 = "" '【③】区切字にスペース以外の1文字を入力した時、区切り位置指定ウィザードに区切字をセット
Const 更新 As Boolean = False '【④】文字列数値を修正する場合はTrueに変更
行除外 = Replace(行除外, " ", ""): If 行除外 = "" Then 行除外 = " "
列除外 = Replace(列除外, " ", ""): If 列除外 = "" Then 列除外 = " "
区切字 = Replace(区切字, " ", ""): 区切字 = Left(区切字, 1)
If 区切字 = "" Then
judge = True: 区切字 = "＿" '【※】区切字にデフォルト値＿(全角アンダースコア)をセット
End If
Debug.Print ActiveWorkbook.Name
ActiveSheet.UsedRange.Select
For rw = Selection(1).Row To Selection(Selection.Count).Row
rMax = -1
For r = 0 To UBound(Split(行除外, ","))
If StrComp(Split(行除外, ",")(r), rw, vbTextCompare) = 0 Then Exit For
rMax = r
Next r
If rMax = UBound(Split(行除外, ",")) Then
For cl = Selection(1).Column To Selection(Selection.Count).Column
Set NowCell = Cells(rw, cl) '【※】処理対象セルをオブジェクト変数にセット
cMax = -1
For c = 0 To UBound(Split(列除外, ","))
If StrComp(Split(列除外, ",")(c), Split(NowCell.Address, "$")(1), vbTextCompare) = 0 Then Exit For
cMax = c
Next c
If cMax = UBound(Split(列除外, ",")) Then
cladrs = NowCell.Address(False, False): hyplnk = "=HYPERLINK(""#"" & """ & cladrs & """,""" & cladrs & """)"
If IsError(NowCell.Value) Then
cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-E" & 区切字 & hyplnk & 区切字 & CStr(NowCell.Value)
ElseIf NowCell.Value = "" Then 'Skip
ElseIf IsNumeric(NowCell.Value) = False Then
If IsDate(NowCell.Value) = True And Left(Evaluate("Cell(""Format""," & cladrs & ")"), 1) = "D" Then '日付はSkip
Else
strwk = NowCell.Value
For i = 0 To 9: strwk = Replace(strwk, i, ""): Next i
If strwk <> NowCell.Value Then
cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-1" & 区切字 & hyplnk & 区切字 & NowCell.Value
End If
End If
ElseIf NowCell.Value <> NowCell.Value * 1 Then
cnt = cnt + 1
If NowCell.Errors.Item(xlNumberAsText).Value = False Then
Debug.Print Format(cnt, "000") & "-2" & 区切字 & hyplnk & 区切字 & NowCell.Value & 区切字 & NowCell.Value * 1
Else
Debug.Print Format(cnt, "000") & "-3" & 区切字 & hyplnk & 区切字 & NowCell.Value & 区切字 & NowCell.Value * 1
If 更新 = True Then
NowCell.Value = NowCell.Value * 1: NowCell.NumberFormatLocal = "G/標準" '【※】抽出セルの訂正処理
End If
End If
ElseIf cellrefer = "" Then cellrefer = cladrs
End If
End If
Next cl
End If
Next rw
If cellrefer <> "" And judge = False Then '【※】区切り位置指定ウィザードの区切り文字に区切字の値をセットする処理
ActiveSheet.Range(cellrefer).Select
Selection.TextToColumns Destination:=Range(cellrefer), DataType:=xlDelimited, Other:=True, OtherChar:=区切字
End If
ActiveSheet.Range("A1").Select
End Sub
このVBAコーティングはシート上どこに数値項目列があっても調べることはできるのですが、変数に値を設定する事で「この行は調べない」「この列は調べない」というチェック除外の指定ができます。
実は上記の状態でVBAコーディングを実行した時は、確認した内容をイミディエイトウィンドウに出力するだけで、既存のワークシートは何も更新処理をしませんのでご安心ください。
なお実行すると、VBAコーティングをコードウインドウに貼り付けたワークシートが処理されます。
ブックの中に複数ワークシートがあり、それぞれ確認をする場合はVBAコーディングを「ThisWorkbook」オブジェクトに貼り付けることで、それぞれのシートに個々に貼り付ける必要がなくなり操作が楽になりますが、本記事では詳しい説明は省略しています。
上記以外にもご説明しなければならない点がありますので、VBAコーディングの詳細説明は別途記事に致したく存じます。
記事は近々公開いたしますので今しばらくお時間をいただければ幸いです。
VBAコーディングした時Excelの終了時に変なメッセージがでる事がある
少し回りくどい表現で恐縮ですが、Visual Basic for Applicationsを使用した時に必ずメッセージが表示されるわけはありません。次の操作・設定がされているExcelブックでVisual Basic for Applicationsを開いた時にメッセージが表示されます。
その操作・設定とは「ブックの検査」を実行して「ファイルを保存するときにファイルのプロパティから個人情報を削除する」設定を有効にする操作になります。
もっとも「ブックの検査」を実行しないと「ファイルを保存するときにファイルのプロパティから個人情報を削除する」設定はグレーアウトされた状態になっています。言い換えますと「ブックの検査」を実行して初めてグレーアウトが解除されデフォルトで設定が有効化される仕様になっています。
なお有効化された設定を解除してブックを一度閉じてから開きなおすと、設定は元のグレーアウトされた状態に戻ります。
どのタイミングでどのようなメッセージが出るのか?
ワークブック終了ボタンをクリックした時
先の章で、VBAコーディングが残っている状態で終了操作をした時に、普段見慣れた「最初のメッセージ」と普段は表示されない「次のメッセージ」が出ることはご説明しましたが、「最初のメッセージ」(一般的な保存確認)で「保存(S)」を押した後に左図のメッセージがでます。
ここで「OK」ボタンをクリックすると、そのまま終了します。
もしも「個人情報を削除する」設定を意図的に有効にされている場合は、VBAコーディングの操作をすると、必ずこのメッセージが表示されてひと手間増えてしまうことになりますが、お含み置きいただければ幸いです。
上図で、「キャンセル」ボタンをクリックすると左図のメッセージが表示されて、「OK」ボタンを押すとワークシートに戻ってしまい、終了する事ができません。
つまり上図メッセージが表示された時は終了するためには必ず「OK」ボタンを押す必要があります。
ワークブック保存ボタンをクリックした時
保存ボタンをクリックすると、前章の「ドキュメント検査機能では…、ご注意ください」のメッセージが表示されて動きは同じになります。
「ドキュメント検査機能では…、ご注意ください」のメッセージが表示される理由
このメッセージが表示される引きがねになる操作は、Visual Basic for Applicationsの画面を表示させる事にあります。なんと単純に開いただけで表示の対象となり、VBAコーディングを実行する・しないは関係ありません。
そのためVBAコーディングを消去していたとしてもメッセージは表示されてしまいます。
また「なぜメッセージが表示されるか？」なのですが「ドキュメントの検査」の操作では「マクロ、フォーム、またはActivXコントロール」があるかどうかの検査をしてくれますが、検査をするだけで実際にその中に含まれるであろう個人情報を削除をしてくれるわけではありません。
そのため代わりに注意喚起のメッセージを表示させているものと推察されます。
「個人情報を削除する」設定がされているブックか?確認する方法
ブックでの設定の有無は次の操作で確認することができます。
なおやり方は複数ありますが一番手数の少ない方法をご紹介いたします。
メニューバーの「ファイル」をクリックし、右の図2のサイドバーで「情報」をクリックすると右の図３の情報画面が表示されます。
図３の赤枠の部分に「これらの情報をファイルに保存できるようにする」という表示がある場合は、「個人情報を削除する」設定が有効化されています。
なおもしも設定を解除するのであれば、「これらの情報をファイルに保存できるようにする」はカーソルを合わせるとクリックできる状態になっていますので、クリックしていただくと表示が消滅します。
消滅した状態でブックを保存した後、いったん閉じて、再度開きおなせば設定は解除されて「ファイルを保存するときにファイルのプロパティから個人情報を削除する」設定はグレーアウトされた状態に戻ります。
最後に
今回はXLSXファイルでVBAコーディングをコピペして実行するやり方についてご説明いたしました。
マクロ有効ファイルは動かすためにそれはそれで煩わしいところがあるのですが、XLSXファイルであればその都度コピペするところを除いて普通のExcelファイルのままで実行されることができるので、VBAコーディングの内容次第では有用性があることと存じます。
何か他に汎用的に使えるVBAコーディングがあれば、今後徐々にサンプルを増やして行きたいと思います。
以上最後までご一読いただき誠にありがとうございました。
