- PR -

Excel：自分で定義したFunctionを使用しているセルの自動再計算

1|2 次のページへ»
投稿者投稿内容
Take-C
常連さん
会議室デビュー日: 2002/05/09
投稿数: 23
投稿日時: 2008-01-07 11:03
Excel 2000でアドインを作成していますが、少し困ったことになってます。
自分で定義したFunctionを使用しているセルを自動的に再計算させようとしています。
アドインでやりたいことは以下のようなことです。

標準モジュール：
コード:
Public Function MyTest(引数) As String



    ・・・

    MyTest = ・・・ '計算



End Function


ワークシートのセルに書く計算式：
コード:
=MyTest(引数)


このような状態でMyTestが計算式に使用されているセルを自動的に再計算させたいのです。再計算のタイミングはCOM接続されたDLL(VC++で作成)で決定します。

考えた方法としては

  1. MyTest(A1, その他の引数)という書式にしてセルA1をVC++から更新する（値は何でもよい）
  2. 現在開いているすべてのワークブックおよびワークシートから計算式が含まれるセルを検出し、計算式に文字列"MyTest"が含まれていればそのセルをVC++から同じ計算式で上書きする(=再計算される)

などです。

1.の方法ならアドインの作り方は簡単ですが、MyTestを使うユーザは常に余分なセル(この例ではA1)を意識する必要があり、あまりスマートな書式には見えません。
2.の方法ならMyTestを使うユーザは純粋に自分が必要な情報だけを引数に指定すればいいので見た目はすっきりするのですが、パフォーマンス上の問題があります。

何かもっとスマートかつパフォーマンス上もさほど気にしないで済むような方法はないものでしょうか？
セルの数が増えればたとえ1.の方法でもパフォーマンスが落ちるのは必至ですが、この場合は止むを得ないと考えてます。

[ メッセージ編集済み 編集者: Take-C 編集日時 2008-01-07 11:04 ]

[ メッセージ編集済み 編集者: Take-C 編集日時 2008-01-07 11:05 ]
unibon
ぬし
会議室デビュー日: 2002/08/22
投稿数: 1532
お住まい・勤務地: 美人谷　　　　　　　　良回答(20pt)
投稿日時: 2008-01-07 12:23
引用:

Take-Cさんの書き込み (2008-01-07 11:03) より:
考えた方法としては

  1. MyTest(A1, その他の引数)という書式にしてセルA1をVC++から更新する（値は何でもよい）
  2. 現在開いているすべてのワークブックおよびワークシートから計算式が含まれるセルを検出し、計算式に文字列"MyTest"が含まれていればそのセルをVC++から同じ計算式で上書きする(=再計算される)

などです。

以下は、あまりプログラマーとしての回答ではなく、日頃 Excel を使っているエンドユーザーとしての回答になります。

1. は (MyTest(その他の引数) + セルA1) でも良いかもしれません。ダミーの引数にしなくても良いという程度です。数値か文字列かを意識しないといけないかもしれませんが。

ほかに思いつくのは、Excel は DDE クライアントですので、DDE サーバーを作ってそれと通信するようにすれば、セルを書き換えるのではなく、DDE で値を更新するやりかたでもできます。
もっとも、そんなややこしいことをしても、
(MyTest(その他の引数) + DDE用のパラメーター)
となり、セルがDDE用のパラメーターに替わるだけで、それほど嬉しくはありませんが。
Take-C
常連さん
会議室デビュー日: 2002/05/09
投稿数: 23
投稿日時: 2008-01-07 15:42
unibonさん、ありがとうございます。
引用:

unibonさんの書き込み (2008-01-07 12:23) より:
引用:

Take-Cさんの書き込み (2008-01-07 11:03) より:
考えた方法としては

  1. MyTest(A1, その他の引数)という書式にしてセルA1をVC++から更新する（値は何でもよい）
  2. 現在開いているすべてのワークブックおよびワークシートから計算式が含まれるセルを検出し、計算式に文字列"MyTest"が含まれていればそのセルをVC++から同じ計算式で上書きする(=再計算される)

などです。

以下は、あまりプログラマーとしての回答ではなく、日頃 Excel を使っているエンドユーザーとしての回答になります。

1. は (MyTest(その他の引数) + セルA1) でも良いかもしれません。ダミーの引数にしなくても良いという程度です。数値か文字列かを意識しないといけないかもしれませんが。

なるほど、そういう方法もありますね。ただ、やはり本来の処理とは関係ないセルを意識する必要性が残ってしまいますね。

引用:

ほかに思いつくのは、Excel は DDE クライアントですので、DDE サーバーを作ってそれと通信するようにすれば、セルを書き換えるのではなく、DDE で値を更新するやりかたでもできます。
もっとも、そんなややこしいことをしても、
(MyTest(その他の引数) + DDE用のパラメーター)
となり、セルがDDE用のパラメーターに替わるだけで、それほど嬉しくはありませんが。

DLLではなくDDEサーバを使って
=MyAPP|MyTest!その他の引数
というような方法も考えたのですが、DDEはMicrosoft自身もあまり推奨していないようです。
http://office.microsoft.com/ja-jp/excel/HP030662101041.aspx

それならOLEで、とも思ったのですが、セルの中の計算式からOLEリンクを参照する方法が見当たりません。

やはり最初の要求そのものに無理があるのでしょうかね。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-07 15:55
MyTest関数を使用しているRangeを取得してから、
一括で再計算させる手順はどうでしょうか。
コード:
  Dim r As Range
  Dim c As Range

  For Each c In Cells.SpecialCells(xlCellTypeFormulas)
    If InStr(1, c.Formula, "MyTest", vbTextCompare) > 0 Then
      If r Is Nothing Then
        Set r = c
      Else
        Set r = Union(r, c)
      End If
    End If
  Next

  r.Dirty

Range取得を事前に済ませておけるなら、もう少し高速化できますし。
キナサイ
会議室デビュー日: 2007/07/13
投稿数: 6
お住まい・勤務地: 神奈川・東京
投稿日時: 2008-01-07 17:27
Volatile メソッドをユーザー定義関数内で使うのでは目的に合わないのですか？
Take-C
常連さん
会議室デビュー日: 2002/05/09
投稿数: 23
投稿日時: 2008-01-07 17:56
こあらさん、ありがとうございます。
Excel 2000のRangeには残念ながらDirtyメソッドはないようです。
やるとすればこんな感じでしょうか。
コード:
Dim b As Workbook
Dim s As Worksheet
Dim r As Range
Dim c As Range

For Each b In Application.Workbooks
  For Each s In b.Worksheets
    Set r = s.Cells.SpecialCells(xlCellTypeFormulas)
    For Each c In r
      If InStr(1, c.Formula, "MyTest", vbTextCompare) > 0 Then
        c.Formula = c.Formula '再計算させるために強引に計算式を上書き
      End If
    Next
  Next
Next

実際には上記の処理をDLLからCOMを通じて行うことになります。

引用:

こあらさんの書き込み (2008-01-07 15:55) より:
Range取得を事前に済ませておけるなら、もう少し高速化できますし。


Range取得を事前にしておきたいのはやまやまなのですが、いつどのセルにユーザがこの関数をセルに書くかはまったく不明なため、毎回計算式があるセルを取得することになりそうです。

「そんな要求を満たすことは不可能です」とわかる資料があればいいんですが。


引用:

キナサイさんの書き込み (2008-01-07 17:27) より:
Volatile メソッドをユーザー定義関数内で使うのでは目的に合わないのですか？

キナサイさん、ありがとうございます。
なんと、こんなメソッドがあったのですね。
再計算させるタイミングはユーザではなく別アプリケーションから制御したいので、そのタイミングの制御が難しそうですが、ちょっと調べてみます。
Error401
常連さん
会議室デビュー日: 2007/03/12
投稿数: 39
投稿日時: 2008-01-07 18:08
よく読んでないんですが、MyTest()の中でActiveSheet.NameやActiveCell.Addressなどの情報をCollectionなどに登録し、更新するタイミングでそれらがまだセルに設定されているかどうかを確認しながら更新していくというのはどうでしょう？（セルに設定されていなかったらCollectionから除去する）
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-07 18:30
引用:

Excel 2000のRangeには残念ながらDirtyメソッドはないようです。

大変失礼致しました。新しいメソッドだったのですね。

Excel2000では Application.CalculateFull で誤魔化すしかないかもしれません。
シートの状態にもよりますが、個々セルごとに代入するよりは速そうです。
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）