マクロで計算した結果を自動的に表にする方法はないでしょうか？

質問

知人から頂いたエクセルファイルで、あるパラメーターを入力すれば計算結果がセルＭ１に出るといったマクロが組んであるファイルがあります。ちなみに、パラメーター入力はセルへの入力ではなく、メッセージボックスが表示されそこに入力する方法です。
パラメーター量は膨大であるため、何とか効率的に計算結果表を作成したいのですが、何か良い方法はないでしょうか？

高度なＶＢＡ知識が必要となるようであれば諦めて地道に頑張ります。

回答No.6

zap35 · ベストアンサー

＃０１です。補足に対する回答です。
＞「value」が含まれている行が見当たりませんでした
　はい、　.Value　はなくても同じことです。
＞”Ｍ６５５３６”
　M列の最終行を意味します。
　Range("M65536").End(xlUp).Offset(1,0).Value = 計算式
でM列のデータ最終行の１つ下のセルに結果を代入しています

あまり多くの回答があると混乱するかもしれませんが、パラメータを１件ずつ入力するのは大変でしょうから、今あるマクロをユーザ定義関数にする方法も説明します。

（１）ALT＋F11でVBE画面を開き、「VBAProjectエクスプローラのシート名右クリック」→「挿入」→「標準モジュール」で表示される画面にペーストして下さい。
Function Kekka(ByVal rng As Range) as Variant
’ここに貼り付ける
End Function

（２）今使っているマクロの最初の行（Sub マクロ名()の行）と最後の行（End Sub)以外の行を（１）に貼り付ける

（３）「変数=InputBox」と書いてある行の先頭に「'」をつけてコメントにし、その行の直下に
　変数名 = rng.Value
と書く。（変数名はInputBoxで入力した値が格納される変数名）

（４）補足にあった、
　Cells(1,M)=計算式
を
　kekka = 計算式
に置き換える。

うまくいけば、シートに戻り
　=Kekka(A1)
と入力すれば結果値が求められるかもしれません。（A1はパラメータを入力したセル）。ただしこれでダメなら実際のマクロを見ないと何ともしがたいので、そのときはあきらめてください

お礼

教えていただいた内容を何とか自分のマクロコードに活かそうと、試行錯誤であちこち変更してみましたが残念ながら上手くいきませんでした。
元のマクロコードをイマイチ理解出来ていないので、それに手を加えようというのはやはり時期早々だったかもしれません。もう少し勉強してマクロコードをある程度理解してから取り組もうと思います。
再度の回答有難うございました。

回答No.5

onlyrom

こんにちは。

ＶＢＡコードを修正するわけですからその知識は必要です。
ましてや他人のコードを修正するとなると尚更のこと。
一応考え方とそのサンプルコードをアップしてみます。

■現在の仕様■
手入力したパラメタを２倍して結果をセルＭ１へ表示する
'----------------------------------------------------
Sub ボタン1_Click()
　Dim Kekka As Double
　Dim Para As Variant
　　　Para = InputBox("パラメータを入力せよ")
　　　Kekka = Para * 2　　'▲計算部分
　　　Range("M1").Value = Kekka
End Sub
'-----------------------------------------------------

■■新しい仕様■■
予め全てのパラメタをＫ列に入力しておき
それをパラメタとして計算し、結果をＭ列の同じ行にいれる
'------------------------------------------------------

Sub ボタン1_Click()

　Dim Kekka As Double
　Dim Para As Variant

　Dim R As Long

'●Ｋ列パラメータを一行目から最終行までくるくる回す

　　　For R = 1 To Range("K65536").End(xlUp).Row

'●InpuBoxの入力値の代わりにＫ列からパラメタを持ってくる

　　　　　Para = Cells(R, "K").Value

'▲▲計算部分は変更しない▲▲

　　　　　Kekka = Para * 2

'●計算結果をパラメタＫ列と同じ行のＭ列へ入れる

　　　　　Cells(R, "M").Value = Kekka

　　　Next R

End Sub
'----------------------------------------------------

上記のように「計算部分」は変えずにその部分をFor～Nextの中にいれておけば簡単にできると思いますが。
ここでいう簡単とは、VBAの知識がある場合です(^_^;;;。
以上です。

お礼

詳しい説明をして頂き有難うございます。
教えていただいたサンプルコードを早速そのままコピペしてためしたところ、「そう、まさにこんな風になって欲しい」という結果が得られました。
しかし、実際の自分のマクロコードではこのサンプルコードをどう応用しどこを変更したらよいのか四苦八苦しています。何箇所か変更したのですが、上手く計算してくれません。
自分のマクロコードをここに記載するわけには行かないのが残念ですが、もう少し勉強してみようと思います。
回答有難うございました。

回答No.4

akina_line

こんにちは、#2です。

　フォーム・ツールバーの「デザインモード」ボタンを押すと、「実行モード」から「デザインモード」に切り替わります。「実行モード」では、ボタンを押すことが出来ましたが、「デザインモード」ではボタンは押せず、大きさや位置が変更できるようになります。
　そこで、ボタンをダブルクリックすると、「Private Sub CommandButton1_Click()」のような表示が別ウィンドウに現れると思います。ここでプログラムの処理が行われています。この「Sub」というのはBasicの「サブルーチン文」というもので、一かたまりの機能を実行するものです。このサブルーチンというものは「関数」というものと異なり、戻り値を持ちません。（値を返しません。）逆に言うと、値を返すものを関数といい、ファンクションといいます。文法的には、以下のように書きます。
--------------------------------------------------------------
' 次のユーザー定義関数は、引数として渡された値の平方根を返します。
Function CalculateSquareRoot(NumberArg As Double) As Double
If NumberArg < 0 Then ' 引数を評価します。
Exit Function ' 終了して、呼び出し側のプロシージャに戻ります。
Else
CalculateSquareRoot = Sqr(NumberArg)
' 平方根を返します。
End If
End Function
--------------------------------------------------------------
（VBAのヘルプの「Function」の項から使用例を引用しました。なお、ヘルプは標準インストールでは入らないので、追加インストールする必要があります。）

「関数」形式にすることで、ワークシートから呼び出せ、値を戻すことが出来ます。

では。

お礼

再度の回答有難うございます。
デザインモードを押した後にマクロボタンを押しても、何故か普通に計算結果が出力されてしまいます。何故でしょう？
せっかくの詳しい説明ですが、残念ながら私の知識が及ばず半分も理解できていないようです。
ＶＢＡについて一冊本を買ってゆっくり勉強してみようと思います。
有難うございました。

回答No.3

ASIMOV

ＶＢＡよりも、計算式が分かれば、普通に表のセルにパラメーターを入力して計算すれば良いのではないかと思います

例えば、パラメーターが１０個有るならば、Ａ列からＪ列をパラメーター入力として、Ｋ列に結果を表示という具合です

計算式が分からないのであれば、ＶＢＡを解析するしかないかもしれませんね

お礼

パラメーターの種類が複数あるというのではなく、パラメーター（Ｘ）に対しＸのデータが膨大にあるという内容なのです。
そして単純な計算式であれば一つ計算式を入力してあとはコピーすれば良いのですが、計算内容が複雑なので自分には上手く出来そうにありません。
やはりＶＢＡの知識が必要となりますか・・・。

回答有難うございます。

回答No.2

akina_line

こんにちは。

　プログラムの形式をファンクションにして、パラメータを引数で受け取り、計算結果をファンクションの戻り値にセットすれば、Excelに組み込まれている他の関数と同様に扱うことができます。
　私は「高度なVBA知識」とは思いませんが。。。。

では。

お礼

すみません、もう少し詳しく教えていただけたら助かります。
ツールバー→フォーム→ボタンによるマクロボタンが作られており、クリックするとメッセージボックスが表示されそこにパラメーターを入力という内容なのですが、これを関数式にするというのはどういう操作をすればよいのでしょうか？

回答有難うございます。

回答No.1

zap35

どのようなマクロか分からないので的確な回答にならないと思いますが、M1に結果を格納する行があると思います（３パターン書きます）
　Range("M1").Value = 変数名　または　計算式
　Cells(1,13).Value = 変数名　または　計算式
　Cells(1,"M").Value = 変数名　または　計算式

これを
　Range("M65536").End(xlUp).Offset(1,0).Value = 変数名　または　計算式
　に変更すれば、M列のデータ最終行の下に計算結果を書き込むようになります。

お礼

マクロコードの中にCells(1,M)=計算式というのは見つかりましたが、「value」が含まれている行が見当たりませんでした。これは同じと考えてよいのでしょうか？
そして”Ｍ６５５３６”と書かれていますが、「６５５３６」の意味は何でしょうか？

回答有難うございました。