Range のパラメータを取得する関数を作る

Range のパラメータを取得する関数を作る

2022年3月11日

Excel VBA というのは便利です。Excel のマクロ記録ボタンを利用すれば、全てではありませんが、だいたいの処理はコピーでき、コードが自動生成されます。
そういうコードを繋ぎ合わせて作るのが実際手っ取り早いわけです。

しかし神経質な人間は、それをやると著しい罪悪感にさいなまされ、データ定義からきっちりしたくなるというものです。

たとえば普通、関数を作って、それに Rangeを渡す場合、

Set before = ActiveSheet
targetSheet.Activate
Fnc ( Range() )
before.Activate

などといった処理で、シートの切り替えをすればよい話です。しかし「もし後から関数を利用する人が、そのシートを戻し忘れたらどうするんだ」などと考えると、「関数内でシートを切り替えをすましておきたい」などという考えが生じます。

すると関数内でシートを切り替えるとなると、親パラメータの取得が必要であり、せっかく、親パラメータを取得するならば、Range から一通りのパラメータを取得する関数を作ろうとなり、そうなると
そういった構造体を定義しようではないか、

という感じで、一つシステマティックな発想が出てくると、なにもかもシステマティックにしようという欲求が出てきて、際限がなくなるのです。

たとえばVBAで以下のような Range から一通りのパラメータを取得する関数を作っている人は多くはないでしょう(VBAは手軽さが利点なので)

 

Rangeに関するパラメータを含むTypeを定義する

Public Type TRange
sRow As Long
eRow As Long
sCol As Long
eCol As Long
rAddress As String
sAddress As String
eAddress As String
sheetName As String
bookName As String
Dir As Byte
End Type

渡したRange から、Rangeに関するパラメータをえる


Public Function getRange(ByVal rng As Range) As TRange
Dim Trg As TRange
Trg.sRow = rng.Row
Trg.sCol = rng.Column
Trg.eRow = rng.Row + rng.Rows.Count - 1
Trg.eCol = rng.Column + rng.Columns.Count - 1
Trg.sAddress = F2C(Trg.sCol) & Trg.sRow
Trg.eAddress = F2C(Trg.eCol) & Trg.eRow
Trg.rAddress = rng.Address(False, False)
Trg.sheetName = rng.Parent.Name
Trg.bookName = rng.Parent.Parent.Name

'' Range がセルか、水平方向か、垂直方向か、範囲かのパラメータDirのセット
If Trg.sAddress = Trg.eAddress Then
Trg.Dir = Dir.Cell
ElseIf Trg.sRow = Trg.eRow Then
Trg.Dir = Dir.Hor
ElseIf Trg.sCol = Trg.eCol Then
Trg.Dir = Dir.Ver
Else
Trg.Dir = Dir.Rect
End If
getRange = Trg
End Function

Function F2C(ByVal fig As Long)
F2C = Split(Cells(1, fig).Address(True, False), "$")(0)
End Function

Public Function C2F(ByVal GetCol As String) As Integer
  C2F = Range(GetCol & "1").Column
End Function

こういったのは、何か作業をするたびに、完璧にものを整理整頓するのと同じような感覚です。

さて、これらの段取りは、なんに利用するかというと、「セルドラッグ」です。

セルドラッグでも引数を簡易チェック

Public Function FillDrag(sRange As Range, eRange As Range) As Byte
Dim Bg As TRange, Eg As TRange
Bg = getRange(sRange)
Eg = getRange(eRange)

'## 引数の正当性を確認begin
bver = ((Bg.sCol = Eg.sCol) And (Bg.eCol = Eg.eCol))
bHor = ((Bg.sRow = Eg.sRow) And (Bg.eRow = Eg.eRow))
bRect = (Bg.Dir = Dir.Rect) And (Bg.Dir = Eg.Dir) And (bver Or bHor)

bver = bver And (Bg.Dir = Dir.Ver)
bHor = bHor And (Bg.Dir = Dir.Hor)

bb = bRect Or bver Or bHor

If bb = False Then
GoTo ArvErr
End If
'## 引数の正当性を確認end


Set bksheet = ActiveSheet

Workbooks(Bg.bookName).Sheets(Bg.sheetName).Activate

Range(Bg.rAddress).Select
Selection.AutoFill Destination:=Workbooks(Eg.bookName).Sheets(Eg.sheetName).Range(Eg.rAddress), Type:=xlFillDefault

bksheet.Activate


GoTo Ed

ArvErr:

GoTo Err
OtherErr:
MsgBox そのほかエラー
Err:
Drag = -1
Ed:
End Function


セル関数を呼び出す

以上の段取りをしておくと、

A = FillDrag(Workbooks("aaa.xlsm").Sheets("Sheet2").Range("A9:A10"), Workbooks("aaa.xlsm").Sheets("Sheet2").Range("A1:A10"))

このように呼び出すときに、気持ちよく使えるというわけです。

もちろん Type など定義しなくても、 関数呼び出し前後に、 Activateをしてシート切り替え・戻しをすればよいのですが・・

「もし戻し忘れたどうするんだ」

という不安が生じると、こういったコードを書いてしまうことになります。

ただし一般的には、こういったデータをきちりと定義しておくのはよくあることで、そういう人からすれば特別、冗長には見えないのかもしれません。

other

Posted by hamahan