【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

【ｼｮｰﾄｶｯﾄ】エクセルクイックアクセスツールの使い方

とっても便利だけどあんまりクイックアクセス使いに出会わない。

そもそもクイックアクセスって何っていう人が多いのじゃないでしょうか？

そんなあなたも、その便利さに気づいてしまえば仕事場のPCでもご自宅のPCでも、新しく使うPCではまずクイックアクセスツールバーの設定からといったヘビーユーザとなること間違いなしです。

そもそもクイックアクセスツールってなにかというと、ユーザ定義のショートカットみたいなものです。

ショートカットというとCTRL+Z(ひとつ前の処理に戻る)とかCTRL+1(書式設定)だとかエクセルには有用で簡単なショートカットキーがあらかじめ用意されています。

が、、、ここで威力を発揮するのが罫線を引くだとか、セルを結合するだとか、普通だとマウス操作が必須になる所をユーザ定義でショートカット化させてしまえちゃうことです。

クイックアクセスツールはエクセルの左枠(黄色くくりのところ)で定義できて、左からWinキー+1~の数字で定義されています。

そこで試しにWinキー＋2で格子罫線を引くを試してみると、

こんな感じに瞬殺になる。

これができるだけでちゃちゃっと表作ってお客さんや上司にイメージ共有するときにマウスいらずでとってもスピーディ。

あんまり褒められることじゃないけど話しながらWebページ設計の画面モックとか、テスト時のデータイメージなんかを即興で作ってすり合わせる作業もしてしまいます。

クイックアクセスツールの設定方法はとっても簡単でエクセル左上で▼からその他のコマンドを選択して、

クイックアクセスツールバーをカスタマイズするだけ。

私のおすすめはクイックアクセスは上の通りで、格子線を一通りとセル結合/解除それにセル削除が入るくらいで、上から順にWin+1から対応するので並びも同じ。

カスタマイズは人それぞれで、あまり多すぎても使いづらいです。
皆さんも最適なカスタマイズを模索してみては？

【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関数の使い所のメインであったりしますが。

【関数】フィルターでの部分一致(複数項目)

前回のポストの3−2−2では部分一致の場合、VBAを駆使しても３項目までしかフィルターがかけられないという問題について触れました。
今回のポストでは、別の方法で３項目以上のフィルターがけを解説します。

◎関数での対処
フィルター対象に対して、部分一致での複数項目がけができないため、フィルター対象が複数項目の条件を満たすかを判定する列を別に追加し、追加した列の判定結果を完全一致でフィルターがけすることで擬似的に３項目以上のフィルターがけを実施します。



上の図では、下の条件のいずれかを満たす商号または名称を絞るため、H列に判定フラグを設けています。
・「　株式会社」を含む
・「和」を含む
・「エンジニア」を含む

判定フラグの列で「TRUE」を絞り込めば、上記条件を満たす商号または名称が絞られることとなります。

○関数解説
判定フラグ列にはこんな風に関数を記載しています。

=(3*LEN($G3) -(LEN(SUBSTITUTE($G3,"　株式会社","")) +LEN(SUBSTITUTE($G3,"和",""))+LEN(SUBSTITUTE($G3,"エンジニア","")))>0

論理式で指定の文字列を含むかを判定しているのですが、わかりづらいので補足すると、
「商号または名称」の文字数と、「商号または名称」から条件の文字列を空で置換した文字列の文字数を比較することで、指定の文字列を含んでいるかを判定しています。

これは文字列がある文字列を含んでいるかを確認する一般的な関数の使い方で、以下のように使います。

■文字列が条件の文字列を含むかを確認する方法
LEN(文字列) 
　- LEN(SUBSTITUTE(文字列,条件の文字列,"")) > 0 

今回は３つ条件があるため、3*LEN($G3)と減算対象を３倍にしているわけです。

【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項目を部分一致で絞りたい場合は、他の方法を模索しなければなりません。

【エクセル関数】文字列の加工① MID関数とFIND関数

外部のデータを利用する上で、一部のデータを加工したい場合があると思います。
たとえば官公庁のデータだったり、お客様のデータや外部システムからの出力データだったりと実際そういう場面は多々あるでしょう。

そういう時活躍する関数がMID関数です。

◼︎MID(文字列,開始位置,文字数)
引数「文字列」の文字列から、引数「開始位置」に指定した文字数目から数えて引数「文字数」分を取得します。

たとえば、文字列「WindowsVista」の1文字目から7文字目まで、つまり「Windows」を取得しようと考えた場合、

=MID("WindowsVista",1,7)

と記述します。

またこの文字列から「Vista」を取得しようと考えた場合、8文字目から5文字分を取得することとなるため、
=MID("WindowsVista",8,5)
と記述します。
この場合、指定する「文字数」はオーバーしても良いため、このように書くこともできます。
=MID("WindowsVista",8,100)

◼︎MID関数の用法
下図に日本郵便の配布するCSVから取得した郵便番号と住所の一部を記載しました。ただし、CSV上では郵便番号がハイフン「-」抜きで書かれているため、MID関数を用いてハイフン「-」ありに文字列を加工することにします。

こんな風に書きます。

=MID($C3,1,3)&"-"&MID($C3,4,4)

「MID($C3,1,3)」で1文字目から3文字の「604」を取得して、「MID($C3,4,4)」で4文字目から4文字の「0991」を取得、最後にハイフン「-」でつなげて郵便番号の形にしています。

このようにMID関数を使えば、抜き出す文字列の始まりと始まりから取得する文字数がわかる場合に限り、文字列を抽出し、加工することができます。
今回は郵便番号が必ず4文字目にハイフン「-」がくる8文字の文字列となるという規則性に着目してMID関数を利用しましたが、文字数が決まっていない場合にもMID関数の利用場面があります。

たとえば、メールアドレスから「@」以降を省いたユーザーIDを取得する場合、こんな風に取得できます。

=MID($C3,1,FIND("@",$C3)-1)

ユーザーIDを取得するにはMID関数を利用するには「開始位置」が1文字目であるかわかっているので、「@」の手前までの「文字数」を取得する必要が有ります。
そのためこの関数式ではFIND関数で「＠」の文字が何文字目に来るかを取得し、その手前までの文字数を計算してMID関数の第3引数にしています。

◼︎FIND(検索文字列,対象,[開始位置])
「対象」に指定した文字列から「検索文字列」を検索し最初の文字列から何文字目であるかを取得する。オプショナルな引数である「開始位置」を指定すると指定した文字数目から「検索文字列」を探すようになります。

◼︎FIND関数の用法
上述の通り。


◼︎より応用的な使い方として
FIND関数のオプショナルな引数である「開始位置」を活用すると、以下のように「検索文字列」が複数ある場合でも対応できます。

例えばC4セルにある「osama-no-mimi@lobanomimi.com」から「-」に囲まれた「no」をMID関数を用いて取得する場合を考えます。
この時FIND関数で初めて「-」がくる位置と、次に「-」がくる位置を求められれば「開始位置」と開始位置からの「文字数」を取得でき、そうすればMID関数を使うことができます。

計算してみると、 この場合最初の「-」は

=FIND("-",C4)
で6文字目に来ることがわかり、

次の「-」は「開始位置」を以下のように設定することで、

=FIND("-",C4,FIND("-",C4)+1)

9文字目に来ることがわかります。

よってMID関数で指定すべき「開始位置」は、
=FIND("-",C4) + 1
(=7文字目)
「文字数」は、

=FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1
(=2文字)

となります。

以上から「no」を取得するには

=MID(C4,FIND("-",C4)+1,FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1)

と記述すれば良いこととなります。


さすがにここまで来るとちょっと読みにくいですね。