(Excel VBA) GAS風にセル範囲を選択する汎用Function

はじめに

Excel VBA と GoogleスプレッドシートのGAS (Google Apps Script) の両方を使っていると、GASの関数に使いやすい(分かりやすい)ものがあるなぁと思うことがあります。その中でも、sheet クラスにある セル範囲を取得する getRange メソッドがとても使いやすいので、Excel VBA に移植してみました。

公式リファレンス

GAS (Google Apps Script) の getRange メソッド

使用サンプル

GAS

const sheet = SpreadsheetApp.getActivesheet();
let targetRange = sheet.getRange(1, 1, 5, 3);

// 基準セル ＝ A1
// セル範囲 ＝ A1:C5

構文と解説

クラス   : Sheet
メソッド : getRange(row, column, numRows, numColumns)
引数     :
  row         [Integer] 基準セルの行番号
  column      [Integer] 基準セルの列番号
  numRows     [Integer] 基準セルからの行数
  numColumns  [Integer] 基準セルからの列数
戻り値   : range

Excel VBAで言えば、基準セルとoffsetを組み合わせたような表現ですが、ここで一点だけ注意点があります。基準セルからの数え方が Excel VBAとGASで異なります。

• Excel VBAで Cells(1, 1).Offset(1, 1)を示すセルはB2セル
• GASでgetRange(1, 1, 1, 1)としたときの最終セルは A1セル

つまりExcel VBAでは基準セルを含まず、GASでは基準セルを含んで1から数えるのです。

Excel VBA

ユーザ定義 列挙型 ueCountMethod を定義しておく

基準セルからの数え方の違いを反映させるため、ユーザ定義の列挙体を作っておきます。こうすることで、VBAに慣れた人でもGASに慣れた人でも使えるようにできます。

Excel VBA

' uFnGetRange用
Public Enum ueCountMethod
  ueCountMethodVBA = 0
  ueCountMethodGAS = 1
End Enum

Public Function プロシージャ : uFnGetRange

Excel VBA

' GAS風にセル範囲を取得する
Public Function uFnGetRange(ByVal aoSheet As Worksheet _
                         , ByVal aBaseRow As Long _
                         , ByVal aBaseCol As Long _
                         , ByVal aNumRows As Long _
                         , ByVal aNumCols As Long _
            , Optional ByVal aCountMethod As ueCountMethod = ueCountMethodGAS _
                                        ) As Range
  ' 仮戻り値
  Dim oRetRange As Range
  
  ' 開始セルを取得
  Dim oStartCell As Range
  Set oStartCell = aoSheet.Cells(aBaseRow, aBaseCol)
  
  ' 終了セルを取得
  Dim oEndCell   As Range
  Select Case aCountMethod
    Case 0  ' ueCountMethodVBA
      Set oEndCell = oStartCell.Offset(aNumRows, aNumCols)
    Case 1  ' ueCountMethodGAS
      ' GAS式の場合 aNumRows,aNumColsに 0 を指定するとエラーになるのを回避
      If aNumRows = 0 Then aNumRows = 1
      If aNumCols = 0 Then aNumCols = 1
      '----------------------------------
      Set oEndCell = oStartCell.Offset(aNumRows - 1, aNumCols - 1)
  End Select
  
  ' セル範囲を取得
  Set oRetRange = aoSheet.Range(oStartCell, oEndCell)
  
  ' 戻り値
  Set uFnGetRange = oRetRange
  
  ' オブジェクト変数の解放
  Set oRetRange = Nothing
  Set oStartCell = Nothing
  Set oEndCell = Nothing
  
End Function

実行例

VBE イミディエイト

? uFnGetRange(Application.ActiveSheet, 1, 1, 1, 1, ueCountMethodVBA).Address
$A$1:$B$2

? uFnGetRange(Application.ActiveSheet, 1, 1, 1, 1, ueCountMethodGAS).Address
$A$1

? uFnGetRange(Application.ActiveSheet, 1, 1, 1, 1).Address
$A$1

注意点 : エラー処理は別途必要

引数に無効な値を入れたときのエラー処理はここでは組み込んでません。

おわりに

セル範囲の指定はよく使うのですが、GASのgetRangeメソッドが本当に秀逸だったので移植しました。これは自分でも結構使っています。よかったら参考にして下さい。