Excel2003 VBA Functionの定数に関して教えて下さい

質問

Excel2003 VBA Functionの定数に関して教えて下さい。

例えば、
Function test(x, y)
test = A * x + B * y + C
End Function

という数式を定義し、プログラム中で使用したいとします。
数式を見て分かる通り、xとyは変数でA, B, Cは定数です。

そして、これらA, B, Cの値を
A = Cells(3,5)
B = Cells(3,6)
C = Cells(3,7)

のようにシート上の値を使用したいのですが、
上記のようにプログラム中で宣言してもFunctionの中では値が入っていないものとみなされてしまいます。

この問題の回避のため、、

Function test(x, y)
A = Cells(3,5)
B = Cells(3,6)
C = Cells(3,7)
test = A * x + B * y + C
End Function

のようにFunctionの中に、定数を宣言を入れてしまうか、

Function test(x, y, A, B, C)
test = A * x + B * y + C
End Function

のようにA, B, Cも定数ではなく、変数として扱う方法があります。

しかしながら、一つ目の方法では、こういったFunctionの数が増えてくると、
同じ定数を複数の場所で宣言することになり、後からプログラムを書き直そうとしたときに
極めて不便です。

一方で、２つめの方法では、test(x, y, A, B, C)のように、
一つのFunctionを呼び出すためにごちゃごちゃしてスペースをとり、
後から見たときに見にくくなります。

後、Constとして定義する方法もありますが、
A = Cells(3,5)
のように、シート上のデータを代入する方法をとりたいと考えています。

上記以外の方法以外でもっとスマートな方法がありましたら
教えて頂けますでしょうか？

回答No.1

kmetu · ベストアンサー

標準モジュールで

Public A As Integer
Public B As Integer
Public C As Integer

と宣言して

Private Sub Workbook_Open()

A = Worksheets(x).Cells(3, 5)
B = Worksheets(x).Cells(3, 6)
C = Worksheets(x).Cells(3, 7)

End Sub

で代入して利用すればいかがですか

回答No.4

Wendy02

>一つ目の方法では、こういったFunctionの数が増えてくると、
>同じ定数を複数の場所で宣言することになり、後からプログラムを書き直そうとしたときに
>極めて不便です。

本来、こういう質問はご自身が工夫するしかないと思うし、スマートって格好良さだと思いますが、そういうスマートさというよりも結果論で収まってしまうものです。それを他人に求めても、私は、どうしようもないものだと思います。

それぞれの回答者の方のご意見もっともですが、私の視点からすると、「Functionの数が増えてくる」という言葉に引っかかります。Function が増えるということに疑問を感じます。おそらく、Sub プロシージャで処理するべきものを、Function ユーザー定義にしているのだろうと思います。どこかで設計自体が間違っているとしかいいようがないですね。いまさらと言われても困るかもしれませんが、Sub プロシージャでセルの値を拾えば、それで済むはずです。

何も面倒なはずはないので、それでうまくできないようなら、技術的な問題だと思います。

回答No.3

jcctaira

関数になら、変数になる部分は引数にするべきだと思います。

Function test(x, y, A, B, C)

Z=(1,2,Cells(3,5).Cells(3,6),Cells(3,7))

ただ、A,B,Cはほとんど変更がないとのことだと思いますので、デフォルト値
を指定し、A,B.Cを省略できるようにすれば便利かと思います。

Function test(x, y, Optional A = 1, Optional B = 2, Optional C = 3)
　　test = A * x + B * y + C
End Function

関数の呼び方は以下のように指定することができます。
z = test(1, 2)
z = test(1, 2, Cells(3, 5).Cells(3, 6), Cells(3, 7))
z = test(1, 2, , , Cells(3, 7))
z = test(1, 2, C:=Cells(3, 7))

回答No.2

mitarashi

>Function test(x, y, A, B, C)
WindowsのAPIを使っていると、引数の5個位あたりまえで、どうって事はないと思いますが...
構造体を使えばすっきりしますか？

Type myType
A As Range
B As Range
C As Range
D As Range
E As Range
End Type

Sub test()
Dim mt As myType

With mt
Set .A = ActiveSheet.Range("A1")
Set .B = ActiveSheet.Range("A2")
Set .C = ActiveSheet.Range("A3")
Set .D = ActiveSheet.Range("A4")
Set .E = ActiveSheet.Range("A5")
End With
Debug.Print funcTest(mt)
End Sub

Private Function funcTest(mt2 As myType)
With mt2
funcTest = .A.Value + .B.Value + .C.Value + .D.Value + .E.Value
End With
End Function