第124回目 計算式作成のお手伝い(その３一般的な計算式)

○第124回目　計算式作成のお手伝い(その３一般的な計算式)

　これまでは、SUMPRODUCT関数という特殊なものを扱ってきましたが、一般的に利用できるマクロはできないでしょうか。
　四則演算を使った普通の計算式をつくるために便利なものはないでしょうか、という問題意識です。
　通常の方法で計算式をつくる時(カーソルを動かして座標を取得し、間に四則演算子等を加えていく方式)に、気になるところは、セルを指定し終わると、カーソルが計算式のある場所に戻ってしまうことでしょうか(この問題はマウスでやれば出てきません。キーボードならではの問題です)。
　例えば、AX7に、=B7/B8という式を入れる場合を考えます。
　1.AX7で=を入れる
　2.カーソルをB7まで移動します
　3./をいれます。ここでカーソルはAX7に戻ります。
　4.カーソルをB8まで移動します。
　5.Enterを押します
　問題は、3.のあとのカーソルの戻りです。
　これがないと計算式作成において、便利なのですが、これの抑止の仕方がわかりません。
　別の問題(問題というかどうか不明)は、シート移動をした時のカーソルの位置です。通常ではあるがままです。つまり、以前にそのシートで最後にカーソルがあった位置となります。これは当然で当たり前のことなのですが、場合によっては、移動前のシートでの場所と同じところにカーソルがあって欲しいと思うときがあります。

　それはさておき、計算式の作成において、最も多いのは座標の入力です。通常はカーソル動かして行います。これをキーボードから入れるのはいやになってしまいます。ここをカバーしたマクロでないと駄目です。

　1.座標の指定は、Application.InputBoxメソッドで行います。
　 Set セル範囲 = Application.InputBox(Prompt:="次のセット範囲の先頭のセルを指定してください", Default:=AAA1,Type:=8)
aaa2 = セル範囲.Address
これと、InputBox関数を絡み合わせて何かを作るという発想で、何とか計算式を作って見ましょう。
2.keisansiki = "xxx= "とおいて、
次に、keisansiki = keisansiki + aaa2　とします。
これをInputBox関数の初期値として設定します。
a = InputBox("計算式に必要な文字を追加してください。", , keisansiki)
keisansiki = a
1.と2.を必要なだけ繰り返します。
1.から2.にいった時、カーソルの場所は直前に指定した場所になります。前の例では、B7にあるということです。ですから、ここからカーソルを次ぎのB8に動かすだけでとなります。

この機能を作って操作してみますと、若干ながらまだひっかかりがありました。
　そこで、InputBox関数の時、追加の場合と置換えの場合とに分けることにしました。
　追加の場合とは、今までのものに今回入力したものを追加するというものです。
keisansiki = keisansiki + a　ということになります。
判定は、"= "が残っているかいないかにしました。
残っていない＝⇒追加、残っている置換え。
ここでのkeisansikiは、文字列となっていますが、先頭の"xxx"をとると計算式になります。InputBox関数の入力の際に、これをとれば直接計算式としてセルに入力されます。

　シートを移動した場合は…
　通常処理の場合、移動したシートでのカーソルの場所は、画面の左上端のようです。ですからおおむね前に指定した位置ということになります。
　したがって、カーソルの位置の問題(カーソルが戻ってしまうという問題)は、同一シート内で考慮すればいいものとなります。
　まずは、同一シート内の場合のみで作ってみたらどうでしょうか(シートの移動が手作業では大変な場合は、このマクロにシート移動を組み込めばメリットが出てきます)。
他のシート、他のファイルを参照したいという場合は、ニーズいかんでしょう。そのようなケースは、計算式を作るという作業のうちのおおむね5%程度しかないのかもしれません。自分のニーズと相談してどこまで拡張するかは考えてください。広げれば操作が多くなるというデメリットが、出てきます。

　意外だった盲点。
　座標の入力、文字列の入力を終了とする判断はマクロでどう書くか。
　文字列の入力の際…キャンセルキーを押すと、入力値に""が入るのでそれで判断。
　座標の入力の際…キャンセルキーを押すとエラーになるのでそれで判断。
　・最初に
On Error GoTo ErrorTrap
　・最後に
Exit Sub
ErrorTrap:
　errflag = 99
　Resume Next
End Sub

　・Application.InputBox文の箇所に
Set セル範囲 = Application.InputBox(Prompt:="使用するセルを指定してください。終了はキャンセルキー。", Default:=bbb, Left:=10, Top:=2, Type:=8)
If errflag = 99 Then Exit Do

以上です。

　追伸；実際に試してみると、いい感じです。しかし、いつも計算式をはなから作るというときばかりではありません。そこで、既にある計算式を取得するかどうかを聞いてみて、必要な場合は既存の計算式を取得するという機能もメリットがあります。この場合、計算式が入っていない場合も取得するとしておいたほうがいいと思います。こうすれば、文字計算式も取得できますので。


＝⇒現在では、この計算式を作るマクロを、座標入力はinputメソッドのままとして、演算子などの入力をフォームのテキストボックスを使用したものに変えています。
　メリット…画面が大きく取れる。文字のフォントを大きくすることができる。表示内部でのコピーの操作ができる。長い計算式でも、一覧できる。その後、inputメソッドで取得する座標を好きな位置に追加することができる(計算の途中に..を入れておき、その部分を置き換える)。
　というものがあげられます。テキストボックスベースのものにチャレンジしてみてください。
　参考までのフォームに貼り付けたマクロ文を載せておきます。
　変数はパブリック変数として定義してください。
　
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
　TextBox1.MultiLine = True
　TextBox1.Text = p_aa '値をセットする
End Sub
　Private Sub TextBox1_Change() 'ボックス内に動きがあると動く
　p_aa = TextBox1.Text '選択結果を取得
End Sub
　Private Sub CommandButton1_Click()
'続けるボタンがクリックされた
　Unload Me 'ユーザーフォームを閉じる
　p_flag = 0
End Sub
Private Sub CommandButton2_Click()
'終了ボタンがクリックされた
　Unload Me 'ユーザーフォームを閉じる
　p_flag = 9
End Sub
　
　ワークシート側でのマクロの記述
p_aa = keisansiki
UserForm3.Show
'ここでUserForm3のマクロに飛ぶ。そのマクロ場終了すると戻る。
keisansiki = p_aa