Excel-DNA で XLL をつくる（その15）

　今回は、Excel-DNA で関数の揮発性を制御してみたいと思います。
（揮発性関数と揮発性操作については、Excel 2010 のパフォーマンス（MSDN） を参照してください）

　例えば、RAND関数 は揮発性関数ですから、ワークシートのどこかのセルに変更を加えると勝手に再計算されます。でも、乱数を使った計算を F9キーで繰り返しながら目的とする組合せを探す場合など、所望の結果を得られたら、もう再計算して欲しくない事だってありますよね？ また、OFFSET()、CELL()、INDIRECT() などの揮発性関数を多用したシートは、データ入力の度に再計算されてレスポンスが悪くなるので、これらの関数が不揮発性だと便利です。逆に、WebAPI を使って外部の最新データを取得したい場合など、揮発性関数を作成したいこともあります。

　Excel の関数は、３つの種類に分類されています。（参照：Excel4/Excel12-Different Types of Functions)　Excel-DNA では、関数の IsMacroType属性を True（=Class1） とするか False（=Class2） とするかによって揮発性を制御することが出来ます。また、IsVolatile属性 によって関数の揮発性を強制的に制御することも出来ます。
（Class3 はいわゆる”関数”ではなく、引数も返り値も持たない”コマンド” で、揮発属性を持ちません）

では、さっそく記述してみましょう。

（こんな感じ↓）

<DnaLibrary Language="CS"> 
<!&#91;CDATA&#91; 
   using System;
   using ExcelDna.Integration; 
   
   public class UnVolatileFunctions
   { 
        &#91;ExcelFunction(IsMacroType = false)&#93; 
        public static object RAND_UV()
        {
            return XlCall.Excel(XlCall.xlfRand);
        }

        &#91;ExcelFunction(IsMacroType = false)&#93; 
        public static object INDIRECT_UV(
　　　　　　　　　&#91;ExcelArgument(AllowReference = true)&#93;object range,object type)
        {
            if(type is ExcelMissing)  // type パラメータが空の場合
            {
                type = true;
            }
             ExcelReference Ref = 
　　　　　　　　　　(ExcelReference)XlCall.Excel(XlCall.xlfIndirect, range,  type); 
             return (object)Ref.GetValue(); 
        }

        &#91;ExcelFunction(IsMacroType = false)&#93;
        public static object OFFSET_UV(
　　　　　　　　　&#91;ExcelArgument(AllowReference = true)&#93; object range,
　　　　　　　　　　　　　　　　　　　　　　　　　　object row_offset,object col_offset) 
        { 
             ExcelReference Ref = 
　　　　　　　　　　　　(ExcelReference)XlCall.Excel(XlCall.xlfOffset, 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　range,  row_offset, col_offset); 
             return (object)Ref.GetValue(); 
        }
   } 

&#93;&#93;> 
</DnaLibrary> 

　このコードでは、それぞれ属性を不揮発性（IsMacroType = false）にしています。ですから、シートの変更やF9キー による再計算は行われず、再計算するにはCtrl + Alt + F9 キーを押します。ここでは、IsMacroType属性を使いましたが、IsVolatile属性でも同じです。（IsmacroType=true,IsVolatile=false として Class2 の関数を不揮発性にすることも出来ます）
　実は、この属性は明示しなくてもデフォルトで不揮発性となります。従って、定義する関数を揮発性としたい場合のみ IsMacroType=true （もしくは IsVolatile=true）と属性を明示する必要があります。例として、与えられた範囲の整数をランダムに返す揮発性関数 RAND_BETWEEN() を定義してみましょう(笑)

（こんな感じ↓）

        [ExcelFunction(IsVolatile = true)]
        public static int RAND_BETWEEN(int min,int max)
        {
             Random rnd=new Random();
             return (int)rnd.Next(min,max + 1);
        }

　いずれかのセルに =RAND_BETWEEN(1,6) と入力して F9キー を押すと1～6 の数値がランダムに表示されます。RANDBETWEEN(1,6) と同じですね＾＾

　余談ですが・・・RAND_BETWEEN は RANDBETWEEN という名前で定義することも出来ます。定義した関数名がすでにExcel で定義されているものと同じ場合は、Excel の組込関数が優先されUDFｓ側の関数は無視されるので、同名の関数を定義しても問題が発生することはありません。新しい関数を古いExcel で使えるようにするアドインとかも簡単に出来ちゃいそうですね♪

注意： 作成した INDIRECT_UV（） 関数は、Excel の組込 INDIRECT（） 関数とは挙動が異なります。
例えば、A1 ～ A5 セルまで、それぞれ 1,2,3,4,5 と入力しA6 ～ A10 は空白にしておきます。B1 セルに、=INDIRECT(“A1:A10”) 、C1 セルに =INDIRECT_UV(“A1:A10”) と、入れ、B1:C1 を選択して下方向にドラッグしてみてください。
（こんな感じ↓）

INDIRECT と INDIRECT＿UV

　ちなみにこの挙動については Office Tanaka の田中亨先生に教えていただきました。

たとえば、セル範囲A1:A10に任意の数値を入力して、ここに「データ1」という名前を付けます。
同じように、セル範囲B1:B10にも任意の数値を入力して、こちらには「データ2」という名前を付けます。
セルC1に「=データ1+データ2」という数式を入力すると、この数式を入力したセルC1(1行目)と「データ1」「データ2」が、それぞれ交差するセルを参照しますので、セルC1には「=A1+A2」の計算結果が表示されます。
セルC1の数式をセルC10までコピーすると、すべてのセルには「=データ1+データ2」という数式が入力されますが、実際に計算(参照)されるのは、数式が入力されている行だけです。

UDFｓ を作成し、既存のシートで組込の関数に代替する場合は挙動も含めてきちんと検証しないと・・・
思わぬ問題を引き起こしそうです。

では、また(笑)