VBAサンプル集
セルの数式をネスト色分けしてコメント表示

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-04-17

セルの数式をネスト色分けしてコメント表示


数式のネストが3段階を超えてくる、なかなか読むのが辛くなってきます。
数式を改行したりして見やすくするにも限界があります。


特別に複雑な数式を解析する場合は、以下を試してみてください。

Excelシートの複雑な計算式を解析するVBA
セルに入力されている数式が折り返されていて複数行(ときに3行以上)になっている場合、数式バーで見ていたのでは、どんな数式なのかがさっぱりわからなくなります。このような複雑な数式を分解し、分かり易く表示するVBAを作成しました。最初に言っておきますが、そもそも、そのような複雑な数式は作らないほうが良いというのが基本です。

このようなツールを使うほどではないが、数式を色分けしてちょっと見やすくなれば、そんな場合を想定しています。


ここで言うコメントは、最新のOffice365にでは「メモ」と呼ばれるものです。
Office365では従来の「コメント」が「メモ」になった訳ですが、ページタイトルでは馴染みのある「コメント」を使っています。

完成イメージは、このようになります。

マクロ VBA サンプル画像
※下の数式が改行されているのは、元々の数式が改行されている為です。

セルの数式をネスト色分けしてコメント表示のVBA

'ネスト色分けした数式コメント作成
Public Sub setNestColorComment(ByVal argRange As Range)
  Dim objComment As Comment
  Set objComment = addFormulaComment(argRange)
  Call setFontColor(objComment)
  objComment.Visible = True
End Sub

'数式コメント作成
Private Function addFormulaComment(ByVal argRange As Range) As Comment
  Dim objComment As Comment
  If Not argRange.Comment Is Nothing Then
    argRange.Comment.Delete
  End If
  Set objComment = argRange.AddComment
  With objComment
    .Text Text:=argRange.Formula
    .Shape.TextFrame.Characters.Font.Size = 14
    .Shape.TextFrame.Characters.Font.Bold = True
    .Shape.Top = argRange.Top + 8
    .Shape.Left = argRange.Offset(, 1).Left + 8
    .Shape.TextFrame.AutoSize = True
  End With
  Set addFormulaComment = objComment
End Function

'数式ネストに段階的に色を設定
Private Sub setFontColor(ByVal obj As Comment)
  Dim i As Long, ix1 As Long, ix2 As Long, lv As Long
  Dim dQuot As Boolean, sQuot As Boolean
  lv = -1
  For i = 1 To Len(obj.Text)
    Select Case Mid(obj.Text, i, 1)
      Case """" 'リテラル"対応
        dQuot = Not dQuot
      Case "'" 'シート名'対応
        If Not dQuot Then sQuot = Not sQuot
      Case "("
        If Not (dQuot Or sQuot) Then
          lv = lv + 1
          '関数の開始位置を取得
          ix1 = getFuncStart(obj.Text, i)
          '閉じ括弧の位置を取得
          ix2 = getPairIndex(obj.Text, i)
          '閉じ括弧があり、最初の関数以外
          If ix1 > 0 And lv > 0 Then
            obj.Shape.TextFrame.Characters( _
              Start:=ix1, _
              Length:=ix2 - ix1 + 1).Font.Color _
              = getNestColor(lv)
          End If
        End If
      Case ")"
        If Not (dQuot Or sQuot) Then lv = lv - 1
    End Select
  Next
End Sub

'開き(に対する関数の開始位置を返す
Private Function getFuncStart(ByVal aString As String, _
               ByVal aStart As Long) As Long
  Dim i As Long
  Dim dQuot As Boolean, sQuot As Boolean
  For i = aStart - 1 To 1 Step -1
    Select Case Mid(aString, i, 1)
      Case "=", "(", "+", "-", "*", "/", "&", ",", " "
      getFuncStart = i + 1
      Exit Function
    End Select
  Next
End Function

'開き(に対する閉じ)の文字位置を返す
Private Function getPairIndex(ByVal aString As String, _
               ByVal aStart As Long) As Long
  Dim i As Long, cnt As Long
  Dim dQuot As Boolean, sQuot As Boolean
  For i = aStart + 1 To Len(aString)
    Select Case Mid(aString, i, 1)
      Case """" 'リテラル"対応
        dQuot = Not dQuot
      Case "'" 'シート名'対応
        If Not dQuot Then sQuot = Not sQuot
      Case "("
        If Not (dQuot Or sQuot) Then cnt = cnt + 1
      Case ")"
        If Not (dQuot Or sQuot) Then
          If cnt = 0 Then
            getPairIndex = i
            Exit Function
          End If
          cnt = cnt - 1
        End If
    End Select
  Next
End Function

'数式のネストの深さにより色を返す
Private Function getNestColor(ByVal lv As Long) As Long
  Dim aryColor
  aryColor = Array(vbBlack, vbBlue, vbMagenta, vbGreen, rgbBrown, vbCyan, vbRed)
  '7進にして色を繰り返す
  getNestColor = aryColor(lv Mod 7)
End Function

大きく二つに分かれています。
コメントを作成して数式をいれる、
addFormulaComment

数式のネストを判定して色分けする、
setFontColor
コメントの位置やフォントサイズ等は適宜修正してください。

関数の始まりと、関数の終りである括弧()の対になっている位置の特定が少し面倒です。
getFuncStart
確実に関数の始まりを見つけるのは結構大変です。
区切り記号として、"=", "(", "+", "-", "*", "/", ",", " ", vbLf、これらを判定しています。
getPairIndex
確実に括弧()を対で見つけるのも結構大変で、愚直に対となる)を探しています。

正規表現等で一発で取得するのは恐らく無理ではないかと思いましたので、愚直に順に探すようにしています。

色分けする色の指定は、
getNestColor
このなかで配列にして使っています。
7色分けです。
さらにネストがあれば、この色を繰り返し使うようにしてみました。
さすがに、そんなにネストしたら・・・とは思いますけど。
基本の色定数を使っていますが、黄色は見づらいので代わりにrgbBrownを入れてみました。
Colorプロパティの設定値一覧
塗りつぶし、文字色、等々の色指定は結構悩ましいものがあります、Excel2003までなら、ColoIndexで56色だけだったので簡単でしたが、Excel2007以降は、フルカラーがつかえるようになった為、色指定が悩ましくなりました。Colorプロパティに設定する色定数について、色見本とともに一覧にまとめておきます。
もちろん、RGB関数で好きな色合いを指定すれば、より見やすくなると思います。
やってみた感じでは、なるべく色合いが交互になるようにした方が見やすい感じを受けました。
色数および何色を使うかは、適宜変更してみてください。

マクロ VBA 数式のネストを色分け

※数式の改行
もともとの数式が改行されていれば、それがそのままコメントに出力されます。
上図では、下の数式はもともとが改行されていたものです。
ちなみにこの数式は、ひらがな⇔カタカナの変換で紹介している数式になります。
「ひらがな」を「カタカナ」に、「カタカナ」を「ひらがな」に変換する方法の説明です。ひらがな→カタカナ変換 A1セル「にっぽんたろう」、これをB1セルに「ニッポンタロウ」と表示するには、B1セルに、=PHONETIC(A1) これで、「カタカナ」で表示されたと思います。
上段の数式は、全く意味のない今回の結果を見せる為だけのサンプル数式です。

ツイッターで、文字列としての()や、シート名の()もあるよという事でしたので、
上記VBAは、これらにも対応するように修正したものになります。

マクロ VBA サンプル画像

セルの数式をネスト色分けしてコメント表示の使用例

Sub SampleMain()
  'アクティブシートの数式が入っているセルを取得
  Dim formulaRange As Range
  On Error Resume Next
  '数式のセル全部、もちろん特定セルでも良い
  Set formulaRange = Cells.SpecialCells(xlCellTypeFormulas)
  If Err Then Exit Sub
  On Error GoTo 0
  
  Application.ScreenUpdating = False
  '最小化されているとCommentが正しく取得できないので
  Dim winState As Long
  winState = Application.WindowState
  Application.WindowState = xlNormal 'xlMaximizedでも良い
  
  '数式が入っている全セルに数式コメントを作成
  Dim myRange As Range
  For Each myRange In formulaRange
    Call setNestColorComment(myRange)
  Next
  
  Application.WindowState = winState
  Application.ScreenUpdating = True
End Sub

アクティブシートの計算式が入っている全セルを対象としています。

特定セル範囲、例えばB列なら、
Range("B:B").SpecialCells(xlCellTypeFormulas)
このようにしてください。

もし1セルだけの場合は、
SpecialCellsはシート全セルになってしまうので、その場合はSpecialCellsは使わないようにしてください。

セルの数式をネスト色分けしてコメント表示の最後に

もともとはツイッターで数式のお題の解答で良く見かけていたものを真似してみました。
ネストが深くなっても、きちんと色分けするように自分なりに工夫したものになります。
ツイッターで数式を紹介するときに、自分でもやってみようというのが作成の動機です。
とはいえ、そもそもあまり深くネストした数式は作らないほうが良いという事だけは申し添えておきます。



同じテーマ「マクロVBAサンプル集」の記事

印刷ダイアログを使用する(xlDialogPrint)
名前定義の一覧と削除(Name)
シートを名前順に並べ替える
数式内の不要なシート名を削除する(HasFormula)
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする(非推奨)


新着記事NEW ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ