VBAでテーブル化と好みのテーブルスタイル設定のマクロ化に取り組んでいます。
前回書いたフローチャートをもとに、早速コーディングしていきます。
【Excel VBA】テーブル変換とスタイル変更 ~処理の流れを整理してみる~ - ゆるおたノート
今回は、テーブル(ListObject
オブジェクト*1)を生成するところまで書きました。
進捗
挑戦!!と付いているモノが本記事のテーマです。
挑戦!!選択範囲をテーブル化
挑戦!!指定範囲にテーブルがあると「実行時エラー」が発生するので、エラーが出てから
On Error ~
で「テーブルの解除」に進んだ方が良いかも。ListObject.Addメソッドの戻り値で既定の名前も選択可。
※こちら↑は誤りでした!!大変申し訳ありません!!!!汗*2
しましまの無い、罫線・見出しだけのテーブルスタイルを作る
- テーブルスタイル関連のオブジェクト
TableStyle
オブジェクトTableStyleElement
オブジェクト /TableStyleElements
コレクション
- テーブルスタイル関連のオブジェクト
ブックの既定のスタイルに登録する
保存先をダイアログで指定
クラスで共通処理を分離
ユーザーフォームで操作簡略化
- (もし出来れば)色は自由に選べるようにしたい
コード
Module1:ConvertIntoTable
Mainプロシージャ
On Error GoTo ~
はなるべく使いたくないので、一旦canContinueProcess()
関数で状況を判定してから先に進めます。
このまま行けば、Main
プロシージャの長さはほぼ1画面に納められそう。(my PC調べ)
Option Explicit Public Sub Main() '念のため変数に入れておく Dim myRange As Range: Set myRange = Selection Dim myBook As Workbook: Set myBook = ActiveWorkbook Dim mySheet As Worksheet: Set mySheet = myBook.ActiveSheet 'エラー対策 If canContinueProcess(myRange) = False Then Exit Sub '=続行不能なら処理は中止する End If Dim listObj As ListObject Set listObj = convertRangeIntoTable(mySheet, myRange) Set myRange = Nothing ' '~コーディング中~ ' MsgBox "処理が完了しました。" End Sub
選択範囲にテーブルがあるかチェック
If文などの制御構文は、可能な限りネストさせないようにすると可読性が上がってコードの安全性も向上するので*3、単純なIf文2つで書いています。
ただし、そうすることで条件式が二重否定(Not ~ = False
など)になってしまうような時は、かえって読みづらくなりがちなので、ほかの表現を考えます。
例えば肯定文に書き換えるとか。
Private Function canContinueProcess(ByRef targetRange As Range) As Boolean If targetRange.ListObject Is Nothing = True Then canContinueProcess = True Exit Function End If If MsgBox("指定範囲にテーブルがあります。" & vbCrLf & _ "こちらをすべて解除してもよろしいですか?", vbYesNo) = vbYes Then Call unlistAllTables(targetRange) canContinueProcess = True Exit Function End If MsgBox "かしこまりました。処理を中止します。" canContinueProcess = False End Function
選択範囲上あるテーブルは解除する
前回の記事で書いた通り、.TableStyle
プロパティに空文字""
を代入するとテーブルのスタイルを「まっさら」状態に出来ます。
Private Sub unlistAllTables(ByRef targetRange As Range) Do With targetRange.ListObject .TableStyle = "" '(お好みで)テーブルのスタイルも初期化 .Unlist End With Loop Until targetRange.ListObject Is Nothing MsgBox "解除しました。" End Sub
Module2:createListObj
ここから本番です。
処理のテーマが変わるので、念のためモジュールを分離しました。
※Private
なプロシージャをモジュール分けするときは、モジュールの宣言セクションにOption Private Module
と書くと、ツールバーの選択肢に上がらなくなります!すごい…!*4
Option Explicit Option Private Module '他モジュールからアクセス可能なまま、一覧からは隠す Public Function convertRangeIntoTable(ByRef targetSheet As Worksheet, _ ByRef targetRange) As ListObject On Error GoTo checkAutoFilter 'エラーが出たらジャンプ Dim newListObj As ListObject 'エラーが出るならココ↓ Set newListObj = targetSheet.ListObjects.Add(SourceType:=xlSrcRange, _ Source:=targetRange, _ XlListObjectHasHeaders:=xlYes) On Error GoTo 0 'ジャンプ命令を解除 Call setTableName(newListObj) Set convertRangeIntoTable = newListObj Exit Function '書き忘れると"End Function"(↓)まで進んで更にエラー処理される… 'エラー処理 checkAutoFilter: unsetAutoFilter (targetSheet) Resume 'エラー発生箇所に戻る MsgBox "予期せぬエラーが発生しました。処理を中止します。" '起こるかな…? Stop 'もし"Resume"を越えたら異常事態 Set convertRangeIntoTable = Nothing End Function
オートフィルターを解除
既に実行時エラーが発生しないように対策していますが、それでもエラーになる時用に。
上記プロシージャのcheckAutoFilter
ラベル以下のエラー処理に入って、こちらのunsetAutoFilter
プロシージャに飛んできます。
この段階ではフィルターが掛ったままの可能性があるので、一旦これをOff。
それでもエラーになる場合の対処法は…ごめんなさい、まだ分かりません…
Private Sub unsetAutoFilter(ByRef targetSheet As Worksheet) With targetSheet If .AutoFilterMode = True Then .AutoFilterMode = False End If End With End Sub
テーブルの名前を決める
エラー処理が終わって無事テーブルを生成できたら、今度はテーブルの名前を決めます。
Private Sub setTableName(ByRef targetListObj As ListObject) With targetListObj Dim defaultListName As String defaultListName = .Name Dim m As String m = "テーブル名を入力して下さい。" & vbCrLf m = m & "既定の名前:" & defaultListName .Name = InputBox(Prompt:=m, Default:=defaultListName) End With End Sub
InputBox()
関数の引数のうちDefault
に値を渡しておくと、はじめから入力欄に値が入った状態でInputBoxが開きます。
既定値をユーザーに知らせたい時に便利です。
ただ、入力値とデフォルト値が一致した時の処理も追加した方が良さそう…
キリがない。サグラダファミリアになってしまう…
感想
やってることは初回のコードとほぼ同じなのですが、エラー処理の流れを修正してみました。
読みやすさが上がっていると良いのですが…
ただ、あまり細分化し過ぎると逆に読みづらくなるような気がしていて、その塩梅が難しいです。
これはどこで学べるんだろう…写経を繰り返すしかないのかな…?
余談
実は、このコードを書くためにVSCode使ってみたりRubberduckなるVBEのアドインを入れてみたり*5しました。
が、操作が理解できず、コメント増やしつつ数時間分かけて書いたコードを全消去してしまって絶望…脱線した罰かな。泣
でも、1回書くと何となく流れを覚えているみたいで、その日のうちに戻せました。(良かった…)
メゲずに続けます。
このシリーズについて
テーブルの変換とテーブルスタイルの新規作成をマクロ1発で使えるように考えています。主に自分向け。
もし間違いやヘンなところ等ありましたら、コメント欄やTwitter、お問い合わせフォーム等でご指摘いただけたら嬉しいです。
次回
【Excel VBA】テーブル変換とスタイル変更 ~標準モジュールでスタイルを新規作成する~ - ゆるおたノート
連載目次
- 【Excel VBA】テーブル変換とスタイル変更 ~一発で変換とスタイル変更を済ませたい~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~処理の流れを整理してみる~ - ゆるおたノート
- 当記事【Excel VBA】テーブル変換とスタイル変更 ~テーブル変換するところまで標準モジュールで書いてみる~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~標準モジュールでスタイルを新規作成する~ - ゆるおたノート
- 【Excel VBA】テーブル変換とスタイル変更 ~ブックの保存先を選ぶ~ - ゆるおたノート
注釈
*1:「Objectオブジェクト」って「頭痛が痛い」みたいな…
*2:詳しくはこちらを… listobjects. Add メソッド (Excel) | Microsoft Docs
*3:詳しくはこちら。
本職でなくても勉強になります。サンプルはJavaScriptなのかな?
<2020/10/13追記>
JavaScriptだけでなく、RubyやScalaもあるみたいです。
新人プログラマに知ってもらいたいメソッドを読みやすく維持するいくつかの原則 - Qiita
*4:素敵な記事をありがとうございます!!
※出典?とされているimihitoさんの記事は見つけられませんでした…
VBA Publicなプロシージャをマクロの実行メニューから隠す方法 - t-hom’s diary
VBA応用(マクロの起動にプロシージャ名を表示させなくする。)
*5:Rubberduckは私の環境では何故か認識されなかった…
また今度、時間を作って試します…