Created
February 28, 2020 15:01
-
-
Save KotorinChunChun/a18499cc1efcb1c38b1abd51c53f0f71 to your computer and use it in GitHub Desktop.
特定のエクセルウィンドウだけを並べて表示する関数
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'特定のエクセルウィンドウだけを並べて表示する | |
'https://twitter.com/KotorinChunChun/status/1228606400768593920?s=20 | |
Sub ExcelWindowArrange(targets, arrange_style As XlOrientation) | |
Const PROC_NAME = "ExcelWindowArrange" | |
Dim win As Window | |
Dim obj As Variant | |
'targetsを解析:整列対象のWindowコレクションを準備 | |
Dim arrangeWindows As Collection: Set arrangeWindows = New Collection | |
For Each obj In targets | |
Select Case TypeName(obj) | |
Case "Window": arrangeWindows.Add obj | |
Case "Workbook": arrangeWindows.Add obj.Windows(1) | |
Case "Worksheet": arrangeWindows.Add obj.Parent.Windows(1) | |
'他の型への対応は必要なら作る | |
Case Else: Debug.Print PROC_NAME, "No Defined TypeName: " & TypeName(obj): Stop | |
End Select | |
Next | |
If arrangeWindows.Count < 2 Then | |
Debug.Print PROC_NAME, "targetsが足りない" | |
' Err.Raise 9999, PROC_NAME, "targetsが足りない" | |
Exit Sub | |
End If | |
'整列対象外とする窓のWindowコレクションを準備 | |
Dim withoutWindows As Collection: Set withoutWindows = New Collection | |
For Each win In Application.Windows | |
If win.Visible Then | |
Dim arrWin As Window | |
For Each arrWin In arrangeWindows | |
If win.Caption = arrWin.Caption Then GoTo ContinueFor | |
Next | |
withoutWindows.Add win | |
End If | |
ContinueFor: | |
Next | |
'-------------------------------------------------- | |
'非表示にして整列されないようにする (別案 win.WindowState = xlMinimized) | |
For Each win In withoutWindows: win.Visible = False: Next | |
'2番目以降のウィンドウを先頭と同じデスクトップへ。 | |
arrangeWindows(1).WindowState = xlNormal '最大時の座標は設定出来ない | |
' Debug.Print arrangeWindows(1).Caption, arrangeWindows(1).Left, arrangeWindows(1).Top | |
For Each win In arrangeWindows | |
win.WindowState = xlNormal | |
win.Left = arrangeWindows(1).Left | |
win.Top = arrangeWindows(1).Top | |
Next | |
'並べて表示 | |
Windows.Arrange ArrangeStyle:=arrange_style | |
'非表示にしておいた窓を再表示 (別案 win.WindowState = xlNormal) | |
For Each win In withoutWindows: win.Visible = True: Next | |
'整列対象のウィンドウを前面へ。先頭窓をアクティブへ | |
For Each win In arrangeWindows: win.Activate: Next | |
arrangeWindows(1).Activate | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment