この記事では、条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる方法についてご説明します。
今回の記事でご紹介するマクロは、以下のマクロを活用しています。
【ExcelVBA】セルの値を横棒グラフで表示させるには(条件付き書式のデータバーを使用)
上の記事でもご説明していますが、今回扱う横棒グラフとは、条件付き書式のデータバーのことを指します。
下の画像が条件付き書式のデータバーが出力された結果です。
見た目は横棒グラフのように見えて、ファイルサイズの値の大小が確認しやすくなっているのが特徴です。
【動画】条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは指定されたトップディレクトリ配下にあるすべてのフォルダ内のファイルをすべて取得します。
次に、取得したファイルの名前とファイルサイズをExcelのシートに出力します。
ファイルサイズが出力されたセルの値を参照し、条件付き書式のデータバーを使ってセルの値を横棒グラフでE列に表示させています。
マクロ作成の流れ
今回のマクロでは次の設定を行います。
・データバーが表示されているセルには値を表示させない
・データバーの最大値(今回は100)と最小値(今回は0)を設定する
・データバーに色を設定する(今回はオレンジ色)
・データバーを塗りつぶし設定にする
Excelファイルの例
今回は次のExcelファイルを用意しました。
A2の黄色いセルに、ファイルサイズを取得したいファイルのトップフォルダのパスを入力して実行ボタンをクリックすると、下のとおりにフォルダにあるファイルサイズが横棒グラフで表示されます。
ちなみに、トップフォルダ配下にあるフォルダ内のファイルは下のとおりです。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim aryCnt As Integer '配列用カウンタ
Dim ws As Worksheet 'ワークシート用変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim filePath As String 'ファイルのパスを格納する変数
Dim cnt As Integer 'カウンタ用変数
Dim fileAry() As String 'ファイル名の格納用配列
Dim file As Variant 'ファイル用変数
Dim valRng As range 'Rangeオブジェクト格納用変数(ファイルのサイズと項番用)
Dim dataRng As range 'Rangeオブジェクト格納用変数(データ出力範囲用)
Dim maxVal As Double 'データバーの最小値用変数
'カウンタを初期化する
aryCnt = 0
'データバーの最小値を設定
Const minVal As Double = 0
'データを出力する開始行位置を設定
Const bgnLPos As Long = 8
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'セルをクリアする
ws.range("A" & bgnLPos & ":CA1048576").ClearContents
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
'データを出力する開始行位置の値でカウンタを初期化する
cnt = bgnLPos
'ファイルを検索するサブルーチンを呼び出す
Call fileSearch(filePath, fileAry, aryCnt)
'フォルダ内のファイルの数分処理を行うFor文
For Each file In fileAry
'B列にファイルパスを出力する
ws.range("B" & cnt).Value = fso.GetFile(file).ParentFolder.Path
'C列にファイル名を出力する
ws.range("C" & cnt).Value = fso.GetFile(file).Name
'ファイルサイズをメガバイト単位でD列に出力する(小数点以下8桁まで)
ws.range("D" & cnt).Value = Format(fso.GetFile(file).Size / 1024 / 1024, "0.00000000")
cnt = cnt + 1
Next file
'データバーを表示させる(E列の)セルの範囲を取得する
Set valRng = ws.range("E" & bgnLPos & ":E" & bgnLPos + UBound(fileAry))
'セルに計算式を設定する(D列のセルの値を参照する数式)
valRng.Formula = "=D" & bgnLPos
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
'B列からE列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":E" & bgnLPos + UBound(fileAry))
'ファイルサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(3), Order1:=xlDescending, Header:=xlNo
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
'A列の項番を表示させるセルの範囲を取得する
Set valRng = ws.range("A" & bgnLPos & ":A" & bgnLPos + UBound(fileAry))
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & bgnLPos - 1
End Sub
Sub fileSearch(FolderPath As String, fileAry() As String, aryCnt As Integer)
Dim fso As Object 'FileSystemObjectオブジェクトのインスタンス用変数
Dim folder As Object 'フォルダ用変数
Dim FileItem As Object '取得したファイル用変数
Dim fTypeCnt As Integer '配列fTypeのカウンタ用変数
Dim SubFolder As Object 'サブフォルダ用変数
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set folder = fso.GetFolder(FolderPath)
'フォルダ内にあるファイルの数分処理を繰り返す
For Each FileItem In folder.files
'動的配列の再宣言を行う(配列に格納されている値を残したまま)
ReDim Preserve fileAry(aryCnt)
'ファイルのフルパスを配列に格納する
fileAry(aryCnt) = FileItem.Path
aryCnt = aryCnt + 1
Next FileItem
'パスのサブフォルダを対象にループする
For Each SubFolder In folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.Path, fileAry, aryCnt)
Next SubFolder
End Sub
注目すべきコード①
最初に見て頂きたいのは34行目から48行目です。
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
コードの説明
以上のコードは、ファイルの置き場のパスを取得するコードです。
トップフォルダのパスは、A2のセル(dirPathという名前付きセル)に入力されたパスから取得しています。
※A2の黄色のセルには「dirPath」という名前を付けています。
コードの詳細
34行目のコードは、A2セルに入力されたパスの末尾に「¥」の文字が含まれているか判定するIFです。
もし「¥」の文字が含まれている場合は、39行目で変数filePathにそのままパスを格納します。
「¥」の文字が含まれていない場合は、46行目でパスの末尾に「¥」を付けて変数filePathに格納します。
注目すべきコード②
次に見て頂きたいのは54行目です。
'ファイルを検索するサブルーチンを呼び出す
Call fileSearch(filePath, fileAry, aryCnt)
コードの説明
以上のコードは、サブフォルダ含めて全てのフォルダ内のファイルを検索するサブルーチン「fileSearch」を呼び出す処理です。
「fileSearch」には以下の引数を渡して呼び出します。
- filePath：ファイル名の検索先フォルダ
- fileAry：ファイル名のフルパスを格納する配列
- aryCnt：配列fileAryの要素数用のカウンタ変数
filePath
filePathにはファイル名の検索先フォルダを指定します。
filePathで指定されたフォルダを「fileSearch」に渡し、「fileSearch」内でファイルが存在するか検索します。
fileAry
fileAryは、サブルーチン「fileSearch」内で取得するファイルのフルパスを格納する配列です。
aryCnt
aryCntは、配列fileAryの要素数用のカウンタ変数です。
注目すべきコード③
次に見て頂きたいのは126行目から129行目です。
'FileSystemObjectオブジェクトのインスタンスを作成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set folder = fso.GetFolder(FolderPath)
コードの説明
以上のコードは、FileSystemObjectのインスタンスを生成し、ファイルを検索したいフォルダのトップ階層のパスのフォルダを取得する処理のコードです。
FileSystemObjectのインスタンスのGetFolderメソッドに、ファイルを検索したいフォルダのトップ階層のパスを指定して実行することで、パス内のファイル名を取得できます。
注目すべきコード④
次に見て頂きたいのは132行目から142行目です。
'フォルダ内にあるファイルの数分処理を繰り返す
For Each FileItem In folder.files
'動的配列の再宣言を行う(配列に格納されている値を残したまま)
ReDim Preserve fileAry(aryCnt)
'ファイルのフルパスを配列に格納する
fileAry(aryCnt) = FileItem.Path
aryCnt = aryCnt + 1
Next FileItem
コードの説明
以上のコードは、フォルダにあるファイル名を取得して配列fileAryに格納する処理のコードです。
コードの詳細
132行目のコードは、フォルダ内にあるファイルの数分処理を繰り返すFor文です。
135行目で配列fileAryを再定義し、138行目でファイルのフルパスを取得して配列fileAryに格納します。
注目すべきコード⑤
次に見て頂きたいのは145行目から150行目です。
'パスのサブフォルダを対象にループする
For Each SubFolder In folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.Path, fileAry, aryCnt)
Next SubFolder
コードの説明
以上のコードは、サブフォルダが存在する間は自分自身のサブルーチンを繰り返し呼び出す再帰処理を行っているコードです。
再帰処理とは、サブルーチンの処理が行われている途中で、強制的に自分自身のサブルーチンを呼び出して再度サブルーチンの最初から処理を行わせることを言います。
強制的に自分自身のサブルーチンを呼び出しているのは148行目です。
148行目でfileSearchが呼び出されると、fileSearchの処理の途中で117行目(fileSearchの最初)から強制的に開始されます。
この再帰処理(fileSearch処理中の再呼び出し)は、145行目のFor文内で繰り返し行われますが、このFor文が終わらないかぎりfileSearchが再度呼び出されます。
なお、For文から抜ける条件は、すべてのサブフォルダの参照が終わることです。(引数にサブフォルダを指定してfileSearchを呼び出す)
すべてのサブフォルダを参照すればFor文を抜けます。
For文から抜ければfileSearchが呼び出されることがなくなるので(再帰処理が行われない)、fileSearchのEnd Subまで処理が進みfileSearchの処理を抜けて、fileSearchの呼び出し元の54行目に遷移します。
そのまま54行目の次の行に処理が遷移します。
注目すべきコード⑥
次に見て頂きたいのは57行目から70行目です。
'フォルダ内のファイルの数分処理を行うFor文
For Each file In fileAry
'B列にファイルパスを出力する
ws.range("B" & cnt).Value = fso.GetFile(file).ParentFolder.Path
'C列にファイル名を出力する
ws.range("C" & cnt).Value = fso.GetFile(file).Name
'ファイルサイズをメガバイト単位でD列に出力する(小数点以下8桁まで)
ws.range("D" & cnt).Value = Format(fso.GetFile(file).Size / 1024 / 1024, "0.00000000")
cnt = cnt + 1
Next file
コードの説明
以上のコードは、指定されたファイルの置き場にあるフォルダ内のファイル名とファイルパス、そしてファイルサイズをすべて取得してExcelのシートに出力する処理のコードです。
コードの詳細
57行目のコードは、指定されたファイルの置き場にあるファイルの数だけ処理を繰り返すFor文です。
60行目のコードでは、指定されたファイルの置き場にあるファイルのパスをB列に出力します。
63行目のコードでは、指定されたファイルの置き場にあるファイル名をC列に出力します。
66行目のコードでは、指定されたファイルの置き場にあるファイルのサイズをC列に出力します。
なお、ファイルのサイズはMB単位で計算し、その値を小数点以下8桁までとして表示させています。
注目すべきコード③
次に見て頂きたいのは73行目から76行目です。
'データバーを表示させる(E列の)セルの範囲を取得する
Set valRng = ws.range("E" & bgnLPos & ":E" & bgnLPos + UBound(fileAry))
'セルに計算式を設定する(D列のセルの値を参照する数式)
valRng.Formula = "=D" & bgnLPos
コードの説明
以上のコードは、データバーを表示させる(E列の)セルの範囲を取得するコードです。
データバーを表示させるのは、列はE列で先頭行はbgnLPosの値(今回は8行目)を行位置とし、その行から下にデータ件数の値の行位置まで(最終行)としています。
また、上の画像のとおりD列の値を、E列のセルにグラフ表示させたいので、76行目でファイルサイズが出力されているD列のセルに対して「”=D” & bgnLPos」の数式を設定しています。(bgnLPosはデータの開始行の行位置。(今回のマクロでは「=D8」))
この数式の設定は、左隣のセルを参照するために行います。
D列のセルはファイルサイズの値を、E列は隣のD列のファイルサイズの値をグラフ表示したいので左隣のセルを参照するよう「”=D” & bgnLPos」と数式を設定しています。
ちなみに、データの最終行までのセルに「”=D” & bgnLPos」の数式を設定すると、D列データが存在している行までのセルすべてに「”=D” & bgnLPos」が設定されてしまい、すべてD列のファイルサイズが入っている先頭行の値を参照するのでは？と思われるかもしれませんが、そうはならずに例えばE9のセルなら「=D9」、E19のセルなら「=D19」と設定されます。(相対参照により、E列それぞれの左隣のセルを参照する)
注目すべきコード④
次に見て頂きたいのは79行目です。
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
コードの説明
以上のコードは、D列に出力されたファイルサイズから最大値を取得するコードです。
WorksheetFunction.Maxメソッドに、ファイルサイズが入ったセルの範囲を引数に指定することで、最大のファイルサイズを取得することができます。
最大のファイルサイズはデータバーの表示も最大にするため、最大値を取得します。
ちなみに、最大値のファイルサイズのグラフは、セルいっぱいに表示されます。
注目すべきコード⑤
次に見て頂きたいのは82行目から85行目です。
'B列からE列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":E" & bgnLPos + UBound(fileAry))
'ファイルサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(3), Order1:=xlDescending, Header:=xlNo
コードの説明
以上のコードは、B列からE列のセル範囲を取得し、3列目(D列)の値で降順にソートする処理のコードです。
今回はファイルサイズが大きい順で表示させるためファイルサイズをキーに降順でソートしています。
注目すべきコード⑥
次に見て頂きたいのは88行目です。
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
コードの説明
以上のコードは、既存の条件付き書式設定を削除する処理のコードです。
条件付き書式のデータバーを表示させるセルに対して、既存の条件付き書式設定を削除しておきます。
注目すべきコード⑦
次に見て頂きたいのは90行目から107行目です。
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
コードの説明
以上のコードは、条件付き書式のデータバーの表示とデータバーの各設定を行う処理のコードです。
コードの詳細
90行目のコードは、条件付き書式のデータバーを表示させるコードです。
93行目のコードでは、データバー内に値を表示しないよう設定します。
なお、Trueならデータバー内に値を表示させます。
96行目のコードではデータバーの最小値を、99行目のコードではデータバーの最大値を設定します。
今回のコードでは、最小値と最大値を以下のコードで設定しています。(最小値は0、最大値はファイルサイズの最大値)
'データバーの最小値を設定
Const minVal As Double = 0
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
107行目のコードでは、データバーの色オレンジに設定します。
105行目のコードでは、データバーを塗りつぶし状態に設定します。
動作確認
「Excelファイルの例」をご覧ください。
最後に
この記事では、条件付き書式のデータバーを使ってサブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させる方法についてご説明しました。
条件付き書式のデータバーを使うと、ファイルサイズの大小が見て分かりやすいようになります。
条件付き書式のデータバーを使って、サブフォルダ含めて全フォルダ内のファイルのサイズを横棒グラフに表示させたい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
