2012年6月14日木曜日

Excel　スタイル設定一覧を、シートに表示するマクロ

先週に悩まされた、「表示形式を追加できません。」や、「セルの書式が多すぎるため、書式を追加できません」のエラーメッセージでは、いろいろと勉強になりましたので、ここで少しまとめておきます。


Excelのスタイルについて

Excelのスタイルとは、「表示形式」「配置」「フォント」「罫線」「パターン」「保護」等の一連の書式をひとまとめにして名前を付けたものです。　よく使う書式の組み合わせをスタイルとして登録しておくと一度に設定できるため、書式を別々に設定するよりも効率が良くなるというわけです。　実はこの機能、今まで使ったことが無く今回初めて知りました。

Excel2007では組み込みスタイルが増えて、こんなスタイルが登録されているようです。


使用できるスタイルの件数

Excel 2007 では、一意のセル書式を 64,000 使用することができますが、以前のバージョンの Excel では一意のセル書式は最大で 4,000 しか使用することができません。一意のセル書式には、ブック内で適用される特定の書式の組み合わせすべてが含まれます。

「以前のバージョンの Excel でサポートされない Office Excel 2007 の機能」より引用　 http://office.microsoft.com/ja-jp/excel-help/HA010077823.aspx


ワークシートをコピーするとスタイルが増える

Excel2000やExcel2003ではセル書式は最大4,000使えるので、一般的な使い方ならばセル書式が4,000もあれば十分かもしれません。しかし、ここに落とし穴がありました。

それはExcel2007で作成されたブックを「Microsoft Office互換機能パック」で変換後、Excel2003を使って編集しようとした時に発生した「ワークシートのコピーでスタイルが増加する」という現象でした。

コピー前は1608件だったスタイル件数が、シートコピー後に3065件に増えました。　この不思議な現象はワークシートのコピーで発生しましましたが、シートからシートへのセル範囲のコピーでは発生しませんでした。　

Microsoftサポートサイトの
「Excel 2007 で、使用されていないスタイルが、あるブックから別のブックへコピーされます。」　http://support.microsoft.com/kb/2553085/ja　
が解決の糸口になるのかもしれませんが、Excel2007を持っていないのでこれは未確認です。

スタイルはブックに登録されるのでブックごとにスタイルの件数が違うようです。　Excel2007で作成されたブックでは、ワークシートのコピーでスタイルが増えるようなので、Excel2007で繰り返しワークシートをコピーして編集するような使い方をしている場合は、気づかないうちにスタイルが増えているのかもしれません。


スタイルが増えすぎるとどうなるのか

Excel2007で作成された、「スタイルが増えすぎたブック」を「Microsoft Office互換機能パック」で変換後に調べてみました。（この時のスタイル件数は3065件）

まっさらなワークシートをひとつだけ残して、他のワークシートをすべて削除した状態で書式設定をすると　Excel2000では「表示形式を追加できません。」　が表示されました。　Excel2003では「セルの書式が多すぎるため、書式を追加できません」　が表示されました。

次にユーザースタイルをマクロを使って一括削除すると再び書式設定が出来るようになりました。

このことから、ワークシート内の書式数では無くて、スタイルの登録件数が多い場合に、このようなエラーが発生することがわかりました。（4,000件に満たない場合でも起こるようです。）



追記：2013年4月10日
スタイルが増えすぎて開くことができない場合は、この投稿が役立つかもしれません。

Office 互換機能パックでxlsxファイルが開けない。
Office 互換機能パックでxlsxファイルを開こうとすると「表示形式を追加できません。」や「セルの書式が多すぎるため、書式を追加できません」が表示される。


追記：2016年6月22日
Excelのスタイルに関連する他の投稿




スタイル設定をシートに出力するマクロ

大量のスタイル設定の内容を一覧表形式で確認したかったので、スタイル設定をシートに書き出すマクロを作ってみました。　一覧表に出力したスタイルを見てみると、Excelが自動的に（勝手に）追加したと思われるスタイルが大量にありました。

シートに出力したスタイル
シートに出力したスタイル

このマクロは、Excel2000とExcel2003を使って、WindowsXP SP3で動作確認しました。　マクロを実行すると、アクティブなブックに新しいシートを追加してスタイル一覧を出力します。

出力する書式設定の内容は、

  • スタイルの種類
  • セルにスタイル適用した結果を表示
  • スタイル名
  • 表示形式
  • 配置
  • 縦横位置
  • フォント名とサイズ
  • 罫線　左、右、上、下、右斜め下、右斜め上
  • パターン
  • 保護
  • 数式表示
としました。

組込スタイルとユーザースタイル別に、登録されたスタイル名順に並んでいます。

スタイルダイアログで「スタイルに設定されている書式」にチェックのない項目は適用されないため、書式の登録があっても表示していません。

スタイルの内容が確認できるように、B列のセルにスタイル設定を適用した表示をしています。


スタイル設定をシートに出力する　VBAマクロ

  1. '新しいシートにスタイルを出力｡
  2. Sub View_Style()
  3.     Dim nCnt As Long
  4.     Dim BltinCnt As Long
  5.     Dim NotBltinCnt As Long
  6.     Dim i As Long
  7.     Dim st As String
  8.     Dim stVal As Long
  9.     Dim cnt As Long
  10.     Dim sTitle As Variant
  11.     Dim sh As Worksheet
  12.     Dim bk As Workbook
  13.     
  14.     Dim kName As Variant
  15.     Dim kXlValue As Variant
  16.     Dim kStr(5) As String
  17.     Dim kNum As Integer
  18.     sTitle = Array("種類", "スタイル表示", "スタイル名", "表示形式", "配置 縦", "配置 横", "フォント名", _
  19.                 "サイズ", "罫線", "罫線", "罫線", "罫線", "罫線", "罫線", "パターン", "保護", "数式表示")
  20.     kName = Array("左", "右", "上", "下", "右斜め下", "右斜め上")
  21.     kXlValue = Array(xlLeft, xlRight, xlTop, xlBottom, xlDiagonalDown, xlDiagonalUp)
  22.     
  23.     On Error GoTo ERR_EXIT
  24.     
  25.     Set bk = ActiveWorkbook
  26.     Set sh = bk.Worksheets.Add
  27.     
  28.     NotBltinCnt = 0
  29.     BltinCnt = 0
  30.  
  31.     'タイトル表示
  32.     sh.Range("a1").Resize(1, 17).Value = sTitle
  33.     
  34.     'スタイル読み出し
  35.     cnt = bk.Styles.Count
  36.     For i = 1 To cnt
  37.         nCnt = i + 1
  38.         
  39.         '種類
  40.         If bk.Styles(i).BuiltIn Then
  41.             '組込みスタイル
  42.             BltinCnt = BltinCnt + 1
  43.             sh.Range("A" & nCnt).Value = "組込"
  44.         Else
  45.             'ユーザースタイル
  46.             NotBltinCnt = NotBltinCnt + 1
  47.             sh.Range("A" & nCnt).Value = "ユーザー"
  48.         End If
  49.         
  50.         'スタイル表示
  51.         sh.Range("B" & nCnt).Value = 1234567890
  52.  
  53.         'スタイル名
  54.         sh.Range("C" & nCnt).Value = bk.Styles(i).NameLocal
  55.  
  56.         '表示形式
  57.         If bk.Styles(i).IncludeNumber Then
  58.             st = bk.Styles(i).NumberFormatLocal
  59.         Else
  60.             st = "-"
  61.         End If
  62.         sh.Range("D" & nCnt).Value = st
  63.         
  64.         '配置
  65.         If bk.Styles(i).IncludeAlignment Then
  66.             '縦 xlVAlign
  67.             stVal = bk.Styles(i).VerticalAlignment
  68.             Select Case stVal
  69.             Case xlVAlignTop
  70.                 st = "上"
  71.             Case xlVAlignCenter
  72.                 st = "中央"
  73.             Case xlVAlignBottom
  74.                 st = "下"
  75.             Case xlVAlignJustify
  76.                 st = "両端揃え"
  77.             Case xlVAlignDistributed
  78.                 st = "均等割り付け"
  79.             Case Else
  80.                 st = ""
  81.             End Select
  82.             sh.Range("E" & nCnt).Value = st
  83.  
  84.             '横 XlHAlign
  85.             stVal = bk.Styles(i).HorizontalAlignment
  86.             Select Case stVal
  87.             Case xlHAlignGeneral
  88.                 st = "標準"
  89.             Case xlHAlignLeft
  90.                 st = "左"
  91.             Case xlHAlignCenter
  92.                 st = "中央"
  93.             Case xlHAlignRight
  94.                 st = "右"
  95.             Case xlHAlignFill
  96.                 st = "繰り返し"
  97.             Case xlHAlignJustify
  98.                 st = "両端揃え"
  99.             Case xlHAlignCenterAcrossSelection
  100.                 st = "選択範囲で中央"
  101.             Case xlHAlignDistributed
  102.                 st = "均等割り付け"
  103.             Case Else
  104.                 st = ""
  105.             End Select
  106.             sh.Range("F" & nCnt).Value = st
  107.         Else
  108.             sh.Range("E" & nCnt).Value = "-"
  109.             sh.Range("F" & nCnt).Value = "-"
  110.         End If
  111.         
  112.         'フォント
  113.         If bk.Styles(i).IncludeFont Then
  114.             'フォント名
  115.             sh.Range("G" & nCnt).Value = bk.Styles(i).Font.Name
  116.             
  117.             'フォントサイズ
  118.             sh.Range("H" & nCnt).Value = bk.Styles(i).Font.Size
  119.         Else
  120.             sh.Range("G" & nCnt).Value = "-"
  121.             sh.Range("H" & nCnt).Value = "-"
  122.         End If
  123.         
  124.         '罫線　XlLineStyle
  125.         If bk.Styles(i).IncludeBorder Then
  126.             For kNum = 0 To 5
  127.                 stVal = bk.Styles(i).Borders(kXlValue(kNum)).LineStyle
  128.                 If stVal <> xlLineStyleNone Then
  129.                     kStr(kNum) = kName(kNum)
  130.                 Else
  131.                     kStr(kNum) = "なし"
  132.                 End If
  133.             Next kNum
  134.             With sh.Range("I" & nCnt).Resize(1, 6)
  135.                 .Value = kStr
  136.             End With
  137.             
  138.         Else
  139.             With sh.Range("I" & nCnt).Resize(1, 6)
  140.                 .Value = "-"
  141.             End With
  142.         End If
  143.         
  144.         'パターン XlPattern
  145.         If bk.Styles(i).IncludePatterns Then
  146.             If bk.Styles(i).Interior.Pattern = xlPatternNone Then
  147.                 st = "網かけなし"
  148.             Else
  149.                 st = "網かけ"
  150.             End If
  151.         Else
  152.             st = "-"
  153.         End If
  154.         sh.Range("O" & nCnt).Value = st
  155.         
  156.         '保護
  157.         If bk.Styles(i).IncludeProtection Then
  158.             'ロック
  159.             If bk.Styles(i).Locked Then
  160.                 st = "ロック"
  161.             Else
  162.                 st = ""
  163.             End If
  164.             sh.Range("P" & nCnt).Value = st
  165.             
  166.             '非表示
  167.             If bk.Styles(i).FormulaHidden Then
  168.                 st = "表示しない"
  169.             Else
  170.                 st = "表示"
  171.             End If
  172.             sh.Range("Q" & nCnt).Value = st
  173.         Else
  174.             sh.Range("P" & nCnt).Value = "-"
  175.             sh.Range("Q" & nCnt).Value = "-"
  176.         End If
  177.     Next i
  178.     
  179.     '並べ替え　種類：降順、　スタイル名：昇順、　先頭行：タイトル
  180.     With sh.Range("A1").Resize(cnt + 1, 17)
  181.         .Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("C2"), Order2:=xlAscending, _
  182.             Header:=xlGuess, OrderCustom:=1, MatchCase:=False
  183.         .Font.Size = 9
  184.         .Columns.AutoFit
  185.     End With
  186.     
  187.     'スタイル表示セルに、スタイルを適用
  188.     For i = 2 To cnt + 1
  189.         sh.Range("B" & i).Style = sh.Range("C" & i).Value
  190.     Next i
  191.     
  192. ERR_EXIT:
  193.     If Err.Number <> 0 Then
  194.         MsgBox "エラー№:" & Err.Number & " " & Err.Description, , "エラーのため終了します"
  195.     End If
  196.     
  197.     Set sh = Nothing
  198.     Set bk = Nothing
  199.     
  200.     MsgBox "組込スタイル=" & BltinCnt & ", ユーザースタイル=" & NotBltinCnt & vbCr & _
  201.     "スタイル合計=" & BltinCnt + NotBltinCnt, , "スタイル件数"
  202. End Sub
  203.  
  204.  
投稿者
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )