【VBA】シート名を列挙する

仕事でちょっと使ったVBA集

'開いている別エクセル(確認.xlsx)のシート名を
'B列3行目から列挙する
Public Sub getSheet()
    Dim mySheet As Worksheet
    Dim trgBook As Workbook 
    Set mySheet = ActiveSheet
    Set trgBook = Workbooks("確認.xlsx")

    Dim i As Long
    For i = 1 To trgBook.Sheets.Count
        mySheet.Cells(i + 2, 2).Value = trgBook.Worksheets(i).Name
    Next i
End Sub

【VBA】SGN関数の盲点

みなさんSGN関数って使ったことありますか？

通常、SGN関数は引数に渡した値の正負を判定するために使われます。

◼︎Sgn(num)

numの判定	戻り値
num > 0	1
num = 0	0
num < 0	-1

でもこんなことしないですよね普通。

みなさん
if sgn(num) = 1 then …
なんて書くよりは
if num > 0 then …
なんて風に素直に書きますよね。

そもそもこんなドマイナーな関数を使うより、誰でも知っているような書き方をするはずで、現場によっては使った場合可読性が低いと怒られるでしょう。

なので、主にこの関数の活躍の場は上記のOfficeデベロッパーセンターの説明外の用法にあるようです。

なんと、以下のように配列の初期化が行われているかどうかを判別するために使われちゃったりします。

◼︎Sgn(array)

arrayの判定	戻り値
arrayが初期化されていない状態	0
arrayが初期化済みの状態	-16064

◼︎SGN関数の用法

Private Sub testSgn()
    '未初期化配列の宣言
    Dim sgnArray() As Variant
    If Sgn(sgnArray) = 0 Then
        MsgBox "配列:sgnArrayは初期化されていません"
    Else
        MsgBox "配列:sgnArrayは初期化済みです"
    End If
 
    '初期化済み配列の宣言
    Dim sgnArray2(2) As Variant
    If Sgn(sgnArray2) = 0 Then
        MsgBox "配列:sgnArray2は初期化されていません"
    Else
        MsgBox "配列:sgnArray2は初期化済みです"
    End If
 
End Sub

結果はもちろん
・配列:sgnArrayは初期化されていません
・配列:sgnArray2は初期化済みです
となります。

ただし、この使い方どのサイトでも「よく分からないけどこうなる」と書いてあります。

さてつかみどころのないSgn関数ですが、
筆者の現場ではツールリリース後に早速「環境によってこの使い方をすると落ちる」という厄介な事象に見舞われ、無事別の方法で対処しました。勘弁してください。

こういうことってvbaに限らずよくあるのですが、
やっぱりわからないものを分からないまま使うって怖いですね。

ちなみに配列の初期化済み確認方法はここに詳しいです。

【VBA】SPLIT関数の使い方

データを扱っていてふと区切り文字で列挙されている情報を分割して、わかりやすくまたは加工しやすく表示したいという状況があります。

①図は海上自衛隊カレーレシピのサイトから取得した「しらせシーフードカレー」のレシピになります。このレシピにそって材料を買い揃えることを考えると、①図の材料表記は「、」区切りで横並びになっており買物には不便です。
そこで材料を縦並びにリスト化して買物一覧(②図)を作ろうと思います。

①レシピシート

②買物一覧シート　↓最終イメージはこんな感じ

そんな時に使われるのがSPLIT関数です。

■Split(対象文字列, 区切り文字)
対象文字列を区切り文字で区切って分割し、配列を返します。

①図の材料をリスト化するには場合は下のようにして区切り文字で分割してあげます。

    Dim zairyoArray As Variant '材料配列
    zairyoArray = Split(Range("C3").Value, "、")
 

さてSplit関数で取得できた情報は分割した材料の配列となります。この場合はFor each構文で取り出すと良いでしょう。こんな感じです。

Private Sub testSplit()
    Dim zairyoArray As Variant '材料配列
    Dim zairyo As Variant '材料名
    zairyoArray = Split(Range("C3").Value, "、")
 
    For Each zairyo In zairyoArray
        Debug.Print (zairyo) 'とりあえずイミディエイト・ウインドウで表示
    Next zairyo
End Sub

最終的に①レシピシートの材料から②買物一覧シートを作ろうとするとこんな感じになります。

Private Sub main()
    Dim plotStRow As Integer '記載開始行番号
    Dim plotRow As Long '記載行番号
    Dim curryCol As Integer 'カレー名記載列番号
    Dim zairyoCol As Integer '材料名記載列番号
    Dim curryNm As String 'カレー名
    Dim zairyoArray As Variant '材料配列
    Dim zairyo As Variant '材料名
    '/*変数セット*/
    plotStRow = 3
    plotRow = plotStRow
    curryCol = 2
    zairyoCol = 3
 
    ' カレー名取得 Cells(3, 2)はRange("B3")と同じ
    curryNm = Worksheets("レシピ").Cells(3, 2).Value
    ' 材料ごとに分割した配列を取得 Cells(3, 3)はRange("C3")と同じ
    zairyoArray = Split(Worksheets("レシピ").Cells(3, 3).Value, "、")
                                                                                       
 
    For Each zairyo In zairyoArray
        '/*レシピプロット*/
        Worksheets("買物一覧").Cells(plotRow, curryCol).Value = curryNm
        Worksheets("買物一覧").Cells(plotRow, zairyoCol).Value = zairyo
     
        plotRow = plotRow + 1 '次の記載行へ(4行目、５行目、...)
    Next zairyo  
End Sub

これができればエクセルでCSVからデータを取得することもできます。
むしろそこがSplit関数の使い所のメインであったりしますが。

【VBA】フィルターでの完全一致・部分一致

大量の情報を絞り込むのに、エクセルのフィルター機能は大変役に立ちます。
手作業で絞り込みをすることもできますが、いつも使う絞り込みルールがある場合、VBAで実装した方が作業が格段に効率化されるでしょう。

今回は下記のテーブルにフィルターを掛ける例を幾つか提示します。

※国土交通省建設コンサルタント登録事業者一覧より抜粋・改変
http://www.mlit.go.jp/totikensangyo/const/totikensangyo_const_tk2_000059.html

1.フィルターをかける

'フィルターをかける
Private Sub filterData()
    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter
End Sub

Sheets("filterTest").Range("B2").CurrentRegionにてB2セルの属するテーブル領域であるB2:G10の範囲を取得し、この範囲に対してフィルターをかけています。

ちなみに、このコードを再実行するとフィルターが解除されます。

2. 完全一致で絞り込む
2−1. 1項目での絞り込み

上図で「商号又は名称」を「株式会社　東横エルメス」で絞り込む際、下記のように書きます。

'フィルター1項目完全一致

Private Sub filterData0()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス"

End Sub

この時field:=6の6はフィルター範囲左端から数えて絞り込み対象列である「商号又は名称」が6列目に当たることを示します。

2−2. 2項目以上での絞り込み

同様に2項目で絞り込む際、下記のように書きます。

'フィルター2項目完全一致

Private Sub filterData1()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス", _

            Operator:=xlOr, _

            Criteria2:="沖昌エンジニアリング　株式会社"

End Sub

2項目以上はCriteria1に配列を指定することで絞り込むことができます。

'フィルター2項目以上完全一致

Private Sub filterData2()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("株式会社　一測設計", "星和電機　株式会社", "株式会社　長大"), _

            Operator:=xlFilterValues

End Sub

3. 部分一致で絞り込む
3-1. 1項目での絞り込み

基本的に完全一致と書き方が同じで、Criteriaに指定する文字列を変えることで絞り込むことができます。

'フィルター1項目部分一致

Private Sub filterData3()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社*"

End Sub

この場合、Criteria1にワイルドカード(*)を用いて「株式会社*」と指定しているので、「株式会社」が前方一致するように絞られます。

3−2. 2項目以上での絞り込み

これも完全一致と書き方は同じとなります。

'フィルター2項目部分一致

Private Sub filterData4()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="*　株式会社", _

            Operator:=xlOr, _

            Criteria2:="*和*"

End Sub

また、配列を指定して下記のように書くこともできます。

'フィルター2項目部分一致

Private Sub filterData5()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社", "*和*"), _

            Operator:=xlFilterValues

End Sub

ただし、残念ながら以下のように3項目以上の指定はできないようです。

'フィルター3項目以上部分一致NGパターン

Private Sub filterData6()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社*", "*和*", "*エンジニア*"), _

            Operator:=xlFilterValues

End Sub

完全一致で複数指定できたのだから普通できるだろうと思うのですが、実際実装してみ
ると何も表示されない絞り込みとなり、VBAエラーともなりません。
エクセルのこの感じなんとも言えません。

そのため、3項目を部分一致で絞りたい場合は、他の方法を模索しなければなりません。