Excell VBA にて配列に定数を代入する方法

質問

Excell-VBAにおいて、大量の定数を二次元配列に代入して行く方法をご教授願います。

下記関数は、JIS並目ねじのM数（引数:B）の知りたい形状値（たとえば「ピッチ」ならば、引数C=1）を出力するものなのですが、40行もの代入文をずらっと並べて書くしか思いつかずに居ります。

昔のN88-BASICなどではREAD～DATA文を用いて、データーを羅列して書いておけば良かったのですが、Excell-VBAにおいては良い方法はありますか？

その他の希望としましては、ワークシートからデーターを読み込む方法では、ワークシートをいじられてはおかしくなってしまいますので、VBAコードだけで完結させたいです。
また、別途データファイルを作成しておいて、Open～命令で、という手法も用いないで作成したいです。
Array関数に、ずらっとデーターを並べるのも避けたいです。
（私は、Access-VBAは多少かじっておりますが、Excell-VBAにはかなりうといです。）

Function NAMIME(B, C)
Dim A(40, 5), D As Single
Dim I As Integer

' ピッチ 引っかかりの高さ 外径 有効径 谷の径
A(1, 1) = 0.25: A(1, 2) = 0.135: A(1, 3) = 1: A(1, 4) = 0.838: A(1, 5) = 0.729
A(2, 1) = 0.25: A(2, 2) = 0.135: A(2, 3) = 1.1: A(2, 4) = 0.938: A(2, 5) = 0.829
A(3, 1) = 0.25: A(3, 2) = 0.135: A(3, 3) = 1.2: A(3, 4) = 1.038: A(3, 5) = 0.929
＜中略＞
A(38, 1) = 5.5: A(38, 2) = 2.977: A(38, 3) = 60: A(38, 4) = 56.428: A(38, 5) = 54.046
A(39, 1) = 6: A(39, 2) = 3.248: A(39, 3) = 64: A(39, 4) = 60.103: A(39, 5) = 57.505
A(40, 1) = 6: A(40, 2) = 3.248: A(40, 3) = 68: A(40, 4) = 64.103: A(40, 5) = 61.505

D = -1
For I = 1 To 40
If A(I, 3) = B Then
NAMIME = A(I, C): D = 0
End If
Next I

If D Then NAMIME = "範囲外"

End Function

回答No.2

hana-hana3 · ベストアンサー

データをシートに記載して、そのシートを非表示（.Visible = xlVeryHidden）にして仕舞えば、マクロ操作以外の方法で表示させる事はできません。

http://www.officetanaka.net/excel/vba/sheet/sheet06.htm

お礼

ご回答、ありがとうございます。

Excelシートを非表示にすることが出来るのですね！
Accessではフォームだけを表示させて、テーブルの不正（不注意）書き換えを防いでいましたので、同様な方法が使えるとはありがたいです。

回答No.1

BLUEPIXY

DATA 文がガマンできるなら、それをシミュレートするようなコードを書けば良いと思います。
例えば、
Public A(40, 5) As Single

Private Sub initialize()
Const data_str As String = _
"0.25,0.135,1,0.838,0.729," & _
"0.25,0.135,1.1,0.938,0.829," & _
"0.25,0.135,1.2,1.038,0.929," & _
'＜中略＞
"5.5,2.977,60,56.428,54.046," & _
"6,3.248,64,60.103,57.505," & _
"6,3.248,68,64.103,61.505"
Dim data
Dim i As Integer, j As Integer, k As Integer
data = Split(data_str, ",")
k = 0
For i = 1 To UBound(A, 1)
For j = 1 To UBound(A, 2)
A(i, j) = CSng(data(k)): k = k + 1
Next
Next
End Sub
とかすればイイと思います。
A( )は、いちいち初期化するのもなんなので、Function MAMIMEから外にだしてブックオープン時などに一度初期化(initializeの呼び出し)します

お礼

ご回答、ありがとうございます。

起動時にSubをCallして、事前に配列に読み込ませておく手段ですね。
付加も”と＆くらいで済みそうですし、記載した程度の小規模配列にはピッタリの手法かと思います。
ご教授、ありがとうございました。

＞DATA 文がガマンできるなら

実は、これ以上の二次元データを扱いたいので、この先も、こんなに苦労して打ち込まなければならないのかなぁ。。。と悩んでいたところなんです。

扱うデータはJISなどの規格物で、そうそう変更があるものではないですから、データ列を一度関数化しておけば、何度もの使い回しに耐え得るので、たくさん作って蓄えておきたいのです。
フリーソフトでもJISの便覧があったりするのですが、パスワードが設定されていたりして、編集が出来なかったりするので、自作にチャレンジしているところなのです。