エクセルVBA100本ノック。15本目:シートの並べ替え
この記事から得るもの
日付が入っているSheet名を並び替える。
Sheet名に入っているのは日付かどうか判断する方法。
1 今回のお題
「2020年04月」から「2021年03月」のシートがあります。
しかし、シートの順番が狂ってしまっています。
「2020年04月」から昇順に並べ替えてください。
・上記以外のシートは存在しません。
・シート名は「yyyy年mm月」これで統一されています。
2 今回のお題の意図
うっかりとっあん坊やが、Sheetの入れ替えをしても綺麗に並び替えれるようにする。
3 回答
シートを並べ替える!?そんなこと今まで考えたこともありませんでした。しかし、年間の売上表を管理しているようなBookがあるやもしれません。何よりお題に出るというとは、実際にそんな事例があるということ。
しかし、今回は全く思いつかず。サイトの回答をカンニングしました。
サイト管理者の回答
Sub VBA100_15_01() Const startYM As Date = #4/1/2020# Dim i As Long On Error Resume Next '万一のシート抜け対応 For i = 1 To 12 Sheets(Format(DateAdd("m", i - 1, startYM), "yyyy年mm月")).Move After:=Sheets(Sheets.Count) Next Sheets(1).Select End Sub
なるほど!!。まず基準日を作る(#4/1/2020#)。その後、ループの中でSheet名に対してFormat、DateAddでSheet名を指定して一番右(After:=Sheets(Sheets.Count))にもっていく。こうすればよかったのか。
とても実用的なコードですね。
4 日付データ(”yyyy年mm月”)の判断
しかし、ふと疑問が。過去に経験があったのですが、yyyyもしくはmmを「全角数字」で入れる人が存在しました。その点をを考慮して以下のコードを追加してみました。
日付かどうか判断する処理を加えたコード
Sub VBA100_15_2() Const startYM As Date = #4/1/2020# Dim i As Long On Error Resume Next '万一のシート抜け対応 'シート名を半角へ変換。表示型式の統一 For Each ws In ThisWorkbook.Sheets ws.Name = StrConv(ws.Name, vbNarrow) '表示型式が日付型なら"yyyy年mm月"にする。 If IsDate(ws.Name) Then: ws.Name = Format(ws.Name, "yyyy年mm月") Next For i = 1 To 12 Sheets(Format(DateAdd("m", i - 1, startYM), "yyyy年mm月")).Move After:=Sheets(Sheets.Count) Next Sheets(1).Select End Sub
まず、StrConv関数でSheet名を半角にします。その後、Sheet名が日付型であればFormat("yyyy年mm月")にSheet名を変換します。
こうすることで、お題の前提である「"yyyy年mm月"」が入っているSheetにすることができます。
しかし、ツイートでこんな意見をもらいました。
「シート名の最後に半角スペースが入ってました」
これは、まさかの行動。でもあり得る。なのでSheet名をTrimした後に日付の判定を行った方がいいのだなと思いました。
しかし、実際半角スペースを入れて挙動を確認したところ、IsDate関数では日付と認識されSheet名から半角スペースもなくなっていました。
Trimは必要ないようです。やってみないとわからないことってありますね。
5 一言
今回のお題でわかったことは、Format関数が便利ということです。
Sheets(Format(DateAdd("m", i - 1, startYM), "yyyy年mm月")).Move など、Sheets()の中でFormatを使うとは思いつきもしませんでした。
勉強になりまくりの回でした。
ではでは、このへんで(^^)/~~~