VBA関数の錬金術師

2020年9月29日 05:49

ある日の業務中

友子「そういえば武ちゃん、こないだ言ってた同じ処理が発生した場合に"独自の関数(ユーザー定義関数)"を使うと、同じ処理が発生した場合に、便利だよって話してたと思うけど、あそこの部分がいまいちピンと来てないから聞いてもいい？」

武ちゃん「ぁー、うん。関数っていうとExcelとかでもあるけど、例えばNow()関数とかだと」

Option Explicit

Sub function_test()
   Dim Day As Date
   Day = Now()
   Cells(1, 1).Value = Day
End Sub

武ちゃん「今現在の年、月、日を判断して(システム日付)するっていう簡単なVBAがもともと持っている関数と、自分独自で作り出せる"関数"というものがあるのよ。」

友子「それがこないだ👇この回で言ってたやつだよね。でもなんだか、独自で作り出せるってあんまり想像できないけど、わかるかな…」

武ちゃん「うん。実は私も始めたころ、VBAがそもそもよくわからなくて、当時"舞戸(まうんと) 太郎"ってやつがいて、そいつがVBAを使えるやつでさ。直属の上司だったんだけど。」

武ちゃん「その頃のあたしは、全然VBAも全然使えなくて、舞戸に毎日毎日できないことを言われたわ。」

回想シーン

上司に怒られるイラスト

舞戸「武さん、この資料またここ間違ってるじゃん。これで何回目？
こんな繰り返しの作業なんだからVBA使えばすぐに出来ると思うんだけど？」

武ちゃん「すいません…。ちょっとまだVBAがよくわかってなくて…」

舞戸「毎日勉強してる？今の時代、VBAなんかググればいくらで情報出てくるんだから、家で勉強してきてよ。私の頃はインターネットがあんまり発達してなかった時代だからいろんな本買って勉強してたよ。」

武ちゃん「こんな感じで毎日毎日舞戸に嫌味を言われて、本当に自分が何も出来ない人間なのかなって、すごく自信を失ってたの。」

武ちゃん「あぁ…。あたしって本当にダメだなぁって。なんでできないんだろうって。」

武ちゃん「でも、舞戸とは毎日顔を合わせなきゃいけないし、言われっぱなしが本当に嫌だったし、何より悔しくて悔しくて。」

武ちゃん「そこから絶対舞戸をぶっ〇〇すつもりで、毎朝5時に起きて、出勤までの時間と帰ってから寝るまでの時間はVBAの勉強して、半年とか1年経つ頃には結構わかるようになってきたの。」

武ちゃん「それから一応は舞戸には何も言われなくなったからよかったんだけど、あそこで諦めずにやってよかったなとは思ってるよ。」

友子「武ちゃんにもそんな時があったんだね…。辛かったね…。
でも、そこですごい努力した結果、今の武ちゃんが存在してるんだね。」

武ちゃん「まぁ、あたしなんかまだまだ大したことなくて、知り合いに"出光鶴雄"さんって方がいて、その人がVBAを良く知ってる人で、なにかあったときに聞けるのが本当に助かったなぁって思ってる。」

友ちゃん「武ちゃんの師匠みたいな人ってことね！」

武ちゃん「まぁ、そんなとこかな。鶴雄さんからは色々教えてもらったけど、そこで印象的だったのはやっぱり"独自関数(ユーザー定義関数)"を使うことだったかな。」

武ちゃん「鶴雄さんは別名"関数の錬金術師"とも呼ばれていて、関数に関しての利便性と使い方を色んな例を用いて説明してくれたわ。
ただ、関数があれば何でも作り出せる、とちょっと勘違いして禁忌を犯した結果、髪の毛を持っていかれてしまったらしいのよ。」

友ちゃん「錬金術師ってすごいね！(関数にも禁忌があるんだ…髪の毛持っていかれた??)」

武ちゃん「うん。実際に教えてもらったのは次のような感じなんだけど、」

武ちゃん「独自関数(ユーザー定義関数)の基本の型はこんな感じで」

値を返さない場合のプロシージャ
sub プロシージャ名(引数名 As データ型)
   処理内容
   
End Sub

値を返す場合の関数
Function 関数名(引数名 As データ型) As 戻り値のデータ型
   処理内容
   
   関数名 = 戻り値
End Function

武ちゃん「たぶんこれだけ見てもなんのこっちゃ？だと思うから順を追って説明していくね！」

武ちゃん「関数の定義として"値を返さない"ものと"値を返す"ものが存在するんだけど、値を返さないものは正確にはプロシージャと言われて」

sub プロシージャ名 (引数名 As データ型)

武ちゃん「この形で定義するんだけど、値を返す、値を返さない、って意味があたしも当時全然わからなくて苦労したんだけど、例を用いながら説明するね」

値を返さないプロシージャとは

Sub oxygen(val1 As String, val2 As String)
  
   If (val1 + val2) = "水素酸素" Then
       MsgBox "水です"
   End If
   
End Sub


Sub main()
   Dim H2 As String
   Dim O As String
   
   H2 = "水素"
   O = "酸素"
   
   Call oxygen(H2, O)
End Sub

武ちゃん「例えばこんな感じの例を用意したんだけど、main()のプロシージャが実行されると処理が進んでいって、Call oxygenとすることでCallに書かれた後のoxygenという名称のプロシージャにジャンプするのよ。図で表すとこんな感じ」

武ちゃん「動きを見ていくと分かりやすいから実際にやってみるね。」

武ちゃん「こんな感じの動きになるんだけど、動きの仕組みはわかったかな？」

友子「oxygenプロシージャの引数は渡された"H2"でも、"O"でもない、val1とval2っていう引数を受け取ってるようだけど、それは大丈夫なの？」

武ちゃん「友ちゃん良いところに目を付けたね！ちょっと最初はなんで同じ引数じゃないのに中身は一緒なの？って思うんだけど、あくまで値を受け取る箱を準備しているようなものだから、受け取る側の引数は同じじゃなくてもOKなのよ。」

友子「なるほど！」

値を返す関数(Function)とは

武ちゃん「じゃあ、今度は戻り値がある例を説明していくね！」

 Function oxygen(val1 As String, val2 As String) As String
   
   oxygen = vbNullString
   Dim water As String
   water = vbNullString
   
   If (val1 + val2) = "水素酸素" Then
       warter = "水"
       MsgBox "水が出来たので返します"
       oxygen = water
   End If
   
End Function
 

Sub main()
    Dim H2 As String
    Dim O As String
     
    H2 = "水素"
    O = "酸素"
    
    Dim Result As String
    Result = oxygen(H2, O)
    
    If Result = "水" Then
       MsgBox "水が錬成出来ました。"
    Else
       MsgBox "錬成に失敗しました。"
    End If
End Sub

武ちゃん「異なる点は、呼び出し先がSubではなくFunctionになったこと、また呼び出し元が、Result = oxygen(H2, O)ここの部分かな。さっきはCallして呼び出してたのが、Callがなくなって、"Result =" ってなってるんだけど、これがどういうことかと言うと…」

武ちゃん「この戻り値って最初意味わからなくて、過去舞戸に"戻り値の意味がよく分からないんですが…"って聞いたことがあったんだけど…」

舞戸「そんなもん調べればいくらでも解説してるサイト出てくるでしょ？
調べたの？調べりゃわかると思うんだけど？」

武ちゃん「って言われて、まぁ質問の仕方も悪かったかもしれないけど、分からないから聞いてるんだけど、そんな感じで言われちゃったからさ。
自分が言われて嫌な思いしたから友ちゃんにはわかりやすく説明してあげたくってさ。」

友子「うん、ありがとう。」

武ちゃん「まぁ、そんな話はどうでもいいんだけど、こんな感じで関数は使うっていうのは何となくわかったかな？」

友子「うん。使い方は何となくわかったんだけど、これを実際にどういう場面で使うのかなぁっていうのがあんまり想像できてないかなぁ。」

武ちゃん「そうだよね。そんなこともあろうかと、実践例も用意しておきました！」

友子「さすが武ちゃん！準備が早い！！」

武ちゃん「実は前回のブック一括変換の処理でも、処理をまとめることが出来る箇所があるのよ。例えばこの部分」

----------------------------------------------------------------------------------------------------
指定のフォルダからエクセルファイルをすべて取得
----------------------------------------------------------------------------------------------------
Sub FileNameChange()

   ThisWorkbook.Activate  
   Set ws1 = Worksheets("ファイル名変換")
   ws1.Activate

武ちゃん「と、この部分」

----------------------------------------------------------------------------------------------------
ファイル名を指定のファイル名に変換処理
----------------------------------------------------------------------------------------------------
Sub FileNameGet()
   
   If ws1 Is Nothing Then
       Set ws1 = Worksheets("ファイル名変換")
   End If

武ちゃん「あと、ここ。」

---------------------------------------------------------------------------------------------
A列、B列の2行目以降を削除
---------------------------------------------------------------------------------------------
Sub clear()
   If ws1 Is Nothing Then
      Set ws1 = Worksheets("ファイル名変換")
   End If

武ちゃん「これすべて共通してる処理があるんだけど、どこかわかる？」

友子「Set ws1 = Worksheets("ファイル名変換")が同じかな？」

武ちゃん「正解！これって同じことを3回も書いてるじゃない？こういう場合にはひとつプロシージャか関数を作っておくことで、そこで処理をやってもらうようにすれば良い。」

武ちゃん「この場合だったら、ws1に代入する処理を」

Set ws1 = Worksheets("ファイル名変換")

武ちゃん「Callステートメントを使って、例えばworksheet_getみたいな名前でプロシージャ名を作って」

Call worksheet_get

武ちゃん「worksheet_get()のプロシージャで共通しているws1への代入処理はやってもらうって感じ」

Sub worksheet_get()
   Set ws1 = Worksheets("ファイル名変換")
End Sub

武ちゃん「今のは戻り値がない例だったけど、戻り値がある例だと…」

例
- 関数にある値の引数を渡して
- 関数内で処理を実行
- その処理結果を戻り値で受け取り
- その結果をもってmainプロシージャで
- 結果Aの場合〇〇の処理をする
- 結果Bの場合××の処理をする

武ちゃん「ざっくりこんな感じが関数を使う際の考え方になるんだけど」

武ちゃん「例えば以下のような頭3桁が101で始まるような適当なID(10桁)を割り振った値を用意して、2列目(B列)に101より後ろの数字(７桁)を抜き出して、抜き出した値の一番右の数字が"1"だった場合には3列目(C列)に転記して、4列目(D列)に対象データであることを表示する。みたいな処理があった場合」

Function Extraction(val As String) As String
   
   val = Mid(val, 4, 7)
   Extraction = val
   
End Function


Sub main()

   Dim ws As Worksheet
   Set ws = Worksheets("抽出")
   
   Dim MaxRow As Integer
   MaxRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   Dim ID As String
   Dim i As Integer
   
   With ws
       For i = 2 To MaxRow
           ID = .Cells(i, 1).Value
           .Cells(i, 2).Value = Extraction(ID)
           
           If Right(.Cells(i, 2).Value, 1) = "1" Then
               .Cells(i, 3).Value = .Cells(i, 2).Value
               .Cells(i, 4).Value = "対象"
           End If
       Next i
   End With
End Sub

武ちゃん「コードを書くとこんな感じに表せられるんだけど、さっきの復習、応用がてらコードの流れを解説していくと」

武ちゃん「まずSub main()側の処理が始まって、A列の値を繰り返し1行１行B列へ転記していくんだけど、転記する前の処理でA列を値をFunction Extractionへ渡して、その関数内で10桁の数字を7桁にして、その値を返す」

val = Mid(val, 4, 7)

武ちゃん「Mid(抜き出す対象の文字列, 文字列を抜き出す先頭位置, 抜き出す文字列の長さ)っていうのは指定した文字列の〇文字目から〇文字目までを抜き出すことができるVBAがもともと持っている関数なんだけど、
この場合、2行目(B列)のIDならval = "1012586791"が値として持っていて、Midに当てはめると
Mid("1012586791", 4, 7)になるから、"1012586791"この数字の4文字目、つまり"2"から7文字分だから"2586791"を抜き出して、再度valへ7桁の値を代入していることになる。」

武ちゃん「で、この7桁の値をSub main()に戻り値として返してあげて、.Cells(i ,2).Value = "2586791"として2行目(B列)に転記していることになるの」

If Right(.Cells(i, 2).Value, 1) = "1" Then
   .Cells(i, 3).Value = .Cells(i, 2).Value
   .Cells(i, 4).Value = "対象"
End If

武ちゃん「で、ここのIf文で、Right(.Cells(i ,2).Value, 1) つまり、Right(抜き出す対象の文字列, 抜き出す文字数)を使って、抜き出したIDの右から1番目の数字が"1"(文字列型としている為""(ダブルクォーテーションが付きます))だった場合に.Cells(i, 3).Value = .Cells(i, 2).Value 2列目(B列)の値を3列目(C列)に、同じ要領で.Cells(i, 4).Value = "対象"で4列目(D列)に転記する。
これを最終行まで繰り返して、全部のID抽出作業を一瞬で終わらせることもできる。」

完成形

友子「VBAでもともと持っている関数と独自関数を組み合わせることで、出来る幅が凄く広がるんだね！」

武ちゃん「そうだね。応用すれば、住所の番地まで入力されたものを都道府県と番地に分けてセルに抽出することもできるわ。」

武ちゃん「あと余談なんだけど、関数を使うようになると、コードを読む際に、関数の定義先で何をしているのか？を確認したい場合が出てくるんだけど、そういう場合は関数名にカーソルを合わせてShift+F2でその関数にジャンプできるのと、ジャンプした直前の位置に戻りたいときはCtrl+Shift+F2で戻ることが出来るから、覚えておくと便利かも。」

友子「うん、色々とありがとう。今回もすごい勉強になったよ！」

ここまでご覧いただきありがとうございました。
今回内容が良かったとか面白かったという方、ぜひフォローやいいね押していただけると飛んで喜びます！
また、ご意見やこんなことが知りたいなどございましたらコメントや、
@kyrieee_aとまでお寄せいただければ幸いです。

おはようございます☀

ちょっとしたVBAの物語を書いてみました。
ご興味ある方は覗いてみてください。

事務作業をしている方なら経験があるかも？
な物語です。#VBA
「これは経理からの処理を管理部がVBAを使って一瞬で完了させた物語」｜カイリーー @kyrieee_a #note https://t.co/UX38BBaNui
— カイリーー@育児エンジニア (@kyrieee_a) September 22, 2020

また、ブログも運営しております。
普段プログラミング学習で思うようにできず、悩んでいる、学習の仕方がわからない、エンジニアってどんなことしているの、など
プログラミングにおけるマインドについて情報発信しております。

この記事が気に入ったらサポートをしてみませんか？