【VBA】OfficeTANAKAさんの数式をコメントで表示するやつ - とりにくを好きな人がVBAを勉強しています。

おはようございます。こんにちは。こんばんは。
最近は時間を見つけたら、YouTubeでExcelの動画を見ちゃったりしているとりにくVBAです。
今回は、最近見た動画でOfficeTANAKAさんのあれにチャレンジしてみましたので、紹介します。

数式を一瞬でコメント表示する

Excelについてネットで検索をしたことがある人なら、この方のサイトに一度はお世話になったことがあるのではないでしょうか？

officetanaka.net

私は何度もお世話になっております。

さて、OfficeTANAKAさんのYouTube動画を見ていたら、セルに入力されている数式を、ポンと一瞬でコメント表示してくださるんですよね。

これは見やすいなー、どうやってるんだろうとか思っていましたら、これについて解説している動画もありました。

www.youtube.com

その正体は個人用マクロブックで自作したもので、「ですよねー」と思いましたが、その中身については公開されませんでした。

動画のなかではExcelの標準機能や関数で代用する方法が紹介されていましたが、コメント表示のほうが何かと使い勝手が良さそうなので、挑戦してみました。

実践

まずは、だいたいこんな感じだろうなあと思ったこと挙げていきます。

セル内の数式を文字列で表示する

指定のセルにコメントを挿入する

コメントのフォントサイズや色を変更する

コメントの表示サイズを変更する

これらを試していきます。

セル内の数式を文字列で表示する

これはFormulaプロパティを使えばよさそうだなと思いました。
この時点でコメントの挿入はよくわかってなかったので、別のセルへ数式を表示してみました。

A1セルにこんな式を打ち込みました。

=IFERROR(VLOOKUP(E7,F7:I17,2,0),"エラーですよ")

これをC1セルに数式で表示します。

Range("C1").value = Range("A1").Formula

ご存知の方もいらっしゃると思いますが、これだと数式が計算されて表示されてしまいます。セル内で数式を文字列として表示する工夫が必要でした。

Range("C1").value = "'" & Range("A1").Formula

ちょっとわかりづらいですが、シングルクォーテーションを先頭につけてあげます。これで、数式が表示されます。

コメントの挿入

コメントの挿入は、Addcommentというメソッドが用意されています。

Range("C1").AddComment "とりにくVBA"

これでC1セルに「とりにくVBA」とコメントが挿入されます。
これは簡単ですね。

ちなみにコメントの削除はこんな感じです。

Range("C1").Comment.Delete

コメントのフォントサイズや色を変更する

コメントを挿入するとデフォルトでは文字が小さくて見づらいんですよね。数式が表示されたとしてもそのままでは使いようになりません。

これ調べたんですけど、長いよ。

Range("C1").Comment.Shape.TextFrame.Characters.Font.Size = 数値

これでコメント内のフォントサイズを変更できます。

Withでまとめて、サイズ、色、太字、種類の変更と試しました。

With Range("C1").Comment.Shape.TextFrame.Characters.Font
        .Size = 15
        .Color = vbBlue
        .Bold = True
        .Name = "Meiryo UI"
End With

コメントの表示サイズを変更する

これはコメントを表示する枠のサイズ変更ですね。数式の長さによってサイズを変更します。

コメント内の文字列はTextプロパティで取得できます。
コメント枠の高さ、幅はそれぞれHeight、Widthが用意されています。

Range("C1").Comment.Shape.Height = 数値

Range("C1").Comment.Shape.Width = 数値

 Dim str As String
 Dim textLen As Long
 Dim cmWidth As Long

　  str = Range("C1").Comment.Text

    textLen = Len(str)

    cmWidth = textLen * (Range("C1").Comment.Shape.TextFrame.Characters.Font.Size + 2)

    Range("C1").Comment.Shape.Width = cmWidth

これで文字列の長さに対応して幅を調整できました。