RCIE-ジャンクのコード屋

主に自分のためにコーディングのTIPSを蓄積しています。

トップ > VBA > VBA これがあれば戦える！実務でよく使う処理TIPS一覧

VBA これがあれば戦える！実務でよく使う処理TIPS一覧

VBA

概要

  • VBAでよく使うものをまとめた個人的なメモ
  • コード中に出てくるプレフィックスの意味
    • n: 整数
    • s: 文字列
    • b: True/False
    • t: 日付・時刻
    • v: Variant
最初にすること
Option Explicit	' Dimなしに変数を使うことは許可しない
Collection（可変長リスト）
Set c = New Collection
c.Add v要素		' v要素を末尾に追加
c.Add v要素, , n通番	' n通番の前にv要素を挿入
c.Remove n通番		' n通番を削除
c.Count			' 要素の数 => n
For Each v要素 In c	' 全てのv要素に対して操作

c(n通番) = v要素 ' n通番にv要素を代入することは「できない」

Dictionary（連想配列）
Set d = CreateObject("Scripting.Dictionary")
d(vキー) = v値		' vキーに対応するv値を代入
d.Add vキー, v値		' vキーとv値の組を追加
d.Remove vキー		' vキーを削除
d.Count			' 要素の数 => n
d.Exists(vキー)		' vキーの存在を確認 => b
For Each vキー In d	' 全てのvキーに対して操作
エラー処理
Function エラー処理例()
    On Error Goto E	' エラー発生時にラベルにジャンプ
    n = n / 0		' エラー発生！
E:			' ラベル
    MsgBox "ゼロ除算"	' エラー処理
End Function
分岐の短縮表現
IIf(b式, v成立時, v非成立時)		' b式の真偽で結果を分岐 => v
Choose(n式, v結果1, v結果2, v結果3, ...)	' n式の値で結果を分岐 => v
文字列
Len(s)				' 文字列の長さ => n
Left(s, n長さ)			' 文字列を左から切り出す => s
Mid(s, n開始)			' 文字列を途中から切り出す => s
Mid(s, n開始, n長さ)		' 文字列を途中から途中まで切り出す => s
Mid(s, n開始) = s置換		' 文字列を途中から置き換える
Right(s, n長さ)			' 文字列を右から切り出す => s
InStr(s全体, s部分)		' s全体の中でs部分が出現した箇所 => n
InStr(n開始, s全体, s部分)	' n開始より後ろから調べる => n
Format(v, s形式)		' vをs形式に従って文字列に変換する => s
'└ s形式の例: "yyyymmdd" , "hhnnss" , "0000", "###,##0.00"
Asc(s)				' sの先頭文字のコードを取得 => n
Chr(n文字コード)			' n文字コードに対応する文字を取得 => s
Val(s)				' sを数値に変換する => n
IsNumeric(s)			' sが数値か判定 => b
LTrim(s)			' 左側の空白ではない文字から切り出す => s
RTrim(s)			' 右側の空白ではない文字から切り出す => s
Trim(s)				' LTrim と RTrim => s
LCase(s)			' 大文字を小文字にする => s
UCase(s)			' 小文字を大文字にする => s
StrComp(s1, s2)			' s1とs2を比較 => -1, 0, 1
Space(n)			' n個の空白を生成 => s
String(n, 0)			' 指定の長さのNULL文字列を確保 => s
高速に大量の文字列連結をする関数
Function 連結(s As String, n長さ As Long, s追加 As String)
    Dim n長さ0 As Long: n長さ0 = n長さ
    n長さ = n長さ + Len(s追加)
    If n長さ > Len(s) Then
	s = s & String(n長さ \ 2, 0)
    End If
    Mid(s, n長さ0 + 1) = s追加
End Function

' 関数の使い方
Dim s As String, n長さ as Long	' 作業用文字列と長さが必要
連結 s, n長さ, "あいう"		' s(長さ0)に「あいう」を連結
連結 s, n長さ, "えお"		' s(長さ3)に「えお」を連結
連結 s, n長さ, "かきく"		' s(長さ5)に「かきく」を連結
s = Left(s, n長さ)		' 8文字だけ切り出す
日付
Date				' 今日の日付 => t
DateSerial(n年, n月, n日)	' 日付を生成する => t
Year(t)				' 年を取得する => n
Month(t)			' 月を取得する => n
Day(t)				' 日を取得する => n
Time				' 現在時刻 => t
TimeSerial(n時, n分, n秒)	' 時刻を生成する => t
Hour(t)				' 時を取得する => n
Minute(t)			' 分を取得する => n
Second(t)			' 秒を取得する => n
Now				' 現在の日付時刻 => t
DateAdd(s単位, n, t)		' tをs単位でn進める => t
DateDiff(s単位, t1, t2)		' t1-t2をs単位で求める => n
' └ s単位の例: "yyyy", "m", "d", "h", "n", "s"
FileSystemObject（ファイル・フォルダ共通の処理）
With CreateObject("Scripting.FileSystemObject")
.GetBaseName(sパス)		' 拡張子ぬき名前を取得 => s
.GetExtensionName(sパス)	' 拡張子を取得 => s
.GetFileName(sパス)		' 拡張子つき名前を取得 => s
.GetParentFolderName(sパス)	' 親フォルダのパスを取得 => s
End With
FileSystemObject（ファイルの操作）
With CreateObject("Scripting.FileSystemObject")
.CopyFile s元, s先				' コピー
.MoveFile s元, s先				' 移動
.DeleteFile sパス				' 削除
.FileExists(sパス)				' ファイルの存在確認 => b
.CreateTextFile(sパス, [b上書き, bユニコード])	' 作成 => TextStream
.OpenTextFile(sパス, [モード, bない場合作成])	' 開く => TextStream
' └ モード: ForReading(読込), ForWriting(書込), ForAppending(追記)
.GetFile(sパス)					' ファイルを取得 => File
End With
File（ファイルの情報）
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.GetFile(sパス)
.DateCreated		' 作成日時 => t
.DateLastAccessed	' 最終アクセス => t
.DateLastModified	' 最終更新 => t
.Path			' パス => s
.ParentFolder		' 親フォルダを取得 => Folder
.Size			' バイト数 => n
.Type			' ファイルの種類 => s
End With
FileSystemObject（フォルダの操作）
With CreateObject("Scripting.FileSystemObject")
.CreateFolder sパス	' 作成
.CopyFolder s元, s先	' コピー
.MoveFolder s元, s先	' 移動
.DeleteFolder sパス	' 削除
.FolderExists(sパス)	' フォルダの存在確認 => b
.GetFolder(sパス)	' フォルダを取得 => Folder
End With
Folder（フォルダの情報）
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.GetFolder(sパス)
.Files			' フォルダ内の全ファイル => Collection
.SubFolders		' フォルダ内の全フォルダ => Collection
.IsRootFolder		' フォルダがルートか判定 => b
.DateCreated		' 作成日時 => t
.DateLastAccessed	' 最終アクセス => t
.DateLastModified	' 最終更新 => t
.Path			' パス => s
.ParentFolder		' 親フォルダを取得 => Folder
.Size			' バイト数 => n
.Type			' ファイルの種類 => s
End With
TextStream（ファイル読み書き）
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(sパス)
.Read(n文字数)	' n文字数だけ読み進める => s
.ReadAll	' 最後まで読み進める => s
.ReadLine	' 行末まで読み進める => s
.Write s	' sを書き込む
.WriteLine	' 改行を書き込む
.WriteLine s	' sと改行を書き込む
.AtEndOfLine	' 行末に到達したことを判定 => b
.AtEndOfStream	' ファイル終端に到達したことを判定 => b
.Line		' 現在のカーソル行 => n
.Column		' 現在のカーソル列 => n
.Skip n文字数	' n文字数だけ読み飛ばす
.SkipLine	' 行末まで読み飛ばす
End With
ワークブック
Set book = Workbooks.Open(sパス)	' ブックを開く => Workbook
Set book = Workbooks.Add		' 新規のブック => Workbook
Set book = Workbooks(sブック名)		' 既に開いたブック => Workbook
Set book = ActiveWorkbook		' 現在のブック => Workbook
With book
.Path		' ブックのあるフォルダ
.Name		' ブック名(拡張子つき)
.Worksheets	' ブックに含まれる全シート => Collection
.Save		' 保存
.SaveAs sパス	' 別名で保存
.Close		' 閉じる
End With
ワークシート
Set sheet = Worksheets.Add		' 新規のシート => Worksheet
Set sheet = Worksheets(sシート名)	' 指定のシート => Worksheet
Set sheet = Worksheets(n通番)		' 指定のシート => Worksheet
Set sheet = ActiveSheet			' 現在のシート => Worksheet
With sheet
.Copy			' 新しいブックにコピー => Worksheet
.Copy(Before:=sheet)	' sheetの前にコピー => Worksheet
.Copy(After:=sheet)	' sheetの後にコピー => Worksheet
.Move			' 新しいブックに移動 => Worksheet
.Move Before:=sheet	' sheetの前に移動
.Move After:=sheet	' sheetの後に移動
.Name = sシート名	' シート名を設定
.Delete			' 削除
End With
セルの取得
Set sheet = ActiveSheet
With sheet
Set rg1 = .Range("A1")		' A1のセル => Range
Set rg2 = .Cells(n行, n列)	' (n行,n列)のセル => Range
.Range("A1:D2")			' A1:D2のセル範囲 => Range
.Range(rg1, rg2)		' rg1とrg2を端とするセル範囲 => Range
.Rows(n行)			' n行すべて => Range
.Columns(n列)			' n列すべて => Range
rg1.Offset(n下, n右)		' (n下,n右)ずれたセル範囲 => Range
rg1.EntireRow			' rg1を含む行全体 => Range
rg1.EntireColumn		' rg1を含む列全体 => Range
rg1.Resize(1, 1)		' rg1の左上のセル => Range
rg1.Resize(n縦, n横)		' rg1の大きさを変更した範囲 => Range
rg1.End(方向)			' Ctrl+方向で移動するセル => Range
' └ 方向: xlUp, xlDown, xlToLeft, xlToRight
.UserdRange			' シートで使われている範囲 => Range
End With
セルに対する操作
With ActiveSheet.Range("A1")
.Value = s		' 内容を設定
.Formula = s		' 数式を設定
.Clear			' 削除
.ClearContents		' 削除(書式は残る)
.RowHeight = n		' 行の高さを設定
.ColumnWidth = n	' 列の横幅を設定
.AutoFit		' セルの内容に合わせて行と列を拡張する
.Copy			' クリップボードにコピー
.Copy range先		' コピペ
.Address		' このセルの位置($A$1など) => s
End With
セルの書式設定
With ActiveSheet.Range("A1")
.Font.Color = RGB(n赤, n緑, n青)			' 文字色
.Font.Bold = b					' 太字
.Font.Italic = b				' 斜体
.Font.Size = n					' 文字サイズ
.Font.Underline = 下線形式			' 下線
' └ 下線形式: xlUnderlineStyleNone, StyleSingle, StyleDouble
.Font.Name = s					' フォント名
.Interior.Color = RGB(n赤, n緑, n青)		' 背景色
.Borders.LineStyle = 罫線の種類			' 範囲を囲む罫線
' └ 罫線の種類: xlNone, xlContinuous, xlDouble, xlDash, xlDot
.Borders(罫線の位置).LineStyle = 罫線の種類	' 特定の罫線
' └ 罫線の位置: xlEdgeTop, xlEdgeBottom, xlEdgeLeft, xlEdgeRight
.Borders.Color = RGB(n赤, n緑, n青)		' 罫線の色
.Borders.Weight = 罫線の太さ			' 罫線の太さ
' └ 罫線の太さ: xlHairLine, xlThin, xlMedium, xlThick
End With
セルの表示形式
With ActiveSheet.Range("A1")
.NumberFormatLocal = 書式
' └ 標準: G/標準
' └ 数値: 0_
' └ 日付: yyyy/mm/dd
' └ 時刻: hh:mm:ss
' └ 百分率: 0.00%
' └ 分数: # ?/?
' └ 指数: 0.E+00
' └ 文字列: @
End With
動作高速化
Application.ScreenUpdating = b	' 画面の更新を有効,無効にする
With ActiveSheet.Range("A1").Resize(n行数, n列数)
.Value = a二次元配列		' 二次元配列を１回で書き込む
End With
二次元配列
Dim a二次元配列() As String
ReDim a二次元配列(1 To n行数, 1 To n列数)	' 行数と列数を指定して配列を作成
a二次元配列(n行, n列) = s		' 代入する
ソート
With ActiveSheet
Set rg = Cells(n行, n列)		' 整列範囲の左上端
.Sort.SortFields.Clear			' 整列方法を初期化
.Sort.SetRange rg.Resize(n縦, n横)	' 整列範囲を設定
.Sort.SortFields.Add _
    Key:=rg.Offset(0, n基準列), _	' 整列の基準列を設定
    Order:=昇順・降順			' xlAscending/xlDescending
.Sort.Header = xlNo			' 範囲にヘッダ行は含まない
.Sort.Apply				' 整列の実行
End With
他のプログラムの実行
nID = Shell(sコマンド, 状態)		' sコマンドを実行する/タスクID => n
' └ 状態: vbHide, vbNormalFocus, vbMinimizedFocus, vbMaximizedFocus
' └     : vbNormalNoFocus, vbMinimizedNoFocus
AppActivate nID				' プログラムをアクティブにする
AppActivate sタイトル			' プログラムをアクティブにする
Shell "Explorer.exe " + sパス		' フォルダを開く
Shell "Explorer.exe /select," + sパス
SendKeys sキー文字列 b待機		' 文字を自動入力する
' └ 特殊文字: {BS}{DEL}{ENTER}{ESC}{UP}{LEFT}{DOWN}{RIGHT}{TAB}{F1}
' └         : Shift +(abc) , Ctrl ^v , Alt %{F4}
Application.Wait DateAdd("s", n, Now)	' n秒停止する
With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText s: .PutInClipboard		' クリップボードにsをコピー
End With
待機中に応答可能にする関数
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal n As Long)
Function 待機(n秒)
    Dim t終了 = DateAdd("s", n, Now)
    Do While t終了 > Now
        DoEvents
        Sleep(15)
    Loop
End Function
正規表現
With CreateObject("VBScript.RegExp")
.Pattern = s					' パターン
.Global = b					' 全体から検索
.IgnoreCase = b					' 大文字小文字同一視
For Each match In .Execute(s)			' 全ての一致に対して操作
    match.FirstIndex				' 先頭の位置
    match.Length				' 長さ
    match.Value					' 文字列
    For Each sサブ一致 In match.SubMatches	' 全てのサブ一致に対して
        MsgBox sサブ一致
    Next
Next
End With
正規表現ヒント
\d \D \s \S .		' 数字・数字以外・空白・空白以外・改行以外
* + ?			' 0回以上・1回以上・0回か1回（最長一致）
*? +? ??		' （最短一致）
{n} {n,m} {n,}		' n回・n～m回・n回以上
^ $			' 行頭・行末
[abc] [a-z] [^xyz] .	' abcどれか・a～zどれか・xyz以外の何か
\w \W			' [a-zA-Z0-9_]・それ以外
pro(ject|gram)		' projectかprogram
vb(?=a)			' vbaを見つけた時のvb
vb(?!s)			' vb_を見つけた時のvb、ただしvbsはダメ
\1 \2 \3		' １番目・２番目・３番目サブ一致
$1 $2 $3		' １番目・２番目・３番目サブ一致（Replace用）
$_ $& $` 		' 入力文字列・一致文字列・一致より前方（Replace用）