Office

エクセルマクロでよく使うロジック

ファイル操作

ファイル出力

'出力ファイルを定義
Dim datFile As String
datFile = ActiveWorkbook.Path & "\test.txt"
'ファイルオープン
Open datFile For Output As #1
'ファイル書込み
Print #1, "test"
'ファイルクローズ
Close #1

シート操作

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

Sheets("Sheet1").Activate

シートをクリアする

'シートをクリア
Sheets("Sheet1").Cells.Clear

'シートの指定行以下を削除
Sheets(sheetNameOutputMFZ).Rows("2:" & Rows.Count).Delete

ワークシート削除

'確認メッセージを一時的にOFF
Application.DisplayAlerts = False
'シートを削除
Sheets("Sheet1").Delete
'確認メッセージをON
Application.DisplayAlerts = True

シート名を変更する

Sheets("変更前").name = "変更後"

シートをコピーする

Sheets("Sheet1").Copy after:=Worksheets(Worksheets.Count)

セル操作

セル領域をクリア

'セル領域をクリア
Range("A10:B10000").Clear
'別の書き方
'Range(Cells(10, 1), Cells(10000, 2)).Clear

セルの最終行、最終列を取得

'最終行の取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

'最終列の取得
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

セル内改行がある場合のデータ取り出し

UserNameArray = Split(UserName, vbLf)
UserName = UserNameArray(0)

見出し行を残してシートのデータをクリアする

'A1から、全選択(CurrentRegion)して、選択範囲を2行下にずらして(offset)クリア
Sheets("Sheets1").Range("A1").CurrentRegion.Offset(2, 0).Clear

変数

変数定義時に初期値も設定

Dim AAA As Integer: AAA = 5

パブリック変数

'プロシージャで共通の変数を利用する場合、Subの外側で変数宣言する
Dim tgesUserColNo As Integer

'モジュール間で共通の変数を利用する場合は、publicで宣言する
Public tgesUserColNo As Integer

'定数の場合は
下記のように記載
Const SheetNameMain As String = "Sheet1"
Public Const SheetNameMain As String = "Sheet1"

演算子

否定の書き方

'Notを用いた否定
If Not IsNumeric(passSeed) Then
End If

インクリメント

i= i + 1

IF文の代入を1行で記述する(三項演算子)

AAA = IIf(aaa = True, "YESの場合", "NOの場合")

配列

配列の定義

'配列は0から定義した添え字まで使える
Dim testArray(50) As String

testArray(0) = "aaa"
~
testArray(50) = "aaa"

配列の初期化

Erase 配列名

#動的配列を初期化すると、要素数を指定する前に戻る
#固定長の配列の場合
 integerの場合はすべて0、stringの場合はすべて空文字が入る

動的配列の要素数設定

ReDim aaaAray(3)

配列を関数に渡す

Sub main()
    Dim userData() As String
    userData(0) = "aaa"
    Call outputUserData(userData)
End Sub

Sub outputUserData(userData() As String)
    MsgBox userData(0)
End Sub

配列を関数の戻り値とする

Sub main()
    Dim returnArray() As String
    returnArray = makeData()
End Sub

Function makeData() As String()
    Dim testArray(50) As String
    testArray(0) = "aaa"
    makeData = testArray()
End Function

配列の要素数をカウント

UBound(配列名)

#多次元の場合
UBound(配列名,次元)

配列が空かどうかを判定する

If (0 < UBound(TestArray)) Then
     #空ではありません
Else
     #空です
End If

文字列を指定文字で区切って配列に格納

#カンマで区切って配列に格納
text="a,b,c,d"
list = Split(text, ",")

ロジック

ループ処理

'For文
    For i = 8 To lastRow
Next
※ほかにもDo~While、Do~Untilが利用できる
'For each文
'variant型のVarの宣言を省略している
For Each Var In userDataArray
Next Var

Exitで処理を途中で抜ける

Exit Sub:Sub 関数を抜ける
Exit Function:Function 関数を抜ける
Exit Do:Do 文を抜ける
Exit For:For 文を抜ける

改行コード

CrLfの改行コードの場合「vbCrLf」となる。
ただし、セル内の改行コードは「vbLf」となる。

CASE文

Select Case dataType
    Case dataTypeA, dataTypeB, dataTypeC
        aaa=bbb
    Case dataTypeD
        aaa=bbb
    Case Else
        aaa=bbb
End Select

文字列操作

文字の切り出し(末尾の1文字を削除)

'文字の切り出し
AAA = Left(文字列, 切り出す文字数)

'末尾の1文字削除
AAA = Left(AAA, Len(AAA) - 1)

文字列置換

'文字列から/を削除
birthdate = Replace("1980/04/01", "/", "")

対象文字列が含まれるか判定

'InStrは検索文字列が存在する位置を返す
If InStr(sexString, "男") > 0 Then
    sexCode = "man"
End If

キャスト

CDate:日付型(Date)へ変換
CInt:整数型(Integer)へ変換
CStr:文字列型(String)へ変換

設定関連

デバッグ、ダンプ

1.「ローカルウィンドウ」を表示させる
オブジェクトに入っている値を自動で出してくれるっぽい。

2.「ウォッチウィンドウ」を表示させる
ダンプしたい変数を右クリック、「ウォッチ式の追加」で追加する。
ブレークポイントを設置して処理を止めると、その時の変数の中身が確認できる。

自動構文チェックを外す

マクロ画面の「ツール」「オプション」「自動構文チェック」のチェックを外す

変数の宣言を強制とする

マクロ画面の「ツール」「オプション」「変数の宣言を強制する」のチェックをつける

関数

subとfunctionについて

sub:戻り値がない
function:戻り値がある
Sub BBB(data As String)
End Sub

'呼び出し
Call BBB
Function AAA(data As String) As Integer
    AAA = 1;
End Function

'呼び出し
userData = AAA("test")

引数の参照渡しと、値渡し

#参照渡し
Sub Proc1(ByRefl aaa As String)
    '値を変更した場合は呼び出し元の値も変更される
End Sub

#値渡し
Sub Proc1(ByVal aaa As String)
    '値を変更しても場合は呼び出し元の値は変更されない
End Sub

ByRefl、ByValを省略した場合、ByRefl(参照渡し)となるので注意
パラメータを渡す際に、計算式を使ったり()でくくったり、配列の場合はByVal(値渡し)になるとのこと
※ややこしいので、どちらか明確に指定するか、パラメータの値を修正するようなコードは書かないようにする。

その他全般

長いコードを複数行に分割する書き方

'アンダースコアでつなげる
text = aaa & bbb & ccc & ddd _
       & eee &fff

「開発」タブの表示

1.「ファイル」タブ、「オプション」を開く
2.「リボンのユーザー設定」を開く
3.右側の「リボンのユーザー設定」で「メインタブ」を選択
4.「開発」の項目にチェックをつける

ボタンを挿入する

「開発」タブ-「挿入」-ボタン

変数の宣言を強制する

オプションの「変数の宣言を強制する」にチェックを付ける。

モジュール追加時に下記コードがモジュール先頭行に追加される。
Option Explicit
既にモジュールがある場合は手書きでOK。

ABOUT ME
hazukei
「はずけい」と申します。 36歳のシステムエンジニアです。最近はシステムのサポートデスクと、運用業務をやっています。そろそろ開発がやりたいです。