(Excel VBA) Shift, Ctrl, A～Z以外の組み合わせでマクロのショートカットキーを作成する

はじめに

以前の記事で「マクロに割り当てることの出来るショートカットキーの一覧」を紹介しましたが、そちらの方法では「Ctrl」「Shift」「A～Z」キーの組合せでしかショートカットキーを作られませんでした。（どうでもいいけど、※同じテーマの記事を間違って2回書いてしまったｗ）
dz11.hatenadiary.jp

しかし、別の方法を使うことで、「Ctrl」「Shift」「Alt」の修飾キーと「キーボード上のほとんどのキー」の組合せでショートカットキーを作ることが可能になります。

Application.OnKeyメソッドでマクロのショートカットキーを作成する

VBAには Application.OnKey というメソッドがあり、これを使うことで Application.MacroOptionsでは成し得なかった様々なキーボードショートカットを作成することが可能です。
docs.microsoft.com

OnKey メソッドの構文

Application.OnKey Key [, Procedure]

  Key          必須          String型     ショートカットキーにしたいキーの組合せ文字列
  Procedure    オプション    Variant型    実行したいマクロのプロシージャ名
               第2引数を省略した場合、指定した組合せ文字列の標準の動作に戻る

サンプル

' ** Shift+Ctrl+← に を割り当てする
Application.OnKey "+^{LEFT}", "TracePrecedents"

' ** Shift+Ctrl+← の動作をデフォルトに戻す
Application.OnKey "+^{LEFT}"

Application.OnKeyメソッドはどこに記述するか

ショートカットキーはExcelの起動時に設定されることが好ましいです。したがって、個人用マクロブック(PERSONAL.XLSB)の「Auto_Open()」または「Workbook_Open()」に記述するのが良いでしょう。どちらも起動時に自動で実行される特殊なプロシージャです。

記述するプロシージャ	プロシージャの場所
Auto_Open()	個人用マクロブックの標準モジュール
Workbook_Open()	Microsoft Excel Objects内のThisWorkbook

Application.OnKey メソッドで使えるキーおよび記述方法の一覧

OnKeyメソッドで指定できるキーを文字で指定します。指定できるキーは一つで、それとは別に三つの修飾キーを組み合わせて付与することが可能です。

修飾キー

修飾キーおよび記述方法は以下の通りで、また自由に組み合わせることが出来ます。

修飾キー	記述方法	備考
Shift	+	正符号
Ctrl	^	カレット,べき乗記号
Alt	%	パーセント記号

Escとファンクションキー

キー	文字列またはキーコード	備考
Esc	{ ESCAPE} または {ESC}	エスケープ
F1 ～ F15	{F1} ～ {F15}	ファンクションキー

QWERTY部分

キー	文字列またはキーコード	備考
Tab	{TAB}	タブ
CapsLock	{CAPSLOCK}	キャプスロック
1 ～ 0	1 ～ 0	QWERTY側数字キー
-	-	ハイフン
a ～ z	a ～ z	アルファベット
@	@	アットマーク
[	[	大かっこ
;	;	セミコロン
:	:	コロン
]	]	大かっこ閉じ
,	,	コンマ
.	.	ピリオド
/	/	スラッシュ
\	\	バックスラッシュ
BackSpace	{BACKSPACE} または {BS}	バックスペース
Enter	~	QWERTYのEnter = チルダ

PrintScreenの付近

キー	文字列またはキーコード	備考
ScrollLock	{SCROLLLOCK}	スクロールロック
Break	{BREAK}	ブレーク

Insertの付近

キー	文字列またはキーコード	備考
Ins	{INSERT}	インサート
Delete または Del	{DELETE} または {DEL}	デリート
Home	{HOME}	ホーム
End	{END}	エンド
PageUp	{PGUP}	ページアップ
PageDown	{PGDN}	ページダウン

カーソルキー

キー	文字列またはキーコード	備考
↑	{UP}	上
↓	{DOWN}	下
←	{LEFT}	左
→	{RIGHT}	右

テンキー

NumLock	{NUMLOCK}	Numロック
0	{96}	テンキーの0
1	{97}	テンキーの1
2	{98}	テンキーの2
3	{99}	テンキーの3
4	{100}	テンキーの4
5	{101}	テンキーの5
6	{102}	テンキーの6
7	{103}	テンキーの7
8	{104}	テンキーの8
9	{105}	テンキーの9
Enter	{ENTER}	テンキーのEnter

Application.OnKeyメソッドの応用：キーを無効化する

Application.OnKeyメソッドを応用して、キーを無効化することが出来ます。
例えば、ファンクションキーの「F1」はヘルプを表示させますが、近年はオンラインヘルプになり、表示も遅いし、セルの編集をしようと「F2」を押そうとして間違って押してしまうことが多いキーですよね。そこで「F1」キーを無効化してみたいと思います。

' ** F1キーを無効にする
Application.OnKey "{F1}", ""

これで「F1」キーは無効になります。「F1」キーを押しても何の反応もなくなります。

おわりに

このように、多くのキーの組合せでショートカットキーが作れますので、いろいろ試してみてはいかがでしょうか。

(Excel VBA)選択範囲の数式の参照形式(※参照タイプとも)を絶対参照と相対参照で相互に変換する

参照形式を変換するメソッド

Application.ConvertFormulaメソッド

構文

Application.ConvertFormula(Formula, FromReferenceStyle _
                           [,ToReferenceStyle] [,ToAbsolute] [,RelativeTo])

引数

  Formula：変換する数式を含む文字列を指定。 
           必ず有効な数式を指定し、数式の先頭には等号 (=) を付ける
    指定例： Formula:=Range("A1").Formula

  FromReferenceStyle：変換元の参照スタイル。xlReferenceStyle列挙。指定必須
  ToReferenceStyle  ：変換先の参照スタイル。xlReferenceStyle列挙。省略可
                     ※省略した場合、変換元の参照形式と同じ
    指定例： FromReferenceStyle:=xlA1

  ToAbsolute：変換先の参照タイプを指定。xlReferenceType列挙。
              相対指定と絶対指定の変換が可能。
              これを"省略"すると参照スタイルの変換は行われない
    指定例： ToAbsolute:=xlAbsolute

  RelativeTo：R1C1形式の相対参照の基点となるRangeオブジェクトを指定する

xlReferenceStyle列挙

名前	値	説明
xlA1	1	A1形式の参照スタイル
xlR1C1	-4150	R1C1形式の参照スタイル

xlReferenceType列挙

名前	値	説明
xlAbsolute	1	行・列ともに絶対参照
xlAbsRowRelColumn	2	行は絶対参照、列は相対参照
xlRelRowAbsColumn	3	行は相対参照、列は絶対参照
xlRelative	4	行・列ともに相対参照

サンプルコード

選択範囲の数式の相対参照を絶対参照に変換する

Public Sub RelToAbs()
  '**ReferenceStyle : from Relative to Absolute
  Dim myCell As Range
  For Each myCell In Selection
    myCell.Formula = Application.ConvertFormula(Formula:=myCell.Formula, _
                                                FromReferenceStyle:=xlA1, _
                                                ToAbsolute:=xlAbsolute)
  Next myCell
End Sub

選択範囲の数式の絶対参照を相対参照に変換する

Public Sub AbsToRel()
  '**ReferenceStyle : from Absolute to Relative
  Dim myCell As Range
  For Each myCell In Selection
    myCell.Formula = Application.ConvertFormula(Formula:=myCell.Formula, _
                                                FromReferenceStyle:=xlA1, _
                                                ToAbsolute:=xlRelative)
  Next myCell
End Sub

指定したA1形式のセル指定文字列をR1C1形式に変換するファンクション

Public Function A2RC1(ByVal myA1 As String) As String
  '**A1形式をR1C1形式に変換 ※列最大 XFD=16384, 行最大 1048576
  On Error GoTo ErrorHundler
  A2RC1 = Application.ConvertFormula(Formula:=myA1, _
                                     FromReferenceStyle:=xlA1, _
                                     ToReferenceStyle:=xlR1C1, _
                                     ToAbsolute:=xlAbsolute)
  Exit Function
ErrorHundler:
  MsgBox "書式：A2RC1(ByVal myA1 As String) As String" & vbCrLf & _
         "エラー内容：引数が間違っている。A1～XFD1048576の範囲", vbCritical
  Err.Clear
  A2RC1 = "#ERROR"
End Function

サンプルの結果

’**VBEのイミディエイトウィンドウにて

Debug.Print A2RC1("A1:Z10")
R1C1:R10C26

どんなときにA1形式をR1C1形式に変換する？

旧形式のマクロ言語「Excel4Macro」でセルを指定するときにR1C1形式で指定しなくてはなりません。Excel4Macroはファイルを開かずに中のセル内容などにアクセスが可能で、データ抽出型マクロの速度を大幅に上げることが可能です。これを使うときに馴染みのあるA1形式をR1C1形式に変換するのです。

例：D:\myFolder\myBook1.xlsx の Sheet1, K4セル ＝ R4C11 の値を取得する場合

Sub Sample1()
  Dim myValue
  myValue = ExecuteExcel4Macro("'D:\myFolder\[myBook1.xlsx]Sheet1'!R4C11")
  Debug.Print "myBook.xlsxのSheet1のK4セルの値は " & myValue
End Sub

Sub Sample2()
  Dim myValue, myFolder, myBook, mySheet, myItem
  myFolder = "D:\myFolder\"
  myBook = "myBook1.xlsx"
  mySheet = "Sheet1"
  myItem = "'" & myFolder & "[" & myBook & "]" & mySheet & "'!" & A2RC1("K4")
  
  myValue = ExecuteExcel4Macro(myItem)
  Debug.Print "myBook.xlsxのSheet1のK4セルの値は " & myValue
End Sub

以上。

(Office VBA)【決定版】マルチディスプレイ環境でユーザーフォームを親ウィンドウの中央に表示する・後編

はじめに

前編では、全体の処理の流れを追いました。今回は実際どこにどのように記述すれば良いかを解説します。

これまでのやり方は乱雑だった……

実はこれまでユーザーフォームを中央に表示させるのに、呼び出し元のマクロの方に中央表示の記述（内容は前回の処理の流れ）をしていました。しかしそれだといちいち同じコードを記述しなくてはならず、元のマクロのコードが乱雑になってしまいます。また、任意のユーザーフォーム名を引数にした中央表示マクロのFunctionプロシージャ化も(私の技量では)うまく作れませんでした。

発想を逆転してみた

ところが、実はもっと簡単な方法があったのです。それは……ユーザーフォームそのものに記述することでした。

サンプルファイルの構成

項目	名前
マクロ付きExcelブック	Sample_Show_UF_Center.xlsm
起動マクロ(標準モジュール)	Sample_Macro_01
ユーザーフォーム	UF_Please_Wait

起動マクロのコード

起動マクロは、標準モジュール Module1 に記述します。要はいつも作っているマクロということです。

Public Sub Sample_Macro_01()
  ' ** サンプルマクロ 01 **

  '  "しばらくお待ちください"の表示 ※時間がかかる処理の直前に呼び出す
  UF_Please_Wait.Show vbModeless

  '  処理 : 時間がかかるような内容 ※例：5秒処理を止める
  Application.Wait Now() + TimeValue("00:00:05")
  
  '  処理完了：ユーザーフォームを閉じる
  Unload UF_Please_Wait
End Sub

ユーザーフォーム UF_Please_Waitのコード

中央表示設定用のSubプロシージャ

コードエディタの一番後ろに記述すると邪魔になりません。

Private Sub UFPositionCenter()
  '**ユーザーフォームを親ウィンドウの中央に表示する
  
  '**変数(T=Top,L=Left,W=Width,H=Height,AW=ActiveWindow,UF=UserForm)
  Dim T_AW As Long, L_AW As Long, W_AW As Long, H_AW As Long
  Dim T_UF As Long, L_UF As Long, W_UF As Long, H_UF As Long
  
  '**親ウィンドウの位置とサイズを取得
  With ActiveWindow
    T_AW = .Top
    L_AW = .Left
    W_AW = .Width
    H_AW = .Height
  End With
  
  '**UFのサイズを取得
  W_UF = Me.Width
  H_UF = Me.Height

  '**UFの表示位置を計算
  T_UF = T_AW + ((H_AW - H_UF) / 2)
  L_UF = L_AW + ((W_AW - W_UF) / 2)
  
  '**UFの表示位置を設定
  Me.StartUpPosition = Manual
      '**Top,Left指定時に必須(ないとLeftがずれる)
  Me.Top = T_UF
  Me.Left = L_UF
End Sub

Private Sub UserForm_Initialize()内に以下を記述

'**UFを親ウィンドウの中央に配置する設定
Call UFPositionCenter

これで親ウィンドウがどこにあろうとユーザーフォームが親ウィンドウの中央に来ます。マルチディスプレイ環境でも大丈夫です。

サンプルの実行例

別のサンプルの実行例(Ｌ字型の3画面)

たとえばこういう画面構成。メインディスプレイは「3」です。

このように表示されます。

サンプルファイルのダウンロード

今回のサンプルファイルは こちらからダウンロード 可能です。

おわりに

いかがでしたか？ 個人的にいろいろ試行錯誤して試してきましたが、この方法が一番楽に出来ると思います。是非使ってみて下さい。

唯一面倒くさいといえば、ユーザーフォームを作るごとに中央表示設定用コードをコピペすることくらいですね。

以上です。

(Office VBA)【決定版】マルチディスプレイ環境でユーザーフォームを親ウィンドウの中央に表示する・前編

はじめに

マルチディスプレイ環境(マルチモニタ環境)で、ユーザーフォームを使ったマクロを表示させると、現在ブックを表示させているディスプレイとは別のディスプレイにフォームが表示されてしまうことがあります。これを防ぎ、現在開いているブックの中央付近に表示させる方法を前編・後編の2回に分けて紹介します。

基本的な処理の流れ

流れとしては以下のようになります。

1. 親ウィンドウ(ブック)の表示座標とサイズを取得する
2. ユーザーフォームをロードする（表示はまだ）
3. ユーザーフォームのサイズを取得する
4. ユーザーフォームの表示位置を計算する
5. 位置を指定してユーザーフォームを表示する

各処理のコード

親ウィンドウの表示座標とサイズを取得する

WindowsオブジェクトのTop, Left, Width, Height の各プロパティを使います。

' ** 親ウィンドウの位置を取得
Dim Tx1 As Long, Lx1 As Long, Wx1 As Long, Hx1 As Long
With ActiveWindow
  Tx1 = .Top
  Lx1 = .Left
  Wx1 = .Width
  Hx1 = .Height
End With

注意点：ここで使う座標プロパティはWindowオブジェクトの座標です。Workbookオブジェクトの座標はワークシート座標(A1セルの左上を原点とした座標)で、ワークシートにオートシェイプなどを描画する目的に使うものです。したがって、ユーザーフォームを中央に表示させるような用途には使用できません。

オブジェクト	用途
Windowオブジェクトの座標値	ウィンドウやフォームなどをディスプレイのどの位置に表示させるか
Workbookオブジェクトの座標値	オートシェイプなどをワークシートのどの位置に表示させるか

取得したTop, Leftがマイナス値になると、本当は計算に全く問題ないはずなのになぜか表示がおかしくなることが多いので、マイナス値を取得した場合は強制的にゼロにしています。再度確認してみたらマイナスでも大丈夫でした。後述する UserForm.StartupPosition = Manual が抜けてただけだったようです。

ユーザーフォームをロードする

' ** ユーザーフォームをロード
Load myUserForm

ユーザーフォームのサイズを取得するために予めロードしておきます。この時点ではロードしているだけで表示させていません。

ユーザーフォームのサイズを取得する

前提としてユーザーフォームのデザイナ上あるいはコードでWidthとHeightが設定されているものとします。

'** UFのサイズを取得
Dim Tx2 As Long, Lx2 As Long, Wx2 As Long, Hx2 As Long
With myUserForm
  Wx2 = .Width
  Hx2 = .Height
End With

ユーザーフォームの表示座標を計算する

下図は概念図です。myUserFormは親ウィンドウ(ActiveWindows)の中央にあります。

'** ユーザーフォームの表示位置を計算
Tx2 = Tx1 + ((Hx1 - Hx2) / 2)
Lx2 = Lx1 + ((Wx1 - Wx2) / 2)

座標を指定してユーザーフォームを表示する

'** ユーザーフォームを表示
With myUserForm
  .StartUpPosition = Manual  '**Top,Leftを指定するときに必ず必要
  '                             これがないとLeftがずれます
  .Left = Lx2                '**X座標
  .Top = Tx2                 '**Y座標
  .Show                      '**ユーザーフォームを表示
End With

表示サンプル

後編に続く

後編では、実際にマクロのどこにどのように記述するかを解説します。
dz11.hatenadiary.jp

(Excel) VBAでのヘッダーとフッターの記述方法

ヘッダーおよびフッターの記述書式

ページ設定のヘッダーおよびフッターの記述については以下を参考にしてください。
dz11.hatenadiary.jp

ヘッダー/フッターの記述(1)

※VBA記述内容は全てダブルクォーテーション(" ")で囲って下さい。

設定項目	ダイアログ記述	VBA記述	備考
ページ番号	&[ページ番号]	&P	.
総ページ数	&[ページ数]	&N	.
日付	&[日付]	&D	.
時刻	&[時刻]	&T	.
フォルダパス	&[パス]	&Z	.
ファイル名	&[ファイル名]	&F	拡張子なし
シート名	&[シート名]	&A	.
フォント	(選択)	&""フォント名""	"を重ねる
スタイル	(選択)	&""スタイル名""	"を重ねる ※記述(2)参照
サイズ	(選択)	&サイズ 例：サイズ12→ &12	.
フォント色	(選択)	&K色番号(RRGGBB形式) 例：赤→ &KFF0000	#は不要
下線	(選択)	&U文字列	閉じる場合は &U文字列&U
二重下線	(選択)	&E文字列	閉じる場合は &E文字列&E
取り消し線	(選択)	&S文字列	閉じる場合は &S文字列&S
上付き文字	(選択)	&X文字列	閉じる場合は &X文字列&X
下付き文字	(選択)	&Y文字列	閉じる場合は &Y文字列&Y
図(画像)の挿入	(選択)	&G	※後日別解説

※フォント名とスタイル名は「,」(コンマ)で区切って入力することも出来ます。
※「図の挿入」と編集については後日別途解説します。

ヘッダー/フッターの記述(2) 文字スタイル-基本

設定項目	ダイアログ記述	VBA記述	備考
標準	(選択)	&""標準""	"を重ねる
太字	(選択)	&""太字""	"を重ねる
斜体	(選択)	&""斜体""	"を重ねる
太字かつ斜体	(選択)	&""太字 斜体""	"を重ねる

※文字スタイルについては、フォントにより記述が異なる場合があります。以下を参考にしてください。
dz11.hatenadiary.jp

記述例

'1. ヘッダー左に「ブック名-シート名」
'2. フッター中央にHGゴシックEの14ポイント、太字かつ斜体、
'    更に色は青(0000FF)で「ページ番号／総ページ数」
With ActiveSheet.PageSetup
  .LeftHeader = "&F-&A"
  .CenterFooter = "&""HGｺﾞｼｯｸE,太字 斜体""&14&K0000FF&P／&N"
End With

以上。

(Excel) VBAでのヘッダーおよびフッター指定に使える主なフォント名とスタイル名の一覧

VBA記述でのヘッダーおよびフッター指定に使える主なフォント名とスタイル名

ここでは、VBA記述でのヘッダーおよびフッターの指定に使える主なフォント名と対応するスタイル名を紹介します。内訳は、ほとんど全ての日本語フォント(@付の縦フォントを除く)、中国語フォント、ハングルフォント、代表的な英語フォントです。

フォント名	言語	標準	太字	斜体	太字かつ斜体
ＭＳ Ｐゴシック	日本語	標準	太字	斜体	太字 斜体
ＭＳ ゴシック	日本語	標準	太字	斜体	太字 斜体
ＭＳ Ｐ明朝	日本語	標準	太字	斜体	太字 斜体
ＭＳ 明朝	日本語	標準	太字	斜体	太字 斜体
MS UI Gothic	日本語	標準	太字	斜体	太字 斜体
メイリオ	日本語	レギュラー	ボールド	イタリック	ボールド イタリック
Meiryo UI	日本語	標準	太字	斜体	太字 斜体
UD デジタル 教科書体 N-R	日本語	標準	太字	斜体	太字 斜体
UD デジタル 教科書体 N-B	日本語	－	－	斜体	太字 斜体
UD デジタル 教科書体 NK-R	日本語	標準	太字	斜体	太字 斜体
UD デジタル 教科書体 NK-B	日本語	－	－	斜体	太字 斜体
UD デジタル 教科書体 NP-R	日本語	標準	太字	斜体	太字 斜体
UD デジタル 教科書体 NP-B	日本語	－	－	斜体	太字 斜体
BIZ UDPゴシック	日本語	標準	太字	斜体	太字 斜体
BIZ UDP明朝 Medium	日本語	標準	太字	斜体	太字 斜体
BIZ UDゴシック	日本語	標準	太字	斜体	太字 斜体
BIZ UD明朝 Medium	日本語	標準	太字	斜体	太字 斜体
HGPｺﾞｼｯｸE	日本語	標準	太字	斜体	太字 斜体
HGPｺﾞｼｯｸM	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGP教科書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGP行書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGP創英ﾌﾟﾚｾﾞﾝｽEB	日本語	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 斜体	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字 斜体
HGP創英角ｺﾞｼｯｸUB	日本語	標準	太字	斜体	太字 斜体
HGP創英角ﾎﾟｯﾌﾟ体	日本語	標準	太字	斜体	太字 斜体
HGP明朝B	日本語	ﾎﾞｰﾙﾄﾞ	ﾎﾞｰﾙﾄﾞ 太字	ﾎﾞｰﾙﾄﾞ 斜体	ﾎﾞｰﾙﾄﾞ 太字 斜体
HGP明朝E	日本語	標準	太字	斜体	太字 斜体
HGSｺﾞｼｯｸE	日本語	標準	太字	斜体	太字 斜体
HGSｺﾞｼｯｸM	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGS教科書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGS行書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HGS創英ﾌﾟﾚｾﾞﾝｽEB	日本語	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 斜体	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字 斜体
HGS創英角ｺﾞｼｯｸUB	日本語	標準	太字	斜体	太字 斜体
HGS創英角ﾎﾟｯﾌﾟ体	日本語	標準	太字	斜体	太字 斜体
HGS明朝B	日本語	ﾎﾞｰﾙﾄﾞ	ﾎﾞｰﾙﾄﾞ 太字	ﾎﾞｰﾙﾄﾞ 斜体	ﾎﾞｰﾙﾄﾞ 太字 斜体
HGS明朝E	日本語	標準	太字	斜体	太字 斜体
HGｺﾞｼｯｸE	日本語	標準	太字	斜体	太字 斜体
HGｺﾞｼｯｸM	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HG教科書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HG行書体	日本語	ﾒﾃﾞｨｳﾑ	ﾒﾃﾞｨｳﾑ 太字	ﾒﾃﾞｨｳﾑ 斜体	ﾒﾃﾞｨｳﾑ 太字 斜体
HG創英ﾌﾟﾚｾﾞﾝｽEB	日本語	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 斜体	ｴｸｽﾄﾗﾎﾞｰﾙﾄﾞ 太字 斜体
HG創英角ｺﾞｼｯｸUB	日本語	標準	太字	斜体	太字 斜体
HG創英角ﾎﾟｯﾌﾟ体	日本語	標準	太字	斜体	太字 斜体
HG明朝B	日本語	ﾎﾞｰﾙﾄﾞ	ﾎﾞｰﾙﾄﾞ 太字	ﾎﾞｰﾙﾄﾞ 斜体	ﾎﾞｰﾙﾄﾞ 太字 斜体
HG明朝E	日本語	標準	太字	斜体	太字 斜体
HG正楷書体-PRO	日本語	標準	太字	斜体	太字 斜体
HG丸ｺﾞｼｯｸM-PRO	日本語	標準	太字	斜体	太字 斜体
Yu Gothic UI	日本語	標準	太字	斜体	太字 斜体
Yu Gothic UI Semilight	日本語	標準	太字	斜体	太字 斜体
Yu Gothic UI Light	日本語	標準	太字	斜体	太字 斜体
Yu Gothic UI Semibold	日本語	－	－	斜体	太字 斜体
游ゴシック	日本語	標準	太字	斜体	太字 斜体
游ゴシック Light	日本語	標準	太字	斜体	太字 斜体
游ゴシック Medium	日本語	標準	太字	斜体	太字 斜体
游明朝	日本語	標準	太字	斜体	太字 斜体
游明朝 Light	日本語	標準	太字	斜体	太字 斜体
游明朝 Demibold	日本語	－	－	斜体	太字 斜体
Microsoft JhengHei	中国語	標準	太字	斜体	太字 斜体
Microsoft JhengHei Light	中国語	標準	太字	斜体	太字 斜体
Microsoft JhengHei UI	中国語	標準	太字	斜体	太字 斜体
Microsoft JhengHei UI Light	中国語	標準	太字	斜体	太字 斜体
Microsoft YaHei	中国語	標準	太字	斜体	太字 斜体
Microsoft YaHei Light	中国語	標準	太字	斜体	太字 斜体
Microsoft YaHei UI	中国語	標準	太字	斜体	太字 斜体
Microsoft YaHei UI Light	中国語	標準	太字	斜体	太字 斜体
NSimSun	中国語	標準	太字	斜体	太字 斜体
MingLiU_HKSCS-ExtB	中国語	標準	太字	斜体	太字 斜体
MingLiU-ExtB	中国語	標準	太字	斜体	太字 斜体
PMingLiU-ExtB	中国語	標準	太字	斜体	太字 斜体
SimSun	中国語	標準	太字	斜体	太字 斜体
SimSun-ExtB	中国語	標準	太字	斜体	太字 斜体
Malgun Gothic	ハングル	標準	太字	斜体	太字 斜体
Malgun Gothic Semilight	ハングル	標準	太字	斜体	太字 斜体
Arial Unicode MS	英語	標準	太字	斜体	太字 斜体
Arial	英語	標準	太字	斜体	太字 斜体
Arial Narrow	英語	標準	太字	斜体	太字 斜体
Arial Black	英語	標準	－	斜体	－
Century	英語	標準	太字	斜体	太字 斜体
Century Gothic	英語	標準	太字	斜体	太字 斜体
Comic Sans MS	英語	標準	太字	斜体	太字 斜体
Courier	英語	標準	太字	斜体	太字 斜体
Courier New	英語	標準	太字	斜体	太字 斜体
Microsoft Sans Serif	英語	標準	太字	斜体	太字 斜体
Tahoma	英語	標準	太字	斜体	太字 斜体
Times New Roman	英語	標準	太字	斜体	太字 斜体

その他のフォント名とスタイル名

ここでは全ての外国語フォントについては紹介しきれませんでした。他のフォントを使いたい場合は画面のようにフォント名とスタイル名を確認して使用して下さい。

以上

dz11.hatenadiary.jp

(Excel) ページ設定ダイアログと対応するVBAコード

はじめに

VBAでのページ設定の高速化

以前から、VBAでのページ設定の速度が遅いことが知られている。これを解消するため、Office2010以降では高速化させるコードが組み込まれた。

'**PageSetup高速化開始
Application.PrintCommunication = False

ここにページ設定処理を入れる

'**PageSetup高速化終了
Application.PrintCommunication = True

PageSetupはシート(Sheet)に対して行う

実際のワークシート(ブック)の操作を思い浮かべて貰うと、ページ設定が各シートに対してそれぞれ行うものだと理解できるだろう。

例：
'**1番左のワークシートのページ設定
Worksheets(1).PageSetup.～

'**Sheet3という名前のワークシートのページ設定
Worksheets("Sheet3").PageSetup.～

'**現在アクティブなワークシートのページ設定
ActiveSheet.PageSetup.～

'**ブックに含まれる全てのワークシートのページ設定
Dim mySheet As Worksheet
For Each mySheet In Worksheets
  mySheet.PageSetup.～～
Next mySheet

「ページ」タブ

印刷の向き

PageSetup.Orientation : 印刷の向きを設定する

' **アクティブシートの印刷の向きを「横」に設定する
ActiveSheet.PageSetup.Orientation = xlLandscape

項目	コード	値
縦	xlPortrait	1
横	xlLandscape	2

拡大縮小印刷

拡大/縮小

PageSetup.Zoom : シートの拡大縮小率を設定する

' **アクティブシートの拡大縮小率を「46％」に設定する
ActiveSheet.PageSetup.Zoom = 46

項目	コード	値
拡大縮小率	-	10～400
「次のページ数に合わせて印刷」の場合	False	-

次のページ数に合わせて印刷

PageSetup.FitToPageTall : シートを縦何ページ分で収めるかを設定する
PageSetup.FitToPageWide : シートを横何ページ分で収めるかを設定する

' **アクティブシートを縦1ページ、横1ページに収まるように設定する
With ActiveSheet.PageSetup
  .Zoom = False
  .FitToPagesTall = 1 
  .FitToPagesWide = 1
End With

項目	値	備考
.Zoom	False	必須
.FitToPageTall	1～	縦
.FitToPageWide	1～	横

用紙サイズ

PageSetup.PaperSize : 用紙サイズを設定する

' **アクティブシートの用紙サイズをA3に設定する
ActiveSheet.PageSetup.PaperSize = xlPaperA3

日本で使う主な値

値	サイズ	幅x高さ
xlPaperA3	A3	297mm x 420mm
xlPaperA4	A4	210mm x 297mm
xlPaperA5	A5	148mm x 210mm
xlPaperB4	B4	250mm x 354mm
xlPaperB5	B5	177mm x 250mm

全ての値

xlpapersize 列挙 (Excel) | Microsoft Docs

印刷品質

PageSetup.PrintQuality : 印刷品質(dpi)を設定する

' **アクティブシートの印刷品質を600dpiに設定する
PageSetup.PrintQuality = 600

※印刷するプリンターによって異なるが、300dpi, 600dpi, 1200dpiなどが一般的。値が大きいほど綺麗に印刷されるが印刷データが大きくなる。(ファイルサイズではない)

先頭ページ番号

PageSetup.FirstPageNumber : 先頭ページ番号を設定する

' **アクティブシートの先頭ページ番号を100に設定する
PageSetup.FirstPageNumber = 100

値	備考
xlAutomatic	自動的に先頭ページの番号が選択される
1～	1～

「余白」タブ

余白およびヘッダー/フッターの高さ

PageSetup.LeftMargin   : 左余白を設定する
PageSetup.RightMargin  : 右余白を設定する
PageSetup.TopMargin    : 上余白を設定する
PageSetup.BottomMargin : 下余白を設定する

PageSetup.HeaderMargin : ヘッダーの高さを設定する
PageSetup.FooterMargin : フッターの高さを設定する

' **アクティブシートの余白を各2.0cm, 
'   ヘッダー/フッターの高さを1.5cmに設定する
With ActiveSheet.PageSetup
  .LeftMargin   = Application.CentimetersToPoints(2.0) 
  .RightMargin  = Application.CentimetersToPoints(2.0)
  .TopMargin    = Application.CentimetersToPoints(2.0)
  .BottomMargin = Application.CentimetersToPoints(2.0)
  .HeaderMargin = Application.CentimetersToPoints(1.5)
  .FooterMargin = Application.CentimetersToPoints(1.5)
End With

単位	コードと値
センチメートル単位 ※ミリ単位ではない点に注意	Application.CentimetersToPoints( )
インチ単位	Application.InchesToPoints( )

ページ中央

PageSetup.CenterHorizontally : 水平方向のページ中央に配置する
PageSetup.CenterVertically   : 垂直方向のページ中央に配置する

' **アクティブシートを水平方向でページ中央に配置し、
'                     垂直方向ではページ中央に配置しない
With ActiveSheet.PageSetup
  .CenterHorizontally = True
  .CenterVertically   = False
End With

項目	コード
ページ中央に配置する	True
ページ中央に配置しない	False

「ヘッダー/フッター」タブ

「ヘッダーの編集」「フッターの編集」

With ActiveSheet.PageSetup
  '**全ページ用ヘッダー/フッターの指定
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = "&P ページ"
  .RightFooter = ""
End With
    
※但し、OddAndEvenPagesHeaderFooter = Trueの場合は奇数ページ用となる

その他のオプション

奇数/偶数ページ別指定

With ActiveSheet.PageSetup
  '**奇数/偶数ページ別指定
  .OddAndEvenPagesHeaderFooter = True
  
  '**偶数ページ用ヘッダー/フッターの指定
  .EvenPage.LeftHeader.Text = ""
  .EvenPage.CenterHeader.Text = ""
  .EvenPage.RightHeader.Text = ""
  .EvenPage.LeftFooter.Text = ""
  .EvenPage.CenterFooter.Text = ""
  .EvenPage.RightFooter.Text = ""
End With

項目	コード
奇数/偶数ページを別指定する	True
奇数/偶数ページを別指定しない	False

先頭ページのみ別指定

With ActiveSheet.PageSetup
  '**先頭ページのみ別指定
  .DifferentFirstPageHeaderFooter = True
  
  '**先頭ページ用ヘッダー/フッターの指定
  .FirstPage.LeftHeader.Text = ""
  .FirstPage.CenterHeader.Text = ""
  .FirstPage.RightHeader.Text = ""
  .FirstPage.LeftFooter.Text = ""
  .FirstPage.CenterFooter.Text = ""
  .FirstPage.RightFooter.Text = ""
End With

項目	コード
先頭ページのみ別指定する	True
先頭ページのみ別指定しない	False

ドキュメントに合わせて拡大/縮小

PageSetup.ScaleWithDocHeaderFooter：ドキュメントに合わせて拡大/縮小する

項目	コード
ドキュメントに合わせて拡大/縮小する	True
ドキュメントに合わせて拡大/縮小しない	False

ページ余白に合わせて配置

PageSetup.AlignMarginsHeaderFooter：ページ余白に合わせて配置する

項目	コード
ページ余白に合わせて配置する	True
ページ余白に合わせて配置しない	False

「シート」タブ

印刷範囲

PageSetup.PrintArea : 印刷範囲を設定する

' **アクティブシートの印刷範囲を$A$1:$AB$32に設定する
ActiveSheet.PageSetup.PrintArea = "$A$1:$AB$32"

印刷タイトル

PageSetup.PrintTitleRows    : 
PageSetup.PrintTitleColumns : 

印刷

枠線

PageSetup.PrintGridlines : 枠線を印刷するか設定する

項目	値
枠線を印刷する	True
枠線を印刷しない	False

白黒印刷

PageSetup.BlackAndWhite : 白黒印刷するか設定する

項目	値
白黒印刷する	True
白黒印刷しない	False

簡易印刷

PageSetup.Draft : 簡易印刷するか設定する

項目	値
簡易印刷する	True
簡易印刷しない	False

行列番号

PageSetup.PrintHeadings : 行と列の番号を印刷するか設定する

項目	値
行と列の番号を印刷する	True
行と列の番号を印刷しない	False

コメント

PageSetup.PrintComments : コメントを印刷するか設定する
PageSetup.PrintNotes    : コメントを印刷するか設定する

項目	コード	値
表示通りにコメントを印刷する	PageSetup.PrintComments	xlPrintInPlace
末尾にまとめてコメントを印刷する	PageSetup.PrintComments	xlPrintSheetEnd
コメントを印刷しない	PageSetup.PrintNotes	False

セルのエラー

PageSetup.PrintErrors : エラーを印刷するか設定する

項目	値
印刷エラーは空白	xlPrintErrorsBlank
印刷エラーはダッシュとして表示	xlPrintErrorsDash
すべての印刷エラーが表示	xlPrintErrorsDisplayed
印刷エラーは使用不可として表示	xlPrintErrorsNA

ページの方向

PageSetup.Order : 印刷するページの方向を設定する

項目	コード	値
左から右	xlDownThenOver	1
上から下	xlOverThenDown	2

(Excel) マクロに割り当てることの出来るショートカットキー一覧

訂正のお知らせ 2019.5.18

組み合わせ表で「Ctrl＋Shift＋O」と「Ctrl＋Shift＋Q」の内容が逆でした。既定値「コメントが入力されているセルを選択」があるのが「Ctrl＋Shift＋O」です。訂正してお詫び致します。

Excelでマクロに割り当てることの出来るショートカットキー

Excelでマクロに割り当てることの出来るショートカットキーは、

• Ctrl + アルファベット26文字(A～Z)
• Ctrl + Shift + アルファベット26文字(A～Z)

のみですが、あらかじめ組み込まれているショートカットキーがいくつもあり、結局使える組み合わせは何なのかよく分からないという状況です。
ここでは、筆者が独自に調べたアルファベットのショートカットキーの一覧と、マクロ割当に使える組み合わせを紹介します。

Ctrl + アルファベット26文字(A～Z)

キーの組み合わせ	処理する内容
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 + アルファベット26文字(A～Z)

キーの組み合わせ	処理する内容
Ctrl＋Shift＋A	マクロ割当可能
Ctrl＋Shift＋B	マクロ割当可能
Ctrl＋Shift＋C	マクロ割当可能
Ctrl＋Shift＋D	マクロ割当可能
Ctrl＋Shift＋E	マクロ割当可能
Ctrl＋Shift＋F	マクロ割当可能
Ctrl＋Shift＋G	マクロ割当可能
Ctrl＋Shift＋H	マクロ割当可能
Ctrl＋Shift＋I	マクロ割当可能
Ctrl＋Shift＋J	マクロ割当可能
Ctrl＋Shift＋K	マクロ割当可能
Ctrl＋Shift＋L	フィルターの追加と削除切替
Ctrl＋Shift＋M	マクロ割当可能
Ctrl＋Shift＋N	マクロ割当可能
Ctrl＋Shift＋O	コメントが入力されているセルを選択
Ctrl＋Shift＋P	[セルの書式設定]フォントタブ
Ctrl＋Shift＋Q	マクロ割当可能
Ctrl＋Shift＋R	マクロ割当可能
Ctrl＋Shift＋S	名前を付けて保存
Ctrl＋Shift＋T	マクロ割当可能
Ctrl＋Shift＋U	マクロ割当可能
Ctrl＋Shift＋V	マクロ割当可能
Ctrl＋Shift＋W	マクロ割当可能
Ctrl＋Shift＋X	マクロ割当可能
Ctrl＋Shift＋Y	マクロ割当可能
Ctrl＋Shift＋Z	マクロ割当可能

標準で設定されているものにも上書きで割り当てられる(※要注意)

標準で設定されているショートカットキーにも上書きで割り当てられますが、一般的に使われるようなショートカットキーには割り当てない方が賢明です。（例：Ctrl + S など)

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

お願い

もし間違えなどありましたら、コメントでご指摘願います。

よーく見たら前にもおんなじテーマで記事書いてましたｗｗｗ

dz11.hatenadiary.jp

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

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

以上。

素のVBAだけで画面の解像度を取得というか推測する手法(API使いません)

VBAで画面の解像度を取得したいときにWeb検索すると、WindowsAPIを使うとか、PowerShell経由で.NET APIを使うとかいう記事がよく出てきますが、結構面倒なイメージがあります。ここでは、そういうものを使わずにVBAだけで画面の解像度を取得というか推測する手法を紹介します。

Excelにおけるポイントという単位とは？

Excelを使っているとよく出てくる単位に「ポイント (Point, pt)」というものがあります。これはExcel (Office)の中で使われる独自の単位で擬似的なピクセルのようなもので、フォントサイズのことではありません。

詳しいことは以下のブログを参考にして下さい。
vbabeginner.net

重要なことは、ポイントとピクセル(画面の解像度で使用する単位)には以下の関係性があるということです。

1ピクセル(pixel)＝0.75ポイント(pt) つまり 1pt ≒ 1.3333pixel

VBAでウィンドウサイズを取得する

Sub myWindowSize()
  '==Excelのウィンドウサイズを取得する==
  Dim myWindowWidth As Long
  Dim myWindowHeight As Long
  
  '**ウィンドウサイズを取得する
  myWindowWidth = Application.Width
  myWindowHeight = Application.Height
  
  MsgBox "ウィンドウ幅＝" & myWindowWidth & "(pt)" & vbCrLf & _
         "ウィンドウ高＝" & myWindowHeight & "(pt)"
End Sub

通常(ウィンドウ)モードで最大領域にした場合

適当なExcelブックを開き、最大化ボタンを使わずに手作業で最大領域まで広げて下さい。以下は新規ファイルを最大領域にしたものです。ちなみに筆者のモニタ環境はフルHD(1920pixel×1080pixel)です。

このウィンドウに対し、先ほどのマクロを動かしてみた結果がこちらです。

このサイズはポイント(pt)単位なので、ピクセル(pixel)単位に変換します。

項目	サイズ(pt)	1.3333倍して四捨五入(pixel)	タスクバーの高さ(pixel)	計(pixel)
画面幅	1440	1920	-	1920
画面高	780	1040	40	1080

ウィンドウサイズ(pt単位)から、画面解像度(pixel単位)が取得換算できました。

最大化モードにした場合

それでは通常モードではなく最大化の場合はどうでしょうか。次のコードを追加して最大化します。（普通に最大化してもいいです）

  '**ウィンドウを最大化する
  ActiveWindow.WindowState = xlMaximized

サイズはポイント(pt)単位なので、ピクセル(pixel)単位に変換します。

項目	サイズ(pt)	1.3333倍して四捨五入(pixel)	タスクバーの高さ(pixel)	計(pixel)
画面幅	1452	1936	-	1936
画面高	792	1056	40	1096

項目	最大化(pixel)	画面解像度(pixel)	差
幅	1936	1920	16
高	1096	1080	16

おそらく、上下左右に 8pixel ずつマージンが取られている……と予測できますね（実際はちょっと違うみたいです）。

そもそも画面の解像度は大体決まっている

そもそもパソコンの画面解像度というのは、いくつかのサイズに決まっていてパソコンのグラフィックチップの性能やモニタの最大解像度で決定しています。

画像解像度の例

名称	幅(pixel)	高(pixel)	画面比率
VGA	640	480	4:3
XGA	1024	768	4:3
SXGA	1280	1024	5:4
UXGA	1600	1200	4:3
WSXGA+	1650	1050	約16:10
WUXGA	1920	1200	16:10
HD	1366	768	16:9
FullHD	1920	1080	16:9
4K	3840	2160	16:9

結論：目的によってはこの方法でも構わない

上記のように解像度は大体決まっているので、最大化して換算して大体その解像度に近いものが画面解像度になる、というものでも十分だと思います。使用目的、例えば画面解像度にしたがってユーザーフォームの大きさを変えるとか、ウィンドウ位置を制御するとか、そういった目的に使うのであればこの程度の精度の取得でも問題ないのではないでしょうか。

以上。

逆FIND：対象文字列を右(末尾)から検索して文字位置を取得するユーザー定義関数を作る

Excelの関数で、対象文字列を検索文字で検索したときに位置が何番目かを取得するワークシート関数「FIND」や「FINDB」がありますが、とても便利ですよね。これらの関数は「対象文字列を左(先頭)から検索したときの位置」を取得するものですが、「右(末尾)から検索したときの位置」を取得するにはちょっと面倒なことをしなくてはいけません。ここでは、VBAを使って分かりやすいユーザー定義関数を作ってみます。

逆FIND：FINDrev

Public Function FINDrev(ByVal KWord As String, _
                        ByRef myCell As Range) As Long
  '== 末尾から検索して検索文字位置を取得するユーザー定義関数 ==
  Dim RevText As String

  '**対象セルの文字列を逆文字にする
  RevText = StrReverse(myCell.Text)

  '**文字位置を取得
  FINDrev = InStr(RevText, KWord)
End Function

書式： FINDrev(検索文字列, 対象セル)
検索文字列は String型、対象セルは Range型、戻り値は Long型 です。

解説

VBA関数でワークシート関数のFINDに相当するものはInStr関数です。

書式： InStr([開始位置, ]対象文字列, 検索文字列[, 比較モード])

開始位置と比較モードは省略可能ですが、今回は明示的に指定しました。なお、比較モードを指定した場合は開始位置も指定しなくてはなりません。
また、対象文字列を逆文字にする関数 StrReverse を使っています。これを使うことで、「ややこしいことをして右からの位置を取得」するのではなく、「逆文字の左からの位置を取得」しています。

逆FINDB： FINDBrev

書式： FINDrev(検索文字列, 対象セル)

こちらはFINDBの逆、つまりバイト単位で調べるものです。InStrB関数を使用していますが、やっていることは同じです。

Public Function FINDBrev(ByVal KWord As String, _
                         ByRef myCell As Range) As Long
  '== 対象文字列を末尾から検索した時の位置(バイト単位) ==
  Dim RevText As String
    
  '**対象セルの文字列を逆文字にする
  RevText = StrReverse(myCell.Text)
  
  '**文字位置を取得
  FINDBrev = InStrB(1, RevText, KWord, vbTextCompare)
End Function

おまけ：FINDrev2

Public Function FINDrev2(ByVal KWord As String, _
                         ByRef myCell As Range) As Long
  '== 末尾から検索して検索文字位置を取得するユーザー定義関数 ==
  FINDrev2 = InStrRev(myCell.Text, KWord)
End Function

文字列単位の場合だけに限られますが、実は InStrRev関数というものもあり、これはそのものズバリ「末尾から検索する関数」です。逆文字を作ることなく文字列単位での末尾からの位置を一発で取得することが可能です。

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

Sub CopySelectedArea()
  ' ## 選択範囲アドレスをクリップボードにコピーする ##
  CS = Selection(1).Address
  CL = Selection(Selection.Count).Address
  
  SCI = CS & ":" & CL
  
  With CreateObject("Forms.TextBox.1")
    .MultiLine = True
    .Text = SCI
    .SelStart = 0
    .SelLength = .TextLength
    .Copy
  End With
  
  MsgBox "クリップボードにセル範囲 " & SCI & " をコピーしました。", vbOKOnly + vbInformation
End Sub

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

エクセルのプロパティ編集のコードを探していて、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)

以上。

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

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

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：テーマカラー

テーマカラーは、文字色や背景色の変更の際によく見かけるテーマの配色です。

このテーマの配色は「ページレイアウト」タブの「テーマ」＞「配色」から様々な決まった組合せの中から選ぶことが出来、テーマを変更することで一括で色を変更することが可能なものです。

また、配色を自分でカスタマイズすることも可能です。

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を使ってコメントを作成する(非表示オプション付き)

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

コード

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 + @)が入っていたらコメントを非表示で追加する処理
• 範囲選択の場合は同じコメントを一斉入力

既にコメントがある場合

コメントを追加

コメントを非表示で追加

以上。

←記事を気に入って頂けたらポチッとしてもらえると励みになります。

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

はじめに

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

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

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

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

エクセルのショートカットについてはものすごく詳しく分かりやすいサイトがあるのでこちらをご覧下さい。
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つだけでした。

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

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

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

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

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

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

標準設定は上書きできる

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

以上です。