この記事では、条件付き書式のデータバーを使ってフォルダ直下にある全フォルダのサイズを横棒グラフに表示させる方法についてご説明します。
今回の記事でご紹介するマクロは、以下のマクロを活用しています。
【ExcelVBA】セルの値を横棒グラフで表示させるには(条件付き書式のデータバーを使用)
上の記事でもご説明していますが、今回扱う横棒グラフとは、条件付き書式のデータバーのことを指します。
下の画像が条件付き書式のデータバーが出力された結果です。
見た目は横棒グラフのように見えて、フォルダサイズの値の大小が確認しやすくなっているのが特徴です。
【動画】条件付き書式のデータバーを使ってフォルダ直下にある全フォルダのサイズを横棒グラフに表示させる実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは指定されたフォルダ直下のフォルダ名とフォルダのサイズをすべて取得します。
次に、取得したフォルダの名前とフォルダサイズをExcelのシートに出力します。
フォルダサイズが出力されたセルの値を参照し、条件付き書式のデータバーを使ってセルの値を横棒グラフでD列に表示させています。
マクロ作成の流れ
今回のマクロでは次の設定を行います。
・データバーが表示されているセルには値を表示させない
・データバーの最大値(今回は100)と最小値(今回は0)を設定する
・データバーに色を設定する(今回はオレンジ色)
・データバーを塗りつぶし設定にする
Excelファイルの例
今回は次のExcelファイルを用意しました。
A2の黄色いセルに、フォルダサイズを取得したいパスを入力して実行ボタンをクリックすると、下のとおりにフォルダにあるフォルダサイズが横棒グラフで表示されます。
ちなみに、上記マクロが参照したフォルダ直下にあるフォルダは下のとおりです。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim rowNum As Long '行数用変数
Dim ws As Worksheet 'ワークシート用変数
Dim rowMax As Long 'Excelのシートの最大行数
Dim folderPath As String 'フォルダのパスを格納する変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim folder As Object 'フォルダ用変数
Dim subfolder As Object 'サブフォルダ用変数
Dim valRng As range 'Rangeオブジェクト格納用変数(フォルダのサイズと項番用)
Dim maxVal As Double 'データバーの最小値用変数
Dim dataRng As range 'Rangeオブジェクト格納用変数(データ出力範囲用)
Const bgnLPos As Long = 8 'フォルダ名・フォルダサイズを出力するセルの先頭行
'データバーの最小値を設定
Const minVal As Double = 0
'フォルダ名・フォルダサイズを出力するセルの先頭行を取得する
rowNum = bgnLPos
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'Excelのシートの最大行数を取得する
rowMax = ActiveSheet.Rows.Count
'セルをクリアする
ws.range("A" & bgnLPos & ":D" & rowMax).ClearContents
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「フォルダのパス」の末尾に「\」が付いている場合
'「フォルダのパス」を変数folderPathに格納する
folderPath = ws.range("dirPath").Value
Else
'入力された「フォルダのパス」の末尾に「\」が付いていない場合
'「フォルダのパス」の末尾に「\」を付けて、パスを変数folderPathに格納する
folderPath = ws.range("dirPath").Value & "\"
End If
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
'Folderオブジェクトを取得する
Set folder = fso.GetFolder(folderPath)
'フォルダ内のフォルダの数だけ処理を繰り返すFor文
For Each subfolder In folder.SubFolders
If (subfolder.Attributes And vbHidden) = 0 Then
'隠しフォルダ以外の場合
If IsNumeric(subfolder.Name) Then
'フォルダ名が数値の場合
'フォルダ名をシートに出力する
ws.Cells(rowNum, 2) = "'" & CStr(subfolder.Name)
Else
'フォルダ名が数値以外の場合
'フォルダ名をシートに出力する
ws.Cells(rowNum, 2) = subfolder.Name
End If
'フォルダのサイズをシートに出力する
ws.Cells(rowNum, 3) = Format(subfolder.Size / 1024 / 1024, "0.00000000")
rowNum = rowNum + 1
End If
DoEvents
Next subfolder
'データバーを表示させる(D列の)セルの範囲を取得する
Set valRng = ws.range("D" & bgnLPos & ":D" & bgnLPos + folder.SubFolders.Count - 1)
'セルに計算式を設定する(C列のセルの値を参照する数式)
valRng.Formula = "=C" & bgnLPos
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
'B列からD列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":D" & bgnLPos + folder.SubFolders.Count - 1)
'フォルダサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(2), Order1:=xlDescending, Header:=xlNo
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を100に設定する
.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 + folder.SubFolders.Count - 1)
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & bgnLPos - 1
'後処理
Set subfolder = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは33行目から47行目です。
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「フォルダのパス」の末尾に「\」が付いている場合
'「フォルダのパス」を変数folderPathに格納する
folderPath = ws.range("dirPath").Value
Else
'入力された「フォルダのパス」の末尾に「\」が付いていない場合
'「フォルダのパス」の末尾に「\」を付けて、パスを変数folderPathに格納する
folderPath = ws.range("dirPath").Value & "\"
End If
コードの説明
以上のコードは、フォルダのパスを取得するコードです。
フォルダのパスは、A2のセル(dirPathという名前付きセル)に入力されたパスから取得しています。
※A2の黄色のセルには「dirPath」という名前を付けています。
コードの詳細
33行目のコードは、A2セルに入力されたパスの末尾に「¥」の文字が含まれているか判定するIFです。
もし「¥」の文字が含まれている場合は、38行目で変数filePathにそのままパスを格納します。
「¥」の文字が含まれていない場合は、45行目でパスの末尾に「¥」を付けて変数filePathに格納します。
注目すべきコード②
次に見て頂きたいのは50行目から53行目です。
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
'Folderオブジェクトを取得する
Set folder = fso.GetFolder(folderPath)
コードの説明
以上のコードは、FileSystemObjectのインスタンスを生成し、フォルダ名とフォルダサイズを検索したいフォルダのパスを取得する処理のコードです。
FileSystemObjectのインスタンスのGetFolderメソッドに、フォルダを検索したいパスを指定して実行することで、フォルダ内のフォルダ名を取得できます。
注目すべきコード③
次に見て頂きたいのは56行目から87行目です。
'フォルダ内のフォルダの数だけ処理を繰り返すFor文
For Each subfolder In folder.SubFolders
If (subfolder.Attributes And vbHidden) = 0 Then
'隠しフォルダ以外の場合
If IsNumeric(subfolder.Name) Then
'フォルダ名が数値の場合
'フォルダ名をシートに出力する
ws.Cells(rowNum, 2) = "'" & CStr(subfolder.Name)
Else
'フォルダ名が数値以外の場合
'フォルダ名をシートに出力する
ws.Cells(rowNum, 2) = subfolder.Name
End If
'フォルダのサイズをシートに出力する
ws.Cells(rowNum, 3) = Format(subfolder.Size / 1024 / 1024, "0.00000000")
rowNum = rowNum + 1
End If
DoEvents
Next subfolder
コードの説明
以上のコードは、指定したフォルダ直下にあるフォルダ名とフォルダサイズを取得する処理のコードです。
コードの詳細
56行目のコードは、フォルダ内にあるフォルダの数だけ処理を繰り返すFor文です。
もしフォルダが3つある場合は、3回処理を繰り返します。
58行目のコードは、フォルダが隠しフォルダかどうかを判定するIF文です。
隠しフォルダの場合はフォルダサイズが取得できない場合があるので、今回のマクロでは対象外にしています。
なお、フォルダ名をセルに出力する際、「0」で始まる数値が並んだフォルダ名だと正しく出力されません。
「0」で始まる数値が並んだフォルダ名というのは「00123」のようなフォルダ名です。
どんなフォルダ名かというと、「00123」のような頭に「0」が付いているフォルダ名です。(62行目のIFで、フォルダ名が数値に該当する場合)
フォルダ名が「00123」の場合、そのままセルに出力させると、頭の「0」が削除されて「123」と表示されてしまいます。
頭の「0」が削除されないよう「00123」とセルに出力させるためには、フォルダ名の頭に「’」(カンマ)を付けます。
フォルダ名の頭に「’」(カンマ)を付けることで、例えばフォルダ名が「00123」という名前だった場合でも「123」とはならず「00123」とセルに出力されます。
フォルダ名が数値以外の場合は74行目でフォルダ名をセルに出力します。
79行目のコードは、フォルダサイズを取得しシートに出力します。
なお、フォルダのサイズはMB単位で計算し、その値を小数点以下8桁までとして表示させています。
注目すべきコード④
次に見て頂きたいのは90行目から93行目です。
'データバーを表示させる(D列の)セルの範囲を取得する
Set valRng = ws.range("D" & bgnLPos & ":D" & bgnLPos + folder.SubFolders.Count - 1)
'セルに計算式を設定する(C列のセルの値を参照する数式)
valRng.Formula = "=C" & bgnLPos
以上のコードは、データバーを表示させる(D列の)セルの範囲を取得するコードです。
データバーを表示させるのは、列はE列で先頭行はbgnLPosの値(今回は8行目)を行位置とし、その行から下にデータ件数の値の行位置まで(最終行)としています。
また、上の画像のとおりC列の値を、D列のセルにグラフ表示させたいので、93行目でフォルダサイズが出力されているD列のセルに対して「”=C” & bgnLPos」の数式を設定しています。(bgnLPosはデータの開始行の行位置。(今回のマクロでは「=C8」))
この数式の設定は、左隣のセルを参照するために行います。
C列のセルはフォルダサイズの値を、D列は隣のC列のフォルダサイズの値をグラフ表示したいので左隣のセルを参照するよう「”=C” & bgnLPos」と数式を設定しています。
ちなみに、データの最終行までのセルに「”=C” & bgnLPos」の数式を設定すると、C列データが存在している行までのセルすべてに「”=C” & bgnLPos」が設定されてしまい、すべてC列のフォルダサイズが入っている先頭行の値を参照するのでは？と思われるかもしれませんが、そうはならずに例えばD8のセルなら「=C9」、D10のセルなら「=C10」と設定されます。(相対参照により、D列それぞれの左隣のセルを参照する)
注目すべきコード⑤
次に見て頂きたいのは96行目です。
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
コードの説明
以上のコードは、D列に出力されたフォルダサイズから最大値を取得するコードです。
WorksheetFunction.Maxメソッドに、フォルダサイズが入ったセルの範囲を引数に指定することで、最大のフォルダサイズを取得することができます。
最大のフォルダサイズはデータバーの表示も最大にするため、最大値を取得します。
ちなみに、最大値のフォルダサイズのグラフは、セルいっぱいに表示されます。
注目すべきコード⑥
次に見て頂きたいのは98行目から102行目です。
'B列からD列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":D" & bgnLPos + folder.SubFolders.Count - 1)
'フォルダサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(2), Order1:=xlDescending, Header:=xlNo
コードの説明
以上のコードは、フォルダサイズで降順にソートさせる処理のコードです。
B列からD列のデータに対し、フォルダサイズをキーにしてソートさせます。
コードの詳細
99行目のコードは、ソートさせる対象のデータの範囲がデータの入ったB列からD列なので、データが入ったB列からD列の範囲を変数dataRngに格納します。
102行目のコードは、フォルダサイズでソートする処理のコードです。
フォルダサイズでソートするので、B列をはじめの列として2列目(フォルダサイズ)でソートするので、Columnsプロパティの引数に2を指定しています。
注目すべきコード⑦
次に見て頂きたいのは105行目です。
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
コードの説明
以上のコードは、既存の条件付き書式設定を削除する処理のコードです。
条件付き書式のデータバーを表示させるセルに対して、既存の条件付き書式設定を削除しておきます。
注目すべきコード⑧
次に見て頂きたいのは107行目から124行目です。
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を100に設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
コードの説明
以上のコードは、条件付き書式のデータバーの表示とデータバーの各設定を行う処理のコードです。
コードの詳細
107行目のコードは、条件付き書式のデータバーを表示させるコードです。
110行目のコードでは、データバー内に値を表示しないよう設定します。
なお、Trueならデータバー内に値を表示させます。
113行目のコードではデータバーの最小値を、116行目のコードではデータバーの最大値を設定します。
今回のコードでは、最小値と最大値を以下のコードで設定しています。(最小値は0、最大値はフォルダサイズの最大値)
'データバーの最小値を設定
Const minVal As Double = 0
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
119行目のコードでは、データバーの色オレンジに設定します。
122行目のコードでは、データバーを塗りつぶし状態に設定します。
注目すべきコード⑫
次に見て頂きたいのは127行目から130行目です。
'A列の項番を表示させるセルの範囲を取得する
Set valRng = ws.range("A" & bgnLPos & ":A" & bgnLPos + folder.SubFolders.Count - 1)
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & bgnLPos - 1
コードの説明
以上のコードは、A列のセルに項番を設定する処理のコードです。
コードの詳細
127行目のコードでは、項番を出力したいセルの範囲を取得します。
130行目のコードでは、127行目のコードで取得した項番を出力したいセルの範囲に対して数式row()を設定しています。
数式row()は、数式row()が入力された行位置を取得します。
例えば8行目に数式row()が入力されると、8の値を返します。
今回は8行目がフォルダサイズを出力する先頭行が8行目なので、数式row()の返す値からbgnLPos(8)の値を1つマイナスした値(7)をマイナスします。
これでA列の8行目は1が表示されます。9行目以降も項番が表示されるようになります。
動作確認
「Excelファイルの例」をご覧ください。
最後に
この記事では、条件付き書式のデータバーを使ってフォルダ直下にある全フォルダのサイズを横棒グラフに表示させる方法についてご説明しました。
どのフォルダが容量大きいのか確認するのに、条件付き書式のデータバーを使って表示させると直感的に確認しやすくて便利なので、本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
