日本語入力ソフトとVBAの覚え書き

・各種日本語入力ソフトの辞書解説 ・ちょっと楽になるExcel VBA集

Excelの入力規則で2段リストを作る方法

f:id:dz_dzone:20170814233145j:plain

Excelでドロップダウンリストで入力をする「入力規則-リスト」が便利ですが、例えばリストを2つ用意して、1つめのドロップダウンリストの結果で2つめのドロップダウンリストを変化させることは可能でしょうか。答えは可能です。ここでは、このような2段リストの作り方を解説していきます。

 

図のようなシートを用意しました。1段目のリストは北東北3県、2段目のリストは各県の市を選択するものです。

f:id:dz_dzone:20180306094420j:plain

 

1段目のリストを作成する

リボン「データ」の「データツール」グループから「データの入力規則」を選択します。

f:id:dz_dzone:20180306101525j:plain

「入力値の種類」を「リスト」に設定します。

f:id:dz_dzone:20180306101535j:plain

「元の値」欄の一番右のアイコンをクリックし、画面上でドラッグして範囲選択します。もう一度右のアイコンをクリックします。

f:id:dz_dzone:20180306101546j:plain

「OK」をクリックして完了です。

f:id:dz_dzone:20180306101558j:plain

2段目のリストを作成する

見出しの下に行を追加する

2段目リストを作るために、見出し行の下に作業行を追加します。ここでは範囲選択肢から右クリックして「挿入」を選びます。

f:id:dz_dzone:20180306101612j:plain

「下方向にシフト」を選択して「OK」をクリックします。

f:id:dz_dzone:20180306101714j:plain

見出し行の下に行が追加されました。

f:id:dz_dzone:20180306101809j:plain

「市」のセル範囲を絶対指定で追加した行に書き込む

先ほど追加した行に、各県の「市」を示すセル範囲を絶対指定で入力します。下図では、セル範囲が「G4~G13」ですので、絶対指定「$G$4:$G$13」となります。

f:id:dz_dzone:20180306101821j:plain

絶対指定「$G$4:$G$13」をG3セルに入力します。

f:id:dz_dzone:20180306101940j:plain

同様に、岩手県、秋田県を入力します。

f:id:dz_dzone:20180306101953j:plain

※選択範囲アドレスをクリップボードにコピーするVBAコード

参考までに選択範囲アドレスをクリップボードにコピーするVBAを書いてみました。こちらの別記事を参照して下さい。

dz11.hatenadiary.jp

「市」に対する作業用セルを追加する

2段目リストを作成するためのもう一つの作業セルを作ります。下図のE7~E8です。

f:id:dz_dzone:20180306102005j:plain

HLOOKUP関数にて、検索値=1段目リストで選択した県(セル:C3)、検索範囲=「市」の見出し行と先ほど追加した行(セル範囲:$G$2:$I$3)とし、2行目(セル範囲)が戻り値となるよう設定します。

f:id:dz_dzone:20180306102108j:plain

2段目リストを設定する

C4セルの「データの入力規則」で、「元の値」欄に「=INDIRECT($E$8)」と入力します。これは先ほどのHLOOKUP関数の戻り値のセルに該当します。また、INDIRECT関数は指定したセルの内容をそのまま取得するものです。

f:id:dz_dzone:20180306102552j:plain

2段目リストの結果

下図のように、1段目リストの結果「秋田県」に対する「市」がリストに表示されました。

f:id:dz_dzone:20180306102946j:plain

「岩手県」の場合

f:id:dz_dzone:20180306103307j:plain

以上のように、2段リストが完成しました。応用すれば3段目以上のリストも作ることが出来ます。

 

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

Officeファイルのプロパティを編集する (BuiltInDocumentProperties)

f:id:dz_dzone:20170814233145j:plain

エクセルのプロパティ編集のコードを探していて、OfficeのうちWord, Excel, PowerPointにはBuiltInDocumentPropatiesとCustomDocumentPropatiesというものがあることが分かり、このうちBuiltInDocumentPropatiesについて調べてみました。

  • Word, Excel, PowerPointのみに対応
  • Access, Outlookなどには対応しない
'**Word
ActiveDocument.BuiltInDocumentProperties(n)

'**Excel
ActiveWorkbook.BuiltInDocumentProperties(n)

'**PowerPoint
ActivePresentation .BuiltInDocumentProperties(n)

各アプリケーションの対応表

番号 Property プロパティ名(日本語) Word Excel PowerPoint
1 Title 表題(タイトル)
2 Subject 副題(サブタイトル)/件名
3 Author 作成者
4 Keywords タグ(キーワード)
5 Comments コメント
6 Template テンプレート名
7 Last author 最終更新者
8 Revision number 改訂番号
9 Application name アプリケーション名
10 Last print date 前回印刷日
11 Creation date 作成日時
12 Last save time 最終更新日
13 Total editing time 編集時間(min) ×
14 Number of pages ページ数 × ×
15 Number of words 単語数 ×
16 Number of characters 文字数(スペースを含まない) × ×
17 Security パスワード保護(1or0) ×
18 Category 分類
19 Format フォーマット
20 Manager 管理者
21 Company 会社
22 Number of bytes バイト数 × ×
23 Number of lines 行数 × ×
24 Number of paragraphs 段落数 ×
25 Number of slides スライド数 × ×
26 Number of notes メモ数 × ×
27 Number of hidden Slides 非表示のスライド数 × ×
28 Number of multimedia clips マルチメディアクリップの数 × ×
29 Hyperlink base ハイパーリンクの基点
30 Number of characters (with spaces) 文字数(スペースを含む) × ×
31 Content type Content type
32 Content status 状態
33 Language 言語
34 Document version バージョン番号
  • 凡例
    • ○:対応しており値もある
    • △:対応しているが値はない
    • ×:対応していない
    • ●:対応しており値もあるが、コードでは表示できず、エクスプローラ>プロパティ>詳細で表示と設定が可能
    • ▲:対応しており値もあるが、コードでは表示できず、エクスプローラ>プロパティ>詳細で表示のみ可能

各アプリケーションで対応していない番号のコードはエラーになります。

'**Excel
Msgbox ActiveWorkbook.BuiltInDocumentProperties(24)

f:id:dz_dzone:20171207104021j:plain

以上。

Excel VBAの色の指定方法について ~組込定数, ColorIndex, SchemeColor, RGB指定, ThemeColor~

f:id:dz_dzone:20170814233145j:plain

2019.6.26 ThemeColorの内容を追加しました

様々な色の指定方法

Excel VBAでの色の指定方法には下記のようなものがあります。

指定方法 概要
組込定数(VBA) 定数で基本的な8色を指定できる
xlRgbColor列挙 定数で135色を指定できる
ColorIndex インデックスカラー値:0~56で指定。0は塗りつぶしなし
1~56はユーザー指定およびリセットが可能
SchemeColor スキームカラー値:1~80で指定
RGB指定 RGB関数:RGB(赤:Red, 緑:Green, 青:Blue)の各要素を10進数(0~255)または16進数(00~FF)で指定
ThemeColor 定数で12色を指定できる。各々の色に対し、プラス5個ずつの濃淡色を指定できる。配色テーマをあらかじめ設定しておくとテーマ変更で一括で配色を変えられるのが特徴だが、複数テーマの同時使用は出来ない

ここではまずそれぞれの指定方法について解説し、最後にColorIndex, SchemeColor, 組み込み定数, RGB指定の関係性を表で示します。

組込定数(VBA)

組込定数(VBA)は基本の8色を指定できます。
用途:主にフォントの色指定セルの枠や背景の色指定に使います。

With Range("B2")
  .Value = "TEST"
  .Font.Color = vbBlue        '**文字色=青
  .Borders.Color = vbMagenta  '**枠の色=マゼンタ
  .Interior.Color = vbYellow  '**背景色=黄色
End With

vbBlack   : 黒
vbWhite   : 白
vbRed     : 赤
vbGreen   : 緑
vbBlue    : 青
vbYellow  : 黄色
vbMagenta : マゼンタ
vbCyan    : シアン

xlRgbColor列挙 (Excel)

VBA言語自身の組込定数の他に、Excelにのみ設定されたxlRgbColor列挙として示される色の定数が142色ありますが、色かぶりがあるため、実際は135色です。
docs.microsoft.com

ColorIndex:インデックスカラー

ColorIndexは、1~56の56色と、0(塗り潰しなし)を指定できます。
用途:主にフォントセルの枠・背景Chartオブジェクト(グラフ)の色指定に使います。

With Range("B2")
  .Value = "TEST"
  .Font.ColorIndex = 5        '**文字色=青
  .Borders.ColorIndex = 7     '**枠の色=マゼンタ
  .Interior.ColorIndex = 24   '**背景色=薄い水色
  .Interior.ColorIndex = 0    '**背景色=塗りつぶしなし
  '**塗りつぶしなしは xlColorIndexNone / xlNone と指定してもよい
  .Interior.ColorIndex = xlColorIndexNone
  '**自動 (xlColorIndexAutomatic / xlAutomatic) : 様々な項目の自動値を指定できる
  '※全ての項目が該当するわけではないので注意
  .Font.Color = xlColorIndexAutomatic
End With

やや冗長になりますが、別の書き方も出来ます。

ActiveWorkbook.Colors(n) '※n= 1~56
' ※ Colors(0) はエラーになります

'// 下記の2つは同じ内容
Range("A1").Interior.ColorIndex = 24
Range("A1").Interior.Color = ActiveWorkbook.Colors(24)

ColorIndexは編集およびリセットが可能

また、1~56のColorIndexは独自に編集することも可能です。更に、編集した色をデフォルトにリセットすることも可能です。

'例:ColorIndex 1 の色を変更する (デフォルト値 0 = 黒 → 赤 RGB(255, 0, 0)へ )
ActiveWorkbook.Colors(1) = RGB(255, 0, 0)

Debug.Print "ColorIndex 1 の色:" & ActiveWorkbook.Colors(1)
ColorIndex 1 の色:255

'例:ColorIndexを全てデフォルトにリセットする
ActiveWorkbook.ResetColors

SchemeColor:スキームカラー

ShcemeColorは、1~80の80色を指定できますが、いくつかの値は同値です。
用途:主に、Shapeオブジェクトの枠や塗り潰しの色指定に使います。

'**オートシェイプ「円」を描画
With ActiveSheet.Shapes.AddShape(msoShapeOval, 50, 50, 75, 75)
  '**枠線の設定
  With .Line
    .Visible = msoTrue         '**表示する
    .Weight = 1.5              '**枠線の太さ
    .ForeColor.SchemeColor = 2 '**枠線の色を「2:赤」にする
    .Transparency = 0          '**透過しない
  End With
  
  '**塗りつぶしの設定
  With .Fill
    .Visible = msoTrue             '**表示する
    .Patterned msoPatternLargeGrid '**パターン「格子(大)」
    .ForeColor.SchemeColor = 4     '**前景色を「4:青」にする
    .BackColor.SchemeColor = 5     '**背景色を「5:黄色」にする
  End With
End With

f:id:dz_dzone:20190829170225j:plain

RGB指定 (RGB関数)

RGB指定はRGB関数(またはその計算値)により色指定するものです。光の三原色(赤Red, 緑Green, 青Blue)の組み合わせにより色が決まります。各色0~255段階の濃度指定が可能です。

Range("B2").Font.Color = RGB(0, 0, 255)

ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)

RGB関数とその計算値

RGB形式はRGB関数によってその値を示しており、実際にはRGB関数の計算値と同値です。

RGB関数 : RGB(r, g, b) = r * 256^0 + g * 256^1 + b * 256^ 2

例:
 RGB(0, 0, 0) = 0
 RGB(255, 255, 255) = 255 + 255 * 256 + 255 * 256^2
                    = 255 + 65280 + 16711680
                    = 16777215
よって、
Range("B2").Font.Color = RGB(255, 255, 255)
は、
Range("B2").Font.Color = 16777215
とも指定できる

計算値からR, G, Bの各要素を取り出す

先ほどと逆の計算を行うことで、画面上の色からRGB各要素の値を求められます。つまり、画面上のセルやオートシェイプなどの色から赤:Red, 緑:Green, 青:Blueの各要素の値を抽出することが可能です。

'**VBAによる計算式
myColor = ActiveCell.Interior.Color
R = myColor Mod 256
G = Int(myColor / 256) Mod 256
B = Int(myColor / 256 / 256)

'**ワークシート関数による計算式
A1セルに数値が入っているものとすると、
R : =MOD(A1,256)
G : =MOD(INT(A1/256),256)
B : =INT(B3/256/256)

RGB値を16進数で指定する

RGB値をWebカラー指定のような16進数で指定することも可能です。このとき、6桁の16進数の頭に「&H」を付加して下さい。

Range("B2").Font.Color = RGB(0, 0, 255)
Range("B2").Font.Color = &H0000FF

ThemeColor:テーマカラー

テーマカラーは、文字色や背景色の変更の際によく見かけるテーマの配色です。
f:id:dz_dzone:20190619110919j:plain
このテーマの配色は「ページレイアウト」タブの「テーマ」>「配色」から様々な決まった組合せの中から選ぶことが出来、テーマを変更することで一括で色を変更することが可能なものです。
f:id:dz_dzone:20190619111308j:plain
また、配色を自分でカスタマイズすることも可能です。
f:id:dz_dzone:20190619111616j:plain

Range("B2").Font.ThemeColor = xlThemeColorAccent1
Range("B2").Interior.ThemeColor = xlThemeColorAccent4

      定数                      値    色の名称
xlThemeColorDark1                1  濃色1(背景)
xlThemeColorLight1               2  淡色1(テキスト)
xlThemeColorDark2                3  濃色2(背景)
xlThemeColorLight2               4  淡色2(テキスト)
xlThemeColorAccent1              5  アクセント1
xlThemeColorAccent2              6  アクセント2
xlThemeColorAccent3              7  アクセント3
xlThemeColorAccent4              8  アクセント4
xlThemeColorAccent5              9  アクセント5
xlThemeColorAccent6             10  アクセント6
xlThemeColorHyperlink           11  ハイパーリンク
xlThemeColorFollowedHyperlink   12  表示済みのハイパーリンク

各指定形式の比較表 (ColorIndex, SchemeColor, 組込定数, RGB形式)

カラーバー ColorIndex SchemeColor 組込定数 RGB指定 16進指定
塗り潰し
なし
0 - - - -
  1 0 or 8 vbBlack RGB(0,0,0) &H000000
  2 1 or 9 vbWhite RGB(255,255,255) &HFFFFFF
  3 2 or 10 vbRed RGB(255,0,0) &HFF0000
  4 3 or 11 vbGreen RGB(0,255,0) &H00FF00
  5 4 or 12 vbBlue RGB(0,0,255) &H0000FF
  6 5 or 13 vbYellow RGB(255,255,0) &HFFFF00
  7 6 or 14 vbMagenta RGB(255,0,255) &HFF00FF
  8 7 or 15 vbCyan RGB(0,255,255) &H00FFFF
  9 16 - RGB(128,0,0) &H800000
  10 17 - RGB(0,128,0) &H008000
  11 18 - RGB(0,0,128) &H000080
  12 19 - RGB(128,128,0) &H808000
  13 20 - RGB(128,0,128) &H800080
  14 21 - RGB(0,128,128) &H008080
  15 22 - RGB(192,192,192) &HC0C0C0
  16 23 - RGB(128,128,128) &H808080
  17 24 - RGB(153,153,255) &H9999FF
  18 25 - RGB(153,51,102) &H993366
  19 26 - RGB(255,255,204) &HFFFFCC
  20 27 - RGB(204,255,255) &HCCFFFF
  21 28 - RGB(102,0,102) &H660066
  22 29 - RGB(255,128,128) &HFF8080
  23 30 - RGB(0,102,204) &H0066CC
  24 31 - RGB(204,204,255) &HCCCCFF
  25 32 - RGB(0,0,128) &H000080
  26 33 - RGB(255,0,255) &HFF00FF
  27 34 - RGB(255,255,0) &HFFFF00
  28 35 - RGB(0,255,255) &H00FFFF
  29 36 - RGB(128,0,128) &H800080
  30 37 - RGB(128,0,0) &H800000
  31 38 - RGB(0,128,128) &H008080
  32 39 - RGB(0,0,255) &H0000FF
  33 40 - RGB(0,204,255) &H00CCFF
  34 41 - RGB(204,255,255) &HCCFFFF
  35 42 - RGB(204,255,204) &HCCFFCC
  36 43 - RGB(255,255,153) &HFFFF99
  37 44 - RGB(153,204,255) &H99CCFF
  38 45 - RGB(255,153,204) &HFF99CC
  39 46 - RGB(204,153,255) &HCC99FF
  40 47 - RGB(255,204,153) &HFFCC99
  41 48 - RGB(51,102,255) &H3366FF
  42 49 - RGB(51,204,204) &H33CCCC
  43 50 - RGB(153,204,0) &H99CC00
  44 51 - RGB(255,204,0) &HFFCC00
  45 52 - RGB(255,153,0) &HFF9900
  46 53 - RGB(255,102,0) &HFF6600
  47 54 - RGB(102,102,153) &H666699
  48 55 - RGB(150,150,150) &H969696
  49 56 - RGB(0,51,102) &H003366
  50 57 - RGB(51,153,102) &H339966
  51 58 - RGB(0,51,0) &H003300
  52 59 - RGB(51,51,0) &H333300
  53 60 - RGB(153,51,0) &H993300
  54 61 - RGB(153,51,102) &H993366
  55 62 - RGB(51,51,153) &H333399
  56 63 - RGB(51,51,51) &H333333
カラーバー ColorIndex SchemeColor 組込定数 RGB指定 16進指定
  - 64 - RGB(0,0,0) &H000000
  - 65 - RGB(255,255,255) &HFFFFFF
  - 66 - RGB(100,100,100) &H646464
  - 67 - RGB(240,240,240) &HF0F0F0
  - 68 - RGB(0,0,0) &H000000
  - 69 - RGB(255,255,255) &HFFFFFF
  - 70 - RGB(160,160,160) &HA0A0A0
  - 71 - RGB(0,120,215) &H0078D7
  - 72 - RGB(0,0,0) &H000000
  - 73 - RGB(200,200,200) &HC8C8C8
  - 74 - RGB(55,55,55) &H373737
  - 75 - RGB(255,255,255) &HFFFFFF
  - 76 - RGB(100,100,100) &H646464
  - 77 - RGB(0,0,0) &H000000
  - 78 - RGB(255,255,255) &HFFFFFF
  - 79 - RGB(0,0,0) &H000000
  - 80 - RGB(255,255,225) &HFFFFFF

InputBoxを使ってコメントを作成する(非表示オプション付き)

f:id:dz_dzone:20170814233145j:plain

コメントを作成するとき、通常「コメントの挿入>内容を編集>場合によって非表示に設定」という流れです。現在仕事でやっている作業では大量の非表示コメントをどんどんつけていくということをやっており、通常の方法では非常に面倒くさいです。そこで、InputBox関数を使ってコメントを作成し、簡単な方法で表示と非表示を選べるコードを書いてみました。

f:id:dz_dzone:20171124111735j:plain

コード

Sub AddKome()
  ' ** コメントを追加する **
  
  If TypeName(ActiveCell.Comment) = "Comment" Then
    MsgBox "既にコメントがあります。", vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  Dim CL As Range
  Dim StrComment As String
  Dim RTmp As Variant
  Dim BoolVisibleComment As Boolean
  
  StrComment = InputBox("コメント? (文末に ` を入れると非表示)", "コメントを追加")
  
  ' ** キャンセル処理
  If StrPtr(StrComment) = 0 Then    'キャンセルボタン
    Exit Sub
  ElseIf StrComment = "" Then       '空白入力
    Exit Sub
  End If
  
  ' ** 文末に'`'が入っていたらコメントを非表示で追加する
  If Right(StrComment, 1) = "`" Then
    RTmp = Split(StrComment, "`")
    StrComment = RTmp(0)
    BoolVisibleComment = False
  Else
    BoolVisibleComment = True
  End If
  
  For Each CL In Selection
    ' ** コメントの追加
    With CL.AddComment(StrComment)
      .Visible = True
    End With
        
    ' ** コメントの書式等
    With CL.Comment
      .Shape.Fill.ForeColor.RGB = RGB(255, 255, 0)
      With .Shape.TextFrame
        .Characters.Font.Size = 9
        .AutoSize = True
      End With
      .Visible = BoolVisibleComment
    End With
  Next CL
End Sub
  • 既にコメントがある場合は警告して終了
  • 空白のままOKした場合キャンセル扱いとして終了
  • 文末に ` (Shift + @)が入っていたらコメントを非表示で追加する処理
  • 範囲選択の場合は同じコメントを一斉入力

既にコメントがある場合

f:id:dz_dzone:20171124111755j:plain

コメントを追加

f:id:dz_dzone:20171124111808j:plain

f:id:dz_dzone:20171124111820j:plain

コメントを非表示で追加

f:id:dz_dzone:20171124111829j:plain

f:id:dz_dzone:20171124111842j:plain

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

マクロに割り当てられるショートカットキーについて ~エクセル編~

f:id:dz_dzone:20170814233145j:plain

はじめに

マクロにはショートカットキーを割り当てることが出来ますが、標準で設定されているショートカットキーもあり、それじゃあ一体どのキーなら使っていいの?ってなりますよね。

マクロにショートカットキーを割り当てる方法

マクロにショートカットキーを割り当てるには、マクロの記録をするときに設定するか、マクロの作成後にマクロのオプション画面から設定します。

f:id:dz_dzone:20171117152011j:plain

f:id:dz_dzone:20171117152022j:plain

そもそもエクセルのショートカットキーってどういうのがあるの?

エクセルのショートカットについてはものすごく詳しく分かりやすいサイトがあるのでこちらをご覧下さい。
excel-hack.com

アルファベットを使っている物だけを抽出すると下表のようになります。

ショートカットキー 機能
Ctrl+A 全てを選択
Ctrl+B 文字を太字(ボールド)にする
Ctrl+C コピー
Ctrl+D 下方向へコピー
Ctrl+E 選択している列の周囲のデータを使用して、その列に値を入力
Ctrl+F 検索
Ctrl+G ジャンプ
Ctrl+H 置換
Ctrl+I 文字をイタリックにする
Ctrl+J (割り当てなし)
Ctrl+K ハイパーリンク
Ctrl+L テーブルの作成
Ctrl+M (割り当てなし)
Ctrl+N 新規ブックを作成
Ctrl+O ファイルを開く
Ctrl+P 印刷
Ctrl+Q クイック分析
Ctrl+R 右方向へコピー
Ctrl+S 上書き保存
Ctrl+T テーブルの作成
Ctrl+U 文字に下線(アンダーライン)を引く
Ctrl+V 貼り付け
Ctrl+W ブックを閉じる
Ctrl+X カット(切り取り)
Ctrl+Y 直前の作業を繰り返す
Ctrl+Z 直前の作業を元に戻す
Ctrl+Shift+F セルの書式ダイアログ(フォント)
Ctrl+Shift+P セルの書式ダイアログ(フォント)
Ctrl+Shift+U 数式バーの展開と格納

意外とたくさんあるものですね。割り当てられていないのは「J」と「M」の2つだけでした。

マクロに割り当てられるキーの種類

アルファベットは入れられるだろうなぁと直感的に思います。そこで数字や記号などを入れてみると次のように怒られます。

f:id:dz_dzone:20171117152330j:plain

入れられるのは結局アルファベットだけな訳です。つまり26個。

Shiftを組み合わせることが可能

入力できるのはアルファベットだけですが、図のようにShiftキーを組み合わせることが可能です。

f:id:dz_dzone:20171117152456j:plain

これで更に26個増えました。

標準設定は上書きできる

f:id:dz_dzone:20171117152845j:plain

52個のうち、標準で割り当てられているのが27個ありますが、実は標準設定のショートカットキーよりもマクロに設定したショートカットキーの方が優先されるのです。頻繁に使うショートカットキー以外だったら上書き設定しちゃってもいいと思います。ほら、使ったことないようなものもあるでしょう?

設定を削除すれば標準のショートカットキーに戻る

なお、設定画面から削除すれば標準のショートカットキーが復活しますので安心して下さい。

VBAのコードで設定することも可能

今までの内容は、VBAのコードで記述することも可能です。

コードの記述

Application.MacroOptions Macro:="マクロ名", _
                         Hasshortcutkey:=True, _
                         ShortcutKey:=A

引数の説明

  Macro          : VBAマクロのプロシージャ名
  Hasshortcutkey : ショートカットキーを持つかどうか
  ShortcutKey    : 指定するキー

指定するキーの記述と実際のショートカットキー

指定できるキーはアルファベットの「a~z」「A~Z」のみになります。小文字の場合は「Ctrl + 文字」、大文字の場合は「Shift + Ctrl + 文字」を意味します。

  アルファベット小文字 a ~ z : Ctrl + A ~ Z
  アルファベット大文字 A ~ Z : Shift + Ctrl + A ~ Z

もっと多くのショートカットキーを使いたい場合 (要VBA)

VBAのApplication.OnKeyメソッドを使えば、キーボード上のほとんどのキーを使ったショートカットキーを作ることが可能です。以下の記事を参照して下さい。
dz11.hatenadiary.jp

以上です。

選択範囲のコメント位置をリセットする

f:id:dz_dzone:20170814233145j:plain

行の切り取り&貼り付けを行っていると、コメント位置がぐちゃぐちゃになってくることが多いため、コメント位置をリセットするマクロを組んでみました。

Sub ResetCommentPosition()
  ' ** 選択範囲のコメント表示位置をリセットする **
  Dim CL As Range
  Dim T, L, W, T2, L2 As Long
  
  For Each CL In Selection
    ' セルにコメントがあれば処理
    If TypeName(CL.Comment) = "Comment" Then
      With CL
        T = .Top
        L = .Left
        W =.Width
      End With
    
      T2 = T - 7
      L2 = L + W + 11
    
      With CL.Comment.Shape
        .Top = T2
        .Left = L2
      End With
    End If
  Next CL
End Sub

コメントの位置(座標)については、オートシェイプと同様に画面左上を原点とした座標に依ります。現在のセルから基本情報(Top, Left, Width, Height)が得られるのでそれを利用します。

f:id:dz_dzone:20171117154333j:plain

f:id:dz_dzone:20171117154348j:plain

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

(Excel) VBAマクロでオートシェイプを描画するときの基礎知識

f:id:dz_dzone:20170814233145j:plain

ExcelのVBAマクロでオートシェイプを描画する場合の基礎知識を紹介します。

座標系

オートシェイプの座標系は画面(セル範囲内)の左上を原点として右方向がX軸、下方向がY軸の、値はポイント単位のものになります。

ポイント単位とは? (2018.12.17追記)

ポイントとはExcel(Office)内で使われる独自の単位で、ピクセルのようなものですがピクセルとは異なります。またフォントのサイズのことでもありません。ポイントとピクセルは以下の式が当てはまります。

1ピクセル=0.75ポイント  ( 1ポイント≒1.3334ピクセル)

座標系(図解)

f:id:dz_dzone:20181218104930j:plain

選択範囲から得られる座標値

描画をするにあたり、その位置を決定するために、選択範囲からいくつかの値が得られるようになっています。上図のように範囲選択をしたときに、

  • Selection.Left = X座標
  • Selection.Top = Y座標
  • Selection.Width = 幅のポイント値
  • Selection.Height = 高さのポイント値

の値が得られます。これらの値を計算することで、右上、左下、右下の座標やその他の座標を得ることも可能です。なお、値は単精度浮動小数点数型(Single型) Variant型になります。

  Dim T, L, W, H As Variant
  
  With Selection
    T = .Top
    L = .Left
    W = .Width
    H = .Height
  End With

オートシェイプを描画するメソッド

開始点(X1, Y1) , 終了点(X2, Y2) を指定するもの

  • AddLine : 直線を描画する
  • AddConnector : コネクタを描画する

左上隅の座標値(X, Y) と 幅(Width) と 高さ(Height) を指定するもの

  • AddShape : いろいろなオートシェイプを描画する (汎用)
  • AddCallout : 輪郭なしの吹き出しを描画する
  • AddTextBox : テキストボックスを描画する

座標値の配列を指定するもの

  • AddPlyLine : ポリラインを描画する
  • AddCurve:ベジェ曲線(スプライン曲線)を描画する

その他

  • BuildFreeform : フリーフォームを描画する

サンプル

工程表・ガントチャートのような直線を描画する

Sub AddLineSample()
  Dim T, L, W, H As Variant
  Dim X1, Y1, X2, Y2 As Variant

  With Selection
    T = .Top
    L = .Left
    W = .Width
    H = .Height
  End With
  
  X1 = L
  Y1 = T + H / 2
  X2 = L + W
  Y2 = T + H / 2

  With ActiveSheet.Shapes.AddLine(X1, Y1, X2, Y2)
    With .Line
      .ForeColor.RGB = RGB(255, 0, 0)
      .Weight = 4
      .DashStyle = msoLineSolid
    End With
  End With
End Sub

サンプルコードはやや冗長に書いていますが、この書き方がオートシェイプを追加するときの基本的な書き方になります。

f:id:dz_dzone:20171113153504j:plain

f:id:dz_dzone:20171113153522j:plain

選択範囲の中心に位置する最大の円を描画する

Sub DrawCircle()
  ' ** 選択範囲の中央に位置する最大の真円を描画する **
  Dim L, T, W, H As Variant
  Dim L2, T2, W2, H2 As Variant
    
  ' **選択範囲の基礎情報を取得
  With Selection
    L = .Left
    T = .Top
    W = .Width
    H = .Height
  End With

  ' **描画開始位置の設定
  If W > H Then
    L2 = L + (W - H) / 2
    T2 = T
  Else
    L2 = L
    T2 = T + (H - W) / 2
  End If

  ' **幅と高さの設定
  If W > H Then
    W2 = H
    H2 = H
  Else
    W2 = W
    H2 = W
  End If

  With ActiveSheet.Shapes.AddShape(msoShapeOval, L2, T2, W2, H2)
    With .Line
      .ForeColor.RGB = RGB(0, 0, 0)
      .Weight = 1
    End With
    With .Fill
      .ForeColor.RGB = RGB(255, 0, 0)
      .Patterned (msoPatternLightUpwardDiagonal)
    End With
  End With
End Sub

f:id:dz_dzone:20171117094023j:plain

f:id:dz_dzone:20171117094034j:plain

以上。

お詫びと訂正

  • 2018.12.17:当初、シェイプの座標の単位を「ピクセル(ポイント)」と説明していましたが、ピクセルとポイントは別の単位で、正しくは「ポイント」でした。相関関係を説明の上、訂正してお詫び致します
  • 2018.12.17:当初、Range.Top, Left, Width, Heightのデータ型を「単精度浮動小数点数型(Single型)」と説明していましたが、正しくは「Variant型」でした。訂正してお詫び致します

コメント枠の自動サイズ調整を一括で有効にするマクロ

f:id:dz_dzone:20170814233145j:plain

大量にあるシートのコメントが見えてたり見えてなかったりするものが多かったため、コメント枠の自動サイズ調整を一括で有効にするマクロを組んでみました。

アクティブシート内全てのコメント枠の自動サイズ調整を有効にする場合

Sub SetCommentAutoSizeInSheet()
  ' ** アクティブシートのコメント枠の自動サイズ調整を有効にする **
  For i = 1 To ActiveSheet.Comments.Count
    ActiveSheet.Comments(i).Shape.TextFrame.AutoSize = True
  Next i
End Sub

選択範囲のコメント枠の自動サイズ調整を有効にする場合

Sub SetCommentAutoSizeInSelection()
  ' ** 選択範囲のコメント枠の自動サイズ調整を有効にする **
  Dim CL As Range
  For Each CL In Selection
    ' セルにコメントがあれば処理
    If TypeName(CL.Comment) = "Comment" Then
      CL.Comment.Shape.TextFrame.AutoSize = True
    End If
  Next CL
End Sub

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

Enterキーを押したときに移動するセルの方向(上下左右)を変更するトグルマクロ

f:id:dz_dzone:20170814233145j:plain

通常、Enterキーを押したときに移動するセルの方向は「下」です。これを「右/上/左」に変更するには「Excelのオプション」を開いて「詳細設定」から変更することが可能ですが、やや面倒くさいです。ここでは、実行するたびに移動するセルの方向を変更するマクロを作ります。

f:id:dz_dzone:20171110110840j:plain

下→右→上→左→下……と切り替える場合

Sub SetMARD()
  ' ★★ Enterを押したら移動する方向を変更 ★★
  Select Case Application.MoveAfterReturnDirection
  Case xlDown
    Application.MoveAfterReturnDirection = xlToRight
    MsgBox "下→右に切り替えました。", vbOKOnly + vbInformation
  Case xlToRight
    Application.MoveAfterReturnDirection = xlUp
    MsgBox "右→上に切り替えました。", vbOKOnly + vbInformation
  Case xlUp
    Application.MoveAfterReturnDirection = xlToLeft
    MsgBox "上→左に切り替えました。", vbOKOnly + vbInformation
  Case xlToLeft
    Application.MoveAfterReturnDirection = xlDown
    MsgBox "左→下に切り替えました。", vbOKOnly + vbInformation
  End Select
End Sub
  • 「Enterキーを押したときに移動するセルの方向」は Application.MoveAfterReturnDirection で取得および設定が可能
  • 上=xlUp
  • 下=xlDown
  • 左=xlToLeft
  • 右=xlToRight

このコードでは、現状の MoveAfterReturnDirection を取得し、その値にしたがって処理を分けたいので、Select Caseを使っています。実行後、どのように方向が変わったのかダイアログを出しています。

f:id:dz_dzone:20171110111110j:plain

下→右→下……と切り替える場合

ほとんど同じです。万が一現状が上や左になっていた場合に備え、その場合は下になるようにコードを変更しました。

Sub SetMARD2()
  ' ★★ Enterを押したら移動する方向を変更 ★★
  Select Case Application.MoveAfterReturnDirection
  Case xlDown
    Application.MoveAfterReturnDirection = xlToRight
    MsgBox "下→右に切り替えました。", vbOKOnly + vbInformation
  Case xlToRight
    Application.MoveAfterReturnDirection = xlDown
    MsgBox "右→下に切り替えました。", vbOKOnly + vbInformation
  Case Else
    Application.MoveAfterReturnDirection = xlDown
    MsgBox "上/左→下に切り替えました。", vbOKOnly + vbInformation
  End Select
End Sub

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

InputBox関数を数値を入力するまで繰り返す処理

f:id:dz_dzone:20170814233145j:plain

InputBox関数で数値だけを入力させたい場合の処理です。

  Do
    StrIB = InputBox("開始数字?")
  Loop Until IsNumeric(StrIB) = True
  • Do~Loopを後判定で実行しています
  • 後判定は最低1回は処理を実行します(この場合はInputBox関数)
  • Untilは条件が成立するまで。Whileにすると条件が成立している間ずっと、になります
  • IsNumeric関数は"数値に見えるもの"は全てTrueを返します

厳密に配列宣言する場合

  Dim m As Long
  Dim StrIB As String
    
  Do
    StrIB = InputBox("開始数字?")
  Loop Until IsNumeric(StrIB) = True
  
  m = CLng(StrIB)
  • InputBox関数の返り値はStringです
  • 実際には数値(Long型)として使いたいのでClng関数で型変換しています
  • 少数を使いたい場合はSingleやDoubleなど適宜変更して下さい

※ちなみに小数を入力してLong型に変換すると小数点第一位で四捨五入されます。


以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

エクセルのコマンドボタン/自作VBAマクロの解説

f:id:dz_dzone:20170814233145j:plain

基本的なこと

  1. リボンとクイックアクセスツールバー

リボン/クイックアクセスツールバーのボタン

  1.  「Excelのオプション」を一発で開くボタン
  2. 「罫線」を手軽に引く/消すボタン
  3. いろいろな「貼り付け」ボタン
  4. 「印刷」に関連したボタン

 

エクセルVBA

基本

  1. VBAマクロ作成を気軽に始めてみよう! ~Excel編~
  2. VBAマクロをリボンやクィックアクセスツールバーのボタンに割り当てる
  3. (VBScript) XLSTARTフォルダを開く (無い場合は作成して開く)
  4. (PowerShell) XLSTARTフォルダを開く、フォルダが無い場合は作成して開く

ループ処理

  1. InputBox関数を数値が入力されるまで繰り返す処理(Do~Loop後判定)

分岐処理

  1. Enterキーを押した後に移動するセルの方向を変更するトグルマクロ(Select Case)

セル

  1. よく使うブック、シート、セルの指定方法
  2. 選択範囲、選択シートに対する処理の定型的書き方
  3. 最終セル、最終行、最終列を選択する
  4. セルの内容を調べる

シート

  1. よく使うブック、シート、セルの指定方法
  2. 選択範囲、選択シートに対する処理の定型的書き方
  3. シートのインデックスと名前と数、表示と非表示の制御

ブック

  1. よく使うブック、シート、セルの指定方法
  2. 作業中のブックを含むフォルダを開く

コマンド

  1. 縮小して全体を表示する
  2. ゼロ値の表示・非表示トグル

セルのコメント

  1. 選択範囲にコメントを一括で追加する
  2. コメントのみ貼り付けるコマンドをを作成する
  3. セルのコメントを一括で表示・非表示にする
  4. 選択範囲のコメント位置をリセットする
  5. コメント枠の自動サイズ調整を有効にする
  6. セルのコメントを編集する(1) InputBox関数編-1
  7. セルのコメントを編集する(2) InputBox関数編-2 複数行対応
  8. セルのコメントを編集する(3) ユーザーフォーム編-1

シェイプ

  1. VBAでオートシェイプを描画する際の基礎

 

 

セルのコメントを編集する(3) ユーザーフォーム編-1

f:id:dz_dzone:20170814233145j:plain

前回までは InputBox関数を使ってセルのコメント編集を行いました。複数行に対応はしましたが、その方法は区切り文字を使うというちょっとまどろっこしいものでした。もう少し編集画面をなんとか出来ないかと思ったのがこの記事を作るきっかけです。

入力ダイアログにユーザーフォームを使う

ユーザーフォームの作り込み

早速作っていきます。Visual Basic Editorのプロジェクトウィンドウ、個人用マクロブック(PERSONAL.XLSB)で右クリックから挿入>ユーザーフォームを選びます。

f:id:dz_dzone:20171025095913j:plain

UserForm1が作られました。

f:id:dz_dzone:20171025100048j:plain

ツールボックスを表示させます。

f:id:dz_dzone:20171025140309j:plain

テキストボックスを配置します。

f:id:dz_dzone:20171025140322j:plain

f:id:dz_dzone:20171025140334j:plain

ボタンを配置します。

f:id:dz_dzone:20171025140343j:plain

f:id:dz_dzone:20171025140354j:plain

オブジェクト名を変更する

プロパティウィンドウでオブジェクト名を分かりやすい名前に変更します。オブジェクト名はコード入力でも使います。ここでは以下のようにしました。

  • ユーザーフォーム:UF_EditComment
  • テキストボックス:TB_EditComment
  • コマンドボタン:Btn_EditCommentOK

オブジェクトのプロパティを設定する

オブジェクトのプロパティはある程度はプロパティウィンドウでも設定できますし、コード記述で設定することも可能です。ここでは以下のようプロパティウィンドウで設定しました。

ユーザーフォーム
プロパティ 内容
(オブジェクト名) UF_EditComment
Caption コメント編集
  • Caption : 表示される名称
テキストボックス
プロパティ 内容
(オブジェクト名) TB_EditComment
MultiLine True
WordWrap False
EnterKeyBehavior True
ScrollBars fmScrollBarsBoth
IMEMode fmIMEModeHiragana
TabIndex 0
  • MultiLine : テキストボックスを複数行対応させるか (True/False)
  • WordWrap : テキストボックスの右端で自動改行させるか (True/False)
  • EnterKeyBehavior : Enterキーを押したときにテキストボックス内で改行するか (True/False)
  • ScrollBars : fmScrollBarsBoth=縦横のスクロールバーを有効にする
  • IMEMode : fmIMEModeHiragana=テキストボックスで自動的にIMEをひらがなモードにする
  • TabIndex : TABキーを押したときにフォーカスが移動する順番 (0, 1, 2, ……)
コマンドボタン
プロパティ 内容
(オブジェクト名) Btn_EditCommentOK
Caption OK
TabIndex 1

f:id:dz_dzone:20171027091032j:plain

標準モジュールのコード

まず標準モジュールにコードを書きます。(2017.10.31追記あり)

Sub EditComment()
  ' ** ユーザーフォーム版コメント編集 **
  If TypeName(ActiveCell.Comment) = "Comment" Then
    ' ** コメントがある場合
    
    Dim BoolCommentVisible As Boolean
  
    ' ** 既存コメントの表示状態を記憶
    BoolCommentVisible = ActiveCell.Comment.Visible
  
    ' ** 既存コメントが非表示なら表示させる
    If BoolCommentVisible = False Then
      ActiveCell.Comment.Visible = True
    End If
        
    ' ** ユーザーフォームをモーダルで表示 **
    UF_EditComment.Show (vbModal)
  
    ' ** 既存コメントの状態を回復
    If BoolCommentVisible = False Then
      ActiveCell.Comment.Visible = False
    End If
  
  Else
    
    MsgBox "コメントがありません。", vbOKOnly + vbExclamation
  
  End If
End Sub

ここでは対象セルのコメントの有無を判定して、あればユーザーフォーム:UF_EditCommentをモーダルで表示するよう指示しています。

(2017.10.31追記)また、コメントが表示状態でないとエラーになるため、コメントが非表示状態の場合はユーザーフォームを表示させる前にコメントを表示状態にしています。フォームが閉じられるとコメントを非表示状態に戻します。(追記ここまで)

  • Modal (モーダル) : フォーカスをユーザーフォームのみに当て、エクセルのメイン画面に当てない
  • Modeless (モードレス) : フォーカスをユーザーフォームとエクセルのメイン画面の両方に当てる

例としては、セルの書式設定ウィンドウがモーダル、検索/置換ウィンドウがモードレスです。

起動テスト

マクロ:EditCommentの起動テストをしてみます。

セルにコメントがある場合、このようにユーザーフォームが表示されます。
f:id:dz_dzone:20171027094416j:plain

テキストボックスに文字を入力して挙動を確認して下さい。

  • テキストボックス内で改行できるか?
  • EnterでフォーカスがOKに飛んでしまわないか?
  • テキストボックスの右端で自動改行されてしまわないか?
  • 横スクロールバーが出てくるか?
  • 縦スクロールバーが出てくるか?

また、TABキーの挙動も確認して下さい。

  • TABを押すたびにテキストボックスとOKボタンにフォーカスが移動するか?

なお、この状態でOKボタンを押しても何の反応もありません。これはユーザーフォームのコードを入力していないからです。右上の×ボタンでフォームを閉じることだけが出来ます。フォームを閉じて次に進みます。

ユーザーフォーム(UF_EditComment)のコード

ユーザーフォームのコードウィンドウを表示させます。
f:id:dz_dzone:20171027095905j:plain

ユーザーフォームの初期化

ユーザーフォームの初期化は決まり文句です。ユーザーフォームを開いたときに処理しておきたい内容を記します。

Private Sub UserForm_Initialize()

End Sub

オブジェクトのプロパティをプロパティウィンドウで設定しなかった場合はユーザーフォームの初期化の中でプロパティを記述する必要があります。ここではプロパティウィンドウで設定済みなのでコードはありません。

今回は必要な処理は下記になります。

  • 現在のセルを記憶させる
  • 既存のコメントをテキストボックスに表示する
Private StrActiveCell As String   ' ** ユーザーフォームの初期化

' ** ユーザーフォームの初期化 **
Private Sub UserForm_Initialize()
    
  ' ** 現在のセルを記憶 **
  StrActiveCell = ActiveCell.Address
    
  ' ** 既存のコメントをテキストボックスに表示 **
  TB_EditComment.Text = ActiveCell.Comment.Text
    
End Sub

OKボタンを押したときの処理

次に、OKボタンを押したときの処理を書きます。

Private StrComment As String

Private Sub Btn_EditCommentOK_Click()   ' ** OKボタンを押したときの挙動
  
  ' ** テキストボックスの内容を変数に代入
  StrComment = TB_EditComment.Text
   
  ' ** 編集内容が空白ならキャンセルとみなしてフォームを閉じる
  If StrComment = "" Then
    Unload UF_EditComment
    Exit Sub
  End If

  ' **セルのコメント内容を上書きする
  With Range(StrActiveCell)
    With .Comment
      .Shape.Select True
      .Text Text:=StrComment
    End With
    .Activate
  End With
    
  ' **フォームを閉じる
  Unload UF_EditComment
End Sub

完成したマクロの動作を確認する

f:id:dz_dzone:20171027114949j:plain

f:id:dz_dzone:20171027115002j:plain

f:id:dz_dzone:20171027115014j:plain

完成しました。

次回:もう少し便利にいろいろ改造してみる

次回は、このユーザーフォームを元にもう少し便利に改造していきたいと思います。


以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

(PowerShell) XLSTARTフォルダを開く、フォルダが無い場合は作成して開く

f:id:dz_dzone:20171026093517p:plain

PowerShellも覚えたいなと思い、少しずつ調べています。PS5対応のリファレンス本とかあればいいんですが……。

「XLSTARTフォルダを開く」をPowerShellで作ってみました

さて、以前VBScriptで作った、
dz11.hatenadiary.jp

をPowerShellで作ってみました。

#XLSTARTフォルダの有無を確認して、あれば開く、なければ作って開く。

$UserProfile = $env:UserProfile

If(Test-Path $UserProfile\AppData\Roaming\Microsoft\Excel\XLSTART){
    #フォルダが存在する場合
    Invoke-Item $UserProfile\AppData\Roaming\Microsoft\Excel\XLSTART
}else{
    #フォルダが存在しない場合
    New-Item $UserProfile\AppData\Roaming\Microsoft\Excel\XLSTART -ItemType Directory
    Invoke-Item $UserProfile\AppData\Roaming\Microsoft\Excel\XLSTART
}

上記のコードをWindows PowerShell ISEにコピー&ペーストして任意のファイル名でps1ファイルを作成して下さい。

f:id:dz_dzone:20171026094537j:plain

スクリプトの実行セキュリティポリシーの変更が必要

PowerShellではセキュリティの関係から初期状態ではスクリプトが動作しません。したがって、実行ポリシーの変更が必要です。

  Set-ExecutionPolicy RemoteSigned

詳細はこちらを参照して下さい。
www.atmarkit.co.jp

スクリプトファイルを開く

デスクトップ等にあるスクリプトファイルはダブルクリックしても起動できません。PowerShellスクリプトを開くには、

f:id:dz_dzone:20171026094303j:plain

このようにします。

VBScript版に比べるとフォルダが開く速さが格段に違いますね。


以上。

セルのコメントを編集する(2) InputBox関数編-2 複数行対応

f:id:dz_dzone:20170814233145j:plain

InputBox関数によるセルのコメント編集マクロ、前回は1行コメントの編集に対応したものを作成しました。今回はこれを元に複数行に対応させてみようと思います。

前回までのコード

Sub KomeHen()
  If TypeName(ActiveCell.Comment) = "Comment" Then
  
    ' ** セルにコメントがあれば処理する **
    Dim StrHen As String
    StrHen = InputBox(Prompt:="※改行は出来ません。", _
                      Title:="コメント編集(一行版)", _
                      Default:=ActiveCell.Comment.Text)
        
    ' ** キャンセル処理
    If StrPtr(StrHen) = 0 Then
      Exit Sub
    End If
    
    ' ** コメント内容を変更する処理 **
    With ActiveCell
      With .Comment
        .Shape.Select True
        .Text Text:=StrHen
      End With
      .Activate
    End With
    Exit Sub
  Else
    ' ** セルにコメントがなかったら終了する **
    MsgBox "コメントがありません。", vbOKOnly + vbExclamation
    Exit Sub
  End If
End Sub

単純に vbCrLf や Chr(13) & Chr(10) とはいかない

改行と言えば vbCrLf や Chr(13) & Chr(10) などが思いつきますが、InputBoxにこれらを単純に入力しても改行にはなりません。

f:id:dz_dzone:20171024105000j:plain
f:id:dz_dzone:20171024105010j:plain

全然出来てませんね。入力したとおりのコメントになってしまいました。

文字列を分割するSplit関数

文字列を分割するのにはSplit関数があります。区切り文字を設定することで文字列を複数文字列に分割することが可能です。分割された文字列は1次元配列に入ります。

    ' ** Split
    Dim RTmp As Variant
    RTmp = Split(StrHen, ",")

返り値が1次元配列なのでVariant型とします。区切り文字を","として StrHen を分割し、1次元配列 RTmp に代入します。

    StrHen = RTmp(0)
    For i = 1 To UBound(RTmp)
      StrHen = StrHen & vbCrLf & RTmp(i)
    Next i

StrHen を書き換えます。まず1行目を代入し、2行目以降は改行(vbCrLf)と文字列を追加していきます。このとき、最大分割数(=配列の最大数)が必要になりますので、UBound関数で最大値を求めます。

f:id:dz_dzone:20171024115246j:plain
f:id:dz_dzone:20171024115256j:plain

既存のコメントが複数行だった場合の処理

既存のコメントが複数行だった場合、InputBoxに代入される文字列は見た目は1行になったように見えます。

f:id:dz_dzone:20171024131126j:plain

この状態で別の場所に改行を入れてみます。「1行目」「2行目」「3行目」の後ろに","を入れて改行します。

f:id:dz_dzone:20171024132001j:plain

指定した箇所で改行されましたが、元々の改行が残っていました。

f:id:dz_dzone:20171024132014j:plain

ワークシート関数 Clean を使う

InputBoxに既存のコメントを表示する際に、不要な改行を削除するため、ワークシート関数「CLEAN」を使用します。

    Dim DefaultComment As String
    DefaultComment = WorksheetFunction.Clean(ActiveCell.Comment.Text)
        
    StrHen = InputBox(Prompt:="※改行したい位置に,を入力して下さい。", _
                      Title:="コメント編集(改良版)", _
                      Default:=DefaultComment)

「1行目」「2行目」「3行目」の後ろに","を入れて改行します。

f:id:dz_dzone:20171024132001j:plain

意図したとおりに改行されました。

f:id:dz_dzone:20171024133200j:plain

完成コード

Sub KomeHen2()
  If TypeName(ActiveCell.Comment) = "Comment" Then
    ' ** セルにコメントがあれば処理する **
        
    Dim StrHen As String
    Dim DefaultComment As String
        
    DefaultComment = WorksheetFunction.Clean(ActiveCell.Comment.Text)
        
    StrHen = InputBox(Prompt:="※改行したい位置に,を入力して下さい。", _
                      Title:="コメント編集(改良版)", _
                      Default:=DefaultComment)
        
    ' ** 空白ならキャンセルとする
    If StrPtr(StrHen) = 0 Then
      Exit Sub
    End If
    
    ' ** Split
    Dim RTmp As Variant
    RTmp = Split(StrHen, ",")
        
    StrHen = RTmp(0)
    For i = 1 To UBound(RTmp)
      StrHen = StrHen & vbCrLf & RTmp(i)
    Next i
        
    ' ** コメント内容を変更する処理 **
    With ActiveCell
      With .Comment
        .Shape.Select True
        .Text Text:=StrHen
      End With
        .Activate
    End With
    Exit Sub
  Else
    ' ** セルにコメントがなかったら終了する **
    MsgBox "コメントがありません。", vbOKOnly + vbExclamation
    Exit Sub
  End If
End Sub

次回:ユーザーフォームを使ったセルのコメント編集

これでInputBox関数を使ったセルのコメント編集マクロは完成です。次回は、ユーザーフォームを使ったセルのコメント編集マクロを作ろうと思います。

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。

セルのコメントを編集する(1) InputBox関数編-1

f:id:dz_dzone:20170814233145j:plain

個人的に仕事で大量のコメントを追加しては日々編集しているんですが、こう大量にあるとエクセル標準のコメント編集機能ではちょっとまどろっこしいところがあります。何か入力ダイアログみたいなもので編集できると便利だなと思ったので今回はこれをやってみたいと思います。

入力ダイアログにInputBox関数を使う

まず最初に考えたのは文字列を入力できるInputBox関数です。まずは簡単にコードを組んでみます。

Sub KomeHen0()
  Dim StrHen As String
  
  ' ** 文字列を入力 **
  StrHen = InputBox(Prompt:="文字を入力", Title:="コメント編集")
  
  ' ** コメント内容を変更する処理 **
  With ActiveCell
    With .Comment
      .Shape.Select True
      .Text Text:=StrHen
    End With
    .Activate
  End With
End Sub

f:id:dz_dzone:20171024093109j:plain
f:id:dz_dzone:20171024093120j:plain
f:id:dz_dzone:20171024093127j:plain

コードの説明

With~End Withをネスト(入れ子構造)してますが、内容は簡単です。
ActiveCellに対し「.Comment.Shape.Select True」は、セルのコメントを選択状態にします。その上で「.Comment.Text Text:=StrHen」でコメントのテキストをStrHenにする」を実行します。最後に「ActiveCell.Activate」で選択状態をコメントからセルに戻します。

基本のコードが出来ました。

エラー処理を追加

現状のコードですと、コメントが設定されていないセルで実行するとエラーになりますので、エラー処理を加えます。

  If TypeName(ActiveCell.Comment) = "Comment" Then
    ' ** セルにコメントがあれば処理する **
  Else
    ' ** セルにコメントがなかったら終了する **
    MsgBox "コメントがありません。", vbOKOnly + vbExclamation
    Exit Sub
  End If

コメントの有無で処理を変えます。

キャンセル処理

現状のコードでキャンセルを押すとエラーが発生します。

f:id:dz_dzone:20171024095523j:plain

InputBox関数のキャンセル処理は定型的な書き方があります。私はこちらを参考にしました。

www.relief.jp

これを踏まえて

    ' ** キャンセル処理
    If StrPtr(StrHen) = 0 Then
      Exit Sub
    End If

既に入力されているコメントをInputBoxに代入する

コメントをまるっきり書き換える場合は今のままで良いのですが、既に入力されているコメントを編集する場合、また同じような文字列を入力するのは面倒です。そこで既に入力されているコメントをInputBoxに代入しておきます。

  StrHen = InputBox(Prompt:="文字を入力", _
                    Title:="コメント編集", _
                    Default:=ActiveCell.Comment.Text)

f:id:dz_dzone:20171024100609j:plain

次回:複数行に対応させる

今回のコードですと一行のコメントにしか対応していません。次回は複数行に対応させてみようと思います。

今回の完成コード

Sub KomeHen()
  If TypeName(ActiveCell.Comment) = "Comment" Then
  
    ' ** セルにコメントがあれば処理する **
    Dim StrHen As String
    StrHen = InputBox(Prompt:="※改行は出来ません。", _
                      Title:="コメント編集(一行版)", _
                      Default:=ActiveCell.Comment.Text)
        
    ' ** キャンセル処理
    If StrPtr(StrHen) = 0 Then
      Exit Sub
    End If
    
    ' ** コメント内容を変更する処理 **
    With ActiveCell
      With .Comment
        .Shape.Select True
        .Text Text:=StrHen
      End With
      .Activate
    End With
  Else
    ' ** セルにコメントがなかったら終了する **
    MsgBox "コメントがありません。", vbOKOnly + vbExclamation
    Exit Sub
  End If
End Sub

以上。

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。