[VBA]文法のメモ & ツール作成時に必要そうなもの一式

#▼前置き
VBA書くときってちょろっとツールを作りたいなぁって思うときなんだけど、
そういう時に必要な関数や書き方が思い出せなくて時間が掛かって、それで時間かかったら意味ないじゃん、、
みたいな。
必要なものはこれ見りゃ思い出せるよ的な意味で書いておこうというメモでございます。

#▼Excelオブジェクトの取得

```vb:sample
    'ブック
    Dim book As Workbook
    Set book = ThisWorkbook
    
    'シート
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets
    Set sht = ThisWorkbook.Sheets("sheet1")
    
    'セル
    Dim rng As range
    Set rng = sht.range("A1")
    Set rng = sht.range("A:A")
    Set rng = sht.range("セルにつけた名前")
```

#▼文法
##1. for each文
```vb:sample
For Each MyR In Selection   'Selection内のセルを操作対象とする'
    MyR.Interior.ColorIndex = 3   
Next MyR
```

##2. if文

```vb:sample
    If fstflg Then
        str = rg.Value
        fstflg = False
    Else
        str = str & "," & rg.Value
    End If
```

#▼関数
##1. 文字列に含まれるか
```vb:sample
If InStr(WS.Name, "月") <> 0 Then   'シート名に【月】が含まれる場合'
   WS.Tab.ColorIndex = 3   
End If
```

##2. セル色
```vb:sample
Range("A1").Interior.ColorIndex = 3   'セルの色を赤にする'
```

##3. Dir関数(ファイルが存在するか)

```vb:sample
Sub Sample2()
    If Dir("C:\Sample\Book2.xlsx") <> "" Then
        Workbooks.Open "C:\Sample\Book2.xlsx"
    Else
        MsgBox "C:\Sample\Book2.xlsx" & vbCrLf & _
               "が存在しません"
    End If
End Sub
```

#▼セル検索
##1. Findメソッド
Findメソッドは、Excelシート上のあるセル範囲の中で指定したデータを含むセルを検索する

```vb:sample

Sub macro1()
    Dim myRange As Range
    Dim myObj As Range
    Dim keyWord As String
    
    'ここでセル範囲指定
    Set myRange = Range("A1:A4")

    '検索値
    keyWord = "エンジニア"

    '検索実行
    Set myObj = myRange.Find(keyWord, LookAt:=xlWhole)
    
    '結果判定
    If myObj Is Nothing Then
        MsgBox "'" & keyWord & "'はありませんでした"
    Else
        MsgBox "'" & keyWord & "'は" & myObj.Row & "行目にあります"
    End If
End Sub

```
指定できるオプションは沢山ある

|引数|定数|説明|
|---|---|---|
|What<font color="Crimson">*</font>|ー|検索するデータを指定|
|After|ー|検索を開始するセルを指定|
|LookIn|xlFormulas|検索対象を数式に指定|
||xlValues|検索対象を値に指定|
||xlComents|検索対象をコメント文に指定|
|LookAt|xlPart|一部が一致するセルを検索|
||xlWhole|全部が一致するセルを検索|
|SearchOrder|xlByRows|検索方向を列で指定|
||xlByColumns|検索方向を行で指定|
|SearchDirection|xlNext|順方向で検索(デフォルトの設定)|
||xlPrevious|逆方向で検索|
|MatchCase|True|大文字と小文字を区別|
||False|区別しない(デフォルトの設定)|
|MatchByte|True|半角と全角を区別する|False|区別しない(デフォルトの設定)||

<font color="Crimson">What</font>のみ必須

#▼データ操作
##1. Dictionary
ハッシュ(連想配列)として使う。
Collectionでも同じことができるが処理速度と便利なメソッドがある点においてDictionaryの方がよい

```vb:test

Sub test()
 
    '宣言と初期化
    Dim obj As Object
    Set obj = CreateObject("Scripting.Dictionary")

    ' キーと値の設定
    obj.Add "foo", 100
    obj.Add "bar", 200
    obj.Add "hoge", 300
 
    ' 値の取得
    Dim key As Variant
    For Each key In obj
        MsgBox obj(key)
    Next key

    'または
    hash("foo") '->100
 
    Set obj = Nothing 
 
End Sub

```

| メソッド名 | 説明 |
|:--|:--|
| Add(key,item) | キーと値のセットを追加 |
| Exists(key) | 指定されたキー(key)の存在 |
| Item(key) | キー(key)に紐づく値 |

##2. Collection
基本的にDictionaryと同じ感覚
ただ動作が顕著に遅いことがある。
沢山オブジェクトを作るとそうなる。

```vb:

'----------------
'宣言と初期化
'----------------
Dim collec As Collection
Set collec = New Collection

'----------------
'値追加 
'----------------
''keyと値のセット
collec.Add "キー","値" 

''値のみ
collec.Add "値" 

'※値の型にバラツキがあっても問題ない
'→String,配列,integerが混ざっててもおけ

'----------------
'値の取り出し
'----------------
''keyを指定して取り出し
cllec("キー") '→値

''拡張forで取り出し
For each value In collec
  Debug.print value
Next value

```

#▼ファイル入出力

##1. ファイル出力

```vb:sample

'A列の値を出力したい
Sub makeText()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
 
'パス/ファイル名の設定
Dim datFile As String
datFile = ActiveWorkbook.Path & "\data.txt"
 
'オープン(下記はOutPutになっているが選べる)
Open datFile For Output As #1
 
Dim i As Long
i = 1
Do While ws.Cells(i, 1).Value <> ""
    '出力
    Print #1, ws.Cells(i, 1).Value
    i = i + 1
Loop

'クローズ
Close #1
 
MsgBox "data.txtに書き出しました"
 
End Sub
```

***▼オープンのオプション***

|キーワード|モード|処理|
|---|---|---|
|Input|入力モード|読み込み|
|Output|出力モード|書き込み|
|Append|追加モード|書き込み|
|Random|ランダムアクセスモード|読み込み/書き込み|
|Binary|バイナリモード|読み込み/書き込み|

##2. ファイル読込(1行ずつ読み込む)

```vb:sample
Sub Sample2()
    Dim buf As String, n As Long
    Open "C:\Sample\Data.txt" For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
            n = n + 1
            Cells(n, 1) = buf
        Loop
    Close #1
End Sub
```

##3. ファイル読込ダイアログ

```vb:sample
Sub Sample2()
    Dim OpenFile As String
    ChDir "C:\Tmp"
　　'複数選択も可能に設定してる
    OpenFile = Application.GetOpenFilename(FileFilter:="Microsoft Excelブック,*.xls?", _
                                       MultiSelect:=True)
    MsgBox OpenFile & " を開きます"
End Sub
```

#▼Function

##1. Concatenateを範囲指定できるようにしたFunction
```vb:sample
Function ConcatenateByRange(rgs As Range)
       'User defined function to Concatenate values refered in Range format 
    Dim str As String
    Dim rg As Range
    str = ""
        For Each rg In rgs
            str = str & rg.Value
        Next
    ConcatenateByRange = str
End Function
```

##2. Concatenate応用(csv出力)

```vb:sample
Function ConcatenateByRange(rgs As Range)
       'User defined function to Concatenate values refered in Range format
    Dim str As String
    Dim rg As Range
    Dim fstflg As Boolean: fstflg = True
    str = ""
        For Each rg In rgs
            If fstflg Then
                str = Chr(34) & rg.Value
                fstflg = False
            Else
                str = str & "," & Chr(34) & rg.Value & Chr(34)
            End If
        Next
    ConcatenateByRange = str
End Function
```

#▼その他参考

めちゃ使えるプロシージャ/ファンクションを公開してくれてる
https://qiita.com/xojan0120/items/21e2f4c220fecc2365db