エクセルでマクロの記録が出来ません。

質問

エクセルでマクロの記録が出来ません。

エクセルのＡ列とＢ列に1,000行のデータがあります。

マクロの記録を起動します。

１．Ｂ１セルを選択
２．右クリックで挿入を選択
３．Ａ列とＣ列にデータが有り、Ｂ列は空白となる。
４．Ｂ１セルを選択します。右クリックの貼り付けを選択。
　　マクロの起動前にクリップボードにあらかじめコピーしておいた
　　以下の式を貼り付けます。

　　=IF(A1="","",IF(ISERROR(FIND("-",ASC(A1))),IF(LEFT(ASC(A1),1)<>"9",MID(A1,1,3)
&"-"&MID(A1,4,5)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),IF(LEFT(ASC(A1),2)=
"9X",MID(A1,1,3)&"-"&MID(A1,4,11),IF(LEFT(ASC(A1),1)="9",MID(A1,1,5)&"-"&MID(A1,6,5)&
"-"&MID(A1,11,2)&"-"&MID(A1,13,2),""))),IF(FIND("-",ASC(A1))=6,A1,MID(A1,1,3)&"-"&
MID(A1,4,11))))

５．「Ctrl」＋「Ｃ」
６．エクセルの左上の名前ボックスを
　　B1　→　B1:B1000に変更。Ｂ列が選択されます。
７．「Ctrl」＋「Ｖ」
８．Ｂ列に式で変換されたデータが入りました。Ｂ列が選択されたままです。
９．「Ctrl」＋「Ｃ」
１０．右クリックで形式を選択して貼り付けで「値」を選んでＯＫをおす。
　　　セルに入っていた式は全て消えました。Ｂ列が選択されたままです。
１１．マウスポインタをセルのＡ１におく。Ｂ列の選択が解除されました。

マクロの記録を終了

これでＮＯ．４の操作の時に「記録できません」と表示されます。

データを一旦削除し、再度、Ａ列とＢ列にデータを入れて
マクロを実行してもデータＢ列がＣ列に移動しＢ列は空白です。

実際にマクロの記録中でもＮＯ．１からＮＯ．１１の操作は出来ています。

なぜ記録されないのでしょうか？
どうすれば記録できますか？
よろしくお願いします。

回答No.4

Wendy02 · ベストアンサー

>　Function ConvertNumbers(ByVal arg As Variant) As String.....以下省略
これは、本来、
Public Function ConvertNumbers(ByVal arg As Variant) As String.....以下省略
と書くべきもので、標準モジュールなら、同じブック内ならどこからでも呼び出せる、ユーザー定義関数(User Defined Function)で、ひとつだけです。標準モジュールに書くと、Public ステートメントは省略出来ます。

それと、あまりモジュール数を増やすのは感心しません。私も経験があるのですが、とても管理しにくくなります。もし、ずっと続けるものならば、そのモジュールの名称を変えてあげるとよいです。my_Utilities とかモジュール名を替えて、同じ種類のものをまとめて入れておくとよいです。

また、もっと使うものは、アドインにしたり、PERSONAL.XLSに入れます。アドインにすると、社内などで配布が可能になります。配布後は、エクスポートで保存しておきます。Basという拡張子ですが、内容はテキストファイルです。

ただ、コードは、ただ、十分にバグを潰さないといけません。プロシージャの名前も、TestMacroではなく、ちゃんとしたものにしたほうがよいです。また、コードの部分には、日付と説明を入れておきます。後々、誰かが、これはなんだろうとみて分かるようにします。

>１種類だけ教えていただければ後は自分で修正しようと思い今回失敗しました。
>今回は、全て１行目からデータがある事を前提に質問しました。

汎用性を持たせるということになるわけですね。
前回の同名マクロを上書きをしてください。
汎用性を持たせるために、多少のエラーオプションと対話型のオプションを設けました。
データのある部分にカーソルを置き、実行すれば、データの先頭に飛び、実行するか聞いてきます。

'//
Sub TestMacro3()
'ここに日付と説明を入れておくと良い
　Dim col As Long
　Dim rw As Long
　'データのある場所のデータのある最上部を選択してください。
　With ActiveSheet
　　col = ActiveCell.Column
　　rw = ActiveCell.Row
　　If WorksheetFunction.CountA(.Columns(col)) < 2 Then _
　　MsgBox "データ列を選択してください。", vbExclamation: Exit Sub
　　If rw > 1 And ActiveCell.Value <> "" Then
　　　 If ActiveCell.Offset(-1).Value <> "" Then
　　　　 rw = .Cells(rw, col).End(xlUp).Row
　　　 End If
　　ElseIf ActiveCell.Value = "" Then
　　　rw = Cells(rw, col).End(xlDown).Row
　　　If rw > 200 Then
　　　　MsgBox "データ内容が不明です。", vbExclamation: Exit Sub
　　　End If
　　End If
　　.Cells(rw, col).Activate
　　If MsgBox("ここから、右側の列に出力されます (" & .Cells(rw, col).Address(0, 0) & ")", vbOKCancel + vbQuestion) = vbCancel Then Exit Sub
　　.Columns(col + 1).Insert
　　With .Range(.Cells(rw, col + 1), Cells(Rows.Count, col).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　　.NumberFormatLocal = "00000000000000"
　　　.HorizontalAlignment = xlLeft
　　End With
　End With
End Sub

最後に、今回の内容は簡単なのですが、実務に対応出来るように作るというのは、なかなか経験がないと出来ないものなのです。できる限り、ベテランの人のマクロを実行してみて、スタイルを学ぶことが必要です。記録マクロから、脱却することも大事です。

お礼

Function ConvertNumbersの件ありがとうございます。
最初に戻ってすいません。9月27日15:28
教えていただいたG列にデータ、H列に編集内容を挿入を
Sub　編集集計()としてModule1に入れてあります。

Sub 編集集計()
With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
'.FormulaR1C1 = "=ConvertNumbers(RC[-1])"
.Value = .Value
.NumberFormatLocal = "00000000000000"　'*
.HorizontalAlignment = xlLeft
End With
End With
End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String
　　以下省略
End Function

単独では問題ないのですが別のプロシージャーからCall　編集集計で呼び出すと駄目です。
教えてもらったものを単独でF8で動かすと
.Formula = "=ConvertNumbers(RC[-1])"の次は
Function ConvertNumbersからEnd Functionの間でループして
データがなくなると
.Value = .Value
に移動します。
別プロシージャーからCallでよぶ場合
同じくF8で動作を見ると
.Formula = "=ConvertNumbers(RC[-1])"の次は
.Value = .Value
に移動でセルH列にはすべて#NAME?になってしまいます。
Call文をやめて直接結合させても駄目でした。
さらにまったく別のプロシージャーからCallでよぶ場合は
正常動作です。
ちなみに駄目な方のプロシージャーのCall文の前はこうです。
F8で確認中はプロシージャーが入っているエクセルとは
別にBOOK2が開いています。

Dim fName As String
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
Dim buf As String
Dim rng As Range
Dim i As Integer
Dim fLine As Variant
Dim fso As Object
Dim dPath As String
fName = Application.GetOpenFilename( _
FileFilter:="CSVファイル(*.csv),*.csv" _
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set fso = CreateObject("Scripting.FileSystemObject")
dPath = fso.GetParentFolderName(fName)
Application.SheetsInNewWorkbook = 1
Set wBook = Workbooks.Add
Set wSheet = wBook.Worksheets(1)
wSheet.Name = "集計"
Set rng = wSheet.Range("A1")
Dim n As Variant
n = FreeFile
Open fName For Input As #n
i = 0
Do Until EOF(n)
Line Input #n, buf
fLine = Split(buf, ",")
rng.Offset(i).Resize(, UBound(fLine) + 1).Value = fLine
i = i + 1
Loop
Close #n
'wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value
wSheet.UsedRange.Formula = wSheet.UsedRange.Value
'「rng」がworkbooks("編集").worksheets("集計").range("A1")

Call 編集集計
（又はCallをやめてここにそのまま記述）

補足

ありがとうございます。正常動作はしました。

＞データのある部分にカーソルを置き、実行すれば、
＞データの先頭に飛び、実行するか聞いてきます。

Ａ列に対象データがある状態でＡ列にカーソルを置いて、
マクロを起動させると
("ここから、右側の列に出力されます
が表示されます。ＯＫをクリックで正常動作します。

記述に
MsgBox "データ列を選択してください。
があるのですがこれが表示されません。

これは表示されるのが正規でしょうか？

回答No.10

Wendy02

今は、私は、ザッピングは出来なくなりました。正直な所、もともと、gx9wxさん自身が、こちらのマクロを加工している時点で、私としては、本来、サポートはしません。

条件が加わるたびに、こちらでは、それに良いと思うコードを書き換えていますが、それには追いついていないようです。私は、前のものは、ほとんど追いません。私個人は、他人の書いたコードを使うのは良しとしていませんし、こちらのコードを大幅に書き換えられることを認めているわけではありませんが、それはgx9wxさんのご自由です。

#8-9のコードで解決しないとなると、残念ながら、これ以上引き伸ばしても解決には至らないと思います。常に、こちらの思惑と違うコードが出てきているようですので、さっぱり見当が付かないのです。私は、基本的に、他人のコードの修正や添削はしていません。それぞれには、その人のスタイルというものが存在するからです。こちらにもスタイルがあります。それを否定されてしまって続けられるほど、寛容ではありません。

もし、今後も今のような方法を取られるなら、出来れば、VBA専門掲示板に変えたほうがよいと思います。専門掲示板ですと、複数の人がサポートとしてくれるし、いろんなレベルの人もいますし、人の書き込みスタイルにこだわりを持たない人たちもいます。おそらくは、gx9wxさん自身の技術の自信があるので、こちらの書いたものを書き換えているのだろうと思います。

実際、こちらは分からなくなるのです。その思考過程を遡って追いかけるのは、ご自身が考えるよりも、倍以上の時間が掛かります。こちらは、こちらのコーディング・スタイルを続けようとしているのに、それをひっくり返されてしまっては、どうしようもないのです。

確かに、こちらのエラーの出る原因を見逃したことへのミスも認めますが、#8で、こちらの書いたポイントさえ理解してくださればよかったのですが、こちらの書いた内容とは違うものを出されるとは思いませんでした。それは、こちらのコードではありませんから、それで解決するなら、こちらもなんともいえないのです。

お礼

気分を害してしまいまして大変申し訳ありません。

＞自身の技術の自信がある

私は自信など有りません。
教えてもらった記述を見て、なぜこういう記述なのかという疑問だらけで
どうしてこの記述だとそういう動きになるのか試すために、
記述を1文字単位で変えて実験をする程度です。

＞こちらにもスタイルがあります。それを否定されてしまって

すいません。私にはそんな意識はなく感謝の気持ちしかないのですが、
そう取られてしまったのは事実ですので深くおわびいたします。

そもそもこのスレッドはマクロ記録ができないでした。
エクセルのセルに式を入れていたのをマクロで行いたい。
でそれはマクロではできないとなり、記述を教えていただきそれは
解決しています。

そこに脱線してＣＳＶファイルのインポートの件を
ここで質問したのがいけませんでした。
別物なのでいけない事でした。
丁寧に対応してくださったので甘えてしまいました。
申し訳ありません。

＞VBA専門掲示板に変えたほうがよいと思います。
多分無理だと思います。
そんな所では土俵にも上がれません。
今後もここで質問（本件は閉じます。別な内容が発生時です。）すると思います。

ですが例のアプリケーションのVBスクリプトの件と
このスレッド本来の質問である関数式をマクロ化できないの件は
Wendy02さんのおかげで解決しています。
大変感謝しています。本当にありがとうございました。

回答No.9

Wendy02

'前の投稿の続き
Sub CSVImport()
　Dim fName As Variant
　Dim mBk As Workbook
　Dim n As Integer
　Dim TextLine As String
　Dim Ar As Variant
　Dim mPath As String
　Dim i As Long, j As Long
　Dim o As Integer
　fName = Application.GetOpenFilename("CSVファイル(*.csv),*.csv", , "ファイルの選択")
　If VarType(fName) = vbBoolean Then Exit Sub
　i = i + 1
　o = Application.SheetsInNewWorkbook
　　Application.SheetsInNewWorkbook = 1
　j = InStrRev(fName, "\")
　If j > 0 Then
　　mPath = Mid$(fName, 1, j)
　Else
　　Exit Sub
　End If
　With Workbooks.Add
　　.ActiveSheet.Name = "集計"
　　n = FreeFile
　　Open fName For Input As #n
　　Application.ScreenUpdating = False
　　Do Until EOF(n)
　　Line Input #n, TextLine
　　If Len(TextLine) > 0 Then
　　　Ar = Split(TextLine, ",")
　　　ActiveSheet.Cells(i, 1).Resize(, UBound(Ar) + 1).Value = Ar
　　End If
　　i = i + 1
　　If i > Rows.Count Then Exit Sub
　　Loop
　Close #n
　ActiveSheet.UsedRange.Columns.AutoFit
　Application.SheetsInNewWorkbook = o
　　 Call mMacro3(7) '*G列を対象とする
　　Application.ScreenUpdating = False
　　ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
　　Application.ScreenUpdating = True
　 fName = mPath & "編集済" & Format$(Date, "yymmdd") & ".xls"
　 If Dir(fName) = "" Then
　　 .SaveAs fName, FileFormat:=xlNormal
　 Else
　　 .SaveAs Mid(fName, 1, Len(fName) - 3) & Format$(Time, "hhnn") & ".xls", FileFormat:=xlNormal
　　 End If
　End With
　MsgBox "編集終了", 64
End Sub

お礼

閉じる前に最後にすいません。2010年10月1日。

本スレッドの本来の目的である、
関数式をマクロで記録できない件の最終回答である
ANo.6で教えていただいた物
↓
どこを選択しても、必ず先頭行（TestMacro4）
記述は一切変更しないで実行しています。

・該当データのセル、列、にカーソルを置いてもエラーメッセージが出る場合がある
・ファイルによっては行の途中から編集がされる

これが原因不明です。

それから、A-NO.8とA-No.9について。
教えてもらった記述の結果を回答する前に自分が実験した内容を
補足とお礼に書き込んでいました。
すいません。

A-NO.8とA-No.9を記述を
一切変更無しで起動すると

・編集というシートが開いたまま終了する。
　（名前をつけて保存されているので閉じたかったのですが。）
・すでにWendy02さんが予測してますが
　（不安なのは、G列を確実に対象とするのは、少し難しいものです。）
　のとうり、G列を編集してH列に表示する過程において
　空白の行と正しく編集される行が混在しています。

これも原因不明です。

以上です。大変ありがとうございました。

補足

こうなっています。

Sub 自動編集（）
・ファイル選択画面が開く
・ファイルを選択するとそのデータが新BOOKに貼り付く
・シート名は編集
・緑の三角マークが除去される
・Callで　Sub　編集()が起動

Sub　編集（）
'G列の書式設定を数値に変更
'G列、C列、D列の優先順で昇順で並べ替え
'G列の右に空白列を挿入しハイフン編集をして貼付

　ここに

　With ActiveSheet
　.Columns(8).Insert
　With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
　.Formula = "=ConvertNumbers(RC[-1])"
　.Value = .Value
　.NumberFormatLocal = "00000000000000"　'*
　.HorizontalAlignment = xlLeft
　End With
　End With
　End Sub

'1行目に空白行を作成
'1行目のセルA1からL1に固定タイトルを挿入
'A列とB列の数値データーを名称に変更
'A列の値とB列の値からJ,K,L列を編集
'C列とH列の幅を広げる
'1行目のセルの値の位置を中央へ
'G列のデータが上の行と変化する行の下に空白行を入れる
'データ部分を罫線で囲む（空白行、空白セルを含む）
'1行目に空白行を作成し固定タイトルをつける

End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String
　　以下省略
End Function

・ファイル名を指定しシステム日付を固定ファイル名の後ろに付けて
　CSVファイルと同じ場所に保存
・編集終了のメッセージを出す。
End Sub

回答No.8

Wendy02

最初に、寄せ集めよりも、私ひとりでやったほうが楽ですね。
この文面に該当するCSVをインポートし、xlsで保存するマクロは、次の書き込みに書きます。

話が前後したような気がしますが、このように作りました。
ここの掲示板では少ないのですが、gx9wxさんのように、複数の人のコードの寄せ集めで作る人もいますが、今回のような場合は、誰かが最後に通しでコードをみないと、不具合が出る可能性がありますね。

>（１）CSVファイルをクリックしてエクセルで開いた時は
>1は1なのですが、これが0001で表示されてしまいます。
　こういうものが出てくるのですか？そういうものが出てくる前提ですと、全体的に話が変わってくるような気がします。

　ただ入れるなら、こうなりますし、それ自体はありませんね。

　With ActiveSheet.UsedRange
　　.Value = .Value
　End With

ただし、書式デフォルトのままです。

>ですがマクロ２で保存されたエクセルファイルを見ると
>Ｈ列がすべて#NAME?になっていました。

よく考えたら、私は間違えました、すみません。通常は、あの方法でよかったのですが、違うブックの場合は、ユーザー定義関数を、グローバル化しなければなりません。その作業は手間も掛かるので、それに代わる方法を示しておきます。スピードが倍以上遅くなります。

Sub　マクロ３()　は、以下のようになります。
あまり2バイト文字はよくありませんので、以下の名前にしました。

Call mMacro(7)
'G列にデータがあることを意味します。
でしたら、7 です。mMacro("G") と入れても構いません。
これは、サプルーチン用のマクロで、最小限のエラー処理でしかしていません。データが出た時は、全体がストップします。不安なのは、G列を確実に対象とするのは、少し難しいものです。

'//
Sub mMacro3(ByVal col As Variant)
　'10/09/28
　Dim rw As Long
　Dim c As Variant
　Dim r As Range
　With ActiveSheet
　　On Error Resume Next
　　If Not IsNumeric(col) Then col = Cells(1, col).Column
　　Set r = .Columns(col).SpecialCells(xlCellTypeConstants, 23).Columns(1)
　　If Err.Number > 0 Then MsgBox "Err":End
　　On Error GoTo 0
　　.Columns(col + 1).Insert
　　rw = r.Cells(1).Row
　　col = r.Cells(1).Column
　　Set r = .Range(.Cells(rw, col), Cells(Rows.Count, col).End(xlUp))
　　Application.ScreenUpdating = False
　　For Each c In r
　　　If Len(c.Value) > 10 Then
　　　c.Offset(, 1).Value = ConvertNumbers(c.Value)
　　　End If
　　Next
　　With r
　　.NumberFormatLocal = String(14, "0")
　　.HorizontalAlignment = xlLeft
　　End With
　　Application.ScreenUpdating = True
　End With
End Sub

お礼

ありがとうございます。2010年9月28日20:49。
最初はデータがあるのがA列で編集したらその値をB列にで教えてもらい、
その後CSVファイルに応用する為
データがG列でH列に編集した値を入れたいとなり以下の回答Ａでした。
でこれはこれで正しく動いてました。
じっさいCSVインポートでもF8で一つ一つ見ていると
G列の右隣にH列が挿入されます。
ただFunction ConvertNumbersへとばず.Value = .Valueになり
その瞬間H列は全て#NAME?です。
ただこの原因は別BOOKであるからとの事です。

>Call mMacro(7)
>'G列にデータがあることを意味します。
>でしたら、7 です。mMacro("G") と入れても構いません。

申し訳ありません。ここがわかりません。
もう回答Ａとはまったく別物と考えればいでしょうか？

・回答Ａ

>単独で実行するなら、以下のようになるのですが、
>B列でひとつ挿入するので、データが右にズレています。
>だから、連続する場合は、それを勘定に入れていなくてはなりません。
>マクロに慣れている人なら、右から実行していくのですが、慣れていないと分からなくなるはずです。
>だから、この次のマクロ(TestMacro3)を使ったほうが無難です。

Sub TestMacro2()
With ActiveSheet
'G列にデータがあることを意味します。
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
.NumberFormatLocal = "00000000000000"　'*
.HorizontalAlignment = xlLeft
End With
End With
End Sub
Function ConvertNumbers(ByVal arg As Variant) As String
Dim n As String
Dim buf As String
If arg = "" Then Exit Function
n = StrConv(Trim(arg), vbNarrow)
Select Case True
Case Left(n, 2) = "9X" And InStr(1, n, "-") = 9 And Len(n) = 14
buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
Case Left(n, 2) = "9X" And Len(n) = 14
buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 11)
Case Left(n, 1) = "9" And InStr(1, n, "-") = 9 And Len(n) = 14
buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
Case Left(n, 1) = "9" And Len(n) = 14
buf = Mid(n, 1, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
Case InStr(1, n, "-") = 9 And Len(n) = 14
buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
Case Left(n, 1) <> 9 And Len(n) = 14
buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
Case InStr(1, n, "-") = 6 And Len(n) = 12
buf = n
Case Left(n, 2) = "9X" And InStr(1, n, "-") = 6 And Len(n) = 11
buf = n
Case InStr(1, n, "-") = 6 And Len(n) = 11
buf = n
Case Else
buf = n '& "(非該当)" '非変換を抽出
End Select
ConvertNumbers = buf
End Function

補足

大変申し訳有りません。

最初は別にいろいろな人に教えてもらったの物をつぎはぎするつもりなど
有りませんでした。

まずは、CSVファイルの方ですが、
これは夜間バッチで基幹システムが吐出、毎夜1日分のデータを日付を
ファイル名にして社内ネットワークの決められた場所に保存をしてくれます。
ただCSVファイルなのでそのままでは資料としては使いづらいです。
かといってエクセルファイルで吐き出されるとおせっかいな場合もあります。
よってCSVファイルで吐出して利用者が自由に編集する運用です。

ただ、毎日同じ編集を行うならマクロでやった方が便利なので、
マクロを作成しました。そのマクロは簡単です。
ある列を対象に並べ替えをしたり、列にタイトルを入れたり
罫線を引いたり、ある列には1と2と3のいずれかの数字あるので例えば
1は全て（作業）と書き換えたりなどです。

でこのマクロは完成させた物の無知な為
このマクロを登録したエクセルファイルを開いて、
次にCSVファイルを開いて、CSVファイルの全データをコピーして
マクロを登録したエクセルファイルのシート１に貼付。
CSVファイルはそのまま閉じる。
でエクセルファイルでマクロを起動し編集を行う。
で名前を変えて保存する。
これで
（１）CSVファイルは元のまま（誰が利用するかわからないから原本のままでないといけない）
（２）マクロが入ったエクセルファイル
（３）マクロで編集されたエクセルファイル
と３個のファイルになります。
ただ（３）にはマクロが入ったまま作成されてしまうので、開くときに
（マクロを有効に）を聞いてくるので、これもいやでした。

で参考書で、新規BOOKを開いて.....があったので挑戦したら
途中でギブアップ。
でこの方法を教えてもらい、
・エクセルファイルを開く
・画面中央のコマンドボタンをクリック
・ＣＳＶファイルを選択
・自動で編集、自動で名前を付けて保存
がうまく出来上がり、これで完結でした。

それとはまったく別の話で、
基幹システムから、いろいろなメニューで必要時吐き出すエクセルファイル。
これの例の11文字から14文字をハイフン編集したいと考えていて
セルに式を入れて式のコピーをして値貼付でやっていました。
もしやマクロの記録でマクロ化できれば楽になるかと思い、
その手動作業をマクロの記録で実践したら記録が出来ず、
ここで質問となりました。
結果はご存知のとうり、マクロ記録では出来ない。
という事でいろいろと教えていただきました。

そのときふと思いついたのが完結していたCSVファイルの方です。
こちらに応用できないかと思い他の方に教えていただいた
CSV呼出自動保存のマクロ内でCallで呼び出している自分で作成したマクロ
　Sub　編集()
に連結しようと思いました。
連結したのは対話型ではなく最初に教えていただいた
>単独で実行するなら、以下のようになるのですが、
>B列でひとつ挿入するのでデータが右にズレています。
>だから連続する場合は、それを勘定に入れていなくてはなりません。
>マクロに慣れている人なら、右から実行していくのですが、
>慣れていないと分からなくなるはずです。
>だから、この次のマクロ(TestMacro3)を使ったほうが無難です。

Sub　編集　()

---私が作成した記述---
その後に連結
With ActiveSheet
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
'.FormulaR1C1 = "=ConvertNumbers(RC[-1])"
.Value = .Value
.NumberFormatLocal = "00000000000000"　'*
.HorizontalAlignment = xlLeft
End With
End With
End Sub
----------------------------------------------　　
Function ConvertNumbers(ByVal arg As Variant) As String
　　以下省略
End Function

で.FormulaR1C1 = "=ConvertNumbers(RC[-1])"から
Function ConvertNumbers(ByVal arg As Variant) As Stringには
行かなくて結果編集がされず#NAME?になります。
でA-NO.7のお礼に書いた方法を選びました。

回答No.7

Wendy02

#4のお礼の文面で、あまり、つなぎ合わせるのはうまくありません。それに、「セルH列にはすべて#NAME?になってしまいます。」というのは、他人のコードの問題です。あまり人様のコードをとやかく言わないのが、マナーですが、一応、こちらのまな板に上がってくると、ちょっと言わざるえなくなりますね。

私はこれにとやかくいうつもりはなかったので書かなかったのですが、細かい所に問題が残っています。人の書いたものだけれども、直しました。特に、i はLong型です。別に、3万いくつにならないなら、i はInteger で良いというのではなくて、基本的にそういう決まりになっています。（と書くと、この前の人のように、反論する人もいますが、そういう人は、絶対上達しないです（^^;）

Sub CSVImport()
　Dim fName As Variant
　Dim mBk As Workbook
　Dim n As Integer
　Dim TextLine As String
　Dim Ar As Variant
　Dim i As Long
　Dim o As Integer
　fName = Application.GetOpenFilename("CSVファイル(*.csv),*.csv", , "ファイルの選択")
　If VarType(fName) = vbBoolean Then Exit Sub
　i = i + 1
　o = Application.SheetsInNewWorkbook
　Application.SheetsInNewWorkbook = 1
　With Workbooks.Add
　　.ActiveSheet.Name = "集計"
　　n = FreeFile
　　Open fName For Input As #n
　　Application.ScreenUpdating = False
　　Do Until EOF(n)
　　Line Input #n, TextLine
　　If Len(TextLine) > 0 Then
　　　Ar = Split(TextLine, ",")
　　　ActiveSheet.Cells(i, 1).Resize(, UBound(Ar) + 1).Value = Ar
　　End If
　　i = i + 1
　　If i > Rows.Count Then Exit Sub
　　Loop
　End With
　Application.ScreenUpdating = True
　Close #n
　Application.SheetsInNewWorkbook = o
End Sub

現在のコードで、フォルダを取る必要は見当たりませんが、このようにして取ります。

　fName = ActiveWorkbook.FullName
　j = InStrRev(fName, "\")
　If j > 0 Then
　　mPath = Mid$(fName, 1, j - 1)
　Else
　　Exit Sub
　End If

CSV に、
Application.Calculation = xlManual
これは必要ないです。それで、Calculateイベントが走るわけがないのです。そこに関数をいれたら、#NANME! になりますね。

よほど経験がなければ、あまり、一緒に処理しようとしないほうがよいと思います。

>単独では問題ないのですが別のプロシージャーからCall　編集集計で呼び出すと駄目です。

その「編集集計」が何を意味するのかは分かりませんが、私の対話型マクロは向きません。それから、同じプロシージャにコードをつなげてしまうのでは、あまり関心しません。修正しにくくなります。

お礼

以下の修正で
・1が1でなく00001と表示される
・ハイフン編集されないで#NAME?になる
・自動保存されない

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

Sub 自動編集()
Dim fName As String
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
Dim buf As String
Dim rng As Range
Dim i As Integer
Dim fLine As Variant
Dim fso As Object
Dim dPath As String
fName = Application.GetOpenFilename( _
FileFilter:="CSVファイル(*.csv),*.csv" _
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
dPath = fso.GetParentFolderName(fName)
Application.SheetsInNewWorkbook = 1
Set wBook = Workbooks.Add
Set wSheet = wBook.Worksheets(1)
wSheet.Name = "集計"
Set rng = wSheet.Range("A1")
Dim n As Variant
n = FreeFile
Open fName For Input As #n
i = 0
Do Until EOF(n)
Line Input #n, buf
fLine = Split(buf, ",")
rng.Offset(i).Resize(, UBound(fLine) + 1).Value = fLine
i = i + 1
Loop
Close #n
wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value

Call 編集集計

wBook.SaveAs Filename:=dPath & "\編集済" & Year(Date) & Month(Date) & Day(Date) & ".xls", _
FileFormat:=XlFileFormat.xlWorkbookNormal
wBook.Close
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
MsgBox "編集終了"
End Sub
---------------------------------------------------------
Sub　編集集計()

----省略---

'（ConvertNumbersの動作を停止）
'With ActiveSheet
'G列にデータがあることを意味します。
' .Columns(8).Insert
' With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
' .Formula = "=ConvertNumbers(RC[-1])"
' .Value = .Value
' .NumberFormatLocal = "00000000000000" '*
' .HorizontalAlignment = xlLeft
'End With

'（以下の文を追加　ハイフン編集）
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
行 = 1
Do
If Cells(行, 7).Value = "" Then Exit Do
n = Cells(行, 7)

Select Case True

Case = ----省略---
Value = ----省略---

----省略---

Case Else
Value = n
End Select
Cells(行, 8) = Value
行 = 行 + 1
Loop

----省略---

End Sub
-------------------------------------------------------
' .Formula = "=ConvertNumbers(RC[-1])"を停止したので呼ばれない

Function ConvertNumbers(ByVal arg As Variant) As String
----省略---
End Function

補足

申し訳ありません。混乱させてしまいました。
そもそも、
１．CSVファイルをクリックしてエクセルで開いて編集をする
２．その編集（約14種類）する為のマクロは完成→マクロ１
３．編集したらCSVでは保存が出来ないからエクセルで保存
４．これだと保存したファイルはマクロ１込みで保存されてしまう
５．また操作者がＰＣにうとい
６．よってボタン１個のクリックでＣＳＶファイルを選んだら
　　自動で編集マクロ１が走り、名前を付けて保存まで完了
　　させる
でこれは他の方に教えていたいただき完成しました。→マクロ２

・マクロ２の内容
　CSVファイルを別BOOKで開いてCallで予備だしたマクロ１で
　編集して名前を付けてエクセル方式で保存

次に、どうせならハイフンの編集もしたくなり
このCallで呼び出すマクロ１に教えていただいた
G列の右に空白行を作成し編集データを貼付する記述を
追加しました。→マクロ３

そのマクロ３を新規エクセルBOOKに組み込み、シート１に
CSVファイルのデータをコピペしてマクロ３を実行。
思ったとうり編集が出来ました。

ではいよいよ、マクロ２を組み込んだBOOKでCallでマクロ３を実行。
思ったとうりに保存までできました。
ですがマクロ２で保存されたエクセルファイルを見ると
Ｈ列がすべて#NAME?になっていました。

また、ハイフン編集を教えてもらっている時に
いろいろ教えていただいているうちに
このCSVファイル自動編集保存ではなく別の編集作業転用したいと考えました。
それで対話型を教えていただきました。

よってこのマクロ２は対話型は使用しません。
ボタン１個をクリックしたら、ファイルを選択以外は
全自動でいきたいので対話は不要だからです。
教えていただくうちに、使い道を分岐させました。

早速教えていただいた内容で検証しています。
（１）CSVファイルをクリックしてエクセルで開いた時は
　　　1は1なのですが、これが0001で表示されてしまいます。
　　　マクロ２を教えていただいた方には
　　　wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value
　　　を入れるように指導していただいたのですが
　　　これを入れるべきでしょうか？入れる位置が不安です。
（２）Callで呼び出したマクロ３でやはり
　　　H列に編集データが入らず#NAME?のままです。
　　　マクロ３にはこの記述を利用しています。
Sub　マクロ３()
---省略---
With ActiveSheet
'G列にデータがあることを意味します。
.Columns(8).Insert
With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
.Formula = "=ConvertNumbers(RC[-1])"
.Value = .Value
.NumberFormatLocal = "00000000000000" '*
.HorizontalAlignment = xlLeft
End With
End With
---省略---
End　Sub
Function ConvertNumbers(ByVal arg As Variant) As String
---省略---
End Function

（３）自動保存の所でERRになります。記述を変更したので、以下の部分も変更が
　　　必要だと思いますが、参考書片手では厳しい状況です。　　　　　

Call マクロ３
wBook.SaveAs Filename:=dPath & "\編集済" & Year(Date) & Month(Date) & Day(Date) & ".xls", _
FileFormat:=XlFileFormat.xlWorkbookNormal
wBook.Close
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
MsgBox "編集終了"
End Sub

回答No.6

Wendy02

最初に、私が考えているマクロのコードの意図は読み取れているようですね。

>また項目行がなく1行目からデータがある場合と、項目が1行目にあって
>２行目からデータが始まる場合があります。
それらは想定の範囲です。マクロに組み入れました。最上行から、200行まで、探します。

>対象データ列を間違えたらアラームを出したいと思いました。
何もデータがない場合に、メッセージを出します。

>列を選択した時に１１文字から１４文字以外の文字数の列の場合は
>アラームを出したくて教えてらった記述を変更しているのですが
>うまくいきません。

すでに私は想定はしていたのですが、それは、「余計なおせっかいも度が過ぎる」と思ったので辞めたのです。エラーチェックする部分を大幅に替えてみました。2万行程度までは問題なく可能だと思います。
処理の後のデータは、このチェックで引っかかります。

どのように変えたかみてください。
今度は、どこを選択しても、必ず先頭行に行きます。

'//
Sub TestMacro4()
　Dim col As Long
　Dim rw As Long
　Dim a As Long, b As Long, i As Long
　Dim r As Range, rr As Range, v As Variant
　'データのある場所の列を選択してください。
　With ActiveSheet
　　On Error Resume Next
　　col = ActiveCell.Column
　　Set r = .Columns(col).SpecialCells(xlCellTypeConstants, 23).Columns(1)
　　If Err.Number > 0 Then MsgBox "この列にデータがありません。", vbExclamation: Exit Sub
　　On Error GoTo 0
　　rw = r.Cells(1).Row
　　col = r.Cells(1).Column
　　i = 1
　　Do Until IsNumeric(r.Cells(i))
　　　i = i + 1
　　　rw = rw + 1
　　　If rw > 200 Then MsgBox "200行までに該当するデータが見つかりません", vbExclamation: Exit Sub
　　Loop
　　v = .Range(.Cells(rw, col), .Cells(Rows.Count, col).End(xlUp)).Address
　　a = Evaluate("MIN(LEN(" & v & "))")
　　If a < 11 And a > 0 Then '下限
　　　Set rr = r.Find(String(a, "?"), LookAt:=1): rr.Select
　　　MsgBox "長さ' " & a & " 'の不要な単語が含まれています。: " & rr.Address(0, 0), vbExclamation: Exit Sub
　　End If
　　b = Evaluate("MAX(LEN(" & v & "))")
　　If b > 14 Then '上限
　　　Set rr = r.Find(String(b, "?"), LookAt:=1): rr.Select
　　　MsgBox "長さ' " & b & " 'の不要な単語が含まれています。: " & rr.Address(0, 0), vbExclamation: Exit Sub
　　End If
　　.Cells(rw, col).Select
　　If MsgBox("ここから、右側の列に出力されます (" & .Cells(rw, col).Address(0, 0) & ")", vbOKCancel + vbQuestion) = vbCancel Then Exit Sub
　　.Columns(col + 1).Insert
　　With .Range(.Cells(rw, col + 1), Cells(Rows.Count, col).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　　.NumberFormatLocal = "00000000000000"
　　　.HorizontalAlignment = xlLeft
　　End With
　End With
End Sub

これで試してみてください。

お礼

混乱させて大変申し訳ありません。
このQNo.6200348の質問は、（マクロ記録が出来ません）でした。
でマクロ記録では出来ませんと教えていただき記述も教えていただきました。
結果的に本来の目的である編集作業をマクロで記録できないの解決の
ために
（１）Ａ列にデータがありＢ列に編集後の値を入れる（TestMacro1）
（２）Ｇ列にデータがありＨ列に編集後の値を入れる（TestMacro2）
（３）変換する対象列を選択すると右隣に列を挿入して処理（TestMacro3）
（４）汎用性を持たせるために多少のエラーオプションと対話型のオプション（TestMacro3上書）
（５）どこを選択しても、必ず先頭行（TestMacro4）

以上５種類を教えていただきました。
この中で（２）をCSVファイルインポート全自動編集自動保存マクロに組み込みます。
（ただ、まだ解決していません。→ConvertNumbersの動作を停止しSelect Case で対応。
　QNo.6198175-ANO.4を流用です。）

別な作業で基幹システムから吐き出ししたいろいろな形態のエクセルファイル。
この編集対象データがどの列にあるかは吐き出ししたメニューで相違する。
よってこれは（４）で対応したいです。
でバージョンアップの（５）を教えていただいたのですが、うまくいきません。

現在の状態
１．エクセルファイルＡに、（５）をセット
２．基幹システムから吐き出したファイルをクリックして
　　別のエクセルＢが開く
３．開いたエクセルＢのデータを全て選択しコピー
４．エクセルＡのシート１に貼付
５．エクセルＢは閉じる
６．マクロの（５）を起動
７．該当データでない列にカーソルがあるとエラーメッセージが出る
８．該当データのセル、列、にカーソルを置いてもエラーメッセージが出る場合がある

このエクセルファイルＢはエクセルですが
・ファイルの種類　Microsoft Excel 2.1ワークシート
・数値データのセルは
　左上に緑の三角があり
　（このセルにある数値がテキスト形式かアポストロフイではじまっています）
　となっています。
　！マークの中の（数値に変換をする）をクリックで緑の三角はとれます。
　右クリックのセルの書式設定で（数値）を選択しても緑三角は取れません。
・対象データの列には緑三角マークはありません

です。

ただファイルによっては行の途中から編集がされる物もあります。
３０行目からはじまったりなど。
それを見ても、その対象列は全て１４文字です。

例えば３行しかデータがなくて
１行目　9C1P1234567890
２行目　9X0A5555-55555
３行目　JX123456789012
の場合だと
３行目から編集され１，２行目は空白です。

で上記の場合で
２，３行目を削除して
１行目　9C1P1234567890　だけにしてマクロを起動すると
とこの１行目が編集されます。

ここがよくわかりません。

あとこれは当然でしょうが
選択したセルが17:57:24という時刻が入力されている列の場合
エラーメッセージでなく
実行時エラー'91’:
オブジェクト変数またはWithブロック変数が設置されていません。
と表示されます。

Set rr = r.Find(String(b, "?"), LookAt:=1): rr.Select
のrr.Selectが黄色です。

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

補足

Wendy02さんへ。2010年9月29日。

QNo.6198175(VBスクリプトで他条件分岐を作成したいです。)
http://okwave.jp/qa/q6198175.html
もう↑でお礼ができないのでこの場を借りてお礼です。

(1)9月4日QNo.6153785-ANO.2で教えてもらった物
　・動作しない
　　QNo.6198175-ANO.4の補足に記述した方法で直りました。
　・誤編集される
　　QNo.6198175-ANO.4の補足の記述した方法で直りました。

(2)9月22日QNo.6198175-ANO.2で教えてもらった物
　・動作OK
　・誤編集される→自分で修正
　　QNo.6198175-ANO.4のお礼でまだ直らないと回答
　　　↓この記述の先頭にmが有りました。mをとったら直りました。
　　mValue = Left(n, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2)_
& "-" & Mid(n, 13, 2)

(3)9月26日QNo.6198175-ANO.4で教えてもらった物(コード短縮）
　そのままで動作もし編集も正しかったです。これで計3個とも正常動作です。

9月26日QNo.6198175-ANO.4で教えてもらった物(コード短縮）
↓
n = Value
Select Case True

'パターン5の編集　頭が9Xで計14桁でハイフンがない
Case Left(n, 2) = "9X" And Len(n) = 14 And InStr(1, n, "-", 1) = 0
'3-11でハイフン編集
Value = Left(n, 3) & "-" & Mid(n, 4)

'パターン3の編集1　頭が9で計14桁でハイフンがある
Case Left(n, 1) = "9" And Len(n) = 14 And InStr(1, n, "-", 1) = 9
'3-11でハイフン編集　元々9桁目にハイフンがあるので結果は3-5-5になる
Value = Left(n, 3) & "-" & Mid(n, 4, 11)

'この↑パターン3の編集1について
'9頭と9X頭の定義が別文で指定されている
'それは9頭の計14桁ハイフン無しと9X頭の計14桁ハイフン無し
'よって計14桁でハイフンがあれば頭9も9Xもそれ以外も
'パターン3の編集2でモーラされるので省いてもいいのでは？
'削除の’を付けて動作させて問題無しを確認済み↓

'パターン3の編集2　頭を問わず計14桁でハイフンがある（削除可）
Case Len(n) = 14 And InStr(1, n, "-", 1) = 9
'3-11でハイフン編集　元々9桁目にハイフンがあるので結果は3-5-5になる（削除可）
Value = Left(n, 3) & "-" & Mid(n, 4, 11)

'パターン4の編集　頭が9で計14桁でハイフンがない
Case Len(n) = 14 And InStr(1, n, "-", 1) = 0 And Left(n, 1) = "9"
'5-5-2-2でハイフン編集
Value = Left(n, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2)_
& "-" & Mid(n, 13, 2)

'パターン1の編集　頭を問わず計14桁でハイフンがない
'9頭と9X頭はすでに条件分岐されているのでこれでいい
Case Len(n) = 14 And InStr(1, n, "-", 1) = 0
'3-5-2-2-2でハイフン編集
Value = Left(n, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) _
& "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)

'上記以外のパターンの場合　主にパターン2
Case Else
'編集しないでnをそのまま表示
Value = n
End Select　

私のせいで脱線してしまいましたが
CSVインポート自動編集自動保存マクロ文の途中でCallで呼び出すマクロの
例の"=ConvertNumbers(RC[-1])"とFunction ConvertNumbersの部分を
動作停止にして上記記述を改造した物を挿入して思ったとおり動作しています。
どうもありがとうございました。

回答No.5

Wendy02

>If WorksheetFunction.CountA(.Columns(col)) < 2 Then _
>MsgBox "データ列を選択してください。
>これは表示されるのが正規でしょうか？

これは、読めませんか？1行以下だったら、その列は間違いだから、「データ列を選択してください」ということです。だから、何もない列でマクロを実行したら、そのメッセージが出ます。

お礼

すいません。補足を間違えました。2010年9月24日20:50

＞If WorksheetFunction.CountA(.Columns(col)) < 2 Then _
＞この２を１２にすると１１行以下は全部このメッセージが出ますか？
＞また１１行以上１４行以下の場合は
＞< 12:15 Then
＞でいいのでしょうか？

意味不明な補足でした。申し訳ありません。
今回教えていただいた編集マクロは
基幹システムが吐き出したエクセルファイルを編集します。
対象データがどの列にあるかは吐き出したモジュールで相違するために
また項目行がなく1行目からデータがある場合と、項目が1行目にあって
２行目からデータが始まる場合があります。
それをモーラしていただきました。
操作してみて対象でない違う列を選択しても編集されてしまうので、
対象データ列を間違えたらアラームを出したいと思いました。
対象データの列は１１文字から１４文字の文字数と決まっています。
それ以外の文字数はありません。
他の列でこの条件に当てはまる列はありません。
よって、列を選択した時に１１文字から１４文字以外の文字数の列の場合は
アラームを出したくて教えてらった記述を変更しているのですが
うまくいきません。
それで、おかしな補足をしてしまいました。

補足

すいません。メッセージ出ました。
てっきりマクロ起動で
（データ列を選択してください）
が表示されてからデータ列を選択し選択するとそのセル（列）がアクティブになるから
マクロがリスタートするのかと思いました。
記述でまだ知らない物がたくさん有り、そこまで分析できませんでした。
申し分けありません。

If WorksheetFunction.CountA(.Columns(col)) < 2 Then _

この２を１２にすると１１行以下は全部このメッセージが出ますか？

また１１行以上１４行以下の場合は
< 12:15 Then
でいいのでしょうか？

回答No.3

Wendy02

単独で実行するなら、以下のようになるのですが、B列でひとつ挿入するので、データが右にズレています。だから、連続する場合は、それを勘定に入れていなくてはなりません。マクロに慣れている人なら、右から実行していくのですが、慣れていないと分からなくなるはずです。だから、この次のマクロ(TestMacro3)を使ったほうが無難です。

Sub TestMacro2()
　With ActiveSheet
　　'G列にデータがあることを意味します。
　　.Columns(8).Insert
　　With .Range("H1", Cells(Rows.Count, 7).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　　.NumberFormatLocal = "00000000000000"　'*
　　　.HorizontalAlignment = xlLeft
　　End With
　End With
End Sub

*書式設定が必要かどうかは分かりませんが、文字列出力をしない場合と、左詰めにしておく場合には、このようなものを加えると良いです。
　.NumberFormatLocal = "00000000000000"
　.HorizontalAlignment = xlLeft

これは、変換する対象列を選択すると、右隣に列を挿入して、処理するものです。
A列にデータがあるなら、B列に挿入されます。H列にデータがあるなら、その隣のI列に挿入されてデータを作ります。

Sub TestMacro3()
Dim col As Long
　'データのある場所を選択してください。
　With ActiveSheet
　　 'その列のデータの数を数え、1個以下なら、取りやめ
　　 If ActiveCell.CurrentRegion.Columns(1).Cells.Count < 2 Then Exit Sub
　　 col = Selection.Column
　　.Columns(col + 1).Insert
　　With .Range(.Cells(1, col + 1), Cells(Rows.Count, col).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　　.NumberFormatLocal = "00000000000000"
　　　.HorizontalAlignment = xlLeft
　　End With
　End With
End Sub

お礼

本当にいろいろありがとうございます。

＞標準モジュールなら、同じブック内ならどこからでも呼び出せる、
＞ユーザー定義関数(User Defined Function)で、ひとつだけです。
＞標準モジュールに書くと、Public ステートメントは省略出来ます。

これは、参考書やWEB検索でもわからなかったです。

＞それと、あまりモジュール数を増やすのは感心しません。
＞私も経験があるのですが、とても管理しにくくなります。
＞もし、ずっと続けるものならば、そのモジュールの名称を変えてあげるとよいです。
＞my_Utilities とかモジュール名を替えて、
＞同じ種類のものをまとめて入れておくとよいです。

はい。退職者から譲り受けたエクセルファイルでモジュールが２０個というのが
あり説明文もなく苦労しましたので、今はモジュールは増やしていません。
今回はテストするために各モジュールに分けました。
使わなくなったら記述だけ保存し、モジュールの開放で消します。

モジュールの名前を変更した時に、プロシージャー名と同じにして
Callでそのプロシージャー名を指定して動作不良を起こし、
ここで他の方に丁寧に教えて絵いただきやっと理解した所です。
http://okwave.jp/qa/q6145802.html

今はモジュール名はそのままでマクロ名だけを変えてます。
またプロシージャー名の後に説明文は入れてます。
今回は
・対象データがＡ列に有りＢ列に編集後のデータを貼り付ける
などです。

また教えてもらった、記述は全部調べ、分1行の前に'で
全部日本語で説明を入れてます。
その為、すごい長文になっています。

＞また、もっと使うものは、アドインにしたり、PERSONAL.XLSに入れます。
＞アドインにすると、社内などで配布が可能になります。
＞配布後は、エクスポートで保存しておきます。Basという拡張子ですが、
＞内容はテキストファイルです。

ちょっと敷居が高いかもです。

＞ただ、コードは、ただ、十分にバグを潰さないといけません。
＞プロシージャの名前も、TestMacroではなく、ちゃんとしたものにしたほうがよいです。
＞また、コードの部分には、日付と説明を入れておきます。
＞後々、誰かが、これはなんだろうとみて分かるようにします。

はい。前述のごとく、プロシージャの名前わかりやすい名前
今回は
Sub　ハイフン編集()
にしました。
また作成日付、自分の名前も入れました。

補足

全部で３つ教えていただきました。ありがとうございます。

１．Ａ列にデータがあり隣のＢ列に値を返す場合：Sub TestMacro1()
２．Ｇ列にデータがあり隣のＨ列に値を返す場合：Sub TestMacro2()
３．変換する対象列を選択すると、右隣に列を挿入して、処理：Sub TestMacro3()

全て正常動作しました。
今覚えておきたいので、すいませんが教えてください。

Module1にNO.1：Sub TestMacro1()
Module2にNO.2：Sub TestMacro2()
Module3にNO.3：Sub TestMacro3()

を記述して、マクロの実行でMacroを指定して実行した時全て誤動作でした。
すべてのModule内に
　Function ConvertNumbers(ByVal arg As Variant) As String.....以下省略
の文を記述していました。

でModule1の
　Function ConvertNumbers(ByVal arg As Variant) As String.....
だけ残して
Module2とModule3の同文は削除したら３つとも正常動作になりました。
この解釈は正しいでしょうか？

もう一つお願いします。
１種類だけ教えていただければ後は自分で修正しようと思い今回失敗しました。
今回は、全て１行目からデータがある事を前提に質問しました。
で対象データがいろいろな列にある場合がある場合は
今回教えていただいた内容でクリアしました。

基幹システムの出力において、対象データが
Ａ列だったり、Ｇ列だったりは説明したとうりなのですが
場合によって、この対象データが２行目から始まる物もあります。
１行目には項目が入ってます。
よってＡ列に対象データが有る場合だとＡ２からＡのデータがある行まで
Ｇ列に対象データがある場合だとＧ２からＧのデータがある行までとなります。
このパターンの場合でも教えていただいた内容で実行したら正常動作しました。
これは作成していただいたマクロ記述の中に根拠が有るという解釈で間違いないでしょうか？
以上よろしくお願いします。

大変申しわけありませんがアドバイスいただければお願いいたします。

回答No.2

Wendy02

>式はとりあえず、質問したときの内容で
>以下の５種類を以下の法則で完璧に編集できていますのでいいかと思いました。

VBAとは違うので、こちらが、ごちゃごちゃ言っても始まらないでした。関数に地道に頑張って回答している人もいますから、そういう人に任せます。VBAの関数と数式を比べてみましたが、食い違いは出ないようです。ただし、文字長の長さの区分けはありますから、もしも、14桁より以上の場合処理されません。

はっきりと取り出したい時は、「アポストロフィ(')」を外してください。
　 buf = n ' & "(非該当)"　

とりあえず、補足のリストから作り直しましたので、それを使ってください。以前のものも、必要だったら差し替えてください。

Function ConvertNumbers(ByVal arg As Variant) As String
Dim n As String
Dim buf As String
If arg = "" Then Exit Function
n = StrConv(Trim(arg), vbNarrow)
Select Case True
　Case Left(n, 2) = "9X" And InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 2) = "9X" And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 11)
　Case Left(n, 1) = "9" And InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 1) = "9" And Len(n) = 14
　 buf = Mid(n, 1, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
　Case InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 1) <> 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
　Case InStr(1, n, "-") = 6 And Len(n) = 12
　 buf = n
　Case Left(n, 2) = "9X" And InStr(1, n, "-") = 6 And Len(n) = 11
　 buf = n
　Case InStr(1, n, "-") = 6 And Len(n) = 11
　 buf = n
　Case Else
　 buf = n '& "(非該当)" '非変換を抽出
End Select
ConvertNumbers = buf
End Function

お礼

質問内容以上に展開をした上で、
すごくわかりやすく教えていただきまして、ありがとうございました。
非常に助かりました。

補足

Sub TestMacro1()
　With ActiveSheet
　　.Columns(2).Insert
　　With .Range("B1", Cells(Rows.Count, 1).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　End With
　End With
End Sub
Function ConvertNumbers(ByVal arg As Variant) As String
Dim n As String
Dim buf As String
If arg = "" Then Exit Function
n = StrConv(Trim(arg), vbNarrow)
Select Case True
　Case Left(n, 2) = "9X" And InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 2) = "9X" And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 11)
　Case Left(n, 1) = "9" And InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 1) = "9" And Len(n) = 14
　 buf = Mid(n, 1, 5) & "-" & Mid(n, 6, 5) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
　Case InStr(1, n, "-") = 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4)
　Case Left(n, 1) <> 9 And Len(n) = 14
　 buf = Mid(n, 1, 3) & "-" & Mid(n, 4, 5) & "-" & Mid(n, 9, 2) & "-" & Mid(n, 11, 2) & "-" & Mid(n, 13, 2)
　Case InStr(1, n, "-") = 6 And Len(n) = 12
　 buf = n
　Case Left(n, 2) = "9X" And InStr(1, n, "-") = 6 And Len(n) = 11
　 buf = n
　Case InStr(1, n, "-") = 6 And Len(n) = 11
　 buf = n
　Case Else
　 buf = n '& "(非該当)" '非変換を抽出
End Select
ConvertNumbers = buf
End Function

で完璧にできました。ありがとうございます。

大変申し訳ありません。
まったく同じ処理をしたいデータがあるのですがそれは社員番号がＧ列に有ります。
よってＧ列の横に空白列を作成しそこに編集後のデータを入れたく

.Columns(8).Insert
　　With .Range("H1", Cells(Rows.Count, 1).End(xlUp).Offset(, 1))

にすれば出来ると思ったのですが
Ｂ列からＨ列がすべて#NAME?になってしまいます。

回答No.1

Wendy02

>なぜ記録されないのでしょうか？
>どうすれば記録できますか？
簡単に言えば、そのような技術は、上級レベルの力が必要です。でも、そのような技術を求められているとは思いません。記録マクロでは、数式を一旦、どこかに置かないといけません。記録マクロは、マクロとは言えません。まず、VBAの基礎的な所から学ばないといけないと思います。

以下で作ってみました。ただ、元の数式がおかしいですね。最初に、<>"9"では、9以外は、みんな、そこに該当してしまいます。

IF(FIND("-",ASC(A1))=6,A1,MID(A1,1,3)&"-"&MID(A1,4,11)　

が生きていません。マクロでは直っているはずです。それから、ここには最低文字長の規定がありませんから、本来は、以下のような処理は必要だと思います。たぶん、これは、電話番号の羅列の数字をチャンク化させるものだと思います。

そのために、一応、このようなオプションを考えました。
'右端に-がある場合に削除
'Do Until Not buf Like "*-"
'　buf = Left(buf, Len(buf) - 1)
'Loop

'//
Sub TestMacro1()
　With ActiveSheet
　　.Columns(2).Insert
　　With .Range("B1", Cells(Rows.Count, 1).End(xlUp).Offset(, 1))
　　　.Formula = "=ConvertNumbers(RC[-1])"
　　　.Value = .Value
　　End With
　End With
End Sub

Function ConvertNumbers(ByVal arg As Variant) As String
Dim buf As String
If arg = "" Then Exit Function
buf = StrConv(arg, vbNarrow)
If InStr(1, buf, "-") = 6 Then
　 buf = Mid(buf, 1, 3) & "-" & Mid(buf, 4, 11)
ElseIf Left(buf, 2) = "9X" Then
　buf = Mid(buf, 1, 3) & "-" & Mid(buf, 4, 11)
ElseIf Left(buf, 1) = "9" Then
　buf = Mid(buf, 1, 5) & "-" & Mid(buf, 6, 5) & "-" & Mid(buf, 11, 2) & "-" & Mid(buf, 13, 2)
ElseIf Left(buf, 1) <> "9" Then
　buf = Mid(buf, 1, 3) & "-" & Mid(buf, 4, 5) & "-" & Mid(buf, 9, 2) & "-" & Mid(buf, 11, 2) & "-" & Mid(buf, 13, 2)
Else
　buf = Mid(buf, 1, 3) & "-" & Mid(buf, 4, 11)
End If
'右端に-がある場合に削除
'Do Until Not buf Like "*-"
'　buf = Left(buf, Len(buf) - 1)
'Loop
　ConvertNumbers = Trim(buf)
End Function

お礼

修正した回答をありがとうございました。

補足

すいません。
マクロの記録ができない質問でしたので式の意味は書きませんでした。
これは社員番号です。
毎深夜、夜間バッチでエクセルファイルが出来ます。
それを朝、抽出して以下の法則でハイフンをかませます。
データは9,000行あります。列も10列あります。
9,000行の中には大きく分かれて以下の5種類がばらばら
に入力された状態で出力されます。
以前は並べ替えやオートフィルタなどを利用して
5種類を区分けして、編集していましたが
この式完成後はＢ列に貼り付けるだけで
簡単に編集ができるようになりました。

いつもはエクセルを開いて
この対象データのあるＡ列と別データのあるＢ列に列挿入して、
質問したＮＯ．１～ＮＯ．１１を手作業で行っています。
毎日行うのでマクロ化を考えました。

式はとりあえず、質問したときの内容で
以下の５種類を以下の法則で完璧に編集できていますのでいいかと思いました。

＞9以外は、みんな、そこに該当してしまいます。
はい。ほぼそうなります。

１．○○○○○○○○○○○○○○（先頭が9以外で14文字）　
　　　↓
　　○○○-○○○○○-○○-○○-○○
（3と4、8と9、10と11、12と13桁の間に半角ハイフンを入れる）

２．○○○○○-○○○○○（5ケタ ハイフン　5ケタ）　
　　　　↓
　　○○○○○-○○○○○（変換しない）

　　9○○○○-○○○○○（先頭が3で５ケタ　ハイフン　５ケタ）
　　　　↓
　　9○○○○-○○○○○（変換しない）

　　9X○○○-○○○○○（先頭が9Xで５ケタ　ハイフン　５ケタ）
　　　　↓
　　9X○○○-○○○○○（変換しない）

３．○○○○○○○○-○○○○○（8桁ハイフン5桁）　
　　　↓　
　　○○○-○○○○○-○○○○○（3と4、8と9桁の間に半角ハイフンを入れる）

　　9○○○○○○○-○○○○○（先頭が9で8桁ハイフン5桁）　
　　　↓
　　9○○-○○○○○-○○○○○（3と4、8と9桁の間に半角ハイフンを入れる）

　　9X○○○○○○-○○○○○（先頭が9Xで8桁ハイフン5桁）　
　　　↓
　　9X○-○○○○○-○○○○○（3と4、8と9桁の間に半角ハイフンを入れる）

４．9○○○○○○○○○○○○○（先頭が9で14桁）　
　　　↓
　　9○○○○-○○○○○-○○-○○（5と6、10と11、12と13桁の間に半角ハイフンを入れる）

５．9X○○○○○○○○○○○○（先頭が3Aで14桁）
　　　↓
　　9X○-○○○○○○○○○○○（3と4桁の間に半角ハイフンを入れる）