メソッド呼び出し時にモジュール名の記述を強制する
画期的なアイディア（嘘）
このときに、
モジュール名を指定しないと使えない、とかだったら便利だと思ったんだけどなあ。
とか書いていたんだが、画期的な解決法が思い浮かんだので記しておく。
同一メソッドを持つダミーモジュール
このときにも述べたことだが、２つ以上のモジュールに同一名のメソッド（プロシージャ）があると、メソッド名だけでは呼び出すことができない。
この習性を利用する。
このように、FoobarというモジュールとFoobar_というモジュールを用意する。
どうでもいいコードなので、画像だけ載せる。
まず、Foobarモジュールがコチラ。
Foobarモジュールのコードはコチラ。
んで、Foobar_モジュールがコチラ。
プロパティとかメソッドの中身だけ全部コメントアウトしてある。
使ってみる
次のコードで実行してみる。
リスト１ 標準モジュール
Public Sub testFoobar() hoge Foobar.hoge Foobar.fuga End Sub
１行目は、単にhogeメソッドを呼んでいる。Foobarモジュールだけなら、フツーに実行できるはずだが、今回はFoobar_モジュールにもhogeメソッド（中身は空だけど）があるので、エラーになるはず。
２、３行目は、それぞれモジュール名を指定しているので、ちゃんと呼び出せるはず。
ちなみに、コード入力中は
このようにintellisenseが効く。便利。
実行すると、
予定どおりコンパイル・エラーになる。
意図どおり呼び出すことができた。当たり前だけれど。
おわりに
めちゃくちゃしょうもない対処法だけれど、これでメソッド呼び出し時にモジュール名付きで呼び出すことを強制できるのではなかろうか。
メソッド呼び出しに制約をかけることで、モジュールを多少細かく切り分けてもメインのコードの可読性が上がったりしないかなあ。
列挙体にはメンバを何個列挙できるか
列挙体はいくつまで列挙できるか
列挙体のメンバ数の最大って、いくつなのだろうか。
ちょっと調べてみた。
Integer型の最大値
Integer型の最大値は32767。へえ。意外と小さいな。
標準モジュールの宣言セクションに次のように書いてみる。
Public Enum MaxSize maxElement = 32767 End Enum
んで、イミディエイトに
Debug.Print maxsize.maxElement
と打ち込んで[Enter]。
とりあえずクリア。
少なくとも３万２千７６８＋１個までは列挙できるみたい。
Integer型の限界を超える
次に、Integer型の限界を超えられるかどうかテスト。
Public Enum MaxSize maxElement = 32767 nextElement End Enum
32767の次はあるのか。イミディエイトに次のように打ち込む。
Debug.Print maxsize.nextElement
んで、[Enter]。
フツーに「32768」が返った。
Long型の最大値
次はLong型でやってみる。
Public Enum MaxSize maxElement = 32767 nextElement realMaxElement = 2147483647 End Enum
イミディエイトには次のように打ち込む。
Debug.Print maxsize.realMaxElement
んで、[Enter]！
ふええ！ 「2147483647」が返ったよ……。
ということは、２１億個以上列挙できるのか！
次はどうなるんだろう。
Long型の限界を超える
お次は、
Public Enum MaxSize maxElement = 32767 nextElement realMaxElement = 2147483647 nextRealMaxElement End Enum
これまで同様、イミディエイトに
Debug.Print maxsize.nextRealMaxElement
と打ち込んで[Enter]。
ファッ！？
ま、ｰ2147483648？？？
ループして負の数の最小値に飛んで行ったのか！
……ということは……、
「２１億個以上」どころか、４３億個近く列挙できる
ということじゃないか！
えらいこっちゃなあ……。
ちなみに、
Public Enum MaxSize maxElement = 32767 nextElement realMaxElement = 2147483647 nextRealMaxElement nextNextRealMaxElement End Enum
こうしておいて、イミディエイトに
Debug.Print maxsize.nextNextRealMaxElement
と打ち込んで[Enter]すると、
やっぱり、正の数の最大値まで行くと、負の数の最小値にループしてインクリメントしていく模様。
Long型の限界を超えるとどうなるか
そこで、
Public Enum MaxSize maxElement = 32767 nextElement realMaxElement = 2147483647 nextRealMaxElement nextNextRealMaxElement overFlowElement = 2147483648# End Enum
としてみる。
「2147483648」と打ち込んで[Enter]を押したら、勝手に右端に「#」がついた。
イミディエイトに
Debug.Print maxsize.overFlowElement
と打ち込んで[Enter]。すると、
エラーになった。
おわりに
というわけで、列挙体のメンバ数の最大値は、（たぶん）
4294967296個
です。
「名前の定義」再入門[Excel]
名前の定義
セル範囲に名前を付ける方法
コチラにExcelVBAer (id:x1xy2xyz3) さんからコメントをいただいた。その中に
①名前の定義で以下の２つを定義
ActCell = INDIRECT(ADDRESS(ROW(),COLUMN()))
UpCell = OFFSET(ActCell,-1,0)
というのがあって、「ん？」となったのだ。
というのも、私は、セル範囲に名前を付ける方法として
セル範囲を選択してから左上の名前ボックスに名前を入力して[Enter]という方法しか知らなかった
のである!!!!!!!!
一瞬私が何を言っているのか分からなかったかも知れないが、これは紛れもない事実なのである!!!!!!!!（←江田島平八の自己紹介風）
というわけで、「名前の定義」に再入門してみた。
「名前の定義」をイチからやってみる
おなじみ、「数式」タブ。
「名前の管理」をクリック。ここまでは知ってた。
すると、
ほんまやｗｗｗ 「新規作成」というのがあるじゃないか！
ちょっとやってみた。
[OK]をクリックすると、
ｗｗｗ
「ち～んｗ」は不適切な名前っぽいｗ
気を取り直して、
これなら文句あるまい。
おおっ！ ちゃんと名前が定義されておる！
おわりに
体系的に学んでいないので、あちこちに大穴が開いているということを再認識させられたのであった。MOSか何か勉強してみようかなあ。
Range.BorderAroundメソッド[Excel]～車輪の再発明
車輪の再発明
Range.BorderAroundメソッド
前回の
に、 ぴぼったー さんからコメントをいただいた。曰く、
つ borderaroundメソッド
なんと、外枠線を引くメソッドがあったのだった。
またしても車輪の再発明をしてしまったのだった。
使ってみた
気を取り直して、Range.BorderAroundメソッドを使ってみる。
リスト１ 標準モジュール
Public Sub testBorderAround()
Dim targetRange As Range
Set targetRange = Selection
Call targetRange.BorderAround(LineStyle:=xlContinuous, _
Weight:=xlMedium, _
ColorIndex:=xlColorIndexAutomatic)
End Sub
選択範囲の外枠に罫線を引く、というだけのコード。
別に
Selection.BorderAround
と書いても良いが、一旦
Set targetRange = Selection
このようにRange型変数に突っ込んでおくことで、
こんなふうにintellisenseが効くので楽。
実行結果
この状態で実行。
ほれ、この通り。簡単に外枠線が引けた。
おわりに
ぴぼったー さん、毎度ありがとうございました。
セル範囲の外枠に罫線を引く[Excel]
セル範囲の外枠罫線
VBAでセルの罫線の設定なんてしたことがなかったので知らなかったが、セル範囲の外枠だけに罫線を設定するのは非常にメンドクサイのだった。
セルの罫線はRangeオブジェクト配下のBordersコレクションで管理されている。
で、罫線の位置を指定する場合は、
Range.Borders(インデックス値)
の形でインデックスを指定してやればよい。
インデックス値は定数が設定されていて、
このように、「XlBordersIndex」という列挙体にまとめられている。
で、このインデックス値を省略すると、格子状に指定したのと同じになる。
私も、単純に罫線を引くだけのことならよくやっていて、
[Rangeオブジェクト式].Borders.LineStyle = xlContinuous
みたいなコードはしょっちゅう書いていたので、それこそ何も見ないでも書けるが、【セル範囲の外枠にだけ罫線を設定する】という場面に出くわして、「あれ、どうやるんだっけ？」となったのであるｗ
セルの外枠にだけ罫線を引く
徹底的に調べたわけではないので、実は簡単なやり方があるのかもしれないが、どうも、
[Rangeオブジェクト式].Borders(xlEdgeTop).LineStyle = xlContinuous [Rangeオブジェクト式].Borders(xlEdgeRight).LineStyle = xlContinuous [Rangeオブジェクト式].Borders(xlEdgeLeft).LineStyle = xlContinuous [Rangeオブジェクト式].Borders(xlEdgeBottom).LineStyle = xlContinuous
というやり方になるみたい。線の太さや色の設定を省略してもこのコード。めんどくさすぎる。
手作業ならツールバーのアイコンクリック一発なのにｗ
というわけで、メソッド化してみた。
drawEdgeLinesメソッドの自作
まずはコードの紹介。
リスト１ 標準モジュール
Option Explicit
Private Type BorderStyle '……(1)'
LineStyle_ As XlLineStyle
Weight_ As XlBorderWeight
ColorIndex_ As XlColorIndex
End Type
Public Sub drawEdgeLines(
ByVal targetRange As Range, _
Optional ByVal kindOfLineStyle As XlLineStyle = xlContinuous, _
Optional ByVal kindOfWeight As XlBorderWeight = xlThin, _
Optional ByVal kindOfColorIndex As XlColorIndex = xlAutomatic) '……(2)'
Dim borderStyle_ As BorderStyle '……(3)'
With borderStyle_
.LineStyle_ = kindOfLineStyle
.Weight_ = kindOfWeight
.ColorIndex_ = kindOfColorIndex
End With
With targetRange '……(4)'
Call setBorderStyle(.Borders(xlEdgeBottom), borderStyle_)
Call setBorderStyle(.Borders(xlEdgeLeft), borderStyle_)
Call setBorderStyle(.Borders(xlEdgeRight), borderStyle_)
Call setBorderStyle(.Borders(xlEdgeTop), borderStyle_)
End With
End Sub
Private Sub setBorderStyle(ByVal targetBorder As Border, _
ByRef borderStyle_ As BorderStyle) '……(*)'
With targetBorder
.LineStyle = borderStyle_.LineStyle_
.Weight = borderStyle_.Weight_
.ColorIndex = borderStyle_.ColorIndex_
End With
End Sub
まずは(1)の
Private Type BorderStyle LineStyle_ As XlLineStyle Weight_ As XlBorderWeight ColorIndex_ As XlColorIndex End Type
１つの罫線につき、３種類の設定（線の種類・太さ・色）があるので、ひとまとめの構造体にした。他から呼び出して使うようなものでもないので、Private指定。
(2)の
Public Sub drawEdgeLines(
ByVal targetRange As Range, _
Optional ByVal kindOfLineStyle As XlLineStyle = xlContinuous, _
Optional ByVal kindOfWeight As XlBorderWeight = xlThin, _
Optional ByVal kindOfColorIndex As XlColorIndex = xlAutomatic)
でメソッド本体の引数設定。
長ったらしく見えるので軽く引くかもｗ
第１引数targetRangeは枠線を設定したいセル範囲。
第２引数kindOfLineStyleは線の種類。「XｌLineStyle」型にしているので、入力時にintellisenseが効く。「オブジェクト・ブラウザー」の使い方が分かってくると、こういう便利な引数指定ができるようになる。
第３引数kindOfWeightは線の太さ。
第４引数kindOfColorIndexは線の色。
(3)からの６行
Dim borderStyle_ As BorderStyle With borderStyle_ .LineStyle_ = kindOfLineStyle .Weight_ = kindOfWeight .ColorIndex_ = kindOfColorIndex End With
で、自作構造体BorderStyleに引数で受け取った罫線の設定をぶち込む。
VBAでは小文字と大文字を区別しないので、BorderStyle型の変数名として「borderStyle」というのが使えない。とはいえ、BorderStyle型というのは自作型で、なおかつ１つしか使わないことが明白なので、変数名を「borderStyle」にしておきたい。そんなわけで、苦し紛れの策としてアンダースコアを付けて「borderStyle_」とした。
Person person = new Person();
って書きたいなあ。VBA大好きだけれど、こういうところが不便。
(4)からの６行
With targetRange Call setBorderStyle(.Borders(xlEdgeBottom), borderStyle_) Call setBorderStyle(.Borders(xlEdgeLeft), borderStyle_) Call setBorderStyle(.Borders(xlEdgeRight), borderStyle_) Call setBorderStyle(.Borders(xlEdgeTop), borderStyle_) End With
いちいち似たようなコードを４回も書かないといけないので、こんなふうに１箇所にまとめた。
「setBorderStyle」というメソッドを引数を変えて４回読んでいるが、これも自作メソッドで、(*)の
Private Sub setBorderStyle(ByVal targetBorder As Border, _
ByRef borderStyle_ As BorderStyle)
With targetBorder
.LineStyle = borderStyle_.LineStyle_
.Weight = borderStyle_.Weight_
.ColorIndex = borderStyle_.ColorIndex_
End With
End Sub
がそれ。BorderStyle型の引数（罫線の３種類の設定が全部入っている）を受け取って、線を引くだけのメソッド。
これで、細い実線をセル範囲の四方に引くだけなら、
Call drawEdgeLines([Rangeオブジェクト式])
だけでおｋ、ということになる。
使ってみた
画面上でこんなふうに範囲選択をして、次のコードを実行。
リスト２ 標準モジュール
Public Sub testDrawEdgeLines()
Call drawEdgeLines(targetRange:=Selection, _
kindOfLineStyle:=xlContinuous, _
kindOfWeight:=xlMedium)
End Sub
一応引数は４つ中３つ渡した。
中太実線を選択範囲に引け、という命令。
この通り、無事に外枠罫線が設定された。
おわりに
外枠罫線だけを一発で引く方法ってあるのかしら？
それと、Bordersの引数を省略したときに「格子」になるようにしたのはさすが。
Bordersの引数を省略して「Bordersコレクションだョ全員集合！」みたいな設定だったら、VBAerはみんな発狂すると思うｗ
追記
なお、今回の記事は、単なる「車輪の再発明」に過ぎないこと、申し添えますｗ
ナゾの書式設定文字列（Range.NumberFormatLocalプロパティ）
ナゾの書式設定文字列「;;;」
前回の
こちらに、 ぴぼったー さんという方からコメントをいただいた。曰く、
本日のサンプルのパターン、
（要望）列方向に連続する場合に表記を省略する
（実装）セルの結合を行う
（Excel屋の本音）結合はマクロの邪魔になるからやめろぉ！
って場合、
A1＝愛知,A2＝愛知,A3＝愛知と入力されたセルに対して
A2・A3をの値を見かけ非表示にする
With Range("A1") .NumberFormatLocal="" .Borders(xlEdgeBottom).LineStyle = xlNone End With With Range("A2") .NumberFormatLocal=";;;" .Borders(xlEdgeBottom).LineStyle = xlNone End With With Range("A3") .NumberFormatLocal=";;;" .Borders(xlEdgeBottom).LineStyle = xlContinuous End Withこんな感じの処理を組んだことがあります。
へえ。ちょっとやってみよう。
やってみた
こんなシートを用意し、
次のコードを実行する。
リスト１ 標準モジュール
Public Sub dummyMergeCells()
With Range("A1")
.NumberFormatLocal = ""
.Borders(xlEdgeBottom).LineStyle = xlNone
End With
With Range("A2")
.NumberFormatLocal = ";;;"
.Borders(xlEdgeBottom).LineStyle = xlNone
End With
With Range("A3")
.NumberFormatLocal = ";;;"
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
End Sub
実行結果
おおっ！
Ａ２セルとＡ３セルの「ち～んｗ」が消えとる！！！！
んでも、
Ａ２セル、Ａ３セルともに「ち～んｗ」という値はあるのに！！！！
おわりに
書式設定文字列（？）の「;;;」って何者？？？
同じ値の連続するセルを結合する[Excel](Range.Mergeメソッド)
同じ値の連続するセルを結合する
Excelは、表計算ソフトとしてよりも、方眼紙として使われているケースが多いと思う。
そこで、大活躍するのが（ｗ）セルの結合機能だと思う。
多くのExcel使いは、データを蓄積するためのシートで見栄えをよくするためにセルの結合を多用しやがるので、Excelをフツーに使いたい私のような善良な市民はいつも迷惑をこうむるわけですｗ
それでもまあ、見栄えを整えるために連続する同じ値のセルを結合する、という操作はそれなりに発生するので、自動化してみようと考えた。
考え方
とりあえず、タテ方向１列限定で考えてみた。複数列複数行はなかなか大変そうなので、ひとまず保留。
処理の手順
指定した範囲の一番上のセルから順に下へ下へと進めていく。
- 同じ値のセルが連続していたら、先頭のセルを変数に格納し、同じ値のセルがいくつ続くかカウントする
- 異なる値のセルにぶつかった時点で、Resizeプロパティを用いて同じ値のセル領域を取得し、結合する
とまあ、このような手順を考えた。
コーディング
リスト１ 標準モジュール
Public Sub mergeSameValueCells(ByVal targetRange As Range)
If targetRange.Columns.Count > 1 Then _
Call makeUserSick("２列以上の範囲を渡すなぼけー！"): Exit Sub
Dim cnt As Long
Dim targetCell As Range
Dim tmpCell As Range
Dim isToBeMerged As Boolean '……(1)'
cnt = 1
For Each targetCell In targetRange
With targetCell
If Not isToBeMerged Then '……(2)'
If .Value = .Offset(1, 0).Value Then '……(3)'
Set tmpCell = targetCell
cnt = cnt + 1
isToBeMerged = True
Else '……(4)'
Set tmpCell = targetCell
tmpCell.HorizontalAlignment = xlCenter
tmpCell.VerticalAlignment = xlCenter
End If
Else '(If isToBeMerged Then)' '……(5)'
If .Value = .Offset(1, 0).Value Then '……(6)'
cnt = cnt + 1
Else '(If .Value <> .Offset(1, 0).Value)' '……(7)'
Application.DisplayAlerts = False
tmpCell.Resize(cnt, 1).Merge
Application.DisplayAlerts = True
tmpCell.HorizontalAlignment = xlCenter
tmpCell.VerticalAlignment = xlCenter
isToBeMerged = False
cnt = 1
End If
End If
End With
Next
End Sub
とりあえず一通り書いてみただけなので、ちょっとクソコードｗ
１列のセル範囲を受け取って処理する想定。
したがって、引数で受け取ったセル範囲が複数列だったら、最初のところで何もせずにreturnしている。
まず、(1)の
Dim isToBeMerged As Boolean
はフラグ変数。ざっくりと言えば、セル連結モードになっているかどうかを表す変数。
isToBeMergedがTrueのときは同じ値のセルを数えている途中、と考えてもらえば良い。
んで、メインの処理。まずは(2)からの23行（！）
If Not isToBeMerged Then If .Value = .Offset(1, 0).Value Then '……(3)' Set tmpCell = targetCell cnt = cnt + 1 isToBeMerged = True Else '……(4)' Set tmpCell = targetCell tmpCell.HorizontalAlignment = xlCenter tmpCell.VerticalAlignment = xlCenter End If Else '(If isToBeMerged Then)' '……(5)' If .Value = .Offset(1, 0).Value Then '……(6)' cnt = cnt + 1 Else '(If .Value <> .Offset(1, 0).Value)' '……(7)' Application.DisplayAlerts = False tmpCell.Resize(cnt, 1).Merge Application.DisplayAlerts = True tmpCell.HorizontalAlignment = xlCenter tmpCell.VerticalAlignment = xlCenter isToBeMerged = False cnt = 1 End If End If
まず、isToBeMergedがFalseのとき、すなわち、同じ値のセルを数える体勢になっていない状態のときには、(3)からの９行に処理が移る。
If .Value = .Offset(1, 0).Value Then Set tmpCell = targetCell cnt = cnt + 1 isToBeMerged = True Else '……(4)' Set tmpCell = targetCell tmpCell.HorizontalAlignment = xlCenter tmpCell.VerticalAlignment = xlCenter End If
まず、１つ下のセルと値を比較して同じ値だったら、targetCellをtmpCellにぶち込んで、cntをインクリメントし、isToBeMergedをTrueにする。これで同じ値のセルを数えるモードに切り替えたことになる。
１つ下のセルと異なる値であったならば、このセルは結合する必要がないということだから、(4)のElseブロックを実行して、値のセンタリングだけしておく。
次に、isToBeMergedがTrueのとき、すなわち、すでに同じ値のセルを数える体勢に入っているときは、(5)から下の11行に処理が移る。
If .Value = .Offset(1, 0).Value Then '……(6)' cnt = cnt + 1 Else '(If .Value <> .Offset(1, 0).Value)' '……(7)' Application.DisplayAlerts = False tmpCell.Resize(cnt, 1).Merge Application.DisplayAlerts = True tmpCell.HorizontalAlignment = xlCenter tmpCell.VerticalAlignment = xlCenter isToBeMerged = False cnt = 1 End If
(6)で１つ下のセルと値を比較し、同じ値だったら、cntをインクリメントするだけで良い。
１つ下のセルと異なる値だったら、セルを結合しなければならないので、(7)からの７行の処理を行う。
Application.DisplayAlerts = False tmpCell.Resize(cnt, 1).Merge Application.DisplayAlerts = True tmpCell.HorizontalAlignment = xlCenter tmpCell.VerticalAlignment = xlCenter isToBeMerged = False cnt = 1
まず、セルを結合するときには一番左上の値しか残らないとか何とか警告メッセージが出るので、Application.DisplayAlertsをFalseにしておく。
次に、tmpCell（同じ値のセルを数え始めたときの最初のセル）のResizeプロパティに引数としてcnt（同じ値のセルの個数）を渡して同じ値の連続するセル範囲を取得し、Mergeメソッドで結合。即座にApplication.DisplayAlertsをTrueに戻しておく。
後は、HorizontalAlignmentプロパティとVerticalAlignmentプロパティの値をxlCenterに設定して中央寄せにし、isToBeMergedをFalseにしてモードをリセットしておく。
これをFor Eachで回す、という算段。
使ってみた
次のコードで実験。
リスト２ 標準モジュール
Public Sub testMergeSameValueCells() Call mergeSameValueCells(Selection) End Sub
選択範囲を引数として渡してmergeSameValueCellsを実行するだけのコード。
まずはこんな表を用意して、
この状態で実行。
ほれ、この通り。
ついでに、
こんな表でもやってみた。
バッチリです。
おわりに
とりあえず組み立ててみただけなので、そもそものロジック含め、まだまだ改良の余地がありそう。
Excelを方眼紙的に使うことを止めるだけの力はないので、各個撃破で対抗するしかないのよね……。
追記
重大な欠陥があったので、メソッドを作り直しました。
