VBAのFormulaArrayについて

質問

VBAについて調べても原因がわからなかったため質問です。

エクセルで統計処理をする先人が書いたコードが動かないため、デバッグしています。
OSはWindowsXP、エクセルは２００７です。
先人が作成していた環境はわかりませんが、5年前に書いたようです。
この時点であきらめろよという話ですが、そうもいかない状況なので一つお付き合いください。

下記の関数の挙動がおかしいです。

Sub　標本入力（）
　　　・・・・省略・・・
　　If ActiveSheet.Name = "連鎖独立グラフ" Then
連鎖独立シート初期化 3
Else
　・・・・省略・・・
Range("C301").Select
Selection.PasteSpecial Paste:=xlValues
独立シート初期化 3
End If
Application.CutCopyMode = False
Cells(11, 3).Select

End

Sub 独立シート初期化(n)　　'ｎは相関行列の変数の個数
ActiveWorkbook.Names.Add Name:="母相関", RefersTo:=Range(Cells(51, 4), Cells(50 + n, 3 + n))
ActiveWorkbook.Names.Add Name:="偏相関", RefersTo:=Range(Cells(101, 4), Cells(100 + n, 3 + n))
ActiveWorkbook.Names.Add Name:="相関残差", RefersTo:=Range(Cells(151, 4), Cells(150 + n, 3 + n)
・・・・省略・・・

Range("母相関").FormulaArray = "=標本相関-相関残差-TRANSPOSE(相関残差)"
Range("偏相関").FormulaArray = "=-逆行列/SQRT(対角*TRANSPOSE(対角))" '★この行を処理していない！！
Range("逆行列").FormulaArray = "=MINVERSE(母相関)"
・・・・省略・・・
End

★のついたコメントの前の行にさしかかると、それ以降の処理が無視されて標本入力（）の処理に戻り、Ifを抜けて「Application.CutCopyMode = False」以降の処理にかかってしまいます。

デバッグしたところ、どうもFormulaArrayがあると処理を無視してしまうようです。しかしエラーは出てこないので、どう改善していいかがわかりません…他の関数で利用されているFormulaArrayはどうも正常に動いているようなのです。

参照設定はデフォルトの状態からSOLVERのみ追加しており、シートの保護はかけていません。
ど素人なので、不明な情報がありましたら追記致します。どうか、御教示頂けると幸いです。

回答No.1

WindFaller · ベストアンサー

>どう改善していいかがわかりません

再現性が取れないので、質問内容では、正確な原因は分かりません。
あくまでも想像の範囲は越えません。

まず、「逆行列」の名前の定義のコードは、たぶん、省略してしまったのでしょうね。
ご質問上のコードでは、唐突に出てきているように見えます。

いずれにしても、逆行列のマトリックス（＝計算表）がシート上に置いてあるのでしょうから、私は、そのコードの再現性を試すことは出来ませんが、ExcelのMinversの逆行列は、サイズが非常に小さかったと思います。その逆行列の計算は、手動で値が出るのでしょうか？（数式のセルにカーソルを乗せ、F2で開けて、再度、Shift+Ctrl - Enterで、配列確定をすれば出るはずです）

一度、手動で試してみたほうがよいです。

正直なところ、Excelの関数を使ってでは、大したことは出来ないはずです。コードとして、FormulaArray は問題ありません。

それと、私の悪い想像だけであってほしいのですが、サブ・プロシージャ上で、ActiveWorkbook.Names.Add という使い方をしていますが、VBAでは、Namesのプロパティが二重の登録になりかねません。本来は、二重登録などありえないことです。なぜ、トラブルが起こるのか正確な理由は分かりません。

私の推測では、Namesの親オブジェクトが、シート、ブック、アプリと何種類もあるということが問題点です。コードとしては、論理的には正しいのですが、実務的には、そのVBAのコードは、トラブルが出やすいのではないかと思います。できれば、名前の定義登録のように、ひとつのブックで1回限りのようなメソッドは、手動でやったほうがよいのです。

もし、範囲指定が、その都度変わるなら、名前の定義登録だけのマクロを置いたほうがよいです。

そうでなかったら、単に、Rangeプロパティに、パラメータとして範囲を入れてあげたほうが、簡単だと思います。

コンピュータ用語が多いので、文章が分かりにくかったらすみません。

お礼

情報少ないのにご回答頂いてありがとうございます。あまりに長いコードでして、省略せざるを得えませんでした。ご指摘頂いたところをもう少し考えてみます。