第108回目 計算式で参照されている場所に飛ばすことができないか(その2)

○第108回目　計算式で参照されている場所に飛ばすことができないか(その2)

続きです。
場所の与え方です。

まずは試験運転です。
セルを2列用意して、シート名と座標を書き込みます。現在のシート名と同じ場合はヌルでいいです。複数行書き込みます。
この場所情報の範囲を選択してから、マクロを実行させることとします。
三つの配列変数を用意します。シート名、座標、それらを結合したものです。
選択範囲の座標を求め(よく出てくる定番のマクロです)、セル内の情報を配列変数に取得します。シート名がヌルの場合は、当該シート名を入れてください。
当該シート名は、= ActiveSheet.Nameとなります。
二つの情報を結合してください。
p_mm0(i) = p_mm1(i) & " --- " & p_mm2(i)
p_mm1(i)；シート名 p_mm2(i)；座標
　そして、UserForm1.Showで、ユーザーフォームマクロに引き渡します。
　※ユーザーフォームマクロには変更はありません。

　さて、コンボボックスで場所が選択され、こちらのマクロに戻ってきました。
　その情報の場所に移動します。これは本当に簡単です。
　配列変数の添え字は、p_bですので、それを使って、あっという間です。
'選択先に飛ぶ
Worksheets(p_mm1(p_b)).Select
Range(p_mm2(p_b)).Select
ここまでくると、このセルの値と計算式は、簡単に取得できますね。
値と計算式を配列変数に入れておけば、計算式の内容一覧表を作れることになります。

　※ここで面白いことに気がつきました。もっと早く気づいているべきなのかもしれませんが、変数値が当該マクロを終了してもそのまま残っているのです。
　パブリック変数だからのようです(今までまったく気がつきませんでした)。
　最初は場所の情報のあるセルを選択して実行します。そして、その中から一つ指定して、その場所に移動しました。そこで終了します。
　次の違う場所に行きたい、といったときどうなるのでしょうか。データが残っていればそれを使えばいいですよね。
　ちょっとした修正(指定しているセルが一行である場合は、情報の取得は行わない)を施しておくと、二回目以降はマクロの実行だけでいいのです。

　次は、もっとレベルアップして、シート名、座標を与えるのではなく、直接、計算式から取得するという機能を付加することにチャレンジしてみましょう。

第107回目 計算式で参照されている場所に飛ばすことができないか(その1)

○第107回目　計算式で参照されている場所に飛ばすことができないか(その1)

　前回のうちの一つです。
　6.計算式で参照されている場所に飛ばすことができないか。
　その第一段階として、
　・複数の場所を与えて、その中から選択して移動できる。コンボボックスを使う。
　に挑戦します。

　コンボボックスって、どうやって作って、どうやって文字を登録して、どうやって使うのでしょうか。
　1.作り方
　エクセルのVBAのシート(エクセルシートからAlt+F11で移動)において、挿入-ユーザーフォームでユーザーフォームを作ります。そこでツールを用いてコンボボックス(窓です)とその上にコマンドボタンを一つ作ります。コマンドボタンの表示文字を｢終了｣にします。
　2.文字の登録
　マクロ上に、次のように書きます。
　 p_mm0(0) = "りんご"
p_mm0(1) = "バナナ"
p_mm0(2) = "みかん"
p_mm0(3) = "メロン"
p_mm0(4) = "ぶどう"
　 UserForm1.Show
'ここでUserForm1のマクロに飛ぶ。そのマクロ場終了すると戻る。

　ここで注意点は、ユーザフォームのマクロはまったく違うプロシージャです。したがって、変数を引き継がなければならないので、使用する変数をみなパブリック変数にします。
　Public p_mm0(20)
　これは、モジュールシートの一番先頭に書いておいてください。
　3.コンボボックスの表示
　今度はユーザーフォームにいきます。
　ユーザーフォームでF7を押すと、ユーザーフォームのマクロシートが出てきます。
　ここに続きのマクロを書きます。
　Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
ComboBox1.List = p_mm0 'コンボボックスのリストに配列の値をセットする
　End Sub
　4.選んだ文字列を取得する
　コンボボックスの中から文字を選びます、これはエンターキーで決めます。
　この動きを処理するのが次のマクロです。これもユーザーフォームのマクロです。
　このマクロは、コンボボックス内に動きがあると動きます。
　Private Sub ComboBox1_Change() 'コンボボックス内に動きがあると動く
p_a = ComboBox1.Text '選択結果を取得
p_b = ComboBox1.ListIndex '選択された配列のインデックスを取得
　End Sub
　ここで、p_a、p_bもマクロシート上でパブリック変数として定義しておいてください。
　5.選択の終了
　選択を終了させるには、終了ボタンを押します。エンターキーでもいいです。文字列をきめてから、エンターキーを2回押すと終了します。
　次のマクロは、終了ボタンが押されたときに動くマクロです。
　Private Sub CommandButton1_Click()
If ComboBox1.MatchFound Then '一致する項目がリストの中にあれば
Unload Me 'ユーザーフォームを閉じる
Else 'なければ
MsgBox "やり直してください", vbExclamation, "みつかりません"
End If
　End Sub
　6.選択結果の表示
　5．が終わりますと、2.のマクロに戻ります。
　2．のマクロの後に、選択結果を表示しすマクロを続けます。
　 (UserForm1.Show；分かりやすくするために重複して書いています)
MsgBox (p_mm0(p_b) & " --- " & p_b)
または
MsgBox (p_a & " --- " & p_b)
これで選んだ文字と配列変数の番号が表示されます。

　※骨格はこれでおしまいです。後はこれをアレンジしていきます。