Excel VBAでユーザ定義型関数(標準モジュールに作成)の処理が遅いので、ワークシート関数のように組込みで高速な関数を作成することは可能でしょうか?

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2006/02/19 14:28:56
  • 終了:--

回答(4件)

id:ohmix1 No.1

ohmix1回答回数235ベストアンサー獲得回数142006/02/19 17:03:46

ポイント10pt

http://www.officetanaka.net/excel/vba/speed/

Office TANAKA - VBA高速化テクニック(目次)

どの部分が遅いのかにもよりますが、

純粋にユーザ定義型関数に記述した処理自体が遅いのであれば...

1.アルゴリズムの見直しを行う

2.VBAの高速化テクニックを使う


※小手先のテクニックよりは高速なアルゴリズムを利用したロジックの見直しの方が効果がある場合が多いです


さらに高速化したければ、関数の処理ロジック部をVCにてDLLまたはCOMを作成し、

ユーザ定義型関数から呼び出すようにすることが考えられます。

id:tired

情報ありがとうございます。検討してみます。

以下の例(例に意味はありません)で10000セル計算するにも非常に時間が掛かりましてワークシートから直接呼べないものかと。

Function ppSum(ByVal A As Long)

ppSum = A * A

End Function

2006/02/19 17:58:31
id:olo No.2

olo回答回数80ベストアンサー獲得回数12006/02/19 19:33:38

ポイント5pt

もっと初歩で、毎回描画しなおしているところで

つまづいてはいないでしょうか?


Private Sub test()

Application.ScreenUpdating = False

’処理

Application.ScreenUpdating = True

End Sub

id:tired

それはないです。単純に回答1に示した関数にScreenUpdatingを付加しても状況は変わりません。(ちなみにVisualBasic Editorを一度開くとEXCELを終了するまで重くなるのだけどそれは仕様だと思うから言及しないでおこうと思いますw)

サンプル:重さ確認のため、計算数とセルの数を増やしました。速いPCだと分からないかもしれませんが。当方P3、1.7GHzで2秒程度です。

マクロを使用していますがウィルスは仕込んでいません。

http://tired.sakura.ne.jp/temp/sample002.zip

2006/02/19 23:13:33
id:olo No.3

olo回答回数80ベストアンサー獲得回数12006/02/20 01:37:50

ポイント110pt

サンプルのシート見せてもらいましたが、

問題が分かった気がします。


そもそも、関数は問題ない普通の関数です。

(べき乗をつかえばもう少し速くはなりますが。)


この例で遅い問題は別にあり、y=f(x)(D列 = function(C列))

の関係だけでよいはずなのに、C列の連番を振るのに

わざわざC列だけでまた関数を使用してしまっています。

このような横の関数、縦の関数が存在する場合、

遅くなってしまうのは仕方のないことでしょう。

性質上、下に行くほど参照元を辿る回数が増えてしまうからです。


今回の例で言えば、連番ルールは固定的で、関数にする

必要がないのであれば、右ドラッグで振るか、

いったん、値貼り付けをして、数式状態を取っ払って

しまえば早いですよね。


それでもやはり、縦の関係でも関数が入ってくるのであれば、

すべてをVBAに移行すれば劇的に早くなります。

サンプルの炊きなおしを書いてみました。

(A*A*の部分は変更しましたけど)

”Sheet2”というシートを作って、マクロを実行してみてください。


--------------------------------------------------

Option&nbspExplicit


Private&nbspConst&nbspc_STRow&nbsp=&nbsp2

Private&nbspConst&nbspc_EDRow&nbsp=&nbspc_STRow&nbsp+&nbsp10000

Private&nbspConst&nbspc_InCol&nbsp=&nbsp3&nbsp’C列

Private&nbspConst&nbspc_OutCol&nbsp=&nbsp4&nbsp’D列


Sub&nbsp計算()


&nbsp&nbsp&nbsp&nbspDim&nbspWS&nbspAs&nbspWorksheet

&nbsp&nbsp&nbsp&nbspDim&nbspi&nbspAs&nbspLong

&nbsp&nbsp&nbsp&nbspDim&nbspj&nbspAs&nbspLong

&nbsp&nbsp&nbsp&nbspDim&nbsplngBase&nbspAs&nbspLong

&nbsp&nbsp&nbsp&nbspDim&nbsplngInput&nbspAs&nbspLong


&nbsp&nbsp&nbsp&nbspApplication.ScreenUpdating&nbsp=&nbspFalse

&nbsp&nbsp&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSet&nbspWS&nbsp=&nbspSheets(”Sheet2”)

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWith&nbspWS

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsplngBase&nbsp=&nbspRange(”B1”)

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp.Cells(c_STRow,&nbspc_InCol)&nbsp=&nbsp0

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFor&nbspi&nbsp=&nbspc_STRow&nbspTo&nbspc_EDRow

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsplngInput&nbsp=&nbsp.Cells(i,&nbspc_InCol)&nbsp’C列の入力を保持

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp.Cells(i,&nbspc_OutCol)&nbsp=&nbspppSum(lngInput)&nbsp*&nbsplngBase&nbsp’D列出力

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp.Cells(i&nbsp+&nbsp1,&nbspc_InCol)&nbsp=&nbsplngInput&nbsp+&nbsp1&nbsp’C列の次の入力値作成

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspNext

&nbsp&nbsp&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEnd&nbspWith

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSet&nbspWS&nbsp=&nbspNothing

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

&nbsp&nbsp&nbsp&nbspApplication.ScreenUpdating&nbsp=&nbspTrue


End&nbspSub


Function&nbspppSum(ByVal&nbspA&nbspAs&nbspLong)

’&nbsp&nbsp&nbsp&nbspApplication.ScreenUpdating&nbsp=&nbspFalse

&nbsp&nbsp&nbsp&nbspppSum&nbsp=&nbspA&nbsp*&nbspA

’&nbsp&nbsp&nbsp&nbspApplication.ScreenUpdating&nbsp=&nbspTrue

End&nbspFunction

--------------------------------------------------


挙げたリンクは、開いたときに自動再計算

するか、手動再計算するかの設定についてです。

ちょっとしたネタです。

id:tired

詳細な回答ありがとうございます。

確かに参照の依存関係が多いと時間が掛かるのですが、今回の場合は、B1の変更に対してC列は依存していないので関数にしても処理時間が変わらないと思います。C列の連番の値を値貼り付けにしても処理時間が変わるように見えないのです。(もちろん先頭の連番の値を変えるとD列の全ての要素が再計算されますが。)

D列(本当はE列なんだけどm..m)を「=ppSum(0)*$B$1 」にしても処理時間は変わらない。

もっと単純にD列を「=ppSum(0)」にしても処理時間は変わらない。(この場合は依存しないので関数自体を更新しないと再計算されませんが)

回答3(上記)の規模の処理量を一瞬で出来れば文句ないのです。私が期待する回答は、マクロ関数の呼び出しコストを減らすためにワークシート関数のようにネイティブに呼ぶ(かどうかは不明)ような実装は出来ないのか?ということです。

回答1

>ユーザ定義型関数から呼び出すようにすることが考えられます。

直接呼び出せれば最高なのですが。

2006/02/20 02:28:31
id:ohmix1 No.4

ohmix1回答回数235ベストアンサー獲得回数142006/02/20 16:30:12

ポイント600pt

回答者1です。


私もワークシートからユーザ関数を呼び出す時の「内部的な名前解決」または「VBAの呼び出し処理」あたりにオーバーヘッドがあるように思います。


EXCEL97の時代ならCALL関数でDLLを直接呼び出せましたが、

現在ではセキュリティ面から関数が無効にされてます。

※EXCEL2003では復帰してるかも?


「Excel97 CALL問題」あたり

http://www.trusnet.com/secinfo/docs/shio2_msoffice/


http://www.atmarkit.co.jp/fwin2k/win2ktips/517exmlt/exmlt.html

@IT:Windows TIPS -- Tips:Excelのユーザー定義関数で複数の結果値を返す

ユーザ関数の呼び出し部が問題ならば、

1回の関数処理で複数のセルに値を返すという方法ではだめでしょうか?


http://support.microsoft.com/default.aspx?scid=kb;ja;289150

[XL2002] RTD 関数の設定および使用方法

XL2002以降では、RTD関数でCOMを呼び出すことが出来るようです。

ただし、どの程度のオーバーヘッドがあるのかは実験してみないと分かりません。


http://msdn.microsoft.com/library/default.asp?url=/library/en-us...


http://support.microsoft.com/default.aspx?scid=kb%3Bja%3B152152

サンプル: FRMWRK32.EXE: Excel の Generic.xll テンプレート を更新しました。

などと、いろいろな方法を調べていたら、

どうやらxllというDLL形式のAdd-In関数が作成できるようです。

リンクに有るFrmwrk32.exeを解凍して、[ツール]-[参照設定]でGeneric.xllを追加すると、func1とfuncsum関数が追加されます。

呼び出し自体は早そうです。

id:tired

おぉ!ありがとうございます。

この情報が欲しかったのです。

全て参考になりそうです。涙で明日が見えません。

ohmix1さん、oloさんありがとうございました。

2006/02/21 01:08:55

コメントはまだありません

この質問への反応(ブックマークコメント)

トラックバック

  • [技術]エクセルはてな(2) [技術]エクセルはてな(2) 2006-03-13 16:13:23
    はじめて「人力検索はてな」を利用した。質問は次の通りだ。 Excel VBAでユーザ定義型関数(標準モジュールに作成)の処理が遅いので、ワークシート関数のように組込みで高速な関数を作成す
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません