Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2010)
CustomOrder実行後、Excel操作で保存エラーが出る
投稿日時: 18/03/29 11:34:30
投稿者: Armal

 VBAにてユーザ設定リストに追加した順に並び替えしました。 並び替え自体は成功するのですが、VBAから脱してExcelのツールバーから保存しようとすると「Microsoft Excelにより回復しています」「Microsoft Excelは動作を停止しました」というメッセージが順次表示され、変更情報が保存されません。 どなたか対応方法がわかれば教えて下さい。
 
 当方の実施概要は以下です。
1.シート上にボタンを配置し、そのイベントでUserFormを表示
2.UserForm.Initilizeで別シートにある都道府県を配列変数(下例のvstr)に読み込みユーザ設定リストに追加
後、Sort実行時およびUserFrom Unload時のために登録番号をGlobal変数(下例のsortNo1)として保存

Private Sub UserForm_Initialize()
    Dim ws_obj As Object, ws_now As Object
    Dim icnt As Integer, vcnt
    Dim vstr()
    Set ws_now = ActiveSheet
    Set ws_obj = Worksheets("Master")
    ws_obj.Activate
    With ws_obj
        .Range("都道府県").Select
        ReDim vstr(Selection.Count - 1)
        icnt = 0
        For Each vcnt In Selection
            vstr(icnt) = vcnt.Value
            icnt = icnt + 1
        Next vcnt
        Application.AddCustomList listarray:=vstr    '並び替え用ユーザ設定登録
        sortNo1 = Application.GetCustomListNum(vstr) '上記アクセス番号を保存
        '
        .Range("八地方区分").Select
        Erase vstr
        ReDim vstr(Selection.Count - 1)
        icnt = 0
        For Each vcnt In Selection
            vstr(icnt) = vcnt.Value
            icnt = icnt + 1
        Next vcnt
        Application.AddCustomList listarray:=vstr    '並び替え用ユーザ設定登録
        sortNo2 = Application.GetCustomListNum(vstr) '上記アクセス番号を保存
    End With
    ws_now.Activate
End Sub

 
5.UserForm上に配したコマンドボタンにて並び替えを実施
Private Sub CommandButton1_Click()
    Dim ws_obj As Object
    Set ws_obj = Worksheets("fruits")
    With ws_obj
        .Activate
        .Range("Table").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("E3") _
            , CustomOrder:=sortNo1
'            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("D3") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'            , CustomOrder:=sortNo2
    End With
    
    With ws_obj.Sort
        .SetRange Range("Table")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 
3.処理終了後フォームを閉じる時にユーザ登録リストから追加したリストを削除
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.DeleteCustomList (sortNo2)
    Application.DeleteCustomList (sortNo1)
End Sub

 
CustomOrderではなくOrderで並び替えると正常に保存できました。
なお、並び替えるシートの情報は以下のようなものです。
A	B		C		D		E		F	G
No	品名		英名		主産地		産地地区	開始月	終了月
1	あんず 		apricot		長野県		中部		6	7
2	いちご 		strawberry	栃木県		関東		12	4
3	いちじく 	fig		愛知県		中部		7	10
4	梅 		plum		和歌山県	近畿		5	6
5	オリーブ 	olive		香川県		四国		8	9
6	オレンジ 	orange		広島県		中国		1	7
7	柿 		persimmon	和歌山県	近畿		10	11
10	すいか 		water melon	熊本県		九州		5	8
11	梨 		pear		千葉県		関東		8	10
12	パイナップル 	pine apple	沖縄県		九州		7	9
14	ぶどう 		grape		山梨県		中部		8	10
15	プルーン 	prune		長野県		中部		6	8
16	みかん 		mandarin	和歌山県	近畿		12	2
17	メロン 		melon		茨城県		関東		4	9
18	桃 		peach		山梨県		中部		7	8
19	ゆず 		yuzu		愛媛県		四国		11	2
20	洋梨 		pear		山形県		東北		8	10
21	ライチ 		lychee		鹿児島県	九州		6	8
22	りんご 		apple		青森県		東北		10	12

 

回答
投稿日時: 18/03/29 14:57:23
投稿者: Suzu

原因は明確には判りません。
ただ経験的にExcelで制限に掛かる様な事や、無茶な事をすると起きるイメージがあります。
 
 
ユーザー設定リストには、上限がある様ですけど大丈夫?
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_windows8-mso_2013_release/%E4%B8%A6%E3%81%B9%E6%9B%BF%E3%81%88customorder/75ead468-21f4-465e-ae0b-89cb49a0de33
 
 
VBAではなく、手動で同様の事を行ったらどうなるのですか?

投稿日時: 18/03/29 15:32:10
投稿者: Armal

Suzuさん
 コメントありがとうございます。
 
 254行の文字制限ですが、削除できない既存のユーザー設定を含めるのか含めないのか分かりませんが、項目数が9項目(9行)のリストでVBA上から実行しても同様の事象になりました。
 
 手動で実行した場合ですが、(そもそもこの試験方法に気づいていませんでした。)正常に保存されました。
さらに、同一環境の別PCで実行しても同様でVBAでは保存エラーが発生し、手動では保存できました。
 
 

投稿日時: 18/03/29 16:08:51
投稿者: Armal

ちなみに、項目数が少ないリスト内容は以下です。
 

八地方区分
北海道
東北
関東
中部
近畿
中国
四国
九州
海外

回答
投稿日時: 18/03/29 16:11:19
投稿者: Suzu

やはりVBAに問題がありそうですね。
 
どこが問題か、問題が解決するかは判りませんが、気づいた点を羅列します。
 
・なぜ配列変数を使わなければなならいのでしょうか。
   listarray:= Range("A1:A5") の様に指定できます。
 
   つまり
   listarray:= Range("都道府県")
   listarray:= Range("八地方区分")
    で良いのでは?
 
・フォームをイニシャライズし、表示しようとしているのに
  なぜ、別のワークシートに対し、Activateメソッドを発行するの?
 
・なぜ、ワークシート Master を Activeにしているの?
  Select メソッドや、Activeメソッド、Slection は使用しないでも たいていは処理可能です。
  なくす方法で考えましょう。

投稿日時: 18/03/30 10:36:24
投稿者: Armal

Suzuさん
 
 こちらの知識が乏しくよくわからないので教えて下さい
 
1.listarrayはsortオブジェクトで利用可能なのでしょうか? また、利用可能な場合どのように利用するのでしょうか
2.formを表示用ボタンは「fruits」シートにあるので、form初期化時に別シートの都道府県、八地方区分をActivateしないでrangeだけで読み込む方法はあるのでしょうか?
 
 並び替えボタンにある.activate等は確かに冗長ですね。
 配列変数にしないでcustomorderが実行できるのであればそれにこした事はないのですが...
 

引用:
1.シート上にボタンを配置し、そのイベントでUserFormを表示
2.UserForm.Initilizeで別シートにある都道府県を配列変数(下例のvstr)に読み込みユーザ設定リストに追加
後、Sort実行時およびUserFrom Unload時のために登録番号をGlobal変数(下例のsortNo1)として保存

投稿日時: 18/03/30 12:25:44
投稿者: Armal

Suzuさん
 理解しました。
 formでユーザリストに登録する時に
 
 listarray:=range("都道府県")
 
とするのですね。 なるほど!!です。
 
 ありがとうございます。

回答
投稿日時: 18/03/30 14:59:56
投稿者: Suzu

listarray:=range("都道府県")
 
正確には、Value を付けないと、その後の GetCustomListNum でエラーになります。
 
Private Sub UserForm_Initialize()
    Dim ws_obj As Worksheet
 
    Set ws_obj = Worksheets("Master")
 
    Application.AddCustomList ListArray:=ws_obj.Range("都道府県").Value '並び替え用ユーザ設定登録
    sortNo1 = Application.GetCustomListNum(ws_obj.Range("都道府県").Value) '上記アクセス番号を保存
 
    Application.AddCustomList ListArray:=ws_obj.Range("八地方区分").Value '並び替え用ユーザ設定登録
    sortNo2 = Application.GetCustomListNum(ws_obj.Range("八地方区分").Value) '上記アクセス番号を保存
End Sub
 
こうなるかと。
 
Private Sub CommandButton1_Click()
    With Worksheets("fruits")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("E3") _
            , CustomOrder:=sortNo1
' , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("D3") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
' , CustomOrder:=sortNo2
 
        With .Sort
            .SetRange .Range("Table")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub
 
どうせオブジェクト変数で受けてWith を行うなら、
With で オブジェクトを与えても良いでしょう。
また、その後の、Activate/Select は不要ですよね
なので、Key は 単なるRnage ではなく、.Range にしました。
 
で、Sort も With の中に取り込みました。

回答
投稿日時: 18/03/30 15:37:37
投稿者: mattuwan44

>配列変数にしないでcustomorderが実行できるのであればそれにこした事はないのですが...
 
ユーザー設定リストに登録したり削除したりしなくても、
これだけでよさそうです。
 
Private Sub CommandButton1_Click()
    Dim rngTable As Range
    Dim n As Long
    Dim s1 As String
    Dim s2 As String
 
    Set rngTable = Application.Range("Table")
    n = Application.CustomListCount
    s1 = Join(WorksheetFunction.Transpose(Application.Range("八地方区分")), ",")
    s2 = Join(WorksheetFunction.Transpose(Application.Range("都道府県")), ",")
 
    With rngTable.Worksheet.Sort
        With .SortFields
            .Clear
            .Add Key:=rngTable.Range("E1"), CustomOrder:="""," & s1 & """"
            .Add Key:=rngTable.Range("D1"), CustomOrder:="""," & s2 & """"
        End With
        .SetRange rngTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

トピックに返信