対話型で処理するマクロ

質問

• 対話型で入力された情報を元に、マクロの記述を書き換えて処理を行う方法を教えてください。
• メッセージボックスを使って対話型にすることで、対象値の列や転記する場所を選択できるようになります。
• 入力した情報に応じて、マクロ内のコメント部分を書き換えて処理を行います。

回答No.1

jcctaira · ベストアンサー

gx9wxさん　こんにちは。
下記は要望に応えたプログラムです。
ただ、勝手に気になった所（無駄等）を修正しました。

1. With　ActiveSheet
　　全く意味がない（使用していない）ので省きました。
　　※セルを指定する時、シートオブジェクトを省略すると「ActiveSheet」が使われます
2.InStr(1, n, "-", 1)
InStrは開始位置や比較方法を省略できます。
　→　InStr(n, "-")
　※省略した方がプログラムミス（入力ミス）も減るかと思います
3.Case InStr(1, n, "-", 1) = 9 '9字目が-
"-"が９文字目より前にあると動作が変わってしまうので直接9文字目で判断した方が良いと思います
　→　Mid(n, 9, 1) = "-"　　　　　　　　　　　　　　　 　　　
4.Selection
　マクロ記録ではSelectionが自動的に記録されますが無駄です
　→　Columns("B:B").Insert Shift:=xlToRight
5.その他
　・今回対象列等を入力していますが、入力チェックや入力確認をするようにした方が良いと思います
　　例えばフォームを使ったり
　・変数宣言(DIM)はした方が良いと思います。
　　入力ミスをすると思わぬ結果になりますので…。

勝手に余計なことを書きましたが、なるべく無駄のないプログラムを作成した方が、後々修正がしやすく
わかりやすいかと思います。　

Sub ハイフン挿入()
　対象値列 = InputBox("対象値のある列を入力してください")
　列挿入 = MsgBox("列挿入しそこに転記しますか？", vbYesNo)
　If 列挿入 = vbYes Then
　　転記列 = InputBox("挿入したい列を入力してください。例：Ｈ列とI列の間→Ｈ")
　Else
　　転記列 = InputBox("転記する列を入力してください")
　End If
　If 列挿入 = vbYes Then
　　Columns(転記列).Insert Shift:=xlToRight
　End If
　行 = 1
　Do
　　n = Cells(行, 1).Value
　　If n = "" Then Exit Do
　　If Len(n) = 14 Then
　　　Select Case True
　　　　Case Left(n, 2) = "9X" And InStr(n, "-") = 0　　　　　'左2字=9X & -が無
　　　　　myStr = Left(n, 3) & "-" & Mid(n, 4)　　　　　　　　　　'3-11で編集
　　　　Case Mid(n, 9, 1) = "-"　　　　　　　　　　　　　　　 　　　'9字目が-
　　　　　myStr = Left(n, 3) & "-" & Mid(n, 4, 11)　　　　　　　'3-5-5で編集
　　　　Case Left(n, 1) = "9" And InStr(n, "-") = 0　　　　　　 '左1字=9 & -が無
　　　　　myStr = Left(n, 5) & "-" & Mid(n, 6, 5) & "-" & _
　　　　　　　　　Mid(n, 11, 2) & "-" & Mid(n, 13, 2)　　　　　　 　'5-5-2-2で編集
　　　　Case InStr(n, "-") = 0　　　　　　　　　　　　　　　　　　　'-が無
　　　　　myStr = Left(n, 3) & "-" & Mid(n, 4, 5) & "-" & _
　　　　　　　　　Mid(n, 9, 2) & "-" & Mid(n, 11, 2) _
　　　　　　　　　& "-" & Mid(n, 13, 2)　　　　　　　　　　　 　　　　'3-5-2-2で編集
　　　　Case Else　　　　　　　　　　　　　　　　　　　　　　　　　　 　'いずれにも属さない14文字
　　　　　myStr = n　　　　　　　　　　　　　　　　　　　　　　　　　　 '編集対象の値を使用する（未編集）
　　　End Select
　　Else　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　'編集対象の値が14文字でない
　　　myStr = n　　　　　　　　　　　　　　　　　　　　　　　　　　　　 '編集対象の値を使用する（未編集）
　　End If
　　Cells(行, 転記列) = myStr
　　行 = 行 + 1
　Loop
End

お礼

>1. With　ActiveSheet

私の記述の入ったBOOK1を開いて
マクロが入っていないBOOK2,3,4,5,6を開いておき
BOOK2を選択して、BOOK1に入っているマクロを使用、
BOOK3を選択して、BOOK1に入っているマクロを使用
と行う場合に必要なのかと思いました。

＞2.InStr(1, n, "-", 1)
＞InStrは開始位置や比較方法を省略できます。

ありがとうございます。初めて知りました。

＞3.Case InStr(1, n, "-", 1) = 9 '9字目が-
＞"-"が９文字目より前にあると動作が変わってしまうので
＞直接9文字目で判断した方が良いと思います

言われてみると確かにです。ありがとうございます。

＞4.Selection
＞　マクロ記録ではSelectionが自動的に記録されますが無駄です
＞　→　Columns("B:B").Insert Shift:=xlToRight

マクロの記録で出来上がった記述であまり行数が多い場合は
調べてなんとか少ない行数にと行動するのですが
２行くらいならいいかなとサボっていました。
申し訳ありません。

＞・変数宣言(DIM)はした方が良いと思います。

これがまだ良くわからなくて困ってます。

今回教えていただいた中の

転記列 = InputBox("挿入したい列を入力してください。例：Ｈ列とI列の間→Ｈ")
Else
転記列 = InputBox("転記する列を入力してください")
Cells(行, 転記列) = myStr

の部分、InputBoxに例えばＨと入力すると
Cells(行, 転記列) = myStr
↓↓
Cells(行, Ｈ) = myStr

（この解釈が間違っているのでしょうか？）

なぜCells(行, Ｈ)でいいのでしょうか？
ここはＨなら８でないといけないのでは？
でも正しく動くので余計に？
です。

＞勝手に余計なことを書きましたが

全然、勝手ではないです。
いろいろ教えていただきまして
大変ありがとうございました。

とりあえず、InputBoxへの入力さえ間違わなければ
思っていたとうりになりました。
どうもありがとうございました。

補足

>・今回対象列等を入力していますが、入力チェックや入力確認を
>するようにした方が良いと思います
>例えばフォームを使ったり
>・変数宣言(DIM)はした方が良いと思います。
>入力ミスをすると思わぬ結果になりますので…。

使ってみて痛感しました。
Ａ～ＩＶ以外は入力できないするようにした方がいいと思いました。

ここ↓↓もです。

＞対象値列 = InputBox("対象値のある列を入力してください")

ここで入れた値より前の値を

＞転記列 = InputBox
＞("挿入したい列を入力してください。例：Ｈ列とI列の間→Ｈ")

ここで入力すると成り立ちませんでした。

最初にＤと入力し
次に挿入希望列でＢと入力すると
対象値のＤ列はＥ列にずれるからです。

ここにも入力制限が必要でした。
最初に入れた値よりも前の値は入力禁止にしなければ駄目でした。

でも方法は．．．．分かりません。

どうもありがとうございました。