メモ2

エクセルアプリケーション(インスタンス)

表示・非表示

エクセル.Visible = True   ' 表示
エクセル.Visible = False  ' 非表示

ウィンドウの最大化・最小化

エクセル.WindowState = xlMaximized  ' 最大化
エクセル.WindowState = xlMinimized  ' 最小化
エクセル.WindowState = xlNormal     ' 通常

定数一覧

定数表示状態
xlNormal通常表示
xlMaximized最大化表示
xlMinimized最小化表示

ブック

マクロ実行中のブックをアクティブにする

Workbooks(ThisWorkbook.Name).Activate

マクロ実行中のブックを選択

ThisWorkbook

マクロ実行中ブックのフルパスを取得

ThisWorkbook.FullName

マクロ実行中ブックのディレクトリパスを取得

ThisWorkbook.Path

マクロ実行中のブック名を取得

ThisWorkbook.Name

名前を付けて保存

ブック.SaveAs Filename:= "ファイル名(フルパス)"

ブックの最大化・最小化

Workbooks("ブック名").WindowState = 定数

定数一覧

定数表示状態
xlNormal通常表示
xlMaximized最大化表示
xlMinimized最小化表示

サンプル:1つ目をブックを最大化

Workbooks(1).Activate
ActiveWindow.WindowState = xlMaximized

サンプル:ブック(Book1)を最大化

Dim window1 As Window
Set window1 = Windows("Book1")
window1.WindowState = xlMaximized

サンプル:1つ目のブックを最大化

Dim window1 As Window
Set window1 = Windows(1)
window1.WindowState = xlMaximized

シート

シートをアクティブにする

Worksheets(1).Activate
Sheets(1).Activate

アクティブのシートを選択

ActiveSheet

アクティブのシート名を取得

ActiveSheet.Name

シートを最後に追加

Worksheets.Add after:=Worksheets(Worksheets.Count)

シートを2つ目に追加(シートを取得する場合)

Set 追加したシート = Worksheets.Add(after:=Worksheets(1))

シートのコピー

Worksheets("Sheet2").Copy After:=Workbooks("Book2").Worksheets("Sheet1")

行列

列の挿入

シート.Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
シート.Range("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

列の非表示

Columns("C:C").EntireColumn.Hidden = True

範囲選択(Range)

値をそのまま取得

Range("A1").Value

見えている値を取得

Range("A1").Text
  • 「指数表記」や数値や日付が表示しきれない場合の「#####」など

周囲が空白の行列に囲まれた領域を取得(CurrentRegion)

CurrentRegionプロパティは空白のセルで囲まれた範囲を読み取り、参照するプロパティです。

Range("A1").CurrentRegion

サンプル:A2を含む範囲で周囲が空白の行列に囲まれた領域を選択

Range("A2").CurrentRegion.Select

コピー&ペースト

アクティブシートの全セルのコピー

Cells.Copy

可視セルのコピー

Selection.SpecialCells(xlCellTypeVisible).Copy

ペースト

ActiveSheet.Paste

値を貼り付け(値)

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
シート.Range("A1").PasteSpecial Paste:=xlPasteValues

貼り付け(図形オブジェクト)

シート.Cells.PasteSpecial

テーブル

テーブル全体を取得

Set オブジェクト = Range("テーブル名")

テーブル内を相対参照

表の左上を (1, 1) として、指定し取得できます。

なお、表の見出し部分は含まれません。ただし、行を「0」と指定することにより、表の見出しを取得することが可能です。

また、「Range」のデフォルトメソッドは「Cells」なので、「Cells」は省略できます。

基本形

Range("テーブル名").Cells(行番号, 列番号)

省略形

Range("テーブル名")(行番号, 列番号)
Set オブジェクト = Range("テーブル名")
オブジェクト(行番号, 列番号)

テーブルの行数を取得

Range("テーブル名").Rows.Count

サンプル:行毎にループ処理を行う

Dim 行番号 As Long
For 行番号 = 1 To 値段表.Rows.Count
  ' ここで行ごとの処理を行います。
Next

テーブルの列数を取得

Range("テーブル名").Columns.Count

特定の見出しの列を取得する

Set オブジェクト = Range("テーブル名[見出し名]")

サンプル:セルを選択

Range("テーブル名[見出し名]").Cells(行番号, 列番号)

なお、取得した範囲は1列に限定されているので、セルを取得する場合は列番号が省略できます。

Range("テーブル名[見出し名]").Cells(行番号)

サンプル:特定の見出しの可視セルをコピー&ペースト

Range("テーブル名[見出し名]").SpecialCells(xlCellTypeVisible).Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

オートフィルタ

<サンプル> A列が0の行を非表示

Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="<>0"

または

Range("A1").AutoFilter Field:=1, Criteria1:="<>0"

配列形式で特定の値を表示(Excel 2007以降限定)

配列形式の場合、非表示にする対象を直接選択することはできません。

よって、非表示にする対象を選択したい場合は、全ての値から非表示にする値を削除した配列を作成する必要があります。

配列に格納された値を指定

シート.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=配列, Operator:=xlFilterValues

配列を直接指定

シート.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Array("値1", "値2", "値3"), Operator:=xlFilterValues

オートシェイプ

オートシェイプの選択(下記は同一)

ActiveSheet.Shapes("オートシェイプ名")
ActiveSheet.Shapes.Range(Array("オートシェイプ名"))

オートシェイプの背景色

考察

  • マクロの記録
    ActiveSheet.Shapes.Range(Array("オートシェイプ名")).Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset28
  • 実行可能
    ThisWorkbook.Sheets(1).Shapes("オートシェイプ名").ShapeStyle = msoShapeStylePreset28
  • エラー
    ThisWorkbook.Sheets(1).Shapes("オートシェイプ名").ShapeRange.ShapeStyle = msoShapeStylePreset28

日付の操作

日付を取得

Msgbox Date	'2014/04/15

指定された日時値から月を取り出す

Month(Date)

指定された日時値から日を取り出す

Day(Date)

日付や時間に特定の間隔を追加して返す

DateAdd(interval, number, date)

なお、DateAdd関数は無効な日付を返しません。(下記参考)

DateAdd("m", 1, "2014/01/31") '2014/02/28

日付のフォーマットを変更

MsgBox Format(Date, "yyyymmdd")

参考

Excel関数の出力

SUM関数

セルにSUM関数を出力したい場合はR1C1形式にすると出力し易いことが多いです。なお、R1C1形式で記述しても出力結果はA1形式で出力されます。

ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

書式設定

セルの幅

Columns("B:B").EntireColumn.AutoFit
シート.Columns("B:B").ColumnWidth = 12.34
シート.Cells.EntireRow.AutoFit

IE

IEの最大化

ret = ShowWindow(IE.hWnd, 3)	’最大化

IEの最小化

ret = ShowWindow(IE.hWnd, 2)	’最小化

ハンドルの取得

ハンドルの取得用のAPI

最前面のウィンドウハンドルを取得

Private Declare Function GetForegroundWindow Lib "user32" Alias "GetForegroundWindow" () As Long
使用サンプル
Dim ハンドル As Long: ハンドル = GetForegroundWindow

座標でハンドルを取得

Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long
使用サンプル
Dim ハンドル As Long: ハンドル = WindowFromPoint(0, 0)

子ウィンドウを指定して、その親ウィンドウのハンドルを取得

Private Declare Function GetParent Lib "user32" Alias "GetParent" (ByVal hwnd As Long) As Long

開いているすべてのウィンドウのハンドルを取得

Private Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

指定したウィンドウの次のウィンドウのハンドルを取得

Private Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long

ハンドル取得後のAPI

タイトルバーの文字列を取得

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
引数
引数説明
hwndウィンドウテキストの取得元ウィンドウを表すウィンドウハンドル
lpStringウィンドウテキストを格納するバッファ
cchウィンドウテキストを格納するバッファのサイズ
サンプル
Function ウィンドウテキストを取得()
  ハンドル = GetForegroundWindow
  テキスト格納バッファ = String(100, Chr(0))
  GetWindowText ハンドル, テキスト格納バッファ, Len(テキスト格納バッファ)
  ウィンドウテキストを取得 = Left(テキスト格納バッファ, InStr(1, テキスト格納バッファ, Chr(0)) - 1)
End Function
  • 必要API:「GetForegroundWindow」「GetWindowText」
  • 格納するバッファサイズを指定する関係上、事前にNull文字で埋めたテキスト格納バッファを用意し、変数を返す時に後方のNull文字をカットする

タイトルバーの文字列を変更

Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String) As Long

ウィンドウの位置とサイズを変更

Private Declare Function MoveWindow Lib "user32" Alias "MoveWindow" (ByVal hwnd As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long

ウィンドウを閉じる

Private Declare Function DestroyWindow Lib "user32" Alias "DestroyWindow" (ByVal hwnd As Long) As Long

キャプチャー

BitBlt関数を使う。

サンプル

サンプル:座標でハンドルを取得してタイトルバーの文字列を変更

'座標でハンドルを取得
Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long
'タイトルバーの文字列を変更
Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String) As Long


Sub 座標によるハンドル取得()
  Dim ハンドル As Long: ハンドル = WindowFromPoint(0, 0)
  Dim Ret As Long: Ret = SetWindowText(ハンドル, "新しいウィンドウテキスト")
End Sub

参考

実践サンプル

コマンドプロンプトの実行および結果の取得

Function コマンドプロンプト(cmd As String) As String
   
   Dim WSH   As Object: Set WSH = CreateObject("Wscript.Shell")
   Dim wExec As Object: Set wExec = WSH.Exec("%ComSpec% /c " & cmd)
   
   Do While wExec.Status = 0
       DoEvents
   Loop
   
   コマンドプロンプト = wExec.StdOut.ReadAll
   Set wExec = Nothing
   Set WSH = Nothing
   
End Function
  • 結果が不要な場合は無視すればOK
  • コマンドプロンプトで返り値がないものは返らない

参考

アタッシェケース

Public Const アタッシェケース本体 = "D:\atcs2828\AttacheCase.exe"

Sub アタッシェケース暗号化(入力パス As String, 出力パス As String, パスワード As String)
   
   
   ' 入力パスの末尾に区切り記号が含まれていたら削除する
   If Right(入力パス, 1) = "\" Or Right(入力パス, 1) = "/" Then
       入力パス = Left(入力パス, Len(入力パス) - 1)
   End If
   
   ' 出力パスの末尾に区切り記号が含まれていたら削除する
   If Right(出力パス, 1) = "\" Or Right(出力パス, 1) = "/" Then
       出力パス = Left(出力パス, Len(出力パス) - 1)
   End If
   
   
   Dim Ret As String: Ret = コマンドプロンプト(アタッシェケース本体 & " " & 入力パス & " /saveto=" & """" & 出力パス & """" & " /exit=1" & " /p=" & パスワード)
   
End Sub
  • ドライブ直下への保存は不可?
  • 入力パスはフォルダまたはファイル
  • サンプル:Call アタッシェケース暗号化("E:\111.txt", "E:aaa\aaa" , "1234")
  • サンプル:Call アタッシェケース暗号化("E:\111" , "E:aaa/aaa/", "1234")

参考

リモートデスクトップ

Sub リモートデスクトップ(ByVal 対象PC As String, Optional ByVal exeファイル As String = "mstsc.exe") 'C:\Windows\System32\mstsc.exe
  Call コマンドプロンプト(exeファイル & " /v:" & 対象PC)
End Sub
  • 対象PC:PC名またはIPアドレス
  • exeファイル:リモートデスクトップ「mstsc.exe」(省略可能)

必要なファイル

  • C:\Windows\System32\mstsc.exe
  • C:\Windows\System32\ja-JP\mstsc.exe.mui

VBA

Call コマンドプロンプト("C:\Windows\System32\mstsc.exe /v:PC名またはIPアドレス")
  • ファイル「C:\Windows\System32\mstsc.exe」は単に「mstsc.exe」でも良い
  • ファイル「C:\Windows\System32\mstsc.exe」は上記の必要なファイルを特定のフォルダに置いて指定しても良い
  • 自作のコマンドプロンプト関数が必要

問題

  • アカウント名およびパスワードが入力できない
  • リモートデスクトップを終了させないとVBAの処理に戻らない

その他

配列の開始番号を変える

「Option Base」を指定すると配列の開始番号を変更することができます。

例)「Option Base 1」を記述し、Dim A(5) As Integer と宣言すると、A(1)~A(5)の5要素となります。

なお、「Option Base」はプロシージャ内には記述できません。

メモ

  • VBA実行ブックを閉じてしまうと、それ以降のVBAは実行されない
  • Rangeで指定している範囲を超えてCellsで値を取得した場合、取得できてしまう
  • Cellsの引数を省略する「例:Rnage(5)」
    範囲が1列なら列が省略されているとみなして、1として実行される
    Range(5, 1) と同様
    
    範囲が2列以上なら行が省略されているとみなして、1として実行される
    Range(1, 5) と同様
  • セル等を選択するには対象シートをアクティブにする必要がある
    シート.Activate
    シート.Range("A1").Select