セルの数式をネスト色分けしてコメント表示
数式のネストが3段階を超えてくる、なかなか読むのが辛くなってきます。
数式を改行したりして見やすくするにも限界があります。
このようなツールを使うほどではないが、数式を色分けしてちょっと見やすくなれば、そんな場合を想定しています。
Office365では従来の「コメント」が「メモ」になった訳ですが、ページタイトルでは馴染みのある「コメント」を使っています。
※下の数式が改行されているのは、元々の数式が改行されている為です。
セルの数式をネスト色分けしてコメント表示の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を入れてみました。
もちろん、RGB関数で好きな色合いを指定すれば、より見やすくなると思います。
やってみた感じでは、なるべく色合いが交互になるようにした方が見やすい感じを受けました。
色数および何色を使うかは、適宜変更してみてください。
※数式の改行
やってみた感じでは、なるべく色合いが交互になるようにした方が見やすい感じを受けました。
色数および何色を使うかは、適宜変更してみてください。
もともとの数式が改行されていれば、それがそのままコメントに出力されます。
上図では、下の数式はもともとが改行されていたものです。
ちなみにこの数式は、ひらがな⇔カタカナの変換で紹介している数式になります。
上図では、下の数式はもともとが改行されていたものです。
ちなみにこの数式は、ひらがな⇔カタカナの変換で紹介している数式になります。
・ひらがな→カタカナ変換 ・カタカナ→ひらがな変換 ・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
アクティブシートの計算式が入っている全セルを対象としています。
Range("B:B").SpecialCells(xlCellTypeFormulas)
このようにしてください。
SpecialCellsはシート全セルになってしまうので、その場合はSpecialCellsは使わないようにしてください。
セルの数式をネスト色分けしてコメント表示の最後に
もともとはツイッターで数式のお題の解答で良く見かけていたものを真似してみました。
ネストが深くなっても、きちんと色分けするように自分なりに工夫したものになります。
ツイッターで数式を紹介するときに、自分でもやってみようというのが作成の動機です。
とはいえ、そもそもあまり深くネストした数式は作らないほうが良いという事だけは申し添えておきます。
ネストが深くなっても、きちんと色分けするように自分なりに工夫したものになります。
ツイッターで数式を紹介するときに、自分でもやってみようというのが作成の動機です。
とはいえ、そもそもあまり深くネストした数式は作らないほうが良いという事だけは申し添えておきます。
同じテーマ「マクロVBAサンプル集」の記事
シートを名前順に並べ替える
数式内の不要なシート名を削除する（HasFormula）
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする（非推奨）
シートを削除：不定数のシート名に対応
セル番地でバラバラに指定されたセルの削除
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.ブック・シートの選択（Select,Activate）｜VBA入門
10.条件分岐（Select Case）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- セルの数式をネスト色分けしてコメント表示
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。