エクセルVBAでEvaluate関数で指定子を使う

質問

テーブル設定された表があります。
表には数字や数式が入っているのですが、
運用しているうちに数式が増え、再計算が時間が長くてストレスになってます。

時間を短くするため、VBAを使用して数式の計算結果と同じ値を設定（“値のみ貼り付け”と同じこと）をして数式を減らそうとしています。
ただし、数式は今後も追加・変更されるため、
そのときのVBAソースコード修正の手間を最小限にしたいという事情もあります。

まずは
【ソースコード１】
For i = 1 To 10000
Cells(i, 金額列).Value = Evaluate("[@単価]*[@数量]")
Next i
このようなコードを書いたのですが、エラー値になってしまいます。
Evaluate関数で指定子（角かっこ）は使えないのでしょうか。

使えないなら別の方法として、
数式を設定した後で、値をもう１度代入する方法を考えてます。
【ソースコード２】
For i = 1 To 10000
Cells(i, 金額列).Formula = "[@単価]*[@数量]"
Next i
Range("A1:Z10000").Value = Range("A1:Z10000").Value ’計算結果だけをもう１度代入して数式を消す

他に良いやり方があったら教えてください。

回答No.3

real beatin（@realbeatin） · ベストアンサー

こんにちは。
２例でお応えします。

まず、ご所望の
.Evaluate メソッドを用いて、定数値配列を直接
必要な範囲全体に一度で出力する方法。ループは不要です。
' ' //
Sub Re9335772Evl()　'　要指定■↓テーブル名
　　Evaluate("テーブル1[金額]") = Evaluate("テーブル1[単価]*テーブル1[数量]")
End Sub
' ' //
正しく階層を追って参照する、と正しい結果が得られます。
上に挙げたのはApplication オブジェクトの.Evaluate メソッド
の省略形になりますが、階層を追って参照という意味では、
　　With Sheets("Sheet1")　'　要指定■シート名、↓テーブル名
　　　　.Evaluate("テーブル1[金額]") = .Evaluate("テーブル1[単価]*テーブル1[数量]")
　　End With
のように、Worksheet オブジェクトに対しての
.Evaluate メソッドをハードに決める方が確実ですし、
統一感が増しますね。
それから、.Evaluate メソッドを使うならループの中ではなく、
配列を一発で出力する場合に絞って考えておいた方が間違いが少ないです。
どうしてもループの内で.Evaluateしたい場合は、
きっちりとエラートラップを掛けてあげないと、
比較的重いトラブルを招くこともありますので、気を付けてください。

次に、ListObject オブジェクトの扱い方ですが、
テーブルの場合も、ループは不要ですね。
デフォルトでは、先頭セルにひとつだけ
数式（というよりクエリ）を入れてあげれば、
最下行まで計算してくれますので、
その後で、固定値にするようにしてみます。
こちらの方が基本操作を扱っていることになりますので、
知っておいた方が今後、困ること少なくなると思います。
' ' //
Sub Re9335772Lst()　'　要指定■↓シート名、↓テーブル名orIndex
　　With Sheets("Sheet1").ListObjects("テーブル1").ListColumns("金額").DataBodyRange
　　　　.Cells(1) = "=[単価]*[数量]"
　　　　.Value = .Value
　　End With
End Sub
' ' //

提示したコードについては、概念的な理解を優先する為に
敢えてべたな書き方を選んでいます。
実際に書く時は適宜、変数や定数を組み合わせるものと思います。

以上です。

お礼

ありがとうございます。

Re9335772Evl()を試したところ
[単価]*[数量] の場合は期待通りの結果が得られたのですが、
LEFT([商品コード], 3) の場合は
1行目の結果が、1行目から最終行まで(すべて同じ値が)設定されてしまいました。
LEFT関数を使うと動きが変わるのか、私のエクセルがおかしい(何か隠しパラメータが設定されてる)のか。。。

Re9335772Lst()であれば、計算式もLEFT関数も期待通りの結果になりました。ありがとうございます。

回答No.6

real beatin（@realbeatin）

No.5 お礼コメントへの返信です。

> 今時点では基本操作の方が理解しやすそうなこと、
> 数式は今後も変わっていくものであり、私以外の(VBAを使い慣れてない)人が追加変更するときも制約が少なそうなことから、
> そのまま渡す方法(Re9335772Evl)にします。

うーんと、文脈からすると、Evaluate メソッドを使わない方、
テーブルに数式（クエリ）を渡して計算させてから固定値にする方、
　Sub Re9335772Lst
にする、ということですよね？
その方が良さそうですね。

> そして質問がどんどん派生してしまいますが、
> そのまま渡す方法を採用しても、[#見出し]のように、#を含んでいるとエラーになってしまいました。
> もしかして#はファイル操作に使う特別な文字なのでエラーになったのでしょうか？
> エスケープするなどの回避策はありますか？

#の問題ではなくて、Excelを日本語環境で使う為のロケール設定が
中途半端な仕様になっていることが原因です。
回避策、２例。
' ' //
　　With Sheet1.ListObjects("テーブル1").ListColumns("上３桁").DataBodyRange　'　要指定■シート名、テーブル名orIndex
　　　　.Cells(1) = "=テーブル1[[#Headers],[商品コード]]&[@商品コード]"
　　　　.Value = .Value
　　End With
' ' //
　　With Sheet1.ListObjects("テーブル1").ListColumns("上３桁").DataBodyRange　'　要指定■シート名、テーブル名orIndex
　　　　.Cells(1).FormulaLocal = "=テーブル1[[#見出し],[商品コード]]&[@商品コード]"
　　　　.Value = .Value
　　End With
' ' //
　#見出し　#Headers
　#すべて　#All
　#集計　#Totals
　#データ　#Data

お好きな方法でどうぞ。

お礼

早い返信、ありがとうございます！
#見出しはダメでしたが、#HeadersだとOKなのですね！

補足

すみません、ご指摘の通りRe9335772EvlではなくRe9335772Lstの方でした。

また何度も質問に答えていただき
お付き合いありがとうございました！！

回答No.5

real beatin（@realbeatin）

No.3 お礼コメント欄への返信です。

> LEFT関数を使うと動きが変わるのか、私のエクセルがおかしい(何か隠しパラメータが設定されてる)のか。。。
こんな数式になります。（[上３桁]というフィールドの[商品コード]の左３桁を出力する例）
　　Range("テーブル1[上３桁]").Value = Evaluate("INDEX(LEFT(テーブル1[商品コード],3),)")
そのままでは配列を返してくれない関数、この場合ではLEFT()関数、
の場合には、"INDEX(LEFT(s,n),)"のような書式で配列を返してくれます。
ここら辺は、Excelの配列数式、中でも、複数セル範囲で確定するタイプの配列数式
の扱い方を参照します。
.Evaluateメソッドそのものは、VBAからExcelワークシートに計算を命令するコマンド
ですので、Excel数式への理解を深める必要はあります。
でも、扱いに困るようでしたら、テーブル側にクエリを渡すやり方の方が
簡単ですよね。
計算が速いという意味では、レコード数が多い程.Evaluate推しですけれど。

お礼

ありがとうございます。奥が深いのですね。
配列数式、勉強します。

今時点では基本操作の方が理解しやすそうなこと、
数式は今後も変わっていくものであり、私以外の(VBAを使い慣れてない)人が追加変更するときも制約が少なそうなことから、
そのまま渡す方法(Re9335772Evl)にします。

そして質問がどんどん派生してしまいますが、
そのまま渡す方法を採用しても、[#見出し]のように、#を含んでいるとエラーになってしまいました。今回は別の数式に置き換えたので大丈夫ですが、
もしかして#はファイル操作に使う特別な文字なのでエラーになったのでしょうか？エスケープするなどの回避策はありますか？

回答No.4

real beatin（@realbeatin）

No.3 です。
間違いがあった訳ではありませんが、修正を１点。

' ' //
Sub Re9335772Evl()　'　要指定■↓テーブル名
　　Range("テーブル1[金額]").Value = Evaluate("テーブル1[単価]*テーブル1[数量]")
End Sub
' ' //

以上のように、出力コードの左辺については、
.Evaluate メソッドを態々使うと、
無駄なオーバーヘッドに掛かってしまいますので、
普通にRangeを使う方法に修正、とさせてください。
テーマが.Evaluate でしたので、
つい、余分に使ってしまいました。

それから、角括弧の話題（？）についてですが、
テーブル参照における識別子、というのは、
質問者さんは、正しく理解されていらっしゃいます。

NO.1さんの仰るのは、Evaluateのショートカット記法のことで、
　[テーブル1[金額]] = [テーブル1[単価]*テーブル1[数量]]
という書き方で.Evaluate メソッドを省略して書く時の
（この式では両辺外側にある）角括弧のことなのだと思いますが、
この記法ではVBAの変数や定数を扱えませんし、
更に余分なオーバーヘッドが掛かりますから、使い途は限定されます。
シートデザインをする時とか、一度きりの処理を短時間で終えたい時などには、
[D11:F20].interior.color=[A1].interior.color
みたいにサッサと書けてラクできる、という意味で重宝します。
しかし、この角括弧のお話は、今回の課題とは全く無縁ですので、
混乱されることのないようにと、申し添えておきます。

.Evaluate メソッドを扱うことに関しては、
比較的情報が少なく、中でもやや錯綜した情報が多い面もあり、
歪んだ発信を目にすることもありますが、
大切なことは、適した場面で正しく使う、ということです。
間違った使い方をすれば、どんなものにも相応の結果が追いてくるだけのこと。

今回の課題は、
　比較的大きなセル範囲を一纏めにして、値の入出力をする
　クエリの代用として識別子（または定義された名前）を扱って演算する
.Evaluate メソッドの特長が、よく表れている、
そのメリットを大きく享受できる、好例であると思います。
そういう意味では、間違いなく、"適した場面"、なのです。

以上、追加補足として。

回答No.2

Nouble

済みません、間違えています
お詫びの上、訂正させて
ください

誤記1
要は、配列数式的に
Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1))*.Range(.cells(1,2),.cells(100,2))
と、したい

正記1
要は、配列数式的に
Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1)).Value　*　.Range(.cells(1,2),.cells(100,2)).Value
と、したい

誤記2
使わず
With　ActiveSheet
　Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1)))*.Range(.cells(1,2),.cells(100,2)))
End　With
で、行けませんか？

正記2
使わず
With　ActiveSheet
　Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1)))).Value　*　.Range(.cells(1,2),.cells(100,2)))).Value
End　With
で、行けませんか？

失礼しました

ところで、
Const、日本語変数名、
を、使えば
明瞭な、コーディングも
可能、ですよ

もう一つ
あらゆる、意味で
確認、してませんが

正直な、話し
もし
千件位、だと

仮に
Evaluate("[@単価]*[@数量]")
的な、事が
Evaluateで、出来た
と、しても

Application.WorksheetFunction.Product([@単価]　,　[@数量])
の、方が
早いかも？

更には、以外と
.Range(
が、
もっと、言えば
.cells(
が、

手動計算下、では
早い
の、です
よね　(^_^;)

お礼

ありがとうございます。
質問の本題ではないので省略しましたが、数式が入っているセルは20列ほどあり、
VLOOKUPやMATCH、INDEX、LEFT、WEEKNUM、IFのネストがふんだんに使われております。

ちなみに私以外の担当者が数式を考えて、私がVBAに反映させることになっています。

回答No.1

Nouble

まず此を
https://msdn.microsoft.com/ja-jp/library/office/ff193019.aspx

実際に
使って、みれば
解る事、ながら
MSは、
[　]は、Evaluateだ
と、しています

しかし、
本当は
少し、違い

Range(
に、近い
感じ、です

Cells(i, 金額列).Value = Evaluate("[@単価]*[@数量]")
と、されていますが

よく解らない
の、ですが
恐らく
@単価、@数量、
は、名前定義
ですかね？

要は、配列数式的に
Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1))*.Range(.cells(1,2),.cells(100,2))
と、したい
て、事ですか？

もし、そうなら
With 　ActiveSheet
　.Cells(i, 金額列).Value = Evaluate(　.Range(@単価).address　*　.Range(@数量).address)
End　With
と、してみてください

http://www.relief.jp/docs/excel-vba-get-value-named-cell-range.html

でも、此だと
単に
With 　ActiveSheet
　.Cells(i, 金額列).Value = .Range(@単価)　*　.Range(@数量)
End　With
と、同じかな？

更には
名前定義、等
使わず
With　ActiveSheet
　Let　.Range(.cells(1,3),.cells(100,3)).Value　=　.Range(.cells(1,1),.cells(100,1))*.Range(.cells(1,2),.cells(100,2))
End　With
で、行けませんか？

蛇足
〉【ソースコード２】
ですが

Formulaを、Valueに
変える、前に
Calculate
した方が
無難、ですよ

で、
其の、際
ですが

加えて
メモリー不足が、出易い
ので
Formula、
Calculate、
Value、
を、
各々
サブルーティン化、し
別モジュールに
置いた、方が
無難、ですよ

http://officetanaka.net/excel/vba/error/execution_error/error_7.htm
OfficeTanaka氏の発言、抜粋
〉巨大なプロシージャなどを作成すると
〉「メモリが足りません」と怒られることがあります。
〉私も過去に、数回経験しています。
〉まぁ、そんなときは、
〉モジュールやプロシージャを分割すればいいだけなので、

と、言う事で
モジュールを
別けられた、方が
良い
ですよ

と、言うか
抑も、
Application.Calculation = xlCalculationManual
を、
して、おかないと

ワークシート、演算待ち
に、よる
同期不善、待ち発生、
で
簡単に、転けます
よ　(^_^;)

で、
此で、転ける
時も
どうやら、メッセージは
メモリー不足
ですね　(^_^;)

お礼

ありがとうございます
@単価、@数量は回答No.3の方の通りです。