◆ [Excel] メモ帳を使ってマクロをデバッグする。
こんにチワワ。どーもボキです。
12連休です。

Excelマクロのデバッグをちょっと楽にするデバッグ用関数WriteNote
引数で渡した文字列をメモ帳に出力するよ。
Dim hNotePad, hNotepadEditClass As Long

' Windows API
Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String _
) As Long

Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" ( _
ByVal hwndParent As Long, _
ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, _
ByVal lpszWindow As String _
) As Long

Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" ( _
ByVal hWnd As Long, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As String _
) As Long

Declare Function PostMessage Lib "user32" Alias "PostMessageA" ( _
ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long _
) As Long

Declare Function SetWindowPos Lib "user32.dll" ( _
ByVal hWnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long _
) As Long

Declare Function SetWindowText Lib "user32.dll" Alias "SetWindowTextA" ( _
ByVal hWnd As Long, _
ByVal lpString As String _
) As Long

' メモ帳に文字列を出力
Public Sub WriteNote(s As String)
Const EM_REPLACESEL As Long = &HC2
Const EM_SETMODIFY As Long = &HB9
Const WM_NULL As Long = &H0
Const ES_AUTOVSCROLL As Long = &H40
Const HWND_TOPMOST As Long = -1
Const SWP_NOSIZE As Long = 1
Const SWP_NOMOVE As Long = 2
Const SWP_NOACTIVATE As Long = &H10

hNotePad = FindWindow("Notepad", vbNullString)
If hNotePad = 0 Then
' メモ帳がない⇒起動
Shell "Notepad.exe", vbNormalFocus
Do While hNotePad = 0
hNotePad = FindWindow("Notepad", vbNullString)
DoEvents
Loop

Call SendMessage(hNotePad, WM_NULL Or ES_AUTOVSCROLL, 0, 0) ' 自動スクロール
Call SetWindowText(hNotePad, ThisWorkbook.Name) ' メモ帳キャプション変更
Call SetWindowPos(hNotePad, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE Or SWP_NOACTIVATE) ' 最前面

hNotepadEditClass = FindWindowEx(hNotePad, 0, "Edit", vbNullString)
End If

Call SendMessage(hNotepadEditClass, EM_REPLACESEL, 0, s & vbCrLf) ' 文字列を送信
Call SendMessage(hNotepadEditClass, EM_SETMODIFY, 0, 0) ' 変更フラグOFF
End Sub


'Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Const WM_QUIT = &H12
' Call PostMessage(hNotePad, WM_QUIT, 0, 0)
'End Sub

' 終了時にメモ帳を閉じる
Private Sub Auto_Close()
Const WM_QUIT = &H12
Call PostMessage(hNotePad, WM_QUIT, 0, 0)
End Sub




[PR]
by yozda | 2015-04-25 20:03 | プログラミング | Trackback(1) | Comments(0)
トラックバックURL : http://yozda.exblog.jp/tb/21159515
トラックバックする(会員専用) [ヘルプ]
Tracked from ( … > Z_ ̄∂ at 2015-04-25 19:57
タイトル : [Excel] OutputDebugStringを使っ..
こんばんワイン。どーもボキです。 こちらはAPIを使う方法。OutputDebugStringでの出力メッセージを拾うため、DebugViewもいるよ。Declare Sub OutputDebugString Lib "kernel32.dll" Alias "OutputDebugStringA" (ByVal lpOutputString As String) Sub dprintf(v) Dim i, s, ityp ...... more
<< [Delphi] TShell... [Excel] OutputD... >>