2020/08/21

Excel図形等の位置、ディスプレイ上の位置の取得




1.背景

「ディジタイザー」というものをご存じでしょうか。
最近は見掛けなくなりましたが、アナログからデジタルへの転換期に使用された転写板です。広げた図面の上に、十字マークの付いた透明なカーソルを乗せ、各ポイントでボタンを押して位置座標をデジタル化するものです。
図面だけで無く、写真や拓本みたいなものも当時盛んにデジタル化し、寸法やら面積やらを計算したものです。

今回紹介するのはそのディジタイザーみたいなもので、測るのはディスプレイ上のアプリの位置です。もちろんExcelのシート上の図形やグラフ等の位置も測定できますし、ディスプレイ上を定規で測った覚えのある方にはお勧めです。

2.概要

外観的には図2-1のように半透明のダイアログが現れますので、タイトル部(ダイアログの上部)をドラッグしながら「透明部の十字の交点」を位置測定の対象(図2-1では図形の右端)に合わせます。するとダイアログの右側に、原点からの距離(単位:ポイント)が表示されます。
ディジタイザーの外観および表示等
図2-1

「原点」について少し補足します。Excelシート上に配置した図形等の位置の原点は「A1セルの左上角」になります。また画面全体の原点は「ディスプレイの左上角」になります。(図2-2)
ディスプレイの原点とExcelの原点
図2-2

今回のダイアログにはその原点の切替スイッチ(トグルボタン)を設けてあり、ディスプレイ左上原点の場合には「Windowモード」を、ExcelのA1セル左上原点の場合には「Excelモード」を選択します。

尚、一番下のサンプルファイルには Sheet1上に「Start」ボタンを配置してあり、ダイアログを起動(モードレスで起動)できます。また、閉じるボタンは設けていませんので、右上の×印で閉じてください。

3.ユーザーフォームの外観を作る

フォーム上に、読み取り位置の目印である十字マークを作る必要があります。直線を2本引けば完了なのですが、Excelのフォーム上に図形を描くことは簡単ではありません。その代用として「Imageコントロール」を細くして直線を作っています。
ユーザーフォームの外観作成
図3-1

また、位置表示用としてLabelコントロールを2つ(X軸用、Y軸用)配置します。
最後に原点変更用にトグルボタンを配置します。今回は原点が2つですので、2つのValue値(True ,False)を持つトグルボタンが使い易いですが、普通のCommandButtonでもコードを修正すれば充分可能と思います。

4.フォームモジュール

4-1.宣言部

フォームの透明化には、Windows APIを使う必要があります。Windows2000の頃に追加された拡張ウィンドウスタイルの中の、透明化を使用します。
  1. '========== ⇩① 宣言部 ====================
  2. Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
  3.    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
  4. Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
  5.    (ByVal hWnd As Long, ByVal nIndex As Long) As Long
  6. Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
  7.    (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
  8. Private Declare Function SetLayeredWindowAttributes Lib "user32" _
  9.    (ByVal hWnd As Long, ByVal crKey As Long, ByVal bAlpha As Long, ByVal dwFlags As Long) As Long
  10. Const GWL_EXSTYLE = (-20)        '拡張ウィンドウスタイル
  11. Const WS_EX_LAYERED = &H80000     'レイヤーウィンドウの作成
  12. Const LWA_COLORKEY = 1&        '実行する設定は「透明にする対象」にする
  13. Const LWA_ALPHA = 2&          '実行する設定は「不透明度」にする
図4-1

フォームを透明化する手順は図4-2の流れです。その手順に必要なAPIと設定値を宣言部で宣言及び定数設定を行います。
フォーム透明化の手順とAPI
図4-2

今回必要なAPIは、図4-2の「FindWindow」「GetWindowLong」「SetWindowLong」「SetLayeredWindowAttributes」の4つですので、それらを2~9行目で宣言しています。
また、そのAPI実行に必要な値を11~14行目で定数宣言しています。
なお13・14行目の定数の内、今回使用するのは13行目の「LWA_COLORKEY」です。「LWA_ALPHA」は使用しないので削除しても構いませんが、こちらを使用するとどのようなフォームになるかは、図4-6で紹介します。

今回使用するフォームの透明化には「拡張ウィンドウスタイル」の中の「WS_EX_LAYERED」を使用します。その他にも図4-3のように、様々なスタイルが存在します。
図4-3に、拡張ウィンドウスタイル一覧を転記(出典:http://chokuto.ifdef.jp/advanced/ prm/extended_window_style.html)します。
拡張ウィンドウのスタイル
拡張ウィンドウのスタイル意味
0x00000000WS_EX_LEFT一般的な左揃えされたプロパティを持つウィンドウを作成します。(デフォルト属性)
0x00000000WS_EX_LTRREADING左から右への読み取り順序を持つプロパティを持ったウィンドウを作成します。(デフォルト属性)
0x00000000WS_EX_RIGHTSCROLLBAR垂直スクロールバーがある場合には、スクロールバーがクライアント領域の右側に置かれます。
(デフォルト属性)
0x00000001WS_EX_DLGMODALFRAME二重の境界線を持つウィンドウを作成します。
0x00000004WS_EX_NOPARENTNOTIFYこのスタイルで作成された子ウィンドウが作成されたり破棄されたりするときに、その親ウィンドウにWM_PARENTNOTIFYメッセージを送らないように指定します。
0x00000008WS_EX_TOPMOSTこのスタイルで作成されたウィンドウは最前面ウィンドウとなることを表します。
ウィンドウが非アクティブな状態でも、ほかのウィンドウの前面に表示されます。このスタイルを追加・削除するには、SetWindowPos関数を使用します。
0x00000010WS_EX_ACCEPTFILESこのスタイルで作成されたウィンドウがドラッグアンドドロップファイルを受け入れることを表します。
0x00000020WS_EX_TRANSPARENTこのスタイルで作成されたウィンドウは、(同じスレッドで作成された)このウィンドウの下にある兄弟ウィンドウが描画されるまでは再描画されません。
下にあるウィンドウのビットは既に描画されているため、このウィンドウは透明であるように見えます。
0x00000040WS_EX_MDICHILDマルチドキュメントインターフェース(MDI)の子ウィンドウを作成します。
0x00000080WS_EX_TOOLWINDOWツールウィンドウを作成します。一般にフローティングツールバーとして使用されます。
ツールウィンドウは標準のタイトルバーより小さいタイトルバーを持っており、ウィンドウタイトルは小さいフォントで描画されます。
ツールウィンドは、タスクバーや、ユーザーが [Alt]+[Tab] キーを押したときに現れるダイアログ内には表示されません。
ツールウィンドウがシステムメニューを持つ場合、タイトルバーにアイコンが表示されませんが、右クリックや [Alt]+[Space] キーを押すことによってシステムメニューを表示させることができます。
0x00000100WS_EX_WINDOWEDGEウィンドウが盛り上がった縁の境界線を持つことを表します。
0x00000188WS_EX_PALETTEWINDOWWS_EX_WINDOWEDGEとWS_EX_TOOLWINDOWとWS_EX_TOPMOSTの組み合わせです。
0x00000200WS_EX_CLIENTEDGE縁が沈んで見える境界線を持つウィンドウであることを表します。
0x00000300WS_EX_OVERLAPPEDWINDOWWS_EX_WINDOWEDGEとWS_EX_CLIENTEDGEの組み合わせです。
0x00000400WS_EX_CONTEXTHELPダイアログボックスのタイトルバーに疑問符([?])ボタンを追加します。
ユーザーがこのボタンをクリックすると、マウスポインタに疑問符が付きます。その後、ユーザーが子ウィンドウをクリックすると、子ウィンドウはWM_HELPメッセージを受け取ります。
子ウィンドウはこのメッセージを親ウィンドウのウィンドウプロシージャに渡すべきです。親ウィンドウはHELP_WM_HELPコマンドを用いてWinHelp関数を呼び出すべきです。
ヘルプアプリケーションは、子ウィンドウに対するヘルプを含んだポップアップウィンドウを表示します。
このスタイルをWS_MAXIMIZEBOXやWS_MINIMIZEBOXとともに指定することはできません。
0x00001000WS_EX_RIGHT右揃えされたプロパティを持つウィンドウを作成します。このスタイルはウィンドウクラスに依存します。
このスタイルは、ヘブライ語やアラビア語をサポートしているシステムで有効です。それ以外では、このスタイルは無視されます。
スタティックコントロールやエディットコントロールに対してこのスタイルを用いると、それぞれSS_RIGHTスタイルやES_RIGHTスタイルと同じ効果になります。
ボタンコントロールに対してこのスタイルを用いると、BS_RIGHTスタイルおよびBS_RIGHTBUTTONスタイルと同じ効果になります。
0x00002000WS_EX_RTLREADING右から左への読み取り順序を持つプロパティを持ったウィンドウを作成します。
このスタイルは、ヘブライ語やアラビア語をサポートしているシステムで有効です。それ以外では、このスタイルは無視されます。
0x00004000WS_EX_LEFTSCROLLBAR垂直スクロールバーがある場合には、垂直スクロールバーがクライアント領域の左側に置かれます。
このスタイルは、ヘブライ語やアラビア語をサポートしているシステムで有効です。それ以外では、このスタイルは無視されます。
0x00010000WS_EX_CONTROLPARENTウィンドウはダイアログボックスナビゲーションの役割を果たす子ウィンドウを含んでいます。
このスタイルが指定されると、ファイアログマネージャは、ユーザーが[Tab]キーやカーソルキー、キーボードニーモニックを処理するようなナビゲーション操作を行ったときに、このウィンドウの子ウィンドウに対する処理を行います。
0x00020000WS_EX_STATICEDGEユーザーの入力を受け付けない項目用の、立体的に見える境界スタイルを持つウィンドウを作成します。
0x00040000WS_EX_APPWINDOWウィンドウが可視状態のときに、トップレベルウィンドウがタスクバー上に置かれるようにします。
0x00080000WS_EX_LAYERED
(今回使用)
Windows 2000/XP: レイヤーウィンドウを作成します。子ウィンドウにこのスタイルを使用することはできません。
また、CS_OWNDCまたはCS_CLASSDCクラススタイルを持つウィンドウにこのスタイルを使用することはできません。
0x00100000WS_EX_NOINHERITLAYOUTWindows 2000/XP: このスタイルのウィンドウは子ウィンドウにウィンドウレイアウトを渡しません。
0x00400000WS_EX_LAYOUTRTLアラビア語・ヘブライ語Windows 98/Me/2000/XP: 水平方向の原点が右端にあるウィンドウを作成します。水平方向の座標の値は左に向かって増加します。
0x02000000WS_EX_COMPOSITEDWindows XP: すべての子孫ウィンドウをダブルバッファリングを用いて下から上に向かっての描画順序で描画します。
CS_OWNDCまたはCS_CLASSDCクラススタイルを持つウィンドウにこのスタイルを使用することはできません。
0x08000000WS_EX_NOACTIVATEWindows 2000/XP: このスタイルのトップレベルウィンドウは、ユーザにクリックされたときにフォアグラウンドウィンドウになりません。
システムは、ユーザーがフォアグラウンドウィンドウを最小化したり閉じたりしたときに、このウィンドウがフォアグラウンドにならないようにします。
このウィンドウをアクティブにするには、SetActiveWindow関数またはSetForegroundWindow関数を使用します。
デフォルトでは、ウィンドウはタスクバーに表示されません。タスクバーに表示されるようにするには、WS_EX_APPWINDOWスタイルを使用します。
図4-3

尚、Microsoftのサイトに「拡張ウィンドウを使用すると、Windows 8.1、Windows Server 2012 R2、Windows 7、Windows Server 2008 R2では、コンピューターがクラッシュします。」とありました。Updateを正しく行っていれば大丈夫そうですが、対象のO/Sを使っている方はご注意下さい。

4-2.フォームのInitializeイベント

フォーム起動時に最初に発生するイベントがInitializeイベントです。ここでは静的なフォームの初期設定をします。
  1. '========== ⇩② Initializeイベント ====================
  2. Private Sub UserForm_Initialize()
  3.  Dim hWnd As Long    '←取得するユーザーフォームのハンドル
  4.  Dim Style As Long    '←取得・設定する拡張ウィンドウスタイル
  5.  Me.BackColor = RGB(0, 0, 0)
  6.  hWnd = FindWindow("ThunderDFrame", Me.Caption)
  7.  Style = GetWindowLong(hWnd, GWL_EXSTYLE)
  8.  Style = Style Or WS_EX_LAYERED
  9.  Call SetWindowLong(hWnd, GWL_EXSTYLE, Style)
  10.  Call SetLayeredWindowAttributes(hWnd, Me.BackColor, 0, LWA_COLORKEY)
  11.  Me.Image1.Width = 0.5
  12.  Me.Image1.Top = 0
  13.  Me.Image1.Height = Me.InsideHeight
  14.  Me.Image2.Height = 0.5
  15.  Me.Image2.Left = 0
  16.  Me.Image2.Width = Me.InsideWidth
  17.  Me.Label1.ForeColor = RGB(255, 0, 0)
  18.  Me.Label2.ForeColor = RGB(255, 0, 0)
  19.  Me.ToggleButton1.ForeColor = RGB(255, 0, 0)
  20.  Me.ToggleButton1.Caption = "Window"
  21. End Sub
図4-4

まず20行目のフォームBackColorの色設定について説明します。
今回26行目の「SetLayeredWindowAttributes」では、フォームの「Me.BackColor」に対して透明化を行っています。調べてみると、その透明にする対象の色は「0x00bbggrr」の形で与えておく必要があるようなのです。
一方初期のフォームのプロパティでは、BackColor=&H8000000F&(ボタン表面)と「システムカラー」が設定されており、色番号の先頭に「80」が付いています。この「80」が付いていると「SetLayeredWindowAttributes」の透明化の対象の色にならないようです。
ですので「SetLayeredWindowAttributes」実行より前の20行目で、フォームBackColorをRGB(0, 0, 0)とシステムカラーでは無い色(色番号の先頭が「00」)に設定します。なお、ここでは RGB(0, 0, 0)=黒色 にしていますが、透明にしてしまうので何色でも問題ありません。

22行目では、API「FindWindow」を使って「フォーム」のウィンドウハンドルを取得しています。この中の第一引数にはウィンドウクラス名を指定しますので、ユーザーフォームのクラス名である「ThunderDFrame」を指定します。
第二引数にはウィンドウのタイトル(ウィンドウ名)を指定しますので「Me.Caption」を指定します。
こうすることで、起動しているフォームのハンドルが得られ、値を変数hWndに代入します。

23行目は API「GetWindowLong」を使い、フォームの現在の拡張ウィンドウスタイルを取得します。
第一引数に22行目で得たハンドルを指定し、第二引数に取得したいデータの値を指定します。取得したいデータは、図4-5から選ぶことになりますが、今回の透明化は拡張ウィンドウスタイルですので「GWL_EXSTYLE(値:-20)」を指定します。
取得した現状の拡張ウィンドウスタイルは、変数Styleに代入されます。

GetWindowLong・SetWindowLongで取得・設定するデータ
定数 値 取得される属性
GWL_WNDPROC-4ウィンドウプロシージャのアドレス
(直接ウィンドウプロシージャを呼び出すには CallWindowProc関数を使う)
GWL_HINSTANCE-6アプリケーションのインスタンスハンドル
GWL_HWNDPARENT-8親ウィンドウのハンドル
GWL_STYLE-16ウィンドウスタイル
GWL_EXSTYLE-20拡張ウィンドウスタイル
GWL_USERDATA-21ウィンドウに関連付けられたアプリケーション定義の32ビット値
GWL_ID-12ウィンドウ ID
図4-5

24行目はその現状のスタイルに、フォームの透明化を可能にする「WS_EX_LAYERED(値:&H80000)」を加えます。
加える時には必ず「+では無くOr」を使います。これは、もし現状のスタイルに既に加えたい項目が入っていた場合に足し算をしてしまうと、計算後の値がまるで違うスタイルの値となってしまう為です。

このスタイルの加え方のカラクリと、拡張ではない標準のウィンドウスタイル「GWL_STYLE(値:-16)」については、「Book内で完結するフォーム表示のHelp画面」を参照して下さい。

フォーム透明化用の WS_EX_LAYERED を加えたStyle値を、25行目で「SetWindowLong」を使って再び拡張ウィンドウスタイルに設定します。

26行目では透明化の設定をします。「SetLayeredWindowAttributes」の構文は以下のようになっています。
SetLayeredWindowAttributes( hwnd, crKey, bAlpha, dwFlags)
 hwnd  :ウィンドウハンドル
 crKey  :レイヤードウィンドウを合成するときに使われる透明色キー
 bAlpha :レイヤード ウィンドウの不透明度を表現するために使用されるアルファ値(完全透明← 0 ~ 255 →不透明)
 dwFlags :LWA_COLORKEY(値:1)、LWA_ALPHA(値:2)のいずれかひとつ、または両方を指定

第4引数の「dwFlags」は、LWA_COLORKEYを指定すれば第2引数の「crKey(設定した色を透明にしてウィンドウを描画)」を有効にし、LWA_ALPHAを指定すれば第3引数の「bAlpha(設定した透明度でウィンドウを半透明に描画)」を有効にするものです。両方を指定「LWA_COLORKEY + LWA_ALPHA」すると第2・第3の両方が有効になり、両方の透明化の組合せで表現されます。

「bAlpha」値を振り「dwFlags」設定の組み合わせで、今回のフォームを表示してみたのが図4-6です。

bAlpha値とcrKey有無でのフォーム透明化の状態一覧
図4-6

bAlphaを有効にした場合、bAlpha値は0~255が設定範囲ですが、ゼロではフォームの形跡すらありませんでした。また、bAlpha値を小さくしていくと、フォームのタイトル部も透明化されていくのが分かります。
一方、crKeyのみを有効にした場合(=今回)、当然ですがbAlpha値には影響を受けず、且つタイトル部は透明化されません。

今回は第4引数にLWA_COLORKEYのみを指定していますので、第2引数のcrKeyを有効としています。つまり20行目で指定した「Me.BackColor」の色の部分を透明にします。
第3引数のbAlphaにはゼロを指定していますが、今回は第3引数は無視していますので、何を設定しても同じです。

28~34行目は、ダイアログ上の十字マークを作っているImageコントロールの設定です。十字マークの縦棒はImage1、横棒はImage2です。
28行目・32行目は、その棒の太さを設定しています。今回は0.5ポイントにしましたが、ユーザーの好みだと思います。但し、2ポイント以上にすると棒が二重になりますので具合が悪いかもしれません。

十字の縦棒(Image1)は、ダイアログの上端面(Top=0)から下端面一杯(Height=Me.InsideHeight)に描画しました。
また横棒(Image2)も左端面(Left=0)から右端面一杯(Width=Me.InsideWidth)に描画しています。
当然ながら、フォーム設計時に太さも含めて配置しても問題ありません。

36~37行目は、位置を表示するためのLabelの色設定です。
文字色(ForeColor)として、黒色(=RGB(0,0,0))を指定すると、フォームのBackColorと同じになってしまうため、文字も透明になってしまいます。そのため赤色(=RGB(255,0,0))にしました。
逆に言えば RGB(0,0,0)でなければ良いので、例えば RGB(0,0,18)を設定すれば「ほぼ黒色の文字」になります。

では、標準のLabelの文字色のままではダメなのかを試してみました。標準Labelの色はシステムカラー「ボタンの文字」で番号は「&H80000012」です。RGBで言えば「RGB(0,0,18)相当」なのですが、実行してみると文字はほぼ透明になります。
理屈については結局分かりませんでしたが、RGBによる色とシステムカラーは、作りが全く違うようです。

39行目は、トグルボタン表面文字の色を赤色に変更しています。理由は文字色の場合(36~37行目)と全く同じです。
また、フォーム起動時のToggleButtonのValue値は「False」であり、それは「原点はディスプレイの左上角」であることを表しているため、「Windowモード」の「Window」の文字をボタン表面に書き込んでいます。
ボタン操作時には、図4-15のプロシージャで文字の書き換えを行います。

Initializeイベントを抜けると、フォームが表示されますが、この透明化処理は非常に重い処理です。私のPCで初回起動時は10秒ほど掛かりました。透明化を行う面積(=フォームの大きさ)は出来るだけ小さい方が良さそうです。

4-3.フォームのLayoutイベント

フォームが起動する際に発生するイベントは「Initialize」→「Layout」→「Activate」の順となります。また起動後にフォームを移動させた際には「Layout」イベントが随時発生します。
ですので、Layoutイベントに位置計算のコードを結び付けておくことで、起動直後もフォーム移動時も位置計算がされることになります。図4-7がLayoutイベントプロシージャになります。
  1. '========== ⇩③ フォームのLayoutイベントプロシージャ ====================
  2. Private Sub UserForm_Layout()
  3.  Call Data_Update
  4. End Sub
図4-7

44行目で、位置計算をするData_Updateプロシージャ(図4-8)を呼び出します。
Data_Update内のコードを全てLayoutプロシージャに記述しても良いのですが、他の機能を追加する可能性もありますので、別プロシージャにして呼び出す形にしています。

4-4.位置計算プロシージャ

図4-7、および図4-15から呼び出されるのが、図4-8の位置計算プロシージャです。
  1. '========== ⇩④ 位置計算 ====================
  2. Sub Data_Update()
  3.  Dim x As Double     '←原点から十字マークまでのポイント(左右方向)
  4.  Dim y As Double     '←原点から十字マークまでのポイント(上下方向)
  5.  Dim xu As Double     '←フォーム端から十字マークまでのポイント(左右方向)
  6.  Dim yu As Double     '←フォーム端から十字マークまでのポイント(上下方向)
  7.  Dim Border As Double   '←フォームの見えない枠線の太さ
  8.  Border = (Me.Width - Me.InsideWidth) / 2
  9.  xu = Me.Image1.Left + Me.Image1.Width / 2 + Border
  10.  yu = Me.Image2.Top + Me.Image2.Heigth / 2 + (Me.Height - Me.InsideHeight) - Border
  11.  If Me.ToggleButton1.Value = False Then      'Window(原点:ディスプレイの左上角)
  12.   x = Me.Left + xu
  13.   y = Me.Top + yu
  14.  Else                     'Excel(原点:Excelシートの左上角)
  15.   x = (Me.Left + xu - ActiveWindow.PointsToScreenPixelsX(0) * 0.75) / ActiveWindow.Zoom * 100
  16.   y = (Me.Top + yu - ActiveWindow.PointsToScreenPixelsY(0) * 0.75) / ActiveWindow.Zoom * 100
  17.  End If
  18.  Me.Label1.Caption = "Left= " & Format(x, "0.00")
  19.  Me.Label2.Caption = "Top= " & Format(y, "0.00")
  20. End Sub
図4-8

4-4-1.変数宣言

48~52行目は、プロシージャ内で使用する変数の宣言です。

最初に52行目の変数Borderについて説明します。
恥ずかしながら今まで「フォーム左上角の位置は、Left・Top で得られる」と簡単に考えていたのですが、実際にやってみると5ポイントほど右にズレている(私のPC(Win10+Excel2016)上で)事を発見。色々なサイトを調べても、その記述はありません。(「少しズレる」との書込みは2件程見つけました)
そこで調べてみた結果、図4-9のように「見えているフォームの外側にフォームの本当の外枠(図4-9の点線の部分)があり、フォームの位置は見えない左上角(図4-9の〇印)を基準にしている」みたいだ、と言うことが分かりました。
今回は「見える外枠の外側に透明な枠がある」様子から、この幅をBorderと言う変数名にしました。
フォームの寸法プロパティ
図4-9

正式な文書は見つかっていないので、以上の事は推測の域を出ませんが、寸法的には良く合います。またExcelのバージョンによって異なっている可能性もありますし、タイトルの上側には見えない枠が何故無いのかも分かりません。

48~51行目の変数は、図4-10の「フォーム内の十字の交点」と「フォームの原点」および「計測の原点」との距離としました。
フォーム内部と原点の距離
図4-10

4-4-2.静的な変数の計算

54~56行目は、固定されている変数の計算をしています。図4-11で説明します。
フォーム内の十字マーク中心までの寸法
図4-11

まず54行目では「Border」の値を求めます。 図4-11の様に「見えない枠がフォームの左右に同じ太さで存在する」と仮定すると「 (Me.Width - Me.InsideWidth) / 2」で求まります。
尚、フォーム下側にも見えない枠があるのですが、それも同じ太さと仮定をしています。

次に左右方向の、フォーム原点(〇印)からImageコントロールで描いた十字の中心までの距離を求めます。(55行目)
Image1.Leftで求まる距離は InsideWidthを基準とした距離ですので、フォーム原点からの距離にするには、見えない枠Borderの太さを足す必要があります。またImage1.LeftはImage1の左端からの距離ですので、Image1の太さ(Width)の半分を足します。
よって求めたい左右方向距離 xu は「Me.Image1.Left + Me.Image1.Width / 2 + Border」になります。

最後に上下方向の、フォーム原点(〇印)からImageコントロールで描いた十字の中心までの距離を求めます。(56行目)
まずImage2の上からの距離(InsideHeight基準)+Image2の太さ(Height)の半分 までは左右方向と同等です。あとはタイトル部の高さを足す必要があるのですが、これは全体の高さHeightからInsideHeightとBorderを引いて求めます。
よって求めたい上下方向距離 yu は「Me.Image2.Top + Me.Image2.Heigth / 2 + (Me.Height - Me.InsideHeight) - Border」になります。

今回フォームのサイズが変えられる様な仕様では無いので、この54~56行目の計算はフォーム起動時に1回だけ実行すればOKです。サンプルファイルでは一連の寸法を分かり易くするために、静的な距離計算と動的な距離計算を一つのプロシージャに記載していますが、実務ではInitializeイベント辺りに記載するのが良いと思います。

4-4-3.動的な変数の計算

58行目では、トグルボタンのON-OFFで計算式を分けています。初期状態のOFF(ToggleButton1.Value=False)の時は、ディスプレイの左上角を原点とする「Windowモード」、ボタンを押したON(ToggleButton1.Value=True)の時は、Excelのシートの角(A1セルの左上角)を原点とする「Excelモード」です。

4-4-3-1.Windowモードの計算
「Windowモード」は、図4-12の方法で計算します。
もともとフォームの位置は、ディスプレイの左上角を原点としてポイント単位で指定します。
ディスプレイ左上角の原点とフォームとの距離計算
図4-12

ですので、フォーム内の十字マークの交点の位置は以下の計算式になります。
 左右方向:x = Me.Left + xu
 上下方向:y = Me.Top + yu

4-4-3-2.Excelモードの計算
次に「Excelモード」です。
まず位置を示す座標には、「画面座標」と「ドキュメント座標」という2つの座標があります。
例えで言うと、海に大きな船が浮いていて、その船の各設備(船長室や荷物室、食堂など)には船首を基準とした番地が付いています。船の上で暮らしているだけであれば、その番地さえ分かっていれば不自由しません。
しかし船は動いていますので、船の各設備の絶対位置(経度・緯度)が知りたい場合にはその設備の所まで行き、GPS等を使うことになります。

ここで、船はExcel(=ドキュメント座標)、海はディスプレイ(=画面座標)に相当します。また、GPSはその間をつなぐ道具で、それに適した「PointsToScreenPixelsX」および「PointsToScreenPixelsY」と言うメソッドがExcelに存在します。
ドキュメント座標と画面座標の間をつないでいる様子を図4-13に表します。
画面座標とドキュメント座標との変換
図4-13

この「PointsToScreenPixelsX」および「PointsToScreenPixelsY」には引数を設定するのですが、その引数は「ドキュメント座標上の位置をポイントで」与えます。図4-13では引数に「ゼロ」を与えているために、Excelの原点であるA1セルの左上角を指すのです。
引数ゼロは、上の例で言えば船首の位置を測っているのと同等です。また船上の各設備の絶対位置を測るには、船上でその設備の位置でGPSを使って測れば良いのと同じように、引数にExcel上での位置を指定すれば良いことになります。

ちなみに「PointsToScreenPixelsX」および「PointsToScreenPixelsY」の戻り値は「ピクセル」です。ピクセルとポイントの間には、以下の関係がありますので、単位変換が可能です。

72pt(ポイント)=96px(ピクセル)   (「ピクセル → ポイント」換算時は、ピクセルに「 72/96 (=0.75) 」を掛ける)

ですので、原点をExcel基準にした場合の計算式は、59~60行目の「ディスプレイ左上角を原点とした距離」に対し、左右方向は「PointsToScreenPixelsX(0)x 0.75(ピクセル→ポイント変換)」を引き算し、上下方向は「PointsToScreenPixelsY(0)x 0.75(ピクセル→ポイント変換)」を引き算したものになります。
なお、「PointsToScreenPixelsX」および「PointsToScreenPixelsY」はWindowオブジェクトのメソッドですので、オブジェクトとしては「ActiveWindow」を指定します。

ここまでは、Excelの表示倍率が100%の時の話しです。
Excelを拡大縮小した場合の考慮が必要です。図4-14に100%画面(左側のシート)と50%画面(右側のシート)を並べてみました。
Excelを拡大縮小した場合の距離
図4-14

左側シート(100%画面)で、Excel原点から「x軸=100ポイント、y軸=100ポイント」離れた点に、今回の透明ダイアログの十字があるとします。この場合は、X軸で言えば「Me.Left + xu - ActiveWindow.PointsToScreenPixelsX(0) * 0.75」= 100 ポイントとなる位置です。
十字の位置をそのままにして、Excelを縮小して50%画面にしたのが図4-14の右側になります。十字の位置は動いていませんが、50%画面になっているため、十字は倍の200ポイントに居ることになります。

Excelの拡大縮小率は「ActiveWindow.Zoom」で取得でき、それを使い計算式を補正したのが以下となります(62~63行目)。

 x = (Me.Left + xu - ActiveWindow.PointsToScreenPixelsX(0) * 0.75) / ActiveWindow.Zoom * 100
 y = (Me.Top + yu - ActiveWindow.PointsToScreenPixelsY(0) * 0.75) / ActiveWindow.Zoom * 100

4-4-4.フォーム内のLabelへの書込み

以上で、ディスプレイ左上角が原点、またはExcelシート左上角が原点の距離(単位:ポイント)が、変数x・変数yに代入されましたので、左右方向(x値)はLabel1に、上下方向(y値)はLabel2に表示します。

表示するにあたり、x値・y値はDouble型ですので小数点以下の桁数が多い場合がありますので、とりあえず小数点2桁に揃えることにしました。
関数としてはFormatを使用し、x値であれば「Format(x, "0.00")」とし、数値の先頭に「Left=」という文字列を結合させて表示しています。(66~67行目)

4-5.トグルボタンによる切替え

今回のダイアログには、ディスプレイ左上角を原点とする「Windowモード」とExcelシート左上角を原点とする「Excelモード」を切り替えられるように作りました。
図4-15は、その切替ボタンであるToggleButtonをクリックした時に動作するプロシージャです。
  1. '========== ⇩⑤ トグルボタンによる切替え ====================
  2. Private Sub ToggleButton1_Click()
  3.  If ToggleButton1.Value = True Then
  4.   ToggleButton1.Caption = "Excel"
  5.  Else
  6.   ToggleButton1.Caption = "Window"
  7.  End If
  8.  Call Data_Update
  9. End Sub
図4-15

初期状態(フォームの起動直後)は、ToggleButtonは「押されていない状態(Value値=False)」です。押されていない状態は「Windowモード」ということにしましたので、初期は図4-4の40行目で「Window」の文字を表面に記入しています。
その状態からボタンが押されると、ToggleButtonのValue値はTrueに変わり、図4-15のプロシージャが呼び出されます。

まず71行目のIf文で、Value値で仕訳けられます。
「ToggleButtonが押された状態(=Excelモード)」では72行目が実行され、ボタン表面の文字列が「Excel」に変わります。続いてIf文を抜け出し、76行目を実行します。
76行目は「Date_Updateプロシージャ(=図4-8)」を呼び出しており、「Excelモード」としての計算をし、ダイアログに位置を表示します。

反対に「ToggleButtonが押されていない状態(=Windowモード)」では74行目が実行され、ボタン表面文字列が「Window」に変わります。その後 If 文を抜け、76行目で「Date_Updateプロシージャ(=図4-8)」を呼び出し、「Windowモード」としての計算を行いダイアログに位置を表示します。

尚、76行目の「Data_Update」プロシージャの呼出しをしないと「ダイアログのボタンを切り替えても表示が変わらない」現象が発生します。これは「ダイアログを移動しないとLayoutイベントが発生しない」→「Data_Update」が実行されない ためです。

5.標準モジュール(Module1)

サンプルファイルでは、透明ダイアログを試行し易いように「シート上にダイアログ起動用のボタン」を配置しました。
ボタンを押すと図5-1のプロシージャが動作し、モードレスでフォームが起動するようにしてあります。
  1. '========== ⇩⑥ シート上のボタンからのフォーム起動 ====================
  2. Sub start()
  3.  UserForm1.Show 0
  4. End Sub
図5-1

実務で使う場合はアドイン登録等をして、どんなファイルにも対応できるように改造して下さい。

6.最後に

今回の「Excelモード」での計測方法は、直接ドキュメント座標で計測しているわけではありません。悪く言えば画面座標上の距離を、最後にドキュメント座標の値に置き換えているだけです。
ですので今回の弱点は「画面分割やウィンドウ枠固定などには対応できない」事です。

これを解決しようとすると、画面分割等のモードや分割位置を検知し、エリアを分けて計算するようなプログラムにする必要があると思います。しかしExcelとしてそこまでの機能が必要なのかは疑問です。
例えばシート上に散らばっている図形の位置を調べようとするなら、シート上の図形を走査して位置を調べる方法があるように、目的を達成するのにはいくつもの方法があるはずです。
「この方法しかない」などと凝り固まらず、頭を柔らかくして他の方法を考えることも重要だと思います。

今回のアプリを通常のExcel作業の中では使うことはあまり無いかもしれませんが、Excel他のアプリを作る際のレイアウト決め等には少しは役に立つのではと思います。


Excel図形等の位置、ディスプレイ上の位置の取得(it-037.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。