2012年6月7日木曜日

BarGraphViewer20120607をリリースしました。

BarGraphViewer20120607をリリースしました。
このページ上部メニューの 「WebSite BarGraphViewer」から、ダウンロードページに移動できます。



変更点

シート「BARGRAPH」の作成時に、言語色の表示とバーグラフの表示色が正しく表示できない問題点( Excel 2000 と Excel 2003 で発生しました。)を修正しました。

色が正しく表示できない状態

問題の発生原因

スタイル名の登録件数によってこの問題が発生することが分かりました。 Excel 2000 と Excel 2003 で発生することを確認しています。



問題の詳細

Excel ブックに登録されているスタイル名を調べてみると、大量のスタイル設定がありました。

スタイル設定は、メニューから書式設定スタイルでダイアログを開き、スタイル名のドロップダウンリストから見ることができます。

どれくらいのスタイル設定が登録されているのか調べるため、マクロを書いてスタイル名の登録件数を調べてみました。

スタイルをカウントする。
Sub Count_Style()
    Dim s, BltinNameCount, NotBltinNameCount
    On Error Resume Next
    
    NotBltinNameCount = 0
    BltinNameCount = 0
    With ActiveWorkbook
        For Each s In .Styles
            If Not s.BuiltIn Then
                'ユーザースタイルをカウント
                NotBltinNameCount = NotBltinNameCount + 1
            End If
        Next
        For Each s In .Styles
            If s.BuiltIn Then
                '組み込みスタイルをカウント
                BltinNameCount = BltinNameCount + 1
            End If
        Next
    End With
    MsgBox "styles count. Bltin=" & BltinNameCount & ", NotBiltin=" & NotBltinNameCount
End Sub

ダウンロードした青木リスト 「bia12.zip」を解凍し、「bia12.xlsx」を  Microsoft Office 互換機能パック で変換後、ファイルを XLS 形式で保存します。 保存直後のスタイル件数は 1,608 件、「BARGRAPH」シートの作成後のスタイル件数は 3,065 件になりました。(これは 6 月 2 日にダウンロードしたファイルで確認した件数です。)

以前のバージョンの Excel でサポートされない Office Excel 2007 の機能」によると。

Excel 2007 では、一意のセル書式を 64,000 使用することができますが、以前のバージョンの Excel では一意のセル書式は最大で 4,000 しか使用することができません。一意のセル書式には、ブック内で適用される特定の書式の組み合わせすべてが含まれます。
となっています。 スタイル名の登録件数が 4,000 件に達していないのに、この問題が発生した理由を考えると。スタイル名の登録件数と 「BARGRAPH」シートに設定した書式との総数が 4,000 件を超えたのが原因と思われます。 このことから Excel 2000 と Excel 2003 でこの問題が発生したようです。また、Excel 2007 では大丈夫だと思います。

ためしに、ブックに新しいシートを 1 つ挿入して、その他のシートを削除した後で挿入したシートに書式を設定すると。 「表示形式を追加できません。」のエラーダイアログが表示され、書式設定ができません。

このことから、ワークシートの内容というよりも、スタイル名の登録件数が多いことが原因と考えられます。

Excel2000の場合

Excel 2003 では、「セルの書式が多すぎるため、書式を追加できません」のエラーダイアログが表示されました。

Excel2003の場合

ここで、問題のユーザースタイル名の登録を削除してみることにします。

削除対象は 3,065 件もありますがスタイルダイアログでは 1 件ずつしか削除できず気の遠くなる数なのでマクロで一括削除します。

(削除対象はユーザースタイルだけです。組み込みスタイルは削除しません。 自分でユーザースタイルを登録している方は、このマクロで一括削除しないように注意してください。

'スタイルを削除する。
Sub Delete_StyleName()
    Dim s, NameCount
    On Error Resume Next
    
    NameCount = 0
    For Each s In ActiveWorkbook.Styles
        If Not s.BuiltIn Then
            NameCount = NameCount + 1
            s.Delete
        End If
    Next
    MsgBox "Delete complete.  count=" & NameCount
End Sub

削除後は Excel 2000 と Excel 2003 のどちらでも、再び書式設定が出来るようになりました。

Excel2000の場合
Excel2003の場合

このことから、ユーザースタイルを削除することで解決の目処がつきましたが、もう一つ問題が残っています。それは、「BARGRAPH」シートの作成後のスタイル件数が 3,065 件と 1.9 倍に増加したことです。

スタイル件数の増加がプログラムのどの部分で発生しているのか調べた結果、シートのコピーで発生していることが分かりました。

該当部分を書き出すと。

'ワークシートをコピー。
Sheets(ws_name_bi).Copy before:=Sheets(1)

'シート名の設定
Sheets(1).Name = ws_name_bar

このようにワークシートを丸ごとコピーすると、この問題が起こるようです。

※ブック上の「シートの移動またはコピー」ダイアログボックスで、「コピーを作成する」を選んだ時にも同じようにユーザースタイルの増加現象が起こりました。

そこでセル範囲をコピーするように修正しました。

コピー元のセル範囲を調べてから、コピーするようにしています。

Dim bi_row   As Integer                 'biリスト行数
Dim bi_col As Integer                   'biリスト列数 (右端の列)
Dim bi_range As Range

'biシートの最終行 STATION列の行数を確認
bi_row = Sheets(ws_name_bi).Cells(Rows.Count, 3).End(xlUp).row

'biシートの最右列 項目名行の列数を確認
bi_col = Sheets(ws_name_bi).Cells(2, Columns.Count).End(xlToLeft).Column

Set bi_range = Sheets(ws_name_bi).Range(Cells(1, 1), Cells(bi_row, bi_col))

'シートを追加、シート名の設定。
Sheets.Add(before:=Sheets(1)).Name = ws_name_bar
'セル範囲をコピー
bi_range.Copy Destination:=Sheets(ws_name_bar).Range("A1")

コピーの方法を、「シート丸ごとコピー」から「セル範囲をコピー」に変更することで、コピーによるスタイル件数が増加が解決。他には、「BARGRAPH」シートを作成後に、言語色の表示とバーグラフの表示色を正しく表示できない問題も解決しました。

今回リリースの「BarGraphViewer20120607」では、ワークシートのコピー方法の変更のみでユーザースタイルの自動削除は行っていません。 Excel ブックの中でスタイルが使われていたり、個人用に登録されているユーザースタイルがあると自動削除するわけにもいかず。 いまのところ 1,608 件程度でも問題ないようなので、しばらく様子を見ています。

もしも、ユーザースタイルの登録件数が気になる方は、この下のマクロでスタイルの一覧を確認してから削除されるのが良いかもしれません。



登録されているスタイルの一覧を、アクティブシートに書き出すマクロです。

このマクロを実行するとアクティブシートを上書きしますので、事前に新しいシートを挿入後アクティブな状態で実行してください。

'スタイルをアクティブシートに書き出す。
Sub View_Style()
    Dim NameCount, BltinNameCount, NotBltinNameCount, i
    On Error Resume Next
    
    NameCount = 0
    NotBltinNameCount = 0
    BltinNameCount = 0
    With ActiveWorkbook
        For i = 1 To .Styles.Count
            If Not .Styles(i).BuiltIn Then
                NameCount = NameCount + 1
                NotBltinNameCount = NotBltinNameCount + 1
                ActiveSheet.Range("A" & NameCount).Value = NotBltinNameCount
                ActiveSheet.Range("B" & NameCount).Value = .Styles(i).BuiltIn
                ActiveSheet.Range("C" & NameCount).Value = .Styles(i).NameLocal
            End If
        Next i
        For i = 1 To .Styles.Count
            If .Styles(i).BuiltIn Then
                NameCount = NameCount + 1
                BltinNameCount = BltinNameCount + 1
                ActiveSheet.Range("A" & NameCount).Value = BltinNameCount
                ActiveSheet.Range("B" & NameCount).Value = .Styles(i).BuiltIn
                ActiveSheet.Range("C" & NameCount).Value = .Styles(i).NameLocal
            End If
        Next i
    End With
    ActiveSheet.Range("A:C").Columns.AutoFit
    MsgBox "styles count. Bltin=" & BltinNameCount & ", NotBiltin=" & NotBltinNameCount & ", Total=" & NameCount
End Sub


2012年 6月14日追記
「Excel スタイル設定をシートに出力するマクロ」を作りました。

http://babooshka-innerjourneys.blogspot.jp/2012/06/excel.html

0 件のコメント :