共有できない問題も解決!ユーザー設定リストの外部ファイル導入法

vbanarabiyuzaseteyecatch

エクセルVBA 並び替え ユーザー設定リストを外部ファイルから入力して並び替え独自ルールをグループ共有できます。
標準ツールでは無理ですが、VBA並び替えマクロツールでは可能です

こんにちは じゅんぱ店長 (@junpa33) です。

VBAを使って、エクセルのセルデータの並び替えを行うツールを作成しています。

今回のテーマは、並び替えの並び順に「ユーザー設定リスト」を導入する方法についてです。ユーザー設定リストを共有化したい方(グループ)には必読ではないでしょうか。

ご存じのように、ユーザー設定リストを使えば、” 数字の昇順・降順やあいうえお順 ” というエクセル流の並び替え法則に縛られることなく、利用者独自の順位付けで並び替えが行えるというものです。

非常に便利な機能をエクセルには装備されているのですが、「エクセル的にはイレギュラーなルール」となるため、利用法をいくら説明されても使い方については ” 面倒なものは面倒 “ でしかありません。

今、作成しているエクセル並び替えマクロツールにおいても、是非とも「ユーザー設定リスト」を利用できるようにしたいのですが、それを独自マクロで使えるようにするには、マクロ内包型リストでは、その都度、コードの書き換えが必要になるなど、この上なく不便になってしまいます。

そこで結論としては、このエクセル並び替えマクロツールで使用するにあたっては、外部データからのインポート方式を採用することといたしました。

この外部ファイル入力(インポート)方式であれば、現在作成中の同ツールを使っていただければ、楽々共有が可能です。

それでは以下、入力コードの説明をしていきたいと思います。

並び替えマクロ 記事階層

この記事の番号は9番です。

ユーザー設定リストを並び替えで利用するためのルール

ユーザー設定リストを利用するためにはチョッとしたルールがあります。

解説文などを熟読すると、こういう様なルールになっているのが分かります。

Excelオプションで、

「ファイル」→「オプション」から「詳細設定」→「全般」を選び、下のほうにある「ユーザー設定リストの編集」で作成せよ。

とか、エクセルVBAで、

CustomOrder:=”札幌,仙台,東京,金沢,名古屋,大阪,広島,福岡,鹿児島” で記述する。

とか、ひじょうにシンプルな説明で明快に書かれていることが多いです。

確かによくわかります。が、VBAを組み立てて、勉強・スキルを上げたい人にとっては、

” 何を言ってるのか良く解りません ” 状態だと思います。

エクセルVBAでSortオブジェクトで、ユーザー設定リストを使いたい時には、

  •  「CustomOrder」という引数と定数でユーザー設定リストを指定する
  •  ユーザー設定リストの内容は、ダブルクォーテンション(二重引用符)で囲んで、各項目は並び順通りにコンマ(,)で区切る

ということは最低限に分かりましたが、同時に最大限分かったことです。

ユーザー設定リストを外部入力するためのVBAコード

この記事でやりたいことは、

外部にあるユーザー設定リストをSortオブジェクトに導入して、リストに沿った並び替えを実行すること!

です。

VBAコードを組み立てる手順をメモる

考える手順として以下の様に考えます。

  1. 「CustomOrder」の定数部分を変数化する必要がある。その変数の値はダブルクォーテーションで囲まれた文字列のコンマ区切りになっている。
  2. 「コンマで区切られた文字列」と言えば ” CSVファイル ” や ” テキストファイル “?
  3. 外部からの導入(インポート)となれば、ファイルダイアログを利用する
  4. 設置する外部ファイルはフォルダに入れて管理。そのフォルダの置き場所はどこに?
  5. ファイルのデータを取り込んで2次加工しないと使えない?
  6. 「CustomOrder」の引数の定数になる変数に代入すればOK

作業手順の中の注意項目

  •  ① VBAで変数にダブルクォーテーションを入れ込むには、「“””” & 〇〇〇 & “”””」という記述をする
  •  ② 外部ファイルからのデータを導入するのは、Excelそのものが一番親和性が高く、あとCSVファイルやTextファイルとなりますが、一番重視したいのが、⑤の 導入後のデータの2次加工は出来るだけしたくないということです。(エラーの要因を増やさない
  •  ④ 外部ファイルの保存場所は、このエクセル並び替えマクロツール本体と同じディレクトリにフォルダ保管されていることが一番扱いやすい。グループ共有をされる場合は、共有フォルダという選択もあります。

その他にも注意したい部分もふんだんですが、VBAコードの作成上で、これを一番のポイントとしました。

ユーザー設定リストをインポートするVBAコード

ユーザー設定リストをインポートするVBAコード

最終的に作成したユーザー設定リストのインポートVBAは非常にシンプルなものになりました。

インポートするユーザー設定リストのファイルタイプは、Windows標準の「メモ帳(.txt)」にしました。

(ちょっとだけ、メモ帳のデータ記述方法に加工が必要になります。下記参照です)

文字化けしますので文字コードを「UTF-8」に指定しバッファに取ったテキストデータを念のため改行マークをコンマに書き換えしています。

VBA
Function CusOdr() As Variant
    Dim buf As String
    Dim TFile As String
    Dim result As Long
    Dim dialog As FileDialog
        Set dialog = Application.FileDialog(msoFileDialogOpen)
        MsgBox "ユーザー設定リストを選択します。", _
            vbInformation, "ユーザー設定"
        With dialog
            .ButtonName = "開く"
            .InitialFileName = ThisWorkbook.Path & _
                "\ユーザー設定リスト\"
            result = .Show
        End With
        If result = -1 Then
            TFile = dialog.SelectedItems.Item(1)
        Else
            MsgBox "ユーザー設定リスト選択がキャンセルされました。" _
                & vbCrLf & "ユーザー設定は利用できません。"
            Exit Function
        End If
        With CreateObject("ADODB.Stream")
            .Charset = "UTF-8"
            .Open
            .LoadFromFile TFile
            buf = .ReadText
            .Close
            CusOdr = Replace(buf, vbLf, ",")
        End With
End Function

共有フォルダから導入する場合は、

.InitialFileName = ThisWorkbook.Path & “\ユーザー設定リスト\”

の部分を、ドライブからの絶対パスで記述してください。

ユーザー設定リストの作成の注意点と導入実行結果

ユーザー設定リストはWindows標準のメモ帳で作成してください。

フォルダ名・ファイル名などはご自由につけていただいて大丈夫です。

ユーザー設定リスト毎にメモ帳ファイルをいくつでも作成できますし、

並び替えの各列ごとにユーザー設定リストを設定することも出来ます。

並び替え用の項目記述方法は、以下を参考にしてください。

エクセルシートからメモ帳にコピペする場合は、

エクセルのデータが1列のリストで、それをメモ帳の最上段から1段改行したところから貼り付けしてください。

手書きの場合、項目の書き出し部分は「(スペース)+(コンマ)」、

項目の終了部分は「(コンマ)+(スペース)」で

内容を挟み込みにするだけです。

このユーザー設定リストで、並び替えを行なったサンプルがこちらになります。

元のリスト
矢印下001
ユーザー設定リストによる並び替え(昇順)

ユーザー設定リストを使用してももちろん「昇順」「降順」の設定は可能ですので、「美味しい牛乳」を最後にすることも可能です。

ユーザー設定リストの外部入力のまとめ

「無いものは作るしかない」

という精神で、ユーザー設定リストを作成しインポートできるSortオブジェクトのVBAを組み立ててみました。

一番のポイントは、ユーザー設定リストをメモ帳で作成する部分だと思います。

外部入力方式なので、並び替えリストを共有したい場合は

基本、管理フォルダをクライアントに置くか、共有ドライブに置くかだけの差

にしかすぎません。

データ量も低kbで管理できるため、心置きなく量産が可能です。(そこまで並び替えはしないか・・・)

参考になる記事情報かどうかは、あなた次第です。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。