見出し画像

「これは経理からの処理を管理部がVBAを使って一瞬で完了させた物語」

カイリーー

ある日の夕方17：30

友子「もうすぐ定時の18：00。今日のタスクはもう終わっているし、ちゃちゃっと帰って愛しのエリーを迎えに行けそう～」

電話「プルルルルルルルル♪」
友子「ん？内線だ。誰だろ。」

着信：経理課 穴呂具

友子「げ、穴呂具さんじゃん。こんな時間に嫌な予感…。
はい。管理部 山田(友子)です。」

穴呂具「お疲れ様です。穴呂具です。
先日いただいた管理部経由でいただいた店舗の清算台帳ですが、こちらのファイル、すべて202009のような形で日付をつけていただけますか？経理部長からの指示で急遽必要になったとのことで、今月からそうしていただきたいです。明日締日となりますので、至急明日のAMまでに訂正し、ご提出をお願いします。」

友子「ぇ？いやいやいやいや、清算台帳は経理課管理のものでしょ？なんでうちが直さなきゃいかんのですか？(怒)」

穴呂具「うちは300店舗分の売掛金のチェックを行わなければならない為、そんなものに時間をかけている余裕はないので、管理部に任せろ。という経理部長からの指示です。よろしくお願いします。ガチャ!!!!」

友子「ぇぇ…ウソでしょ？これ500個もあるじゃん…。
あのヤクザみたいな経理部長に逆らえないし…どうしよう。」

友子「仕方ないけど、乙骨さんにお願いするしかないかな…。
またグチグチ言われるんだろうなぁ…。
男の癖にネチネチ次の日も言われるからあんまり頼みたくないんだけどなぁ…。」

と、困り果てた顔をしていると…

武ちゃん「友ちゃんどうしたの？アンパンマンが顔を水で濡らしちゃって元気が出ないような顔してるけど。」

友子「武ちゃん。そ、そんな顔してた…？
えっとね、穴呂具さんにこのエクセルファイル500個のファイル名をすべて日付を付けて明日のAMまでに提出してくれって言われちゃって…これから娘も迎えに行かなきゃいけなくて、これやってたら間に合わなくなっちゃうから、乙骨さんにお願いしようと思ってたの。」

武ちゃん「ﾁｯ!」

友子「（ぇ…今舌打ちした？）」

武ちゃん「ん？あーごめん。穴呂具って聞くだけでイラッとしちゃって。
あたしが〇っとばしておこうか？
今度会ったときに立ち直れないような罵詈雑言を浴びせておくわ。」

友子「う、うん…ありがとう。」

武ちゃん「ぁ、あとこれなんとかなりそうだからあたしに任せてもらえる？たぶんすぐ出来ると思う。」

友子「え！そうなの!?」

武ちゃん「うん。VBAでエクセルの自動化処理をすれば多分一瞬で終わるよ」

友子「一瞬で!? 武ちゃんエクセルの神様だと思ってたけど、VBAもできるの？」

武ちゃん「うん。とにかく今日は早く帰って娘ちゃん迎えに行ってあげて！明日また説明してあげる♪」

友子「ありがとう～‼武ちゃん神だわほんと。」

翌日

武ちゃん「友ちゃんおはよう～☀」

友子「武ちゃんおはよう～☀昨日はごめんね！大丈夫だった？」

武ちゃん「大丈夫。あたしにかかれば朝飯前、最近のTwitter界隈の朝活前よ。」

友子「（Twitter界隈…？）さ、さすが武ちゃん…神だわ。どうやったの？」

武ちゃん「昨日もちょこっと話したけどVBAを使えば楽ちんに出来ちゃうのよ。一応完成品はこんな感じになってて…」

画像1

武ちゃん「ファイル名取得ボタンを押すと対象のファイルの名前をすべて取得してA列に挿入されて、B列には事前に変換したいファイル名を入力しておいて、ファイル名変換ボタンを押すと、フォルダにあるファイル名がすべて変換されてるって寸法よ！」

武ちゃん「順番に説明していくね！まずは、下準備として、次のようにエクセルの表を作ってみて」

画像3

武ちゃん「シート名はわかればなんでもいいんだけど、"ファイル名変換"とつけておこう。」
武ちゃん「で、穴呂具のやつから言われたエクセルファイルをCドライブにフォルダ作ってその中に入れる。」

画像8

武ちゃん「ここからVBAを書いていくよ。まずは"Alt+F11"でVBEっていうプログラミングのコードを書いていく画面が開く。」

画像4

武ちゃん「そしたら次は、ツール⇒オプションから変数の宣言を強制するにチェックをつけておいて。これについてはすでにチェックつけてある場合は省略してOK！」

画像5

武ちゃん「そこまで出来たら今度はVBEの画面に戻って、VBAProjectにカーソルがある状態で右クリック⇒挿入⇒標準モジュールの順で選択。」

画像6

画像7

武ちゃん「そうするとこんな感じで標準モジュールが表示されていればOK！ここまで大丈夫？」
友子「うん、OK！」
武ちゃん「そしたらまずは完成形のコードがこれなんだけど」

Option Explicit
   Dim ws1 As Worksheet
   Dim wb As Workbook
   Dim FolderPath As String             'ブックのフォルダパス
   Dim OldFileName As String
   Dim NewFileName As String

'----------------------------------------------------------------------------------------------------
'指定のフォルダからエクセルファイルをすべて取得
'----------------------------------------------------------------------------------------------------
Sub FileNameChange()
   ThisWorkbook.Activate
   Set ws1 = Worksheets("ファイル名変換")
   ws1.Activate
   
   FolderPath = vbNullString
   'フォルダー選択処理
   Call FolderSelect
   
   'キャンセルであれば処理終了
   If FolderPath = vbNullString Then Exit Sub
   
   Dim IntLoop As Long
   Dim buf As String
   buf = Dir(FolderPath & "*.xls")
   IntLoop = 2
   
   Do While buf <> ""
       ws1.Cells(IntLoop, 1) = buf
       IntLoop = IntLoop + 1
       buf = Dir()
   Loop
End Sub
'----------------------------------------------------------------------------------------------------
'ファイル名を指定のファイル名に変換処理
'----------------------------------------------------------------------------------------------------
Sub FileNameGet()
   '最終行取得変数
   Dim LastRow As Long
   
   If ws1 Is Nothing Then
       Set ws1 = Worksheets("ファイル名変換")
   End If
   
   LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
   Dim IntLoop2 As Integer
   If LastRow < 2 Then
       GoTo cancel
   End If
   
   'ファイル変換ボタンを先に押した時の対処
   If FolderPath = vbNullString Then
       MsgBox "フォルダー選択がされておりません"
       Exit Sub
   End If
   
   Dim fso As Object
   Set fso = New FileSystemObject
   '繰り返しA列の値を取得
   For IntLoop2 = 2 To LastRow
       '現在のファイル名取得
       OldFileName = ws1.Cells(IntLoop2, 1)
       '変更後のファイル名取得
       NewFileName = ws1.Cells(IntLoop2, 2)
       
       If fso.FileExists(FolderPath & OldFileName) = False Then
           GoTo cancel
       End If
       
       If NewFileName <> "" And OldFileName <> "" Then
           Name FolderPath & OldFileName As FolderPath & NewFileName
       Else
           'ファイル名が入力されていないとき
           GoTo cancel
       End If
   Next IntLoop2
   
   MsgBox "処理が完了しました"
   
   Exit Sub
   
cancel:
   MsgBox "A列またはB列にファイル名が入力されていません。" & vbCrLf & _
          "またはフォルダにA列のファイル名が存在しません。" & vbCrLf & _
          "処理を中断します｡ "
End Sub
           
'---------------------------------------------------------------------------------------------
'A列、B列の2行目以降を削除
'---------------------------------------------------------------------------------------------
Sub clear()
   If ws1 Is Nothing Then
       Set ws1 = Worksheets("ファイル名変換")
   End If
   ws1.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
End Sub

'---------------------------------------------------------------------------------------------
'フォルダー選択
'---------------------------------------------------------------------------------------------
Private sub FolderSelect()
   With Application.FileDialog(msoFileDialogFolderPicker)
       If .Show = False Then
           MsgBox "キャンセルしました"
           Exit sub
       End If
       FolderPath = .SelectedItems(1)
       FolderPath = FolderPath & "\"
   End With
End sub

友子「うわ…なんだか難しそう。こんなの説明されてもわかるかな…」

武ちゃん「最初は難しいけどね。でもひとつひとつ解説していくからわからなかったら遠慮なく質問して？」

友子「うん。ありがとう。」

武ちゃん「まずは、ここかな」

Option Explicit
   Dim ws1 As Worksheet
   Dim FolderPath As String             'ブックが存在するフォルダパス
   
'----------------------------------------------------------------------------------------------------
'指定のフォルダからエクセルファイルをすべて取得
'----------------------------------------------------------------------------------------------------
Sub FileNameGet()
 
   ThisWorkbook.Activate
   Set ws1 = Worksheets("ファイル名変換")
   ws1.Activate
    
   FolderPath = vbNullString
   'フォルダー選択処理
   Call FolderSelect
   
   'フォルダー選択がキャンセルであれば処理終了
   If FolderPath = vbNullString Then Exit Sub
   
   Dim buf As String
   buf = Dir(FolderPath & "*.xls")

武ちゃん「Option Explicitっていうのは変数宣言をするセクションで、共通で使う変数を宣言するときに使うって今は思ってくれてていいかな。
Sub FileNameGet()からファイル取得処理が開始されて」

   ThisWorkbook.Activate
   Set ws1 = Worksheets("ファイル名変換")
   '"ファイル名変換"のシートをアクティブな状態、選択されている状態にする
   ws1.Activate

武ちゃん「ThisWorkbook.Activateでこのエクセルファイルをアクティブな状態、要するに選択されている状態にする」

武ちゃん「Set ws1 = Worksheets("ファイル名変換")ここは、複数あるワークシートがあると、どのワークシートか判断できないから"ファイル名変換"という名前を指定してws1に代入」

武ちゃん「例えばこんな感じで、複数のワークシートがあった場合、どのワークシート？っていうのが機械が判断できないから、それをこれだよ！っていうのを指示してあげるのよ。
で、ws1.Activateで"ファイル名変換"シートをアクティブ(選択された状態)にしてあげる。そうしないと機械に怒られちゃうから。」

画像20

画像21

 FolderPath = vbNullString
 'フォルダー選択処理
 Call FolderSelect

武ちゃん「変数の初期化処理(変数の中身を空にすること)をした後、次にここなんだけど、独自の"関数"というものを使ってて、ちょっとここの説明は難しいんだけど、簡単に言うと同じ処理が発生した場合に同じコードを書くと手間になるし、エラーを発生させる原因にもなるから、同じ処理は”関数”にやってもらうって感じかな。」
武ちゃん「細かい処理の流れについては今度"関数の錬金術師"の回で詳しく紹介するわ」

友子「（ぇ…?  関数の錬金術師の回…? 何言ってるんだろ…）」

Private Function FolderSelect()

'Application.FileDialogを使用することで次の図で表す画面を表示することができる
'msoFileDialogFolderPickerというものを指定することによってフォルダーを指定することができる

With Application.FileDialog(msoFileDialogFolderPicker)

'以下のIf文でフォルダー選択がキャンセルされた場合には、このファイル取得の処理自体を終了させるためExit Subでこの処理を終わらせるよ。
    If .Show = False Then
       MsgBox "キャンセルしました"
       Exit Sub
    End If
    'If .Show = Falseではない要するにフォルダを選択した場合には
    'SelectedItemsで(1)番目に指定したフォルダのパスを指定する。（今回の場合フォルダは一つしか指定していないので必然的に「1」が入るよ。

    FolderPath = .SelectedItems(1)
    FolderPath = FolderPath & "\"
 End With

武ちゃん「Application.FileDialog(msoFileDialogFolderPicker)の処理で、ダイアログ画面が表示されて、フォルダ選択画面が表示されてOKされると」

画像9

武ちゃん「FolderPath変数にはC:\testのフォルダのパスが入ってくるの。これをする理由はどこのフォルダにあるか？を認識させるためにFolderPathの変数に記憶させてるの。」

画像21

武ちゃん「試しにデバッグして確かめてみるとちゃんとC:\testがFolderPathに入っているのがわかるでしょ？」

友子「本当だ！」

武ちゃん「どんどん行くよ！次は取りたいファイルのフォルダのパス情報が取れたから、そのフォルダにあるエクセル形式のファイル名を全部取りたいわけよ。それが次のコードなんだけど」

Dim buf As String
buf = Dir(FolderPath & "*.xls")
    Dim IntLoop As Integer
    IntLoop = 2
Do While buf <> ""
   ws1.Cells(IntLoop, 1) = buf
   IntLoop = IntLoop + 1
buf = Dir()
Loop

武ちゃん「さっき取得したFolderPathの中にある、拡張子がxls～始まるファイルをDir()っていう関数を使ってxls～始まるファイルが存在すればbuf変数にそのファイル名を入れていくの。」
武ちゃん「具体的には.xls .xlsx .xlsmみたいなやつかな。」
武ちゃん「「*」はワイルドカード検索と言って、xlsから始まる文字列の検索を行うこと。今回のターゲットはエクセルファイルだから*.xlsとしておけば、エクセルファイルすべて取得してくるってわけ。」

友子「なるほどね！逆にワイルドカード検索を入れておかないと、万が一".xls"とか".xlsx"みたいな古い形式と新しい形式の拡張子だった場合にうまく拾ってこれないってことね！」

武ちゃん「そゆこと！」
武ちゃん「次にCドライブにあるファイルの名前を変数に入れることができたのはいいんだけど、それをすべてのファイル数分取ってこなきゃいけないから、ここで繰り返し処理を行って全ファイルの名前を取りに行くよ！」

Dim IntLoop As Integer
IntLoop = 2

Do While buf <> ""
   ws1.Cells(IntLoop, 1) = buf
   IntLoop = IntLoop + 1
buf = Dir()
Loop

武ちゃん「ここなんだけど、Do While buf <> "" ってのは、bufっていう変数が空ではない場合、要するに何かしらの値が入っている場合には、
次の処理を実行するって意味なんだけど、この場合、bufの値が入っている場合には、

1.取得したフォルダの中にあるファイル名をws1("ファイル名変換")シートの、Cells(行数, 列数)に挿入していく。
ws1.Cells(IntLoop, 1) = buf
2.次の行数に対してファイル名を挿入していきたいので、ループ変数に+1する。
IntLoop = IntLoop + 1
3.1個目のファイル確認ができたら、2個目のファイルを、2個目の次は3個目を、とファイルが存在する分繰り返しA列にファイル名を挿入するため、Dir()関数でファイルが存在する限りbufにファイル名を入れていく。
buf = Dir()

武ちゃん「図にするとこんな感じかな」

画像11

武ちゃん「ここまでで一回処理を実行してみようか！VBEの▶のボタンがあるからそこをクリック！」

画像13

武ちゃん「そうするとここの情報が」

画像12

武ちゃん「こうやって取れてきてればOK！」

画像14

友子「おおぉぉぉーーーー！！本当に一瞬だね！」

武ちゃん「そう。楽でしょ？ここまでできたら、今度は取得出来たファイル名を"_202009"みたいな感じでいれたいから、一旦A列をCtrl+Shift+↓で全選択してCtrl+CでコピーしてB列にCtrl+Vで貼り付けしよう」

武ちゃん「そしたら今度はCtrl+Hで文字列の置き換えをする。」

画像16

武ちゃん「B列を選択した状態で置き換えたい文字列を指定してあげて、"すべて置換"をすれば名前を置き換えられるよ」

画像16

画像17

武ちゃん「ここまでで、前準備はOK。あとはこれをフォルダの中にあるファイルに対して変更を加えていくコードを書いていくよ」

Sub FileNameGet()

   If ws1 Is Nothing Then
       Set ws1 = Worksheets("ファイル名変換")
   End If

   '最終行取得変数
   Dim LastRow As Long
   LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

   Dim IntLoop2 As Integer
   If LastRow < 2 Then
       GoTo cancel
   End If
   
   'ファイル変換ボタンを先に押した時の対処
   If FolderPath = vbNullString Then
       MsgBox "フォルダー選択がされておりません"
       Exit Sub
   End If
   
   Dim fso As Object
   Set fso = New FileSystemObject
   
   '繰り返しA列の値を取得
   For IntLoop2 = 2 To LastRow
       '現在のファイル名取得
       OldFileName = ws1.Cells(IntLoop2, 1)
       '変更後のファイル名取得
       NewFileName = ws1.Cells(IntLoop2, 2)
 
       If fso.FileExists(FolderPath & OldFileName) = False Then
           GoTo cancel
       End If
       
       If NewFileName <> "" And OldFileName <> "" Then
           Name FolderPath & OldFileName As FolderPath & NewFileName
       Else
           'ファイル名が入力されていないとき
           GoTo cancel
       End If
   Next IntLoop2
   
   MsgBox "処理が完了しました"

武ちゃん「まず、どうやってフォルダに存在するファイル名を変更していくか？なんだけど、途中細かい確認処理は必要だけどざっくりはこんな感じ」

1.B列の最終行はどこか？を判断させる
2.B列に記載された値を繰り返し処理で適用させる
3.指定したフォルダ内に存在するファイル名をB列で指定した名前に変更→それを最終行まで繰り返し処理する
4.エラー処理を加える

武ちゃん「こんな流れの処理になるから順番に説明していくよ」

If ws1 Is Nothing Then
    Set ws1 = Worksheets("ファイル名変換")
End If

武ちゃん「ここの処理は、エラーを出さない為の処理で、"ファイル名取得"よりも先に"ファイル名変換"の処理を実行しちゃうとエラーで止まっちゃうから、その対処をしている処理だと思ってもらえればOK。
何でエラーになるかと言うと、"ファイル名変換"処理が先に実行されるとws1には何も代入されてない変数として認識されるから、後に出てくる、"ws1.Cells(Rows.Count, 1).End(xlUp).Row"を実行しようと思ったときに、こんな感じでエラーになっちゃうから、それを防いでるの。」

画像22

友子「なるほど。事前に操作する人が困らないように考えられてるんだね。」

武ちゃん「人に使ってもらうときは、急にエラーで止まっちゃうとビックリしちゃうからね。」

Dim LastRow As Long
LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

武ちゃん「次"最終行を取得する"の部分なんだけど、なんで最終行を取得しなきゃいけないかというと、最終行がどこかを指示してあげないと、どこまで繰り返し処理をすればいいの？がわかんないからなんだよね。」

武ちゃん「そのためにまずはLastRowっていう変数を準備しておいて、
ws1.Cells(Rows.Count, 1).End(xlUp).Rowで最終行を判断させるんだけど…」

友子「"ws1.Cells(Rows.Count, 1).End(xlUp).Row"ここの部分がなにやら
呪文のように見えますが…」

武ちゃん「うん。よくわからず"これを書いて置けば最終行を取得できる"って思ってる人が大半なんだけど、これにも当然ちゃんと意味があって、

ws1.
これはWorksheets("ファイル名変換")が代入された変数だから、
"ファイル名変換シートの"を意味してる。
「.（ドット）」を付けることで「～の」を意味する。

Cells(Rows.Count, 1)
Cells(行数,  列数)を意味してて、行数に「Rows.Count」とすることでExcelの行数をカウントをする。
Excelの行数はver2007以降であれば1048576行あって、cells(1048576行, 1列)を意味してる。
End(xlUp).Row
End(xlUp)でExcelの最終行(1048576行)から上に向かって、入力値のあるセルでストップして、その値を取得する.Rowとすればそのセルの行数を取得できる。

武ちゃん「多分この説明だけじゃ分かりづらいから図で説明すると」

画像18

武ちゃん「こんな感じ。下からカウントしていって、行きついた"7"が最終行となり、.Rowとすることで、その行数を取得してLastRowの変数へ行数を代入する」

友子「なるほど！呪文も一つ一つ解読していくとちゃんと読めるようになるんだね。」

武ちゃん「うん。この最終行がどこで生きてくるかというと、繰り返し処理をしたときに、"常に最終行に入力された値まで"を判断してくれるから、便利なんだよね。」

   If LastRow < 2 Then
       GoTo cancel
   End If

武ちゃん「で、次のこのコードなんだけど、LastRowが2より小さい場合は、GoTo cancelってなってるんだけど、この意味については後ほどまた詳しく説明するけど、要するに最終行が2より小さい場合ってのは、2行目以降に何も入力されてない場合ってことだから、その場合にはその次からの処理をスキップさせて処理を終了させるのがここのコードの意味。」

'ファイル変換ボタンを先に押した時の対処
   If FolderPath = vbNullString Then
       MsgBox "フォルダー選択がされておりません"
       Exit Sub
   End If

武ちゃん「ここの処理は仮に"ファイル名変換"の処理を"ファイル名取得処理"のボタンよりも先に押しちゃった場合の対策として、この処理を記述してるの。
初めて"ファイル変換ボタン"が押された場合は、フォルダーのパスが選択されてない状態だと思うから、警告でまだフォルダー選択してませんよ、ということを知らせる為の処理ね」

Dim fso As Object
Set fso = New FileSystemObject​

'繰り返しA列の値を取得
   For IntLoop2 = 2 To LastRow
       '現在のファイル名取得
       OldFileName = ws1.Cells(IntLoop2, 1)
       '変更後のファイル名取得
       NewFileName = ws1.Cells(IntLoop2, 2)

          
       If fso.FileExists(FolderPath & OldFileName) = False Then
           GoTo cancel
       End If

       'FolderPath配下のファイル名を旧⇒新のファイル名に変更する
       Name FolderPath & OldFileName As FolderPath & NewFileName
   Next IntLoop2

武ちゃん「で、そこからやっと繰り返し処理の実行していくんだけど、
A列とB列を繰り返し処理を使って、
2行目から、最終行目までループ変数にセルの値を代入していっていくとこんな感じになるの。」

画像22

友子「Next IntLoop2で2行目が終わったら3行目、3行目が終わったら4行目って感じでLastRowまで繰り返し処理を続けるわけね！」

武ちゃん「そゆこと」

武ちゃん「途中、変更前のファイルの存在確認を行ってからファイル名変換を行っているんだけど、仮にフォルダにあるファイルを消してしまってA列にはファイル名が記載されているのに、実際にファイルが存在しないなどはエラーになっちゃうから、次のコードはその対策ね！」

Dim fso As Object
Set fso = New FileSystemObject

'途中の処理
   
If fso.FileExists(FolderPath & OldFileName) = False Then
    GoTo cancel
End If

画像25

武ちゃん「FileSystemObjectっていうのはファイルやフォルダ全般に関する操作するためのもので、まず事前にVBEの画面からツール→参照設定→Microsoft Scripting Runtimeに✔を付けてOK
これで"Set fso = New FileSystemObject"でFileSystemObjectを操作するためのライブラリが使えるようになるの。」

武ちゃん「If fso.FileExists(FolderPath & OldFileName) = False Then
ここの部分は、fsoはファイルシステムオブジェクトのFileExists要するにファイルが存在しているかどうか？の確認をしてるの。」

武ちゃん「fso.FileExists("どこの何のファイル？")この、どこの何のファイル？の部分にパスと確認したいファイルを指定してあげると、ファイルが存在するときはTrue、存在しない時はFalseが返ってくるから、それを
If fso.FileExists(FolderPath & OldFileName) = False Thenとすることで、"ファイルが存在しない場合にはその後の処理をスキップしてね"ということになるの。」

画像26

武ちゃん「これをやってから変更前のファイル名→変更後のファイル名を実際のファイル名に適用させていくんだけど、"Name"というステートメント(命令文)を使って変換処理を行っているのがここの部分」

Name FolderPath & OldFileName As FolderPath & NewFileName
Nameステートメント
Name 変更したいファイル名 As 変更後のファイル名

武ちゃん「Nameステートメントはパスとファイル名を指定して、どこのファイルの何から何へを指定して、FolderPath & OldFileNameで
C:\test & 〇店舗台帳 → C:\test & 〇店舗台帳_202009って感じでB列に記述されたファイル名に繰り返し変更していくの。」

友子「これでファイルがいくつあろうが、A,B列にファイル名が入力されていれば、B列に指定した名前に全部書き換えてくれていくってわけなんだね！これならたしかに処理はすぐ終わっちゃいそうだね！」

武ちゃん「そうなのよ。だからTwitter界隈の朝活前って言ったでしょ？」

友子「う、うん（ちょっと何言ってるかよくわかんない…）」 

'ファイル名が入力されていれる場合のみ、ファイル名変更処理
       If NewFileName <> "" And OldFileName <> "" Then
           MsgBox "ファイル名を変更します"
       Else
           'ファイル名が入力されていないとき
           GoTo cancel
       End If
   Next IntLoop2

武ちゃん「ここはちょっと細かいんだけど、A列とB列が入力されていることが大前提で、まずはA列とB列に入力された値が空でないか？を確認して、もし空だったら処理を終了させる処理を書いて、"入力されてませんよ"、を促してあげる。」

GoTo cancel
cancel:
   MsgBox "A列またはB列にファイル名が入力されていません。" & vbCrLf & _
          "処理を中断します｡ "

武ちゃん「ここはGoto の後に書かれた文字(ラベル)にジャンプする意味なんだけど、A、B列にの値が空だった場合には、間の処理をすっ飛ばして、メッセージ表示させて処理を終了させてしまうってわけ。」

画像23


Sub clear()
   If ws1 Is Nothing Then
       Set ws1 = Worksheets("ファイル名変換")
   End If
   ws1.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
End Sub

武ちゃん「最後のここはおまけみたいなもんなんだけど、多分これからこのツールを使う時があるかもしれないから、ボタン一つでA、B列をクリアできるように付けた処理」

武ちゃん「ここでもクリアボタンが初めて押された場合にエラーとならないように始めに、ws1がもし空の場合には、"ファイル名変換"シートを代入して、」
武ちゃん「CurrentRegionっていうのはある範囲の入力されたデータのかたまりを取得するって意味で、この場合だとA1セルから入力されている範囲までを選択状態にしてくれるんだけど、もはやここは先代のExcelVBAの神々達のサイトを参考にすると分かりやすいと思う。」

武ちゃん「上記のサイトでCurrentRegionの意味も分かったと思うけど、Offset(1, 0).ClearContentsなんだけど、
まず、Offsetっていうのはセルの選択位置をずらす時に使うのよ。」

Offset(行方向, 列方向）

武ちゃん「つまりA1を選択状態にしたら、1行ずらしてA2行目から下全部をClearContentsでセルに入力された値を削除するってことをしてるの。」

画像24

友子「でもボタン1つで削除できるのは楽だからあると便利な機能だよね。」

武ちゃん「最後にそのボタンの配置方法についてなんだけど、Excelの上の部分に"開発"というタブが表示されてなかったら、ファイルからオプション→リボンのユーザー設定→メインタブの開発に✔を付けるを表示されるようになって」

画像26

画像27

武ちゃん「開発タブの挿入→ボタンを選択」

画像28

武ちゃん「こんな感じでボタンを作って、テキストを適当な名称に変えて」

画像29

画像34

武ちゃん「そのボタンの処理しているものをリストから選択してOKすればマクロの登録が完了。
例えばクリアボタンにクリア処理のマクロを登録する場合には、clearを選択すればOK」

画像31

画像32

画像33

武ちゃん「ということで穴呂具に渡してやったら、鳩が豆鉄砲食らったような顔してたから、またなんかあったら相談してね！」
友子「うん。ありがとう！本当に助かったよ～」

とこのような形で、無事経理からの急な指示をVBAで回避できました。
皆さんも似たような経験がないでしょうか？

今回の内容がよかったとか、面白かったと思いましたら、いいねやフォロー、またご意見ご感想などもお待ちしておりますので、ご連慮なくご連絡ください。

Twitterでもこんなことをつぶやいてたりしますのでご興味ある方ごらんください。


また、ブログも運営しております。
普段プログラミング学習で思うようにできず、悩んでいる、学習の仕方がわからない、エンジニアってどんなことしているの、など
プログラミングにおけるマインドについて情報発信しております。




この記事が気に入ったらサポートをしてみませんか？