LoginSignup
4
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@SoreKiita

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

Last updated at Posted at 2019-03-09

▼前置き

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

▼Excelオブジェクトの取得

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文

sample
For Each MyR In Selection   'Selection内のセルを操作対象とする'
    MyR.Interior.ColorIndex = 3   
Next MyR

2. if文

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

▼関数

1. 文字列に含まれるか

sample
If InStr(WS.Name, "月") <> 0 Then   'シート名に【月】が含まれる場合'
   WS.Tab.ColorIndex = 3   
End If

2. セル色

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

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

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シート上のあるセル範囲の中で指定したデータを含むセルを検索する

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

Whatのみ必須

▼データ操作

1. Dictionary

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

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と同じ感覚
ただ動作が顕著に遅いことがある。
沢山オブジェクトを作るとそうなる。


'----------------
'宣言と初期化
'----------------
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. ファイル出力

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行ずつ読み込む)

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. ファイル読込ダイアログ

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

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出力)

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

4
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?