えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU
トップ > VBA実験 > ExcelVBAのRemoveDuplicatesの使い方を調べてみた

ExcelVBAのRemoveDuplicatesの使い方を調べてみた

VBA実験 VBA Excel

今回はExcelの「重複の削除」機能をVBAで実行するための「RemoveDuplicates」で、引数に配列変数を指定したい場合の使い方を調べてみました。

きっかけ

先日、Twitterにて以下のような内容の疑問がツイートされていたが、なぜエラーになるのか原因が分からなかった。

'実行時エラー5 「プロシージャの呼び出し、または引数が不正です。」が発生する。
Sub NG1()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=ary, Header:=xlNo
End Sub

マクロ実行前（選択セル範囲）


解決した方法

結論だけ先に書いておくと、実引数の動的配列変数をCVarで囲えば良い。

Selection.RemoveDuplicates Columns:=CVar(ary), Header:=xlNo


RemoveDuplicatesとは

本題に入る前に、RemoveDuplicatesの一般的な使い方をおさらいしておこう。

これはExcelの標準機能の中にある「データ」＞「データツール」＞「重複の削除」を実行するメソッドである。

先のプログラムは、選択したセル範囲に対して「重複の削除」を実行し、1列目、2列目、4列目のすべてが同一のレコードを削除するためのものである。

通常はこのように記載すれば動作する。

Sub OK0()
    Selection.RemoveDuplicates Columns:=Array(1, 2, 4), Header:=xlNo
End Sub

マクロ実行後

要するに

  • Selection 選択範囲であるRange("B2:G6")に相当
  • Columns:=Array(1, 2, 4) 重複チェックを行いたい列インデックスの配列（インデックスは指定セル範囲内における列を1から数えた時の数値）つまりB列、C列、E列に相当
  • Header:=xlNo 先頭の行はヘッダーと見なさず、指定セル範囲全てを編集対象とする

という事である。


調査


思いついたアイディアを試してみる

コードを見る限りでは、どちらもArray()を使用したVariantデータを使用しているため、何がダメなのかさっぱりわからない。

唯一の違いは「一旦変数に格納している」という事である。


両者に違いがあるとは考えにくいが、嫌な予感を潰すべくウォッチで内容を調べてみた。

やはり、全く同じ結果であった。


そうだ、実引数にカッコを付けて配列を明示（ary()）して渡してみよう。

Sub NG2()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=ary(), Header:=xlNo
End Sub

'実行時エラー5 「プロシージャの呼び出し、または引数が不正です。」

撃沈


早くも、打つ手が無くなったので・・・


ヒントがないかとググってみた

悔しいが、Googleさんの出番である。

そして、あっさりヒントが見つかった。見つけてしまったのだ・・・。

以下、教えて！goo - VBA　RemoveDuplicatesが動かない から抜粋

Range(.Cells(1, Colref), .Cells(LastRow, Colref)).RemoveDuplicates Columns:=CVar(Colref), Header:=xlNo

CVar()・・・だと・・・？

以下、docs.microsoft.com - データ型変換関数 から抜粋

データ型変換関数

各関数では、式が特定のデータ型に強制的に変換されます。

_

構文

CVar(expression)

_

戻り値の型

CVar Variant 数値の場合は Double と同じ範囲です。 数値以外の場合は String と同じ範囲です。

_

CVar 関数の例

この例では、CVar 関数を使用して、式を Variant に変換します。

Dim MyInt, MyVar

MyInt = 4534 ' MyInt is an Integer.

MyVar = CVar(MyInt & 000) ' MyVar contains the string

' 4534000.

どんな思考をしたらVariant型変数に対して、Variantへ変換するという発想になるんだ？

質問者は天才か？

きっと、どこかに情報があるに違いない！！！

というわけで、 RemoveDuplicates CVar でGoogle検索♪♪♪

約 92 件 （0.26 秒）

おいおいおい、ネット上にソースが無いんじゃないか？

もしかして、どこぞの組織で語り継がれる裏情報なのだろうか？

英語のページはじっくり読んでいないので、もしかしたらこの中にあるのかもしれないが。


ネット上の情報を元に修正してみる

と、まあ納得はいかないが、CVar()を使って試してみた。

'正常に動作する
Sub OK2()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=CVar(ary), Header:=xlNo
End Sub

そして、本当に動いたのである。


因みに、CVar()を代入部分に記述するのはダメだった。

'エラー5 「プロシージャの呼び出し、または引数が不正です。」が発生する。
Sub NG3()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    ary = CVar(ary)
    Selection.RemoveDuplicates Columns:=ary, Header:=xlNo
End Sub

要するにRemoveDuplicatesの実引数が変数となる場合にはCVar()が必須ということのようだ。（訂正((後に()だけで良い事が判明した)

こんな重要な話ならネット上に情報が転がっていてもおかしくないのだが、やっぱり見つけられないのは何故なのだろうか。


さらなる問題点の発見

ネットの海を彷徨っていたところ、もう一つ問題があることがわかった。

なんでも、「Option Baseを1に変更するとRemoveDuplicatesでエラーが出る」らしい。

矢塚の備忘録 - Optional baseとRemoveDuplicatesの不仲


VBAではモジュールの先頭にてOption Base 1を記載することで、配列の要素番号の開始値を0から1に変更することが出来る。※一部のステートメントに限る

本件のArray()関数は、Option Baseの影響を受ける代表的な関数である。

例えば、Array(1, 2, 4)の結果は以下のようになる。


Option Base 0または省略時

式 値 型
Array(1, 2, 4) Variant/Variant(0 to 2)
Array(1, 2, 4)(0) 1 Variant/Integer
Array(1, 2, 4)(1) 2 Variant/Integer
Array(1, 2, 4)(2) 4 Variant/Integer

Option Base 1

式 値 型
Array(1, 2, 4) Variant/Variant(1 to 3)
Array(1, 2, 4)(1) 1 Variant/Integer
Array(1, 2, 4)(2) 2 Variant/Integer
Array(1, 2, 4)(3) 4 Variant/Integer


この状態で、以下のプログラム（当初）を実行するとエラーが出る。

'実行時エラー9 「インデックスが有効範囲にありません。」が発生する。
Sub OK1_OptionBase1Mod_NG()
    Selection.RemoveDuplicates Columns:=Array(1, 2, 4), Header:=xlNo
End Sub


当然、CVar()を使った修正版も同様である。

'実行時エラー9 「インデックスが有効範囲にありません。」が発生する。
Sub OK2_OptionBase1Mod_NG()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=CVar(ary), Header:=xlNo
End Sub


対処法としては、ReDim Preserve ～を使って要素番号を0開始にシフトさせる方法が考えられる。

'正常に動作する
Sub OK2_OptionBase1Mod_OK()
    Dim ary As Variant
    ary = Array(1, 2, 4)
    ReDim Preserve ary(0 To UBound(ary) - 1)
    Selection.RemoveDuplicates Columns:=CVar(ary), Header:=xlNo
End Sub


別解として、Array()でOption Base 1の影響を回避するためによく使われるVBA.Array()を使う方法もある。

この方法は先の記事のリンクにもあった answers.microsoft.comでも紹介されている。

'正常に動作する
Sub OK3()
    Selection.RemoveDuplicates Columns:=VBA.Array(1, 2, 4), Header:=xlNo
End Sub

'正常に動作する
Sub OK4()
    Dim ary As Variant
    ary = VBA.Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=CVar(ary), Header:=xlNo
End Sub


2019/7/14 11:00 追記

本記事を読んだエクセルの神髄の中の人から、別解を紹介していただいた。

配列の生成を関数化した場合にはCVar()が必要なくなるようだ。

'正常に動作する
Sub OK5()
    Selection.RemoveDuplicates Columns:=aryFunc, Header:=xlNo
End Sub

Function aryFunc() As Variant
    aryFunc = VBA.Array(1, 2, 4)
End Function


2019/7/14 11:30 追記

両者の共通点は、配列変数のメモリのコピーを生成してから渡していることだ。

従って、CVar()を使わなくとも、ただの()でも良いのではないかと思い試した結果、その考えは正解であった。

'正常に動作する
Sub OK6()
    Dim ary As Variant
    ary = VBA.Array(1, 2, 4)
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub


ここまでの結果を整理すると、次のようになる。

NG　「プロシージャの呼び出し、または引数が不正です。」

  • 変数のみ

OK

  • Array()
  • CVar(変数)
  • 関数の戻り値
  • (変数)


私の仮説だが、以下の２つを予想した。

  • RemoveDuplicatesは変数=外部から変更される恐れのあるメモリの指定を良しとしていない。
  • RemoveDuplicatesの行で確保したメモリでなければならない。


VBAでは配列変数を実引数に置くと必然的に参照渡しになってしまうが、OKのような事例では呼び出し元に変数が無いため値渡しと同じような状態になる。RemoveDuplicatesはソレを求めているのかもしれない。

他のExcelに依存した関数でも起きてもおかしくないので、今後は同じエラーが出たときには注意する必要がありそうだ。


当初のTwitterでの質問への回答

ここまで列番号の配列作成にはArray()やVBA.Array()を使うことを前提に話をしたが、実務では動的に作ることもあると思う。

当初のTwitterでの問いかけでも、動的に配列を作成してエラーが出たためArray()で検証していた。

以上を踏まえて「選択範囲のすべての列が重複するレコードを削除する」プログラムを作成すると以下のようになる。

'選択範囲のすべての列で重複するレコードを削除する
Sub OK0()
    '有効データ領域に拡大する場合は、.CurrentRegionを追記
    With ActiveWindow.RangeSelection
        
        Dim i As Long
        Dim arr As Variant
        
        ReDim arr(0 To .Columns.Count - 1)
        For i = LBound(arr) To UBound(arr)
            arr(i) = i + 1
        Next

       'ヘッダを考慮する場合はxlYesに変更
        .RemoveDuplicates Columns:=CVar(arr), Header:=xlNo
        
    End With
End Sub


補足

Excelの重複の削除にはバグがある

一時期「Excelの重複の削除にはバグがある」という話があったが、2019年前期には「いつのまにか修正されている。」という話を聞いた。

もともとのバグの内容と再現方法、そしてバグと考えられる挙動すべてが修正されたのか、正確には把握できていない。

よって、「重複の削除」を使うのは注意したほうが良いことをここに書き残しておく。

※「列に異なる型のデータがある場合」に発生していたと聞き及んでいる。


Dictionaryで重複を削除する方法もある

さて重複の削除はDictionaryを使って行う方法もある。

Dictionaryなら誤動作の心配は無いし自由度が高くて応用が効くが、ソースコードが冗長になる傾向がある。

特に複数列に対応しようと思うと、事前にデータを連結したりと準備に手間がかかる。

他にも一旦メモリに全て取り込まなければならないという欠点がある。

一方で、RemoveDuplicatesならRangeに対して直接実行出来るため、ソースコードはシンプルになる。


UNIQUE関数を使う方法もある

※Office365アップデートに基づいて 2020/2/3追記

昨今（2020年1月）Excelに新たにUNIQUE関数が搭載された。※Office365限定

これと同時に、WorksheetFunction.Unique()も開放されており、こちらも複数列を処理できるので一考の余地があると思う。

ただし、Excel2019までは対応しておらず互換性問題があるので、もう数年は様子見したほうが良い気がする。


Split関数で作成した配列が使えない

※コメント欄の質問に基づいて2020/4/25追記

「Split関数で作成した配列がCVar()を使用しても実行時エラー458になる」とのコメントがあった。


再現ソースコード

Sub NG9_1()
    Dim ary As Variant
    ary = Split("1,2", ",")
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub

実行結果

実行時エラー '458'
Visual Basic でサポートされていないオートメーションが変数で使用されています。


Splitで生成されるのは{"1","2"}というデータに過ぎないはずだ。

同じように{"1","2"}をArrayで作成する次のようなソースコードでは正常に動作する。

Sub OK9_2()
    Dim ary As Variant
    ary = VBA.Array("1", "2")
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub


では、一体両者の何が違うのか。それは「配列の型」である。

式 値 Splitで生成した型 Arrayで生成した型
ary Variant/String(0 to 1) Variant/Variant(0 to 1)
ary(0) "1" String Variant/String
ary(1) "2" String Variant/String

よ～～～く見ると、「配列の型」が違うのだ。

そこでSplitは消して、全通り試してみた。

'実行時エラー '458'
'Visual Basic でサポートされていないオートメーションが変数で使用されています。
Sub NG9_3()
    Dim ary(0 To 1) As String
    ary(0) = "1": ary(1) = "2"
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub

'実行時エラー '13'
'型が一致しません。
Sub NG9_4()
    Dim ary(0 To 1) As Integer
    ary(0) = 1: ary(1) = 2
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub

'正常終了
Sub OK9_5()
    Dim ary(0 To 1) As Variant
    ary(0) = "1": ary(1) = "2"
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub

'正常終了
Sub OK9_6()
    Dim ary(0 To 1) As Variant
    ary(0) = 1: ary(1) = 2
    Selection.RemoveDuplicates Columns:=(ary), Header:=xlNo
End Sub

結論としては、「配列の型」は必ずVariantでなければならない。


おわりに

正確な理由は分からないが、RemoveDuplicatesのColumns引数に変数を指定する方法は分かった。

色々手法があるが、一番簡単なのは(arr)だ。

でも開発者の意図が伝わりやすいのはCVar(arr)だと思う。

その辺りは好みなので好きにすれば良いと思う。

ただ、意図が読みづらい挙動なので、将来消されるのを防ぐためにもコメントを入れておくほうが良いかもしれない。

つまり、この記事のURLを残しておけば良のだ！（冗談です。リンクフリーですのでご自由にどうぞ）


※後日、このテクニックが役に立つ他の事例が見つかった。思ったより重要な手法のようだ。

VBAのInStr関数の不思議な挙動について調べてみた - えくせるちゅんちゅん

www.excel-chunchun.com

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

プライバシーポリシー