えくせるちゅんちゅん

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

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

今回はExcelの「重複の削除」機能をVBAで実行するための「RemoveDuplicates」メソッドの使い方を調べてみました。


きっかけ

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

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

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

マクロ実行前(選択セル範囲)

f:id:Kotori-ChunChun:20190714105809p:plain

マクロ実行後

f:id:Kotori-ChunChun:20190714105812p:plain


調査

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


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

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

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


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

f:id:Kotori-ChunChun:20190714041717p:plain

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


そうだ、カッコを付けて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 CVarGoogle検索♪♪♪

約 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(変数)
  • 関数の戻り値
  • (変数)


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

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


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

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


※後日、このテクニックが求められる他の事例が見つかった。

やはり、潜在的


実務に話を戻す

ここまで列番号の配列作成には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の重複の削除にはバグがある」という話があったが、2019年前期にかけて「いつのまにか修正されている。」という話を聞いた。

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

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

※「異なる型の混在するデータに対して実行した場合」におかしいとかなんとか聞いている。


まとめ

なぜCVar()を使えば良いのかさっぱりわからないが、RemoveDuplicatesで変数を指定する方法は分かった。

重複の削除はDictionaryを使ったほうが自由度が高くて使いやすいが、ソースコードが冗長になるし一旦メモリに全て取り込まなければならないという欠点がある。

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

あまり使う場面は無さそうだが、お手軽に重複を削除する方法としてうまく活用していきたい。

以上


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

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

プライバシーポリシー