マクロ：エクセルVBAでのワークシート関数の参照について

質問

VBA内でワークシート関数を使用する際の、引数の参照について困っています。
満たしたい条件は、

(1)マクロ実行後のシート上においても、セル選択状態で数式「=B1*sum(A1:A3)」
といったように、「値」ではなく、「数式」が入力されているようにしたい。
↑
Application.WorksheetFunction.Sum(・・・は使えない？

(2)上記例のB1およびA1:A3については、マクロ内のユーザ入力によって位置が変わり、C1にもD1にもなり得るので、
cells(1,1)="=B1*sum(A1:A3)"
といった「B1」[A1:A3]的な参照を使いたくない。

イメージとしては、
sub sample()
number = inputbox("数字を入力してください")
cell_a = inputbox("セル１")
cell_b = inputbox("セル２")

yourrange = range(cells(cell_a,1),cells(cell_b,1)

cells(1,1)="=number*sum(yourrange)"・・・※
end sub

みたいなことをしたいのですが、※のようなことを
する方法はありますでしょうか？
説明が下手なもので、補足等あれば随時いたします。
使用環境はWindows2000、Office2000です。
よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

その種類のマクロは、練習用のみだと思います。実務レベルにはあまり良くないサンプルだと思います。
ダイアログ型は、よほど、複雑なものでない限りは、これレベルでは、回数は1回ぐらいにします。
なお、Application.Input( ,Type:=8) は、Range型ですから、範囲を選択できます。ただし、以下のコードは変更しなければなりません。

Sub TestSample()
　Dim Numver As Variant
　Dim cell_a As Range
　Dim cell_b As Range
　Dim myRange　As Range
　Dim msg As String
　On Error GoTo EndLine
　Number = Application.InputBox("数字を入力してください", Type:=2)

　'数字以外の入力のエラー処理
　If IsNumeric(Number) = False Then
　　 MsgBox "その入力は出来ません。"
　　 Exit Sub
　ElseIf Number = "False" Then
　　 Exit Sub
　End If

　msg = vbCrLf & "をクリックしてください。"
　Set cell_a = Application.InputBox("セル１" & msg, Type:=8)
　Set cell_b = Application.InputBox("セル２" & msg, Type:=8)

　'循環参照のエラー処理
　If Not Intersect(Cells(1, 1), Range(cell_a, cell_b)) Is Nothing Then
　　 MsgBox "その範囲選択は出来ません。"
　　 Exit Sub
　End If

　Set myRange = Range(cell_a.Cells(1), cell_b.Cells(1))

　Cells(1, 1).FormulaLocal = "=" & Number & "*SUM(" & myRange.Address(0, 0) & ")"
EndLine:
End Sub

Address(0, 0) は、相対参照。絶対参照は、(0,0) を取り、myRange.Address　とします。
FormulaLocal は、Excel Nativeモードで入力値を与える場合は、Formulaのみでも、.Valueプロパティでも良いのです。PCのコントロールパネル側の地域に関わる部分がある時、FormulaLocal プロパティが必要になります。

補足

早速のご回答ありがとうございました!!
お忙しい中恐れ入ります。

だいたいわかってきたのですが、もしご存知でしたら、
１つのセルをワークシート関数の中で、絶対参照、相対参照する方法を教えていただければと思います。

上記例の中で「Number」が「値」ではなく、
あるセルの数値を参照したいとき、のやり方です。

回答No.3

Wendy02

こんばんは。Wendy02です。

>１つのセルをワークシート関数の中で、絶対参照、相対参照する方法を教えていただければと思います。

少し、文章があいまいなのですが、ワークシート上のセルの数式を絶対参照、相対参照する方法と読み替えます。

Sub testFormula()
　MsgBox AbsRel(ActiveCell)
End Sub

'ユーザー定義関数を作りましたので、試してみてください。

Function AbsRel(mRange As Range)
Dim mStyle As Integer
Const ABSO As Integer = xlAbsolute
Const RELAT As Integer = xlRelative

　　If mRange.HasFormula And Not mRange.HasArray Then
　　If InStr(mRange.FormulaLocal, "$") > 0 Then
　　　 mStyle = RELAT　'相対
　　Else
　　　 mStyle = ABSO　'絶対
　　End If

　　AbsRel = Application.ConvertFormula( _
　　　　 Formula:=mRange.FormulaLocal, _
　　　　 fromReferenceStyle:=xlA1, _
　　　　 toReferenceStyle:=xlA1, _
　　　　 toabsolute:=mStyle)
　　 End If
End Function

>「Number」が「値」ではなく、
>あるセルの数値を参照したいとき、のやり方です。

セルの数値とInputBox で入れる　Number とは、変数では、共有できません。仮に、Variant 型にしても、Set で、数値を格納するときにエラーが出てしまいます。

Set cell_a = Application.InputBox("セル0", Type:=8)
Number =cell_a.Value

という形ぐらいだと思います。

お礼

ご回答ありがとうございます。
ご丁寧な説明をしていただいたおかげで、
解決することができました。
まだまだわからないことだらけですが、
またよろしくお願いします。

回答No.1

masa_019

こんばんは。
多分こんなことがしたいのかなと思いますが

Sub sample1()
Dim Number
Dim cell_a As Long
Dim cell_b As Long
Dim yourrange As String
Number = InputBox("数字を入力してください")
cell_a = InputBox("セル１")
cell_b = InputBox("セル２")
yourrange = Range(Cells(cell_a, 1), Cells(cell_b, 1)).Address
Cells(1, 1).Formula = "=" & Number & "*sum(" & yourrange & ")"
End Sub

いかかでしょう?

補足

早速のご回答ありがとうございました!!
お忙しい中恐れ入ります。

だいたいわかってきたのですが、もしご存知でしたら、
１つのセルをワークシート関数の中で、絶対参照、相対参照する方法を教えていただければと思います。

上記例の中で「Number」が「値」ではなく、
あるセルの数値を参照したいとき、のやり方です。