excelのシートに関数を詰め込み過ぎて、再計算の砂時計に占領された。もはや、vbaを書くしかない。

　複数の列の値が一致したら、その行の特定の列を抽出するために、ユーザー定義の関数をvbaのfunctionで書いてみた。
　autofilterで複数の列をフィルターし、特定の列を取得するコードを書いてみた。まずは、debugで１行ずつ、動かして、OKだった。
　次にシートにその自作関数を埋め込むと、#VALUE!エラーとなった。どうも関数を埋め込んだシートとは異なるシートにautofilterでフィルターをかけるのが無理なようだ。
　そこで、AutoFilterでの自作関数は諦めて、index関数とsumproduct関数、row関数のみで作ってみた。例えば、こんな感じだ。
　=INDEX(Z:Z,SUMPRODUCT((X:X= Tn )*( Y:Y= Sn ), ROW(Z:Z)))
　X列の値がTnで、Y列の値がSnの行を見つけて、その行番号のZ列の値を抽出するという式である。
　埋め込みの箇所が少ないうちは、快調に動いてくれたのが、excelのシートに関数をたくさん埋め込むと、やたらと再計算で、砂時計がワタシのやりたいことを阻む。
　やむを得ず、関数で実現していた処理をvbaで書いた。まず、Autofilterを使わず、まず、X列の値がTn,Y列の値がSnの行を求め、Z列の値を取得するコードを書いた。そして、実行ボタンで必要な時に動かすようにした。この方式でも数が増えると、砂時計が出てきて、時間がかかるようだ。AutoFilterで複数の列をフィルターし、特定の列を取得するコードにすると、数が増えても砂時計は現れず、スーと終わった。AutoFilterは高速だな。シート内関数としては、呼べないけど。やれやれ。それそれ。

Function GetValue(Tn as long,Sn as Long) as String
  With thisworkbook.worksheets("SearchList")
    With .Range("A1")
      AotoFilter ColumnNoForX,Tn
      AutoFilter ColumnNoForY,Sn
      If WorksheetFunction.Subtotal(3,.Range("A:A")) > 1 then
        With .Range("A1").CurrentRegion.Offset(1,0) 
          For Each R in .Resize(.Rows.Count-1).SpecialCells(xlCellTypeVisible).Rows
            GetValue = R.Range("Z1").Value 
          Next R
        End With
      End If
    End With
  End With
End Function