こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

1.B1セルを選択
2.右クリックで挿入を選択
3.A列とC列にデータが有り、B列は空白となる。
4.B1セルを選択します。右クリックの貼り付けを選択。
  マクロの起動前にクリップボードにあらかじめコピーしておいた
  以下の式を貼り付けます。
  
  =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))))

5.「Ctrl」+「C」
6.エクセルの左上の名前ボックスを
  B1 → B1:B1000に変更。B列が選択されます。
7.「Ctrl」+「V」
8.B列に式で変換されたデータが入りました。B列が選択されたままです。
9.「Ctrl」+「C」
10.右クリックで形式を選択して貼り付けで「値」を選んでOKをおす。
   セルに入っていた式は全て消えました。B列が選択されたままです。
11.マウスポインタをセルのA1におく。B列の選択が解除されました。

マクロの記録を終了

これでNO.4の操作の時に「記録できません」と表示されます。

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

実際にマクロの記録中でもNO.1からNO.11の操作は出来ています。

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

投稿日時 - 2010-09-22 18:07:59

QNo.6200348

困ってます

質問者が選んだベストアンサー

> 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ではなく、ちゃんとしたものにしたほうがよいです。また、コードの部分には、日付と説明を入れておきます。後々、誰かが、これはなんだろうとみて分かるようにします。

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

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

'//
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

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

投稿日時 - 2010-09-24 14:59:56

補足

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

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

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

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

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

投稿日時 - 2010-09-24 18:07:33

お礼

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をやめてここにそのまま記述)

投稿日時 - 2010-09-27 15:34:45

ANo.4

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(10)

ANo.10

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

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

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

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

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

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

投稿日時 - 2010-10-01 10:14:16

お礼

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

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

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

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

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

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

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

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

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

投稿日時 - 2010-10-01 11:26:37

ANo.9

'前の投稿の続き
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-09-28 18:59:37

補足

こうなっています。

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

投稿日時 - 2010-09-28 20:35:08

お礼

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

本スレッドの本来の目的である、
関数式をマクロで記録できない件の最終回答である
ANo.6で教えていただいた物

どこを選択しても、必ず先頭行(TestMacro4)
記述は一切変更しないで実行しています。

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

これが原因不明です。

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

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

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

これも原因不明です。

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

投稿日時 - 2010-10-01 13:04:30

ANo.8

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

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

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

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

 With ActiveSheet.UsedRange
  .Value = .Value
 End With

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

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

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

Sub マクロ3() は、以下のようになります。
あまり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-09-28 18:57:22

補足

大変申し訳有りません。

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

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

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

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

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

それとはまったく別の話で、
基幹システムから、いろいろなメニューで必要時吐き出すエクセルファイル。
これの例の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のお礼に書いた方法を選びました。

投稿日時 - 2010-09-28 20:13:02

お礼

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

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

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

・回答A

>単独で実行するなら、以下のようになるのですが、
>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

投稿日時 - 2010-09-28 20:49:56

ANo.7

#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 編集集計で呼び出すと駄目です。

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

投稿日時 - 2010-09-27 18:28:35

補足

申し訳ありません。混乱させてしまいました。
そもそも、
1.CSVファイルをクリックしてエクセルで開いて編集をする
2.その編集(約14種類)する為のマクロは完成→マクロ1
3.編集したらCSVでは保存が出来ないからエクセルで保存
4.これだと保存したファイルはマクロ1込みで保存されてしまう
5.また操作者がPCにうとい
6.よってボタン1個のクリックでCSVファイルを選んだら
  自動で編集マクロ1が走り、名前を付けて保存まで完了
  させる
でこれは他の方に教えていたいただき完成しました。→マクロ2

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

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

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

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

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

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

早速教えていただいた内容で検証しています。
(1)CSVファイルをクリックしてエクセルで開いた時は
   1は1なのですが、これが0001で表示されてしまいます。
   マクロ2を教えていただいた方には
   wSheet.UsedRange.FormulaR1C1 = wSheet.UsedRange.Value
   を入れるように指導していただいたのですが
   これを入れるべきでしょうか?入れる位置が不安です。
(2)Callで呼び出したマクロ3でやはり
   H列に編集データが入らず#NAME?のままです。
   マクロ3にはこの記述を利用しています。
Sub マクロ3()
---省略---
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

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

Call マクロ3
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

投稿日時 - 2010-09-28 09:44:46

お礼

以下の修正で
・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

投稿日時 - 2010-09-28 16:52:31

ANo.6

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

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

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

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

すでに私は想定はしていたのですが、それは、「余計なおせっかいも度が過ぎる」と思ったので辞めたのです。エラーチェックする部分を大幅に替えてみました。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

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

投稿日時 - 2010-09-25 11:48:07

補足

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の部分を
動作停止にして上記記述を改造した物を挿入して思ったとおり動作しています。
どうもありがとうございました。

投稿日時 - 2010-09-29 11:40:02

お礼

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

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

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

現在の状態
1.エクセルファイルAに、(5)をセット
2.基幹システムから吐き出したファイルをクリックして
  別のエクセルBが開く
3.開いたエクセルBのデータを全て選択しコピー
4.エクセルAのシート1に貼付
5.エクセルBは閉じる
6.マクロの(5)を起動
7.該当データでない列にカーソルがあるとエラーメッセージが出る
8.該当データのセル、列、にカーソルを置いてもエラーメッセージが出る場合がある

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

です。

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

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

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

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

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

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

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

投稿日時 - 2010-09-28 19:17:13

ANo.5

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

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

投稿日時 - 2010-09-24 20:17:47

補足

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

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

この2を12にすると11行以下は全部このメッセージが出ますか?

また11行以上14行以下の場合は
< 12:15 Then
でいいのでしょうか?

投稿日時 - 2010-09-24 20:37:29

お礼

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

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

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

投稿日時 - 2010-09-24 20:52:23

ANo.3

単独で実行するなら、以下のようになるのですが、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

投稿日時 - 2010-09-23 22:01:32

補足

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

1.A列にデータがあり隣のB列に値を返す場合:Sub TestMacro1()
2.G列にデータがあり隣のH列に値を返す場合:Sub TestMacro2()
3.変換する対象列を選択すると、右隣に列を挿入して、処理: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の同文は削除したら3つとも正常動作になりました。
この解釈は正しいでしょうか?

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

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

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

投稿日時 - 2010-09-24 12:09:48

お礼

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

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

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

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

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

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

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

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

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

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

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

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

投稿日時 - 2010-09-24 18:29:37

ANo.2

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

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

投稿日時 - 2010-09-23 11:29:15

補足

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

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

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

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

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

投稿日時 - 2010-09-23 15:22:47

お礼

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

投稿日時 - 2010-09-24 18:32:20

ANo.1

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

投稿日時 - 2010-09-22 22:47:11

補足

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

いつもはエクセルを開いて
この対象データのあるA列と別データのあるB列に列挿入して、
質問したNO.1~NO.11を手作業で行っています。
毎日行うのでマクロ化を考えました。

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

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


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

2.○○○○○-○○○○○(5ケタ ハイフン 5ケタ) 
    ↓
  ○○○○○-○○○○○(変換しない)

  9○○○○-○○○○○(先頭が3で5ケタ ハイフン 5ケタ)
    ↓
  9○○○○-○○○○○(変換しない)

  9X○○○-○○○○○(先頭が9Xで5ケタ ハイフン 5ケタ)
    ↓
  9X○○○-○○○○○(変換しない)

3.○○○○○○○○-○○○○○(8桁ハイフン5桁) 
   ↓ 
  ○○○-○○○○○-○○○○○(3と4、8と9桁の間に半角ハイフンを入れる)

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

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


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


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

投稿日時 - 2010-09-23 07:56:29

お礼

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

投稿日時 - 2010-09-23 15:13:21

あなたにオススメの質問