Q．商品リストなどをExcelで扱う際に、商品の種類だけを知りたいなどで、2行以上ある同じ商品のリストを1行にまとめることはVBAで可能でしょうか。

A．VBAを使えばとても簡単に実現可能です。

重複行の削除は、VBAのプログラムでひとつひとつ地道にリストを辿れば可能ですが、実はVBAに便利なコマンドがあります。
今回はそれを紹介します。

まずはシート上に以下のようなリストを作成して下さい。これは1日の商品の売り上げリストをイメージしました。

(A列) (B列)
番号 販売商品
1 A5ノート
2 鉛筆10本入り
3 A4ノート
4 A4ノート
5 クリアファイル
6 消えるボールペン
7 クリアファイル
8 A4ノート

さて、VBAの画面を開き、下記のコードを入力します。

Sub 重複データ削除
Range(“A1″).CurrentRegion.RemoveDuplicates Columns:=2, Header:=xlYes
End Sub

たったこれだけです。RangeオブジェクトでA1セルを含む表を指定し、そのRemoveDuplicatesメソッドで重複を除去します。
重複を判定する列は、2列目すなわちB列(販売商品)ですね。最後の Header:=xlYes は、1行目がヘッダー(「番号」「販売商品」)なので、データではないことを示しています。

プログラムが完成したら、「デバッグ」メニューの「VBAProjectのコンパイル」をして、エラーが出ないことを確認します。
シートの画面に戻り、「開発」リボン※の「マクロ」で、上記のプログラムの名称「重複データ削除」を実行すると、シート上にテキストファイルの内容が出力されます。

いろんな場面で使用できそうなコマンドですので是非覚えてみて下さい。

※「開発」リボンが見えない場合は、下記の記事を参考にしてください。
http://www.office-kaiketsu.com/excel%ef%bc%88%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%ef%bc%89%e3%83%9e%e3%82%af%e3%83%ad%e3%83%bbvba/%e3%83%9e%e3%82%af%e3%83%ad%e9%96%8b%e7%99%ba%e3%82%bf%e3%83%96%e8%a1%a8%e7%a4%ba%ef%bd%9c%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab2007/

Q．マクロを使用して集計を行いたいのですが、Excelシート上では入力できる合計「=SUM(･･･)」は、VBAでは見当たらず、どのように行えば良いでしょうか。

A．WorksheetFunctionクラスのSUMメソッドが使えます。

WorkSheetFunctionクラスには、その名の通り、「ワークシートの関数」がいろいろ用意されています。
そのうちのSUMメソッドを使用して、下記のように記述します。

Sub ワークシートのSUMファンクション
Dim sumValue as integer
sumValue = WorksheetFunction.Sum(Range(“A1:A12″))
End Sub

Sumメソッドの引数に、集計したいセルを表示すれば良いのです。

WorkSheetFunctionのメソッドは沢山あります。Visual Basic Editorで「WorksheetFunction.」とドットを打てばメソッドの一覧が表示されますので、他にも使える関数がないか確認してみて下さいね。

Q．最近VBAを使用して、シート上の数値などを集計したりしているのですが、ブックを閉じる際にブックを保存するか確認する表示がされます。保存する必要はないので、これを表示させないようにすることは可能でしょうか。

A．いくつかの方法がありますが、今回はそのうちのひとつをご紹介します。

シート上にボタンを配置し、「閉じる」などと表示し、このボタンを押してブックを閉じることとします。
さて、この配置したボタン(Button1とします)のクリックイベントを記述するため、VBAの画面を開き、下記のコードを入力します。

Private Sub CommandButton1_Click()
ActiveWorkbook.Saved=True ‘★
ActiveWorkbook.Close
End Sub

まずこのブックを保存して下さい。(保存せずに閉じるマクロのため)
そして、シート上のButton1をクリックすると、何もメッセージが出力されず、閉じられたと思います。

これはどういうことかというと、Excelのワークブックは、SavedプロパティがFalseのとき、まだ保存がされていない、という判断がされ、
その判断に基づいて、保存を促すメッセージが表示されます。
従って、SavedプロパティをTrueに設定してあげれば(コード中★の部分)、実際に保存されていなくても、保存済のように振る舞うことが可能となるのです。

いかがでしょうか。この他の方法は、またの機会にでも投稿することにしますね。

以前投稿した「VBAマクロ開発を効率的に行う方法(イミディエイトウィンドウ編）」の続編です。

※以前の記事はこちら
http://www.office-kaiketsu.com/%e3%81%9d%e3%81%ae%e4%bb%96/vba%e3%83%9e%e3%82%af%e3%83%ad%e9%96%8b%e7%99%ba%e3%82%92%e5%8a%b9%e7%8e%87%e7%9a%84%e3%81%ab%e8%a1%8c%e3%81%86%e6%96%b9%e6%b3%95%e3%82%a4%e3%83%9f%e3%83%87%e3%82%a3%e3%82%a8%e3%82%a4%e3%83%88/

以前の投稿では、イミディエイトウィンドウに直接「? Cells(1,1).Value」等をを入力し、変数の値やシートの値を表示させることを学びましたが、このイミディエイトウィンドウへの表示は、実はプログラムから出力することが可能となっています。どのようなことなのか、以下のプログラムを見てみてください。

For i = 1 To 100
If Left(Cells(i, 1).Value, 4) = “株式会社” Or Left(Cells(i, 1).Value, 4) = “合同会社” Or Left(Cells(i, 1).Value, 4) = “有限会社” Then
Cells(i, 2).Value = Cells(i, 1).Value & “御中”
Else
Cells(i, 2).Value = Cells(i, 1).Value & “様”
End If
Next

このプログラムでは、A列の名前の先頭が株式会社、合同会社、有限会社であった場合、B列にA列の内容に”御中”を追加し、そうでない場合は”様”を追加します。
さて、ここでシートのデータを見るとどうも会社のデータのようだが、B列で”様”が追加されている行があったとしましょう。
このような場合は、for文の中のELSEに入った場合のA列の内容がどのようになっているかを見たくなるでしょう。ただしfor文のループを100行デバッグで見ていくのは大変です。そのような場合こそイミディエイト・ウィンドウが活用できます。下記のようにプログラムを追加します。(★の部分)

For i = 1 To 100
If Left(Cells(i, 1).Value, 4) = “株式会社” Or Left(Cells(i, 1).Value, 4) = “合同会社” Or Left(Cells(i, 1).Value, 4) = “有限会社” Then
Cells(i, 2).Value = Cells(i, 1).Value & “御中”
Else
Debug.Print i & “:” & Left(Cells(i, 1).Value, 4) ‘★
Cells(i, 2).Value = Cells(i, 1).Value & “様”
End If
Next

ELSEに入る場合に判定されているのは、Left(Cells(i, 1).Value, 4)の部分ですから、これがどのようになっているかを確かめるのです。
これでマクロを実行させると、イミディエイトウィンドウには下記のように出力されます。(※テストデータはご自身でA列に作成下さい。下記は例となります。)

10:鈴木一郎
11:田中次郎
23:□有限会 ←(注)□は空白を示しています
29:山田花子
・・・

これで23行目に、頭に余分な空白の入った”有限会社”があったためとすぐにわかります。
このように、プログラム内で「Debug.Print」を実行することで、効率的にデバッグ(バグの修正)を行うことができるようになります。

以前投稿した「テキストファイルの内容をエクセルシート上に読み込む」の続編です。

※以前の記事はこちら
http://www.office-kaiketsu.com/excel%ef%bc%88%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%ef%bc%89/%e3%83%86%e3%82%ad%e3%82%b9%e3%83%88%e3%83%95%e3%82%a1%e3%82%a4%e3%83%ab%e3%81%ae%e5%86%85%e5%ae%b9%e3%82%92%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%e3%82%b7%e3%83%bc%e3%83%88%e4%b8%8a%e3%81%ab%e8%aa%ad/

さて今回は、逆にエクセルシート上の内容をテキストファイルに書き込んでみましょう。

書き込み先のファイルは前回の読み込み元と同じです。c:\tmp フォルダに 電話帳.txt として作成します。

フォルダがなければ作成し直してください。ファイルは今回既にあってもなくてもかまいません。(あったら上書きされます。なかったら作成されます。)

ExcelからVisual Basic Editorを開き、下記のプログラムを入力します。
前回と同じく、プログラムで FileSystemObject というオブジェクトを使うために、「ツール」メニューの「参照設定」で「Microsoft Scripting Runtime」が参照設定されているのを確認しておきましょう。

Sub WriteTelephoneData()

Dim fso As New FileSystemObject
Dim stream As TextStream
Dim lineData(3) As String
Dim row As Integer: row = 1

Set stream = fso.CreateTextFile(“c:\tmp\電話帳.txt”)

With stream
Do Until Cells(row, 1).Value = “” ‘★
lineData(0) = Cells(row, 1).Value
lineData(1) = Cells(row, 2).Value
lineData(2) = Cells(row, 3).Value
.WriteLine (lineData(0) & “,” & lineData(1) & “,” & lineData(2))
row = row + 1
Loop
.Close
End With

End Sub

プログラムが完成したら、「デバッグ」メニューの「VBAProjectのコンパイル」をして、エラーが出ないことを確認します。
シートの画面に戻り、下記のようにシート上に入力します。左上はA1セルになるようにします。

入力できたら「開発」リボン※の「マクロ」で、上記のプログラムの名称「WriteTelephoneData」を実行すると、シート上の内容でテキストファイルが作成(上書き)されます。

プログラムのポイントは、★ の行で、シート上を上から下に辿っていき、データが空白になったら終了するところです。これがないと、延々と空のデータが出力されてしまうのがわかりますか？

こちらもとても簡単な作りですが、慣れたExcelでテキストファイルを簡単に作れますから、いろいろ応用ができそうですね。

※「開発」リボンが見えない場合は、下記の記事を参考にしてください。

http://www.office-kaiketsu.com/excel%ef%bc%88%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%ef%bc%89%e3%83%9e%e3%82%af%e3%83%ad%e3%83%bbvba/%e3%83%9e%e3%82%af%e3%83%ad%e9%96%8b%e7%99%ba%e3%82%bf%e3%83%96%e8%a1%a8%e7%a4%ba%ef%bd%9c%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab2007/