Hatena::ブログ(Diary)

はけの徒然日記 このページをアンテナに追加 RSSフィード

2005 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2006 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2007 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2008 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2009 | 01 | 02 | 04 | 05 | 08 | 09 | 10 | 12 |
2010 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 10 | 11 |
2011 | 01 | 02 | 03 | 04 | 11 | 12 |
2012 | 02 | 03 | 05 |
2014 | 02 | 03 | 04 | 05 | 12 |
2015 | 05 | 07 | 08 | 09 | 10 | 11 | 12 |
2016 | 01 | 02 | 04 | 05 | 08 | 09 | 11 | 12 |
2017 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 10 |
2018 | 02 | 03 | 04 | 05 |

2018-03-18(Sun)

EXCEL VBAメモ - ユーザーフォームを常に最前面にする(Excel2016)

背景

職場PC現在Windows7 Excel2010の環境統一されていて、そこで以下のようなExcelマクロ使用している。

  1. マクロ付ブックを起動する
  2. 同時にユーザーフォーム自動で(VbModelessオプションにて)起動する
  3. 対象ブックを開く(この時ユーザーフォーム最前面に表示されている)
  4. 対象ブックに対し、ユーザーフォーム操作しながら作業を行う。

Excel2016での問題点

ユーザーフォーム操作すると、マクロ付ブックがアクティブになり、対象ブックが隠れてしまう。また、対象ブックをアクティブにすると今度はユーザーフォームが隠れてしまう。

これは、Excel2013以降でExcel自体が変更になった結果みたい。

でも、これでは今後Windows10等に環境移行した際に、作業に支障でまくりなので何等かの対策必要となる。


ユーザーフォームを常に最善面にくる様にする方法

標準モジュールに以下を追加(64bit用のコード

Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1


Public Const HWND_TOP = 0
Public Const HWND_BOTTOM = 1
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2


Public Declare PtrSafe Function SetWindowPos Lib "user32" _
    (ByVal hWnd As LongPtr, _
        ByVal hWndInsertAfter As LongPtr, _
        ByVal X As LongPtr, _
        ByVal Y As LongPtr, _
        ByVal cx As LongPtr, _
        ByVal cy As LongPtr, _
        ByVal uFlags As LongPtr) As Long


Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

ユーザーフォームに以下を追加

Private Sub UserForm_Initialize()
    Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"

    Dim ret As Long
    Dim formHWnd As Long

    'Get window handle of the userform
    formHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)
    'If formHWnd = 0 Then Debug.Print Err.LastDllError

    'Set userform window to 'always on top'
    ret = SetWindowPos(formHWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
    'If ret = 0 Then Debug.Print Err.LastDllError

    Application.WindowState = xlMinimized ' この操作は必須

End Sub

ただしこれでも、ユーザーフォーム操作後に、対象ブックのセルを選択しなおす等の操作をする際に、一旦対象ブックを選択しなおすという作業必要なので、Excel2010と完全に同じ操作手順というようにはいかない。


参考サイト

2017-08-12(Sat)

EXCEL VBAメモ - xlsmファイル中のマクロの書き換え

マクロ入りの雛形ファイルコピーして作成されたExcelファイルが多数ある場合の一括マクロ更新などに使用



基本的更新対象ファイル対象モジュールの内容を一括削除して、予め更新マクロExcelファイルに用意した、同名のモジュールの内容をそのままコピーさせるのがわかり易いかな。

事前設定

更新マクロ動作させるExcelで以下の設定を行う

ファイル

オプション

セキュリティセンター

セキュリティセンターの設定

マクロの設定

VBAプロジェクトオブジェクトモデルへのアクセスを信頼する。にチェック


機能用のプロシージャ

コードモジュールの行数表示

Private Sub コードモジュールの行数表示()
    Dim cnt As Long
    cnt = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.CountOfLines
    MsgBox "Module1の行数 " & cnt
    
    cnt = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.CountOfLines
    MsgBox "Sheet1の行数 " & cnt
End Sub

ThisWorkbookの全行を削除

Private Sub ThisWorkbook全行削除()
    Dim cnt As Long
    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook")
        cnt = .CodeModule.CountOfLines
        .CodeModule.DeleteLines 1, cnt
    End With
End Sub

Sheetモジュールの全行を削除

Private Sub シートモジュール全行削除()
    Dim cnt As Long
    With ThisWorkbook.VBProject.VBComponents("Sheet1") ' "Sheet1"はオブジェクト名であり、Nameプロパティの値ではない
        cnt = .CodeModule.CountOfLines
        .CodeModule.DeleteLines 1, cnt
    End With
End Sub

Module1の全行を削除

Private Sub コードモジュール全行削除()
    Dim cnt As Long
    cnt = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.CountOfLines
    ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.DeleteLines 1, cnt
End Sub

Module3の全行をModule1へコピー

Sub コードモジュールを別モジュールへコピー()
    Dim Code As String
    With ThisWorkbook.VBProject.VBComponents("Module3").CodeModule
        Code = .Lines(1, .CountOfLines)
    End With
    With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
        .AddFromString Code
    End With
End Sub

Module1の全行をTarget.xlsmのModule1へコピー

Sub コードモジュールを別ブックへコピー()
    Dim decLine As Long
    Dim allLine As Long
    
    Dim Code As String
    Dim wb As Workbook
    
    Set wb = Workbooks.Open("C:\Users\〇〇\Target.xlsm")
    
    With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
        allLine = .CountOfLines              ' 全行数
        decLine = .CountOfDeclarationLines   ' 宣言部行数
    
        ' 宣言部以外のコードをコピー
        Code = .Lines(decLine + 1, allLine - decLine)
    End With
    With wb.VBProject.VBComponents("Module1").CodeModule
        .AddFromString Code
    End With

    wb.Save
    wb.Close
End Sub

Module1を解放

Private Sub コードモジュール解放()
    With ThisWorkbook.VBProject
        .VBComponents.Remove .VBComponents("Module1")
    End With
End Sub

Module1をインポート

Private Sub コードモジュールインポート()
    With ThisWorkbook.VBProject
        .VBComponents.Import ThisWorkbook.Path & "\Module1.bas"
    End With
End Sub

標準Moduleを追加(Module名は自動Module xになる)

Private Sub 標準モジュール追加()
    With ThisWorkbook.VBProject
        .VBComponents.add vbext_ct_StdModule
    End With
End Sub

標準Moduleリストアップ

Private Sub 標準モジュール数表示()
    Dim cnt As Long, i As Long
    Dim msg As String


    With ThisWorkbook.VBProject
        msg = "全モジュールの個数:" & .VBComponents.Count & vbNewLine
        cnt = 0
        For i = 1 To .VBComponents.Count
            If .VBComponents(i).Type = vbext_ct_StdModule Then
                cnt = cnt + 1
                msg = msg & .VBComponents(i).Name & vbNewLine
            End If
        Next
        msg = msg & "標準モジュールの個数:" & cnt
    End With
    MsgBox msg
End Sub

2017-01-08(Sun)

PowerShell - Excelの操作

EXCEL用のCOMオブジェクト作成して、あとはVBAと同じ感覚で操作できるっぽいです。

一点、配列やコレクションの括弧の形状がVBAだと( )ですが、[ ]にしないとメソッドと間違えられてエラーになるので注意。


$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true

$book = $excel.Workbooks.Add()
$sht = $excel.Worksheets[1] # 括弧の形状に注意
$sht.Name = "PowerShell"

$sht.Cells(1,1).Value = "Hello World"
$sht.Range("A2").Value = "PowerShell"


$book.Save()
$book.Close()
$excel.Quit()

プロセスの解放

$excel = $null
[GC]::Collect()

または

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sht)