対話型で入力された情報にて処理を行うマクロ

質問

以下のプロシージャーを多様しています。
記述を見ていただくと分かりますが
A列の値をSelect Caseの条件で編集してB列に転記します。
A列にデータがあるまで処理を繰り返します。
ですが基幹システムの吐き出しがメニューの種類によって
必ずA列に対象値があるとは限らず、
また転記したい場所もB列とは限りません。

例えば、
対象値がC列で、転記したい場所はF列とか
対象値がE列で、転記したい場所はG列とH列の間に
列挿入してそこに転記など。

場面によって記述のコメント部分の●部分を書き換えて使用しています。
もしくは吐き出したデータを列単位で下記記述に合うように列移動し
マクロ終了後、元の配列に直しています。

メッセージボックスなどを利用して、対話型にして
入力した情報からマクロの記述を書き換えてそのマクロを起動させる事は可能でしょうか？

例えば
１．メッセージBOX
　　「対象値のある列を入力してください」
　　入力例：Ｇ
２．メッセージボックス
　　「列挿入しそこに転記しますか？」
　　YES/NO　選択

３．YESの場合
　　メッセージBOX
　　「挿入したい列を入力してください。例：Ｈ列とI列の間→Ｈ」
　　入力例：Ｈ

　　NOの場合
　　メッセージBOX
　　「転記する列を入力してください」
　　入力例：Ｊ

すると以下の記述のコメント部分の●部分が入力内容にそって書き換わる。

よろしくお願いします。

　Sub　ハイフン挿入（）
　'対象列をハイフン挿入し指定列に転記
　 With　ActiveSheet
　 '●空白列を作成。
　 '（"B:B"）が挿入した場所。例：ＤとＥの間→("D:D")
　 '挿入しない場合は以下２行をコメントアウトする
　　Columns("B:B").Select
　 Selection.Insert Shift:=xlToRight
行 = 1
Do
'データが無ければ停止させる。その列を指定。
'●(行,1）の1はA列です。例：C列なら3にする。
If Cells(行, 1).Value = "" Then Exit Do
'編集対象の値の列を指定
'●(行,1）の1はA列です。例：C列なら3にする。
n = Cells(行, 1)
'値が14文字である事？
If Len(n) = 14 Then
Select Case True
'左2字=9X & -が無
Case Left(n, 2) = "9X" And InStr(1, n, "-", 1) = 0
'3-11で編集
myStr = Left(n, 3) & "-" & Mid(n, 4)
'9字目が-
Case InStr(1, n, "-", 1) = 9
'3-5-5で編集(3文字目と4文字目に-を入れれば3-5-5になる）
myStr = Left(n, 3) & "-" & Mid(n, 4, 11)
'左1字=9 & -が無
Case Left(n, 1) = "9" And InStr(1, n, "-", 1) = 0
'5-5-2-2で編集
myStr = Left(n, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
'-が無
Case InStr(1, n, "-", 1) = 0
'3-5-2-2で編集
myStr = Left(n, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
'いずれにも属さない14文字
Case Else
'編集対象の値を使用する（未編集）
myStr = n
End Select
'編集対象の値が14文字でない
Else
'編集対象の値を使用する（未編集）
myStr = n
End If
'編集した値を転記する場所を指定
'●(行,2）の2はB列です。例：D列なら4にする。
Cells(行, 2) = myStr
'次の行に移動
行 = 行 + 1
Loop
End　with
End Sub

回答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列の間→Ｈ")

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

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

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

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

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