[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ユーザー設定リストで並べ替え後、上書き保存出来ない』（稲葉）
エクセル2007から2013に更新後に同一ブック、コードで起きるようになりました。 範囲を小さくしたり、条件付き書式を消したり、環境依存文字を消したりと 考えられる限り実行しましたが、どうにもならなかったので、 同様の現象に心当たりが有る方、直し方教えてください。
データはテストデータジェネレータで作成しました。
再現手順
「名簿」シート
B3セルにフォームコントロールで、「並べ替え」マクロを設定
|[A]|[B]|[C] |[D] |[E] |[F] |[G] |[H]
[1] | | | | | | | |
[2] | | | | | | | |
[3] | | | | | | | |
[4] | |No |社名 |担当 |性別|生年月日|契約日 |満了日
[5] | | |ABC綜合|藤沢 勤 |男 |90.06.02|15.08.21|15.11.20
[6] | | 1|ABC綜合|石橋 伸江 |女 |70.04.11|15.07.29|
[7] | | 2|ABC綜合|石橋 朋 |女 |88.04.21|15.08.21|
[8] | | 3|ABC綜合|長田 僧三郎|男 |70.08.22|15.08.21|
[9] | | 4|ABC綜合|高田 文吾 |男 |76.12.21|15.08.21|
[10]| | 5|XYZ戦力|志村 敬 |男 |86.05.16|15.08.21|
[11]| | |XYZ戦力|菅 幸信 |男 |87.05.29|15.08.21|15.11.20
[12]| | |XYZ戦力|三谷 勝英 |男 |90.09.19|15.08.21|15.11.20
[13]| | 6|XYZ戦力|菅野 純隆 |男 |87.08.10|15.08.21|
[14]| | 7|DEF商会|宮崎 秀俊 |男 |93.05.26|14.04.09|
[15]| | |XYZ戦力|丹羽 茂信 |男 |84.05.04|15.08.21|15.11.20
[16]| | |DEF商会|島崎 健生 |男 |71.04.22|15.11.21|16.02.20
[17]| | 8|DEF商会|宮川 三重子|女 |74.04.02|15.11.21|
[18]| | 9|DEF商会|西尾 邦江 |女 |84.07.02|15.11.21|
[19]| | 10|DEF商会|飯島 克 |男 |93.09.29|15.05.21|
[20]| | 11|DEF商会|西野 威佐夫|男 |93.09.20|15.11.21|
[21]| | 12|DEF商会|古谷 広重 |男 |76.06.28|15.11.21|
[22]| | 13|DEF商会|山田 永基 |男 |86.08.01|15.09.17|
[23]| | |DEF商会|黒田 令 |女 |76.07.08|15.11.21|16.02.20
[24]| | 14|XYZ戦力|新田 邦美 |女 |86.11.14|15.12.24|
[25]| | | | | | | |
「並べ替えリスト」シート
|[A]
[1]|会社一覧
[2]|ABC綜合
[3]|XYZ戦力
[4]|DEF商会
[5]|
標準モジュール（Module1）
Option Explicit
Sub 並べ替え()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("名簿")
With Sheets("並べ替えリスト")
Application.AddCustomList ListArray:=.Range("A1:A" & .Range("A100").End(xlUp).Row)
End With
With WS.Sort
With .SortFields
.Clear
.Add Key:=WS.Range("C5:C501") _
, SortOn:=xlSortOnValues _
, Order:=xlAscending _
, CustomOrder:=Application.CustomListCount _
, DataOption:=xlSortNormal
End With
.SetRange WS.Range("B4:AQ501") '//範囲が拡張したとき用にAQまで。
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.DeleteCustomList (Application.CustomListCount)
End Sub
上の状態で並べ替えマクロを実行し、上書き保存しようとするとエラーが出て保存できません。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
「Microsoft Excelは動作を停止しました」現象は再現。 Windows7 Pro 64bit Excel2013 とりあえず試したこと ・Excel97-2003 ブック(*.xls)形式にしたら動作停止は発生しなくなった ・Application.DeleteCustomList Application.CustomListCountを削って動かす １．並べ替え後、保存しても動作停止は【発生しない】 ２．「１」で保存後、DeleteCustomListを動かして保存すると動作停止が【発生する】 ３．「１」で保存後、Excelを起動しなおしてからDeleteCustomListを動かして保存すると動作停止は【発生しない】 スマートな解決方法は見つけ切れなかった。 なんか発生パターンがコレに似ている気もする [[20140404152537]] 『UFClr1228の不具合』（matsudesk） （ご近所PG） 2015/12/29(火) 15:24
ご近所PGさん 確認ありがとうございます。 頂いたヒントを元に、検索したところ、マイクロソフトコミュニティで以下のスレッドを見つけ、 下記コードの通り修正したところ、現象を回避出来ました。
==引用ここから ＞ ルビちゃん 返信日 3月 7, 2014投稿履歴を表示する ＞ユーザー設定リストでソート後 ＞ソートを行ったシート.Sort.SortFields.Clear ＞（中略） ＞恐らく、Excel2007以降ソートの仕様が変わり ＞ソートの情報をシートなりセルなりに保存できるようになり ＞そのシートのソート情報には【ユーザー設定リストの何番目】という情報があるのに ＞その【ユーザー設定リストの何番目】は削除されたため ＞Excelがどうしたらよいかわからなくなり→何も言わずに異常終了 ==引用ここまで
Sub 並べ替え()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("名簿")
'★DeleteCustomListを消しただけだと、追加するときにエラーになる
With Sheets("並べ替えリスト")
Application.AddCustomList ListArray:=.Range("A1:A" & .Range("A100").End(xlUp).Row)
End With
With WS.Sort
(省略）
.Apply
Application.DeleteCustomList (Application.CustomListCount) '★記述場所の変更
.SortFields.Clear '★追加
End With
End Sub
2007からSort変わった癖に、2013でエラーってどういうことだと言いたいっす・・・
お騒がせしました。解決です。 （稲葉） 2015/12/29(火) 15:51
もう、ご覧にならないかもしれませんが。
>2007からSort変わった癖に、2013でエラーってどういうことだと言いたいっす・・・
お気持ちはわかります。ただ、稲葉さんが見つけられたトピの記載にもありますように、 もともと、手作業で並び替えを行っても、それが、直近、どいうった要件で並び替えられたのかという情報が ダイアログに書きこまれていますよね。つまり、エクセルは、保存時に、そういった情報も書きこむんだろうと想像します。 で、見つからないので、エラー。まぁ、なんらかのメッセージを出し、情報は書きこみません なんて知らせてくれればありがたいと思いますね。
ところで、2007 から、登録済みのユーザー設定リストを使う以外に、自分で、リスト配列を指定できるという機能がありますね。 CustomOrder:="hoge1,hoge2,hoge3,・・・・" と指定可能ですね。 つまり、AddCustomListへの Add や Delete が不要になります。 この文字列を並び替えリストデータから生成して適用してやることもできます。
以下、ご参考まで。
Sub 並べ替え2()
Dim w As Variant
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("名簿")
With Sheets("並べ替えリスト")
w = WorksheetFunction.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
End With
With WS.Sort
With .SortFields
.Clear
.Add Key:=WS.Range("C5:C501") _
, SortOn:=xlSortOnValues _
, Order:=xlAscending _
, CustomOrder:=CStr(Join(w, ",")) _
, DataOption:=xlSortNormal
End With
.SetRange WS.Range("B4:AQ501") '//範囲が拡張したとき用にAQまで。
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
（β） 2015/12/29(火) 16:20
↑ これはユーザー設定リストへの登録でも同じことが言えますが、配列文字列が 255 を超えると不具合が発生しますので 留意が必要ですね。
（β） 2015/12/29(火) 16:23
βさん いつもありがとうございます！ ＞ ところで、2007 から、登録済みのユーザー設定リストを使う以外に、自分で、リスト配列を指定できるという機能がありますね。 全く、忘れておりました。 早速そのように変更致しました。 1日悩んでいた自分が恥ずかしい限りです。
ありがとうございました！
（稲葉） 2015/12/29(火) 16:58
すみません、ついでにもう一つ教えてください
変更点の抜粋です。
Dim w As String 'Variant
w = CStr(Join(WorksheetFunction.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)))
, CustomOrder:=w _
これだとエラー13になって （同） , CustomOrder:=cstr(w) _
だと通るってどういう理屈ですか？ VarTypeで見ると、どちらも8なのですが・・
（稲葉） 2015/12/29(火) 17:13
以前、最初に、この方式でコードを書いたときに私も悩みました。 で、いろいろ試行錯誤しながら、Joinしたものを文字列変換したものを与えるとＯＫになるというところに たどり着きました。
理屈はわからないのですが、純粋な文字列型と内部形式文字列型のVariant型の違い・・・なんでしょうかね？
（β） 2015/12/29(火) 18:06
遅くなりました。 ありがとうございます。 バージョンが変わるたびに四苦八苦です・・・。
今年もお世話になりました。 来年もよろしくお願いいたします
よいお年を！ （稲葉） 2015/12/29(火) 20:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.