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

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

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


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


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

Excelシートの複雑な計算式を解析するVBA
・複雑な計算式を解析するVBAの概要 ・複雑な計算式を解析する全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プロパティの設定値一覧
・Excelのカラーについて ・ColorIndex、カラー定数、XlRgbColor列挙、RGB値 ・システム カラーの定数 ・サイト内の色関連関連ページ
もちろん、RGB関数で好きな色合いを指定すれば、より見やすくなると思います。
やってみた感じでは、なるべく色合いが交互になるようにした方が見やすい感じを受けました。
色数および何色を使うかは、適宜変更してみてください。

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

※数式の改行
もともとの数式が改行されていれば、それがそのままコメントに出力されます。
上図では、下の数式はもともとが改行されていたものです。
ちなみにこの数式は、ひらがな⇔カタカナの変換で紹介している数式になります。
・ひらがな→カタカナ変換 ・カタカナ→ひらがな変換 ・PHONETIC関数の問題点 ・漢字は変換しない方法 ・漢字は変換しない方法・・・関数だけでやる方法 ・やはりマクロ(VBA)で
上段の数式は、全く意味のない今回の結果を見せる為だけのサンプル数式です。

ツイッターで、文字列としての()や、シート名の()もあるよという事でしたので、
上記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サンプル集」の記事

シートを名前順に並べ替える
数式内の不要なシート名を削除する(HasFormula)
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする(非推奨)
シートを削除:不定数のシート名に対応
セル番地でバラバラに指定されたセルの削除


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

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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