m-eye blog

ノンプログラマによるVBAとPythonの学習記録

Excel VBAでIEを操作して電車の運行情報をしゃべらせるマクロその3

VBA Web

f:id:m-eye:20181006130806p:plain

　

はじめに

今回もきのうの続きです。

www.m-eye.net

きのう、ソースコートの全体を載せたので、順番をかんがえながら説明をしていきます。

　

まずは下準備

マクロの本題にはいるまえに「おまじない」が2つ必要です。

　

このマクロでは参照設定が必要です

参照設定が必要なライブラリは2つあります。

Microsoft Internet Controls
Microsoft HTML Object Library

この2つはExcel VBAでIEをあつかうにはかならず必要になってくるライブラリを参照する設定です。

VBE上で「ツール」→「参照設定」とたどっていってください。

ひらいたダイアログでMicrosoft Internet ControlsとMicrosoft HTML Object Libraryにチェックをいれて、「OK」をクリックしてください。そうすると、設定が完了します。

f:id:m-eye:20181031211031p:plain

この2つを参照設定することによって、VBE上でプロパティやメソッドの候補がでてきたり、Excel VBAでIEを操作したり、HTML文書（HTMLDocumentオブジェクト）を取得・操作することができるようになります。

　

マクロ内で使用するWindows API関数を宣言します

#If VBA7 Then
    Private Declare PtrSafe Function GetInputState Lib _
        "user32" () As LongPtr
#Else
    Private Declare Function GetInputState Lib _
        "user32" () As Long
#End If

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" _
        (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" _
        (ByVal ms As Long)
#End If

Windows API関数は、Windowsの機能をVBAなどのプログラミング言語からよびだすための関数であり、プログラムの先頭で宣言してからつかいます。

ここではGetInputState関数とSleep関数を宣言しています。

HTML文書を取得するファンクション・プロシージャF_GetHTMLDocのなかで、ExcelでIEを操作する際につかいます。

具体的なつかいかたはあとで説明します。

API関数を宣言するにはDeclareステートメントをつかいます。

Function GetInputStateとSub Sleepはよびだす関数名を表記しています。

Lib "user32"とLib "kernel32"と、関数をふくむライブラリ（DLL）名を表記しています。

ふつう、32ビット版のExcelをつかっている場合は、

Private Declare Function GetInputState Lib "user32" () _
    As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)

という表記です。

しかし、ここでは32ビット版と64ビット版の両方のExcelに対応する表記方法をつかっています。

64ビット版のExcelの場合は、

Private Declare PtrSafe Function GetInputState Lib _
    "user32" () As LongPtr
Private Declare PtrSafe Sub Sleep Lib "kernel32" _
    (ByVal ms As LongPtr)

になります。

32ビット版との宣言方法の違いは、DeclareのあとにPtrSafeをつけること、32ビット版のLong型は64ビット版ではLongPtr型で宣言すること、です。

32ビット版のExcelと64ビット版のExcelの場合のきりかえのためにVBA7という「条件付きコンパイル定数」と#If〜〜〜Then〜〜〜#Elseディレクティブをつかいます。

#If VBA7 Then
   ' 64ビット版のWindows API関数の宣言
#Else
   ' 32ビット版のWindows API関数の宣言
#End If

64ビット版のExcelのVBAのバージョンは「７」で、32ビット版のExcelのバージョンは「６」ですので、それを利用して場合分けをしています。

ちなみに、とくに理由がなければOSがWindows 10などの64ビットOSだとしても、Excelは32ビットにしておいたほうがいいと思います。

32ビット版Excelと64ビット版Excelの実質的なちがいは32ビット版のアドインが使えるかどうかだけであとは違いがありません。

ほとんど違いがなくて、万が一のときにアドインを使うかどうかということなので、使いたいときにアドインを使えないとこまるので、32ビット版Excelのほうがいいと思います。

64ビット版Excelでこの宣言をおこなうと、32ビット版の宣言の部分が赤字で表記されます。

わたしの環境は32ビット版Excelなので、赤字表記にはなりません。

しかし、赤字で表記されるからといって、エラーが発生するわけではありませんので、安心して使うことができます。

　

メインプロシージャについて

f:id:m-eye:20181101194536p:plain

メインプロシージャは、この画像の「運行情報取得マクロ自動起動」ボタンに登録する、このマクロの主要なプロシージャです。

メインプロシージャは、一定の時間間隔でExcelに電車の運行情報をしゃべらせるマクロです。

　

ソースコード

Public Sub S_SpeakTrainInfo()
    Dim TargetTime As Date

    TargetTime = Now + TimeValue("1:00:00")

    Call S_SpeakTrainInfo_Core

    Application.OnTime TargetTime, "S_SpeakTrainInfo"

End Sub

　

解説

このプロシージャでは、肝心の機能の「Excelに電車の運行情報をしゃべらせる」機能については、サブプロシージャを呼び出すだけです。

ここでは、一定の時間間隔でサブプロシージャをくりかえし呼び出すだけになってて、面倒な「Excelに電車の運行情報をしゃべらせる」機能はサブプロシージャまかせになってます。

まずは日付型の変数TargetTimeを宣言して、この変数に処理をくりかえす時間間隔を格納します。

Nowは現在の日付・時刻を取得する関数です。

TimeValueは引数に文字列で表示された時刻の日付型のデータ、TimeValue("1:00:00")の場合は1時間という日付型のデータを返します。

TargetTime = Now + TimeValue("1:00:00")で、変数に現在から1時間後の時刻を格納します。

Call S_SpeakTrainInfo_Coreは、このプログラムのコア（中核）となる「Excelに電車の運行情報をしゃべらせる」サブプロシージャを呼び出して実行します。

Application.OnTimeメソッドは、指定した時間TargetTiimeに指定したプロシージャS_SpeakTrainInfoをよびだします。

一定間隔で自動的に「Excelに電車の運行情報をしゃべらせる」にはすこしだけ工夫が必要です。

メインプロシージャをよびだした瞬間からかならず1時間後の時刻を取得して、1時間後に再度メインプロシージャそのものを呼び出します（再帰的呼び出し）。

これは意識的に無限ループをつくっていることになります。

1時間ごとにメインプロシージャを呼び出すことを無限にくりかえします。

ふつうの場合、無限ループはよくないプログラムの例としてよく出されますが、この場合はあえて無限にくりかえす（1時間ごとにサブプロシージャを呼び出す）プログラムなので、例外的に悪くはないプログラムです。

しかし、このままでは永遠におわらないプログラムであることはたしかです。

でも、カンタンにこのプログラムを終了する方法があります。

Excelそのものを終了しましょう。

そうすることでリセットされ、このプログラムの実行はなかったことになります。

　

おわりに

今回は、参照設定とWindows API関数の宣言、メインプロシージャの解説をしました。

VBAらしいことは、Application.OnTimeメソッドくらいでしたね。

どんなプログラムでもそうだと思いますが、ある程度の目的をもったプログラムは、かならず前処理が必要になってきます。

その前処理と本題のプロシージャはきりはなしてることが普通だとおもいます。

次回は本題のプロシージャに入っていきたいとおもいます。

　

Excel VBAでIEを操作して電車の運行情報をしゃべらせるマクロその2

Excel Web

f:id:m-eye:20181006130806p:plain

　

はじめに

今回はきのうの続きです。

www.m-eye.net

きのうは、マクロをまず書きはじめるまえに、あたまの中で（紙の上で、あるいはVBEにコメントを残すかたちで）考えるべきことを書きました。

要は、全体を把握しながら細部のプログラミングをすすめましょう、ということです。

じゃないと、「あれ、今なにやってるんだろ？」ということになりかねません。

全体像の把握力というか、イメージする力はプログラミングにも必要ですね。

　

プログミングの全体像と流れをVBEでコメントにしてみる

昨日、おおまかな流れを頭のなかでかんがえたので、それをコメントにしてみました。

Sub Excelに電車の運行情報をしゃべらせるマクロ()
    Call Excelに電車の運行情報をしゃべらせるマクロの中核 _
         となるマクロ

   ' 一定の間隔でこのマクロを呼び出す命令文
End Sub

Sub Excelに電車の運行情報をしゃべらせるマクロの中核 _
    となるマクロ()

    Call 電車の運行情報をスクレイピングするマクロ

    Call Excelにセルの内容をしゃべらせるマクロ
End Sub

Sub 電車の運行情報をスクレイピングするマクロ()
    Call 山手線の運行情報をスクレイピングするマクロ

    Call 東急線の運行情報をスクレイピングするマクロ

   ' ブックを保存する命令文
End Sub

Sub 山手線の運行情報をスクレイピングするマクロ()
   ' HTML文書 = HTML文書を取得する関数

   ' HTML文書を分析する命令文
   ' 必要な要素を取得する命令文
   ' ワークシートに情報を落とし込む命令文
End Sub

Sub 東急線の運行情報をスクレイピングするマクロ()
   ' HTML文書 = HTML文書を取得する関数

   ' HTML文書を分析する命令文
   ' 必要な要素を取得する命令文
   ' ワークシートに情報を落とし込む命令文
End Sub

Function HTML文書を取得する関数 As HTML文書
   ' ExcelでIEを操作する命令文
   ' IEでHTML文書を取得する命令文
End Function

Sub Excelにセルの内容をしゃべらせるマクロ()
   ' セルの内容をしゃべらせる命令文
End Sub

これはあくまでもブログでの説明用です。じっさいに書く書かないはべつにして、あたまの中ではこう考えています。

そして、わたしが自分でコメントを書くときは、サブプロシージャやファンクションプロシージャの名前は最初から英語で、コメントは日本語で書きます。

ここからプログラムを具体化していきます。

　

具体化の手順のまえに全体のソースコードを確認

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetInputState Lib _
        "user32" () As LongPtr
#Else
    Private Declare Function GetInputState Lib _
        "user32" () As Long
#End If

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" _
        (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" _
        (ByVal ms As Long)
#End If

Private Const YAMANOTE_LINE As String = _
    "https://transit.yahoo.co.jp/traininfo/detail/21/0/"

Private Const TOKYU As String = _
    "http://www.tokyu.co.jp/i/unten_i.cgi"

Public Sub S_SpeakTrainInfo()
    Dim TargetTime As Date

    TargetTime = Now + TimeValue("1:00:00")

    Call S_SpeakTrainInfo_Core

    Application.OnTime TargetTime, "S_SpeakTrainInfo"

End Sub

Public Sub S_SpeakTrainInfo_Core()
    Call S_ScrapeTrainInfo

    Call S_SpeakCellValue

End Sub

Public Sub S_ScrapeTrainInfo()
    With Worksheets("TrainInfo")
        .Activate
        .Range(.Columns(1), .Columns(3)).Delete
    End With

    Dim IE As InternetExplorer
    Set IE = CreateObject("InternetExplorer.Application")

    Call S_ScrapeTrainInfo_Yahoo(IE, YAMANOTE_LINE)
    Call S_ScrapeTrainInfo_Tokyu(IE, TOKYU)

    IE.Quit: Set IE = Nothing

    ThisWorkbook.Save

End Sub

Private Sub S_ScrapeTrainInfo_Yahoo _
    (ByVal IE As InternetExplorer, _
     ByVal URL As String)

    Dim Doc As HTMLDocument
    Set Doc = F_GetHTMLDoc(IE, URL, 10)

    With Worksheets("TrainInfo")
        Dim myRow As Long: myRow = 1

        .Cells(myRow, 1).Value = _
            Doc.getElementsByClassName("title")(0) _
                .innerText & "運行情報"

        .Cells(myRow + 1, 1).Value = _
            Doc.getElementsByClassName("subText")(0) _
                .innerText

        If Not Doc.getElementsByClassName("normal")(0) _
             Is Nothing Then

            .Cells(myRow + 2, 1).Value = _
                Doc.getElementsByClassName("normal")(0) _
                    .innerText

        Else
            .Cells(myRow + 2, 1).Value = _
                Doc.getElementsByClassName("trouble")(0) _
                    .innerText

        End If

        .Columns(1).AutoFit

    End With

    Set Doc = Nothing

End Sub

Private Sub S_ScrapeTrainInfo_Tokyu _
    (ByVal IE As InternetExplorer, _
     ByVal URL As String)

    Dim Doc As HTMLDocument
    Set Doc = F_GetHTMLDoc(IE, URL, 10)

    With Worksheets("TrainInfo")
        .Cells(1, 3).Value = _
            Doc.getElementsByTagName("div")(0).innerText

        .Cells(2, 3).Value = _
            Doc.getElementsByTagName("div")(1).innerText

        .Hyperlinks.Add _
            Anchor:=.Cells(3, 3), _
            Address:=URL, _
            TextToDisplay:="東急線運行情報サイト"

        With .Columns(3)
            .ColumnWidth = 50
            .AutoFit
        End With

        .Range(.Rows(1), .Rows(3)).AutoFit

    End With

    Set Doc = Nothing

End Sub

Private Function F_GetHTMLDoc _
    (ByVal IE As InternetExplorer, _
     ByVal URL As String, _
     ByVal mySecond As Long, Optional _
     ByVal Flag As Boolean = False) _
     As HTMLDocument

    IE.Navigate URL

    IE.Visible = Flag

    Dim myTime As Date
    myTime = Now + TimeSerial(0, 0, mySecond)

    Do While IE.Busy = True Or _
             IE.ReadyState <> READYSTATE_COMPLETE

        If GetInputState() = True Then DoEvents

        Sleep 1

        If Now > myTime Then
            IE.Refresh
            myTime = Now + TimeSerial(0, 0, mySecond)
        End If

    Loop

    myTime = Now + TimeSerial(0, 0, mySecond)

    Do While IE.Document.ReadyState <> "complete"
        If GetInputState() = True Then DoEvents

        Sleep 1

        If Now > myTime Then
            IE.Refresh
            
            myTime = Now + TimeSerial(0, 0, mySecond)
            
        End If

    Loop

    Set F_GetHTMLDoc = IE.Document

End Function

Public Sub S_SpeakCellValue()
    With Worksheets("TrainInfo")
        .Cells(1, 1).Speak
        .Cells(2, 1).Speak
        .Cells(3, 1).Speak
        .Cells(1, 3).Speak
        .Cells(2, 3).Speak
    
    End With

End Sub

おわりに

きょうはここまで。次回は今回掲載したソースコードと具体化の手順をこまかくみていきましょう。

　

Excel VBAでIEを操作して電車の運行情報をしゃべらせるマクロその1

VBA Web

f:id:m-eye:20181006130806p:plain

　

はじめに

ExcelでIEを自動的に操作して、Webサイトから電車の運行情報（平常通り運行しているか、事故・遅延情報はないかということ）を取得して、その内容をExcelに一定の時間間隔でしゃべらせてみたいと思います。

　

スクレイピングの大前提

プログラミング言語を用いて、Webサイトから情報を取得することをスクレイピング（Scraping）といいます。

当然のことながら、情報を取得したいWebサイトの内容が変更されれば、つくったマクロは無価値になり、あらたに作り直さなければならなくなります。

スクレイピングでは、Webサイトの内容が変更されていないことが前提となります。

サイトの内容の変更に追随するマクロもつくれないわけではないですが、最初はまず基本的なものから考えていきましょう。

　

電車の運行情報をしゃべらせる流れ

ざっと思い浮かぶだけでも、次のような処理が必要ですね。

  • 取得するべきサイトを確認する
  • ExcelでIEを操作する
  • IEでHTML文書を取得する
  • HTML文書を分析する
  • 必要な要素を取得する
  • ワークシートに情報を落とし込む
  • シートの内容をしゃべらせる

これらを順番にみていきましょう。

　

取得するべきサイトを確認する

まずはカンタンなサイトを2つ選んでみました。

　

山手線の運行情報 - Yahoo!路線情報

欲しい電車の運行情報のサイトの構造が複雑な場合、Yahoo!路線情報を利用するのがいいでしょうね。

f:id:m-eye:20181029192854p:plain

transit.yahoo.co.jp

ほしい情報は現在､事故･遅延に関する情報はありません。という文字列です。

この文字列を取得できるかどうかを確認していきます。

　

東急線運行情報

数ある電車の運行情報のサイトのなかでも、きわめてシンプルな構造のサイトです。

f:id:m-eye:20181029185943p:plain

http://www.tokyu.co.jp/i/unten_i.cgiwww.tokyu.co.jp

ほしい情報は東急各線は、平常通り運転しています。という文字列です。

この文字列を取得できるかどうかを確認していきます。

　

プログラミングの大きな流れをかんがえる

流れでいうと、3段階にわかれます。

頭のなかでやってもいいし、紙にかいてもいいし、VBEにコメントをかいてもいいと思います。

でも、なにも考えずにいきなり1行目からコードを書きはじめても、とっちらかってしまって、作業効率はおちるだけだと思います。

専門家は「上流工程」ということばをつかうようですが、ノンプログラマだからといって、上流工程を軽視して、いきなりコーディングをはじめていいわけではありません。

　

HTML文書を取得する

Excelで直接HTML文書を取得できるわけではないので、ExcelでIEを操作して、間接的にHTML文書を取得します。

　

ExcelでIEを操作する

ExcelでIEをうごかして、Webサイトを表示したり、IEの裏側でおこなっている操作を自動でおこなったりすることです。

やったことのないひとにとっては、まずここが「壁」になります。

しかし、なれてくれば単なる作業になります。

　

IEでHTML文書を取得する

ExcelでIEをうごかして、Webサイトを表示した時点で、HTML文書を取得したも同然ですから、そんなにむずかしい話ではありません。

もちろん、JavaScriptで動的に構成され、つぎからつぎへと変化するようなWebサイトのなかには取得がむずかしいサイトもあります。

さいしょは取得するのがカンタンそうなWebサイトのにおいをかぎわける必要がありそうです。

　

Webサイトをスクレイピングする

ここからがWebサイトごとにちがう実際のスクレイピング作業です。

　

HTML文書を分析する

ここはHTMLを実際に分析する作業と、それをVBAのコードにおとしこむ作業にわかれます。

　

必要な要素を取得する

HTML文書の分析結果をVBAのコードにおとしこんだものが実行されれば、自動的に「返り値」として要素を取得することができます。

　

ワークシートに情報を落とし込む

ここはExcelをつかっている強みで、取得した要素はシート上のセルに情報をカンタンに格納することができます。

特別になにか用意しなくても、セルに情報を展開すればいいので、こんなラクなことはありません。

　

シートの内容をしゃべらせる

Excelには、シート上のデータをしゃべらせる機能がありますので、カンタンにしゃべらせることができます。

　

おわりに

きょうはここまで。次回からはソースコードをみていきたいと思います。

　

PythonをVBAと比較しながら覚えようPart2

Python VBA

f:id:m-eye:20181010190201j:plain

　

はじめに

PythonをVBAと比較しながら覚えようのPart2です。

今回は「値」と「演算子」が中心ですね。

　

値の種類

値は、大きく数値、文字列、論理値にわかれます。

　

数値

PythonとVBAでは、あつかうことのできる数値に違いがあります。

　

Pythonにおける数値

Pythonでは、整数型・浮動小数点数型・2進数型・8進数型・16進数型・複素数型の数値を扱うことができます。

数値の型 数値の範囲 説明
整数型 小数点以下がない数値 0と負の数を含む
浮動小数点数型 小数点以下がある数値 0と負の数を含む
2進数型 0と1の数字で表す 0bを先頭につける
8進数型 0〜8の数字で表す 0oを先頭につける
16進数型 0〜9・A〜Fで表す 0xを先頭につける
複素数型 実部＋虚部 虚部にはjをつける

ほんとはほかにもありますが、最初はこれでかまわないと思います。

　

整数

整数には正の数と負の数を含みますが、厳密には、マイナスの符号-とプラスの符号+は、それぞれ負の数、正の数を表す演算子です。-3、+5という表現も可能です。

　

浮動小数点数

浮動小数点数で、整数部が0、あるいは小数点以下が0のときは、.99、10.のように0を省略できます。

また、整数と浮動小数点数との計算結果は、浮動小数点数になります。整数同士の割り算では、割り切れても結果は浮動小数点数になります。

大きな桁数の数値には指数表記も可能です。たとえば、12300000は1.23e+7、0.00096は9.6e-4です。eは大文字でもかまいません。e+7は10の7乗、e-4は10のマイナス4乗です。

　

2進数・8進数・16進数

2進数で0b0101は10進数の5、8進数で0o011は10進数の9、16進数で0xFFは10進数の255を表します。

虚数とは、2乗すると-1のになる、実世界には存在しない想像上の数値（Imaginary Number）の単位です。複素数は虚数部分を含む数値です。人間の想像力はすごいですね。

　

VBAにおける数値

VBAでは、バイト型・整数型・長整数型・単精度浮動小数点型・倍精度浮動小数点型・通貨型・日付型の数値を扱うことができます。

VBAに多くの数値型があって、Pythonのほうが数値型がすくないのは意外ですね。実は、Pythonの組み込み型には数値型がすくないですが、datetimeという標準ライブラリがあって、日付や時間は値の種類ではなく、オブジェクトの種類であり、プロパティ（Pythonではアトリビュート）とメソッドで操作します。

VBAのバイト型・整数型・長整数型が、Pythonの整数型にあたります。

数値の型 数値の範囲 説明
バイト型（Byte） 0〜255 符号なし8ビットの整数
整数型（Integer） -32,768～32,767 長整数型よりも範囲が狭い
長整数型（Long） -2,147,483,648～2,147,483,647 整数型よりも範囲が広い
単精度浮動小数点型（Single） -3.402823E38～-1.401298E-45(負)、1.401298E-45～3.402823E38(正)の範囲 倍精度浮動小数点型よりも範囲が狭い
倍精度浮動小数点型（Double） -1.79769313486232E308～-4.94065645841247E-324(負)、4.94065645841247E-324～1.79769313486232E308(正)の範囲 単精度浮動小数点型よりも範囲が広い
通貨型（Currency） -922,337,203,685,477.5808～922,337,203,685,477.5807 金額を扱う・整数型よりも扱える範囲が広い
日付型（Date） 西暦100年1月1日～西暦9999年12月31日 「1900/1/1 0:00」が内部値「1」となり、１日が「１」ずつ増える値をとる

こうしてみると、VBAではそれぞれの型で扱える範囲をこまかく限定しているのがわかりますね。むかし、PCの性能がよくなかった頃の影響でしょうか。いまではその影響を考える必要がないくらい、PCの性能があがっていますが。

したがって、今では整数型・単精度浮動小数点型を使う積極的な理由はありません。長整数型・倍精度浮動小数点型を使うのがよいでしょう。

　

PythonとVBAの違い

Pythonでは、ふつうの数値にくわえて、2進数・8進数・16進数や複素数があつかえます。いっぽう、VBAでは用途によってこまかく数値の型を変更することができます。

　

演算子

数値や文字の演算に使います。

　

数値演算子

数値演算子も、PythonとVBAとで若干違いがあります。

　

Pythonの数値演算子

演算子 例 説明
+ a + b 足し算
- a - b 引き算
* a * b 掛け算
/ a / b 割り算
// a // b aをbで割った商の整数値（小数点以下を切り捨て）
% a % b aをbで割って、割り切れなかった余り（剰余）
** a ** n aをn回掛けた値（べき乗）

ここにない演算には、mathモジュールが必要です。

　

VBAの数値演算子

演算子 例 説明
+ a + b 足し算
- a - b 引き算
* a * b 掛け算
/ a / b 割り算
¥ a ¥ b aをbで割った商の整数値（小数点以下を切り捨て）
Mod a Mod b aをbで割って、割り切れなかった余り（剰余）
^ a ^ n aをn回掛けた値（べき乗）

Modだけが異質ですね。なにか理由があるのでしょうか。

　

文字列

　

シングルクォートとダブルクォート

Pythonでは、文字列をシングルクォートまたはダブルクォートで囲み、'シングルクォート'、"ダブルクォート"というように表現します。

VBAでは、文字列をダブルクォートで囲み、"ダブルクォート"というように表現します。

VBAでのシングルクォートは、コメント行の開始を意味しますので、比較しながら覚える場合は注意が必要です。

　

エスケープシーケンス

　

Pythonのエスケープシーケンス

エスケープシーケンス 説明
\n 改行（ラインフィード）
\t 水平タブ
\r キャリッジリターン
\" ダブルクォート
\' シングルクォート
\\ バックスラッシュ

Windowsでは、\は¥で表示されます。

　

VBAのエスケープシーケンス

エスケープシーケンス 説明
vbLf 改行（ラインフィード）
vbTab 水平タブ
vbCr キャリッジリターン
vbCrLf キャリッジリターン・ラインフィード
"" ダブルクォート
シングルクォートはふつうにダブルクォートの中に埋め込むことができます
¥¥ 円マーク

　

複数行の文字列

　

Pythonにおける複数行の文字列

クォートを3回つづけて、'''〜'''あるいは"""〜"""のように囲むと、複数行の文字列を作ることができます。

poem = '''すすめの子
そこのけそこのけ
お馬がとおる'''

すすめの子
そこのけそこのけ
お馬がとおる

また、\nを挿入すると、その部分で改行することもできます。

poem = 'すすめの子\nそこのけそこのけ\nお馬がとおる'

　

VBAにおける複数行の文字列

改行したい文字列と文字列を、エスケープシーケンスvbCrLfで連結します。

ただし、セルのなかでは、エスケープシーケンスvbLfで連結します。

Dim Poem As String

'セル以外の場合
Poem = "すずめの子" & vbCrLf & "そこのけそこのけ" & vbCrLf & "お馬がとおる"

'セル内の場合
Poem = "すずめの子" & vbLf & "そこのけそこのけ" & vbLf & "お馬がとおる"

すすめの子
そこのけそこのけ
お馬がとおる

　

文字列の演算子

　

Pythonの文字列の演算子

演算子 例 説明
+ 'a' + 'b' 文字列aとbの連結。文字列がabになる
* 'abc' * n 文字列abcをn回繰り返す。nが2ならば、'abcabc'となる 
print('a' + 'b')
print('abc' * 2)

ab
abcabc

　

VBAの文字列の演算子

演算子 例 説明
& "a" & "b" 文字列aとbの連結。文字列がabになる

VBAには、文字列を繰り返す演算子はありません。しかし、指定した1文字を繰り返すString関数、指定した2文字以上を繰り返すREPT関数、指定した数のスペースからなる文字列を返すSpace関数があります。

MsgBox "a" & "b"
MsgBox String(2, "ABC")
MsgBox WorksheetFunction.Rept("abc", 2)
MsgBox Space(5)

ab
AA
abcabc
     '空白文字が5回繰り返されている

　

数値と文字列を連結する

Pythonでは、数値と文字列を連結したい場合は、あらかじめ明示的に数値を文字列型に変換する必要があります。変換にはstr()を使います。

数値と文字列をそのまま連結するとタイプエラー（TypeError）になります。

VBAでは、数値と文字列をそのまま連結しても問題ありません。VBAが自動的に型変換してくれます。

　

文字の取り出し

id = 'abc123xyz'
print(id[0]) # 先頭から1文字目
print(id[2]) # 先頭から3文字目
print(id[-1]) # 後ろから1文字目
print(id[4:]) # 5文字目から最後まで
print(id[4:4+4]) # 5文字目から4文字
print(id[:-7]) # 後ろから数えて7文字目の手前まで

a
c
z
23xyz
23xy
c123xyz

文字列から文字を取り出すには[]を利用します。文字の位置は1文字目を0と数え、マイナスは後ろから数えます。-1が最後の文字です。

Dim id As String
id = "abc1234xyz"
MsgBox Left(id, 1) '先頭から1文字目
MsgBox Mid(id, 3, 1) '先頭から3文字目
MsgBox Right(id, 1) '後ろから1文字目
MsgBox Mid(id, 5) '5文字目から最後まで
MsgBox Mid(id, 5, 4) '5文字目から4文字
MsgBox Right(id, 7) '後ろから数えて7文字目の手前まで

a
c
z
23xyz
23xy
c123xyz

Pythonでは、文字列を文字の配列としてとらえて文字を取り出していますが、VBAでは、Left関数・Mid関数、Right関数で取り出しています。また、VBAでは文字の位置は1文字目を1と数えています。

　

論理値

論理値は、PythonでもVBAでもTrueとFalseです。

Pythonでは、1がTrue、0がFalseです。

VBAでは、-1がTrue、0がFalseです。

　

比較演算子

　

Pythonの比較演算子

演算子 例 説明
== a == b aとbが等しいとき、True
!= a != b aとbが等しくないとき、True
> a > b aがbより大きいとき、True
>= a >= b aがb以上のとき、True
< a < b aがbより小さいとき、True
<= a <= b aがb以下のとき、True

aとbが等しいときは==を使い、等しくないときは!=を使います。

　

VBAの比較演算子

演算子 例 説明
= a = b aとbが等しいとき、True
<> a <> b aとbが等しくないとき、True
> a > b aがbより大きいとき、True
>= a >= b aがb以上のとき、True
< a < b aがbより小さいとき、True
<= a <= b aがb以下のとき、True

aとbが等しいときは=を使い、等しくないときは<>を使います。

　

右結合・左結合

Pythonの比較演算子には右結合・左結合といった概念がなく、常に両隣の値を比較して、その論理積が式の値となるため、次のような式が可能です。

age = 16
if 13 <= age <= 20:
    print('True')

VBAで同じことをやろうとすると、次のような式になります。

Dim Age As Long
Age = 16
If (13 <= Age) And (Age <= 20) Then
    MsgBox "True"
End If

　

Pythonの論理演算子

Pythonには、and、or、not、という3つの論理演算子があります。

演算子 例 説明
and a and b 論理積。aかつbの両方がTrueのときTrue。一方でもFalseならFalse
or a or b 論理和。aまたはbのどちらか一方でもTrueならばTrue。両方ともFalseならばFalse
not not a 否定。aがTrueならばFalse。aがFalseならばTrue 
# 論理積
>>> True and True
True

>>> True and False
False

# 論理和
>>> True or True
True

>>> True or False
True

>>> False or False
False

# 否定
>>> not True
False

>>> not False
True

# 変数aが50以上かつ100以下のときTrue
>>> a = 80
>>> (a >= 50) and (a <=100)
True

>>> a = 110
>>> (a >= 50) and (a <=100)
False

>>> a = 'NG' ; b = 'OK'
>>> (a == 'OK') or (b == 'OK')
True

>>> True + False
1

>>> True + True
2

>>> 1 and 1
1

>>> 1 or 0
1

>>> 2 or 3 # 左項を採用
2

>>> 2 and 3 # 右項を採用
3

　

Pythonのビット演算子

ビット演算子は2進数の値をビットごとに演算します。ビット演算ではビットの合成や打ち消し（ビットの立て落とし）ができるので、画像の合成などで活用されます。

　

論理積・論理和・排他的論理和

演算子 例 説明
& a&b 論理積（AND）。両ビットともに1のとき1
| a|b 論理和（OR）。どちらかのビットが1ならば1
^ a^b 排他的論理和（XOR）。比較したビットの値がことなるとき1
~ ~a ビット反転（NOT）。ビットの1、0を反転させる

f:id:m-eye:20181027210409p:plain

左シフト・右シフト

演算子 例 説明
<< a<<1 左シフト。ビットを左にずらす。値は２倍になる
>> a>>1 右シフト。ビットを右にずらす。値は1/2になる

直接の意味としては、コンピュータの処理能力の低かった時代にかけ算・わり算のかわりに高速な左シフト・右シフトをつかったり、ハードウェア寄りの処理をおこなうときに用いたりするのですが、必要にせまられなければ、いまどき使うこともないでしょう。

>>> a = 0b001011
>>> a
11

>>> a << 1 # 左に1桁シフト
22

>>> a = 0b001011
>>> bin(a << 1)
'0b10110'

　

ビットマスク

必要な部分を1にした値とANDする（論理積を求める）ことで必要なビットをぬきだすことができます。

　

下3桁のビットマスク

>>> a = 0b100110
>>> bin(a & 0b111)
'0b110'

　

途中のビットのビットマスク

>>> a = 0b10101
>>> bin((a>>1) & 0b11)
'0b10'

0b10101を0b1010にしておいて0b11とビットマスク演算をすることで、もとの数値の3桁目と2桁目の10を取り出せます。

　

VBAの論理演算子・ビット演算子

VBAの論理演算子は、ビット演算にもつかいます。

IF文などにつかわれるAnd、Or、Notのつかいかたは簡単ですのでここでは省略します。

VBAのビット演算でいちばんよく使われるのは、ビット落としなどをつうじて、ファイルの属性変更（読み取り専用属性の解除）をおこなうときなどです。

演算子 例 説明
And a And b 論理積。両ビットともに1のとき1
Or a Or b 論理和。どちらかのビットが1ならば1
Not Not a ビット反転。ビットの1、0を反転させる
Xor a Xor b 排他的論理和。ビットが一致しないときは1
Eqv a Eqv b 論理等価。ビットが一致するときは1
Imp a Imp b 論理包含。(Not A) Or B。

EqvとImpはほとんど使われないので、おぼえる必要もないと思います。

?True And True
True

?True And False
False

?True Or True
True

?True Or False
True

?False Or False
False

?Not True
False

?Not False
True

?True + False
-1 

?True + True
-2 

' 数値の論理演算はビット演算になる

?1 and 1
 1 

?1 or 0
 1 

?2 or 3 '"10" or "11" 論理和を求める
 3      '"11"

?2 and 3 '"10" and "11" 論理積を求める
 2       '"10"

なお、VBAで左シフト・右シフトをおこなうときは、BITLSHIFT関数、BITRSHIFT関数をつかいます。

　

型変換

Pythonでは、値の型については最低限のことを知っておけばそれで足ります。

VBAでは、値の型については通常の型以外に、なんでも入るVariant型、総称オブジェクト型のObject型、などについても知っておく必要があります。

VBAでの型変換については、自動キャスト（型変換）がおこなわれるので、あまり意識する必要はないといえます。

　

型を調べる

Pythonでは、値の型はtype()でしらべられます。

>>> type(1)
<class 'int'>

これは数値1の型がint型であることをしめしています。

>>> n = 12.3 ; name = '山田'
>>> type(n)
<class 'float'>

>>> type(name)
<class 'str'>

浮動小数点数の型はfloat、文字列の型はstrです。

　

VBAでは、値の型はTypeName関数やVarType関数でしらべられます。

Dim a As Integer
a = 1
MsgBox TypeName(a)   ' Integer(文字列)を返す
MsgBox VarType(a)    ' 2(vbInteger)を返す

TypeName関数の戻り値

文字列 内容
WorkBook ブック
Worksheet シート
Range セル
Chart グラフ
TextBox テキストボックス（ActiveXコントロール）
Label ラベル（ActiveXコントロール）
CommandButton コマンドボタン
Object オブジェクト
Unknown 種類が不明なオブジェクト
Nothing オブジェクト変数の初期値

VarType関数の戻り値

値 定数 内容
0 vbEmpty 値（未初期化）
1 vbNull 値（無効な値）
2 vbInteger 整数型
3 vbLong 長整数型（long)
4 vbSingle 単精度浮動小数点数型（Single）
5 vbDouble 倍精度浮動小数点数型（Double）
6 vbCurrency 通貨型（Currency）
7 vbDate 日付型（Date）
8 vbString 文字列型
9 vbObject オートメーションオブジェクト
10 vbError エラー型
11 vbBoolean ブール型（Boolean）
12 vbVariant バリアント型 (Variant) (バリアント型配列にのみ使用)
13 vbDataObject 非オートメーションオブジェクト
17 vbByte バイト型
8192 vbArray 配列（Array）

　

数値を文字列に変換する

数値を文字列と連結しようとするとエラーになりますので、str()をつかって数値を文字列に変換します。

>>> len = 10 * 1.23
>>> ans = '長さ' + str(len) + 'cm'
>>> ans
'長さ12.3cm'

>>> ans = '5<10は' + str(5<10) + 'です。'
>>> ans
'5<10はTrueです。'

VBAではつぎのようにします。

Dim len As Single
len = 10 * 1.23
Dim ans As String
ans = "長さ" & CStr(len) & "cm"
MsgBox ans ' 「長さ12.3cm」を返す

Dim ans As String
ans = "5<10は" & CStr(5 < 10) & "です。"
MsgBox ans '5<10はTrueです。

　

いろいろな型変換

関数名 内容
str() 文字列型への型変換
int() 整数型への型変換
float() 浮動小数点数への型変換
bool() 論理値への型変換
bin() 2進数の文字列に変換
oct() 8進数の文字列に変換
hex() 16進数の文字列に変換 
>>> int('250') * 3 # 整数に型変換
750

>>> float('1.5') + 0.2 # 浮動小数点数に型変換
1.7

# 浮動小数点数を整数化すると小数点以下は切り捨てられる
>>> int(12.9) 
12

>>> bin(10)
'0b1010'

>>> oct(10)
'0o12'

>>> hex(10)
'0xa'

VBAの場合はつぎのようになります

関数名 内容
CBool ブール型（Boolean）へのデータ変換
CByte バイト型（Byte）へのデータ変換
CCur 通貨型（Currency）へのデータ変換
CDate 日付型（Date）へのデータ変換
CDbl 倍精度浮動小数点数（Double）へのデータ変換
CInt 整数型（Integer）へのデータ変換
CLng 長整数型（Long）へのデータ変換
CSng 単精度浮動小数点数型(Single)へのデータ変換
CVar バリアント型(Variant)へのデータ変換
CStr 文字列型(String)へのデータ変換 
MsgBox CInt("250") * 3 ' 750を表示

MsgBox CSng(”1.5”) + 0.2 ' 1.7を表示

'浮動小数点数を整数化するときに直接「CInt」関数をつかってはならない
MsgBox CInt(RoundDown(Val("12.9"))

'2進数の数値に変換
'512以上の数値の場合にエラー発生
MsgBox WorksheetFunction.Dec2Bin(10)'1010を表示

'8進数の数値の変換
'-536870912〜536870911の範囲外の数値の場合エラー発生
MsgBox WorksheetFunction.Dec2Oct(10) '12を表示

'16進数の数値に変換
MsgBox WorksheetFunction.Dec2Hex(10)'Aを表示

　

変数

  • 値を一時的に保管する箱のようなもの
  • 箱の中身は変更することができる
  • 変数をつかって式が書ける（実際はこちらの役割のほうが大きい）

　

変数の作成

Pythonの変数には宣言が不要。

値を代入することで変数が作成されます。

=を代入演算子という。

>>> width = 20.0
>>> height = 10.0
>>> area = width * height / 2
>>> print(area)
100.0

VBAの変数には宣言が必要。

=は代入演算子であり、等価演算子（Pythonでは==）でもある。

Dim width As Single: width = 20.0
Dim height As Single: height = 10.0
Dim area As Single: area = width * height / 2
MsgBox area '100が表示される

　

変数名のつけかた

Pythonでは

  • 半角英数と_（アンダーバー）でつけます。
  • ひらがなやカタカナや漢字もつかえますが、一般的にはつかいません。
  • 慣例として変数名は小文字でつけます。
  • 慣例として定数名は大文字でつけます。
  • 慣例として変数名は何をしめす値かがわかる名前をつけるようにします。
  • わかりやすい変数名にするために、複数の小文字の単語と単語を_（アンダーバー）でつなぐ命名法がよくつかわれています。

　

VBAでは

  • 文字 (英数字、漢字、ひらがな、カタカナ) と_（アンダーバー） でつけます。
  • 日本語の変数名もよくつかわれます。
  • 変数名の先頭の文字は、英字・漢字・ひらがな・カタカナのいずれかでなければなりません。
  • わかりやすい変数名にするために、単語と単語の区切りを大文字にする命名法がよくつかわれます。

　

つかえない変数名

Pythonでは

  • 変数名の1文字目には、数字・演算子・記号・予約語は使えません。

VBAでは

  • 変数名の1文字目には、数字・演算子・記号・予約語は使えません。

　

大文字と小文字

Pythonでは

  • 大文字と小文字は区別されます。point_aとpoint_Aは似た名前の別の変数です。

VBAでは

  • 大文字と小文字は区別されません。point_aとpoint_Aという変数は同じスコープの中では同時に宣言することはできません。

　

変数の型

Pythonでは

  • 変数の値には型がありますが、変数には型はありません。
  • 文字列が入っている変数に数値を代入してもエラーにはなりません。

　

VBAでは

  • 変数には型がありますが、どんな型の値でもいれられるバリアント型（Variant）の変数があります。
  • オブジェクトならなんでもいれられる総称オブジェクト型（Object）もあります。
  • 変数の型を宣言しない場合、すべての変数はバリアント型（Variant）になります。
  • 一般的にはOption Explicitを宣言して、変数の型宣言を強制する場合がおおいです。

　

定数

  • 値を保管する箱のようなもの
  • 箱の中身を変更することはできない
  • 定数をつかって式が書ける

　

Pythonの場合

Pythonには定数がありません。慣習的に大文字で書いた変数を他言語でいう定数として扱います。

TRIANGLE_WIDTH = 100

　

VBAの場合

VBAの定数はConstステートメントで宣言します。

Const TRIANGLE_WIDTH As Integer = 100

　

複合代入演算子

まず、VBAには複合代入演算子はありません。

Pythonでは

>>> age = 10
>>> age = age + 1
>>> age
20

を次のように書きかえることができます。

>>> age = 19
>>> age += 1
>>> age
20

age = age + 1とage += 1とは同じ操作を意味します。

+=のことを複合代入演算子といいます。

　

複合代入演算子の種類

演算子 例 説明
+= a += b a = a + b。aにbをたした値を代入
-= a -= b a = a - b。aからbをひいた値を代入
*= a *= b a = a * b。aにbをかけた値を代入
/= a /= b a = a / b。aをbでわった値を代入
//= a //= b a = a // b。aをbでわった整数値を代入
%= a %= b a = a % b。aをbでわった余りを代入
**= a **= b a = a ** b。aをb回かけあわせた値を代入

　

+=を使って文字列を連結する

Pythonでは

>>> who = '猫'
>>> text = ''
>>> text += '我が輩は'
>>> text += who
>>> text += 'である。'
>>> text
'我が輩は猫である。'

VBAには複合代入演算子がないので

Dim who As String: who = "猫"
Dim text As String: text = "我が輩は"
text = text & who
text = text & "である。"
MsgBox text '我が輩は猫である。

　

変数に値を代入するとは

>>> walllet_1 = 100
>>> wallet_1
100

>>> wallet_2 = wallet_1
>>> walllet_2
100

>>> wallet_1
100

wallet_1の値をwallet_2に代入しても、wallet_1の値は100のままです。これはPythonでも、VBAでもおなじです。

これを「値渡し」といいます。「値」のコピーを渡すイメージですね。

これに対して「参照渡し」という引数の渡し方もあります。

この違いは「参照渡し メモリ番地」という語で検索するとたくさん説明がでてきますよ。

　

おわりに

「演算子」では意外とPythonとVBAに違いがでてきましたね。

Pythonはなんでもできるスクリプト言語、VBAはExcelというオブジェクトを操作するのに適した言語、という違いがでてきてだんだんと比較するのがむずかしくなってきたな、という印象です。

　

「Windows PC + JISキーボード」から「Mac + USキーボード」に変えてよかった点

Mac

f:id:m-eye:20181028110956p:plain

　

きっかけ

きっかけは「iPhone」でした。

それまでガラケーをつかっていたのが、はじめてのスマホとして2013年に「iPhone 4s」を購入。

以来、ずっとiPhoneをつかってます。

その母艦として、当初は2万円で購入した「ThinkPad Eシリーズ」の中古をつかっていたのですが、Windows版「iTunes」と「iCloud」の、原因不明な誤動作になやまされ、ネットで「MacBook」について検索した結果、「MacBook Pro」の購入にふみきりました。

　

結論

用途によって使い分けましょう。

わたしも今はMacがメインですが、いつWindowsがメインになるか、わかりません。

いまのところ、自宅でつかうのがWindowsメインになるとはおもいませんが。

　

変遷

「MacBook Pro」を購入するまでは、「Windows PC」一辺倒で、直近は「ThinkPad Eシリーズ」をつかっていました。

2014年に「MacBookPro 15inch 2014」を購入。その当時は、自宅ではあまりVBAプログラミングをおこなっていなかったので、おもに「iPhone」の母艦として、ネットサーフィンの道具としてつかっていました。

また、「Office for Mac 2011」を「MacBook Pro」に導入しただけでなく、「MacBook Pro」に「Parallels」を導入。「MacBook Pro」上でWindowsをつかえるようにしました。当然、「Microsoft Excel」もつかえるようにしました。

しかし、自宅で「Microsoft Excel」をつかうのは「家計簿」をつけるのがおもな用途だったので、「Parallels」もあまり起動しませんでした。

2017年にWordpressのブログを書きはじめて、自宅でVBAプログラミングをやるようになって、「ThinkPad X1 Carbon（2017モデル）」を購入。

メインでつかっていた「MacBook Pro」はサブマシンに降格。「ThinkPad X1C」をメインでつかうようになりました。会社でも自宅でもVBEでVBAプログラミングをおこなうようになり、特に不満もありませんでした。

サブマシンに降格した「MacBook Pro」は、おもに「YouTube」や動画配信サイトの動画視聴にのみ、つかっていました。

しかし、会社のPCのモニターは19インチ、自宅の「ThinkPad X1C」は14インチと、モニターの大きさには歴然とした差があり、そこに不満をいだくようになってきました。

そこで、2018年に「Python」と「はてなブログ」をはじめるにあたり、モニターが15インチと「ThinkPad」よりも大きい「MacBook Pro」に「Python」の環境を構築するのと同時に、メインマシンを「MacBook Pro」に変更しました。

　

MacとWindowsのちがい

f:id:m-eye:20181028125100j:plain

　

プログラミングや画像・映像・音楽制作などをせずに、ネットサーフィンやメール、YouTubeなどの動画視聴をおこなっているかぎり、特段ちがいはありません。

むしろ、Windowsをしらないなら、Macのほうがつかいやすいでしょう。

特にトラックパッドはMacのほうがつかいやすい。Windows PCはマウスなしでは絶対に操作できませんが、Macにはマウスは不要です。

WindowsのトラックパッドがMacと遜色ないというのはウソですから、信じない方がいいです。

「ThinkPad X1 Carbon（2017モデル）」のトラックパッドは、それまでのWidows PCからすると素晴らしい出来上がりですが、それ以上にトラックポイントの出来上がりがすばらしいので、トラックパッドを使う気にはなりません。

Windowsユーザにとって、Macがつかいにくい理由はただひとつ。

「Windowsに慣れていること」です。「Windowsとちがうこと」がただひとつの理由です。

つまり、「Windowsの操作性が保証されること」が必要なかたは、MacやChromeBookなどには手を出さないほうがいいでしょう。

　

Macをつかってはいけないひと

  • Windows版しかないソフトウェアを多用する方
  • PCゲームをする方
  • PCを自作する方

などは、Macには不向きです。

　

USキーボードの特徴

f:id:m-eye:20181028120559p:plain

　

「かな」刻印がない

「かな」入力の方には致命的な欠点になりえますが、「ローマ字」入力の方にはキートップがすっきりしていて、わかりやすいでしょう。

　

キーの数がすくない

「変換」や「無変換」、「ローマ字」キーなどのキーがないので、キー全体の数がすくなくなってます。その分、スペースキーが長くなったり、キーボードの右側のキーが不自然にちいさくなってることもなくなってます。

キーの大きさは、そのおかげで自然な大きさになってると感じます。

　

日本語入力の起動が「Command + Space」である

「漢字」キーがないため、日本語入力の起動キーは「Command + Space」に割り当てられています。

これは面倒なので、「Karabiner Elements」というソフトウェアをつかって、「右Command」キーに日本語入力オンを、「左Command」キーに日本語入力オフを設定しました。

もともと、Windows PCのときも、「変換」キーに日本語入力オンを、「無変換」キーに日本語入力オフを割り当てていたので、それに近い感覚で操作できるようになりました。

「Parallels」上の「Windows」では「alt-ime-ahk」というソフトウェアをつかって、似た感覚で操作できるようにしています。

ちなみに、「Parallels」上の「Windows」でのデフォルトの日本語入力のオンオフは、Alt + ~（オルト + チルダ）です。「日本語Windows上でUSキーボードをつかう設定」に変更すると、そうなります。

　

記号の配置が合理的

あとから作られた日本語キーボードは、キーの数がおおいため、むりやり記号類をおしこめた印象がありますが、USキーボードは合理性を感じます。

ですので、日本語キーボードをつかっている方がUSキーボードに慣れるのは比較的カンタンですが、逆はむずかしいと思います。

あまりつかわない「チルダ」と「バッククォート」は左上に、おなじくあまりつかわない「\」と「|」は右上にあります。

いちばんよくつかう「カンマ」「ピリオド」「スラッシュ」「<」「>」「?」は同じ位置にあります。

「セミコロン」と「コロン」、「シングルクォート」と「ダブルクォート」、「[」と「]」、「{」と「}」は、Shiftを押す押さないの感覚がおなじです。

「イコール」と「プラス」、「マイナス」と「アンダーバー」の関係も利用頻度からいえば、USキーの配置のほうがだんだん合理的におもえてきます。

日本語キーボードからUSキーボードにのりかえて、感覚的にいちばんとまどうことは「右かっこ」と「左かっこ」の配置でしょう。

日本語キーボードでは、「右かっこ」と「左かっこ」はそれぞれ数字の「8」キーと「9」キーに配置されていますが、USキーボードでは、、「右かっこ」と「左かっこ」はそれぞれ数字の「9」キーと「0」キーに配置されています。

つまり、日本語キーボードとUSキーボードでは、「右かっこ」と「左かっこ」の配置が1つずれているのです。

まったく違うのなら、かえってとまどうことはすくないと思います。「1つずれているだけ」という配置のちかさがかえって混乱をよぶと思います。

　

Macにしてよかった点（メリット）

操作しててたのしい

ThinkPadを操作しててたのしいとおもうことはありませんが、Macは思い入れがあるぶん、たのしいですね。

　

画面がおおきくて見やすい

14インチと15インチのたった1インチの違いなのですが、圧倒的にMacのほうが見やすいです。

ThinkPadのほうが横長で縦に短いためによけいそう感じるのかもしれません。

　

トラックパッドの操作性

上にも書きましたが、とにかく操作しやすいです。これに慣れたら、Windows PCに戻る気はしません。

正直「Windowsが必要なら、Parallels上でWindowsを動かせばいいじゃん」とおもってます。

そうすれば、Macのトラックパッドをつかえますからね。

　

Apple製品との相性がよい

とくに日本では「iPhone」がおおいだけにこのメリットは大きいとおもいます。

これからの時代、個人利用ではPCよりもスマホがメインになってくるとおもいます。

そういうライトなつかいかたならば、MacBookで十分でしょう。

　

わるかった点（デメリット）

これといってデメリットは感じません。

しかし、これからWindowsからMacへの移行をかんがえている方のためにかんがえてみると、

  • コストがかかる（ハードウェア + Parallelsとその上でうごくWindows代）
  • 会社ではWindowsというところがおおい

というところですが、わたしにとってはデメリットではなかったですね。

　

おわりに

個人的な感想では、

  • Mac使いには、Windowsも使えるひとがおおい

  • Windows使いには、Windowsしか使ったことがないひとが圧倒的におおい

という印象ですね。

　

VBAでカタカナをひらがなに変換するマクロ - 不渡届その4

VBA

f:id:m-eye:20181019094618p:plain

　

はじめに

  • ファイルはYahoo! ボックスに公開しているので、ここからダウンロードして自由につかってください。
  • このファイルには「不渡届その1・その2・その3」で作成したマクロを含みます。
  • 動作確認は、Offie 365 Solo + Windows 10でおこなっています。
  • 動作は無保証です。

　

カタカナをひらがなに変換したい

不渡届では、法人名または個人名の「フリガナ」を書くと、「索引（かしら字）」というものを書かなければなりません。

「ハテナブログ」というフリガナに対して「は」というひらがな1文字を書くということです。

「ハテナブログ」　→　「は」

それをいちいち手書きすると面倒くさいので、VBAで自動的に表示してくれ、というリクエストがあり、開発することになりました。

　

変換するためのルール

  • 法人名または個人名のフリガナの頭文字1文字をひらがなに変換する、というルール
  • 濁音（「ば」など）、半濁音（「ぱ」など）は清音（「は」など）に変換する、というルール
  • 「ヴ」を「う」に変換するというルール

この3つのルールにもとづいて、フリガナをひらがな1文字に変換します。

　

カタカナとひらがなについて研究する

カタカナをひらがなに変換するには、なんらかの関連性が必要ですが、パソコンであつかう文字にはそれぞれ文字コードがありますので、それを確認します。

　

文字コードを確認する2つの方法

いきなりですが、Excelには文字コードを確認する方法は2つあります。

1つはむかしながらの方法であるCODE関数をつかう方法。これはWorksheetFunctionオブジェクトにふくまれていないので、VBAでつかうときはEvaluateメソッドとともに実行する必要があります。

もう1つはExcel2013からの方法であるUNICODE関数をつかう方法です。こちらはWorksheetFunctionオブジェクトにふくまれますので、VBAでつかうときはWorksheetFunctionオブジェクトとともに実行します。

CODE関数も、UNICODE関数も、文字列の1文字目の文字コードを取得します。

ここでは、UNICODE関数をつかう方法で説明していきます。

ですので、Excel2013以降でないと、実行できません。

Excel2010で試したいときは、CODE関数をつかって試してみてくださいね。

　

文字と文字コードを相互に変換する

UNICODE関数をつかうと、UNICODE("ア")=12450となって、文字を文字コードに変換できます。

文字を文字コードに変換して、カタカナとひらがなの関連性について研究するとしても、最終的に文字コードから文字に変換できなければなりません。

そのときは、文字コードを文字に変換するUNICHAR関数をつかいます。

UNICHAR関数をつかうと、UNICHAR(12450)="ア"となって、文字コードを文字に変換することができます。

ちなみに、Excel2010ではUNICHAR関数ではなく、CHAR関数をつかいます。

　

カタカナとひらがなのそれぞれの文字の文字コードをしらべてみる

UNICODE関数をつかって文字コードをしらべてみます。

f:id:m-eye:20181027130316p:plain

上の図は、ぜんぶのカタカナとひらがなの文字コードをExcel上で一覧表にしたものです。

カタカナについては

列名 内容 列名 内容
A列 カタカナの清音の文字 B列 その文字コード
C列 カタカナの濁音の文字 D列 その文字コード
E列 カタカナの半濁音の文字 F列 その文字コード

ひらがなについては

列名 内容 列名 内容
H列 ひらがなの清音の文字 I列 その文字コード
J列 ひらがなの濁音の文字 K列 その文字コード
L列 ひらがなの半濁音の文字 M列 その文字コード

カタカナとひらがなの関連性については

列名 内容
O列 カタカナの清音の文字コードの数値から、ひらがなの清音の文字コードの数値を引いた差の数値

　

一覧表からわかったこと

  • カタカナの清音の文字コードの数値から、ひらがなの清音の文字コードの数値を引いた差の数値は96である
  • 濁音の文字コードは清音の文字コードに1をたしたものである
  • 半濁音の文字コードは濁音の文字コードに1をたしたものである
  • 半濁音の文字コードは清音の文字コードに2をたしたものである

以上の性質を利用して、マクロを作成します。

　

変換するためのルールを具体化してみる

  1. カタカナの文字列からUNICODE関数でその1文字目の文字コードを取得して、その文字コードから96をひいた数値をUNICHAR関数の引数にして、ひらがなの1文字を取得する
  2. カタカナの濁音・半濁音は清音にしてからひらがなに変換する
  3. カタカナの「ヴ」は「ゔ」にできないで、「う」に変換する

この3つを具体的にみていきましょう。

　

カタカナをひらがなに変換する

これが「変換の大原則」です。

セル「J7」に入力されたフリガナをもとに、セル「B8」にひらがなの頭文字を入力します。

　

ソースコード

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case True
        Case Not Intersect(Target, Range("J7")) Is Nothing
            Dim Katakana As Range
            Dim Hiragana As Range
            
            Set Katakana = Range("J7")
            Set Hiragana = Range("B8")
    
            If Len(Katakana.Value) = 0 Then
                Hiragana.Value = ""
            
            Else
                Dim myCode As Long
                myCode = _
                WorksheetFunction.Unicode(Katakana.Value)
                Hiragana.Value = F_NormalConverter(myCode)
                
            End If
            
            Set Hiragana = Nothing
            Set Katakana = Nothing
    End Select
End Sub

Private Function F_NormalConverter(ByVal myCode As Long) _
    As String
    
    F_NormalConverter = _
        WorksheetFunction.Unichar(myCode - 96)
End Function

　

解説

Worksheet_Changeサブルーチンをつかって、フリガナを入力するセル「J7」の値が変化したときに、セル「B8」の値を変化させます。

セル「J7」にはフリガナを入力するので、オブジェクト変数Katakanaにセル「J7」を代入します。

セル「B8」にはひらがなの頭文字を代入するので、オブジェクト変数Hiraganaにセル「B8]を代入します。

なれてないうちは、「文字列型変数」にセル「J7」・「B8」の値を代入しがちですが、VBAになれてきたら、ぜひとも「オブジェクト型変数」を活用しましょう。

オブジェクト型変数のほうが、プロパティとメソッドをつかうことができるので、自由がきいて使いやすいです。

Katakanaの値をDELETEキーで消去したときは、Hiraganaの値を消去します。

そうでないときは、フリガナが入力されている状態なので、Katakanaセルの値をUNICODE関数で文字コードに変換して、その値を変数myCodeに代入します。

myCodeはカタカナの文字コードの値なので、その値から96を引くと、ひらがなの文字コードの値になります。その値をF_NormalConverterファンクション・プロシージャに代入しします。

F_NormalConverterファンクション・プロシージャでは、myCode - 96の値をUNICHAR関数の引数にして、ひらがなの文字列を取得します。取得したその値はF_NormalConverterファンクション・プロシージャの返り値になりますので、それをHiraganaセルに代入します。

　

カタカナの濁音・半濁音は清音にしてからひらがなに変換する

これはカタカナの文字列を文字コードにもとづいて分類し、変換の大原則に還元してやるということです。

これは一覧表をみて考えるとわかりやすいと思います。

カタカナの濁音・半濁音は清音にするということから始まります。

一覧表をみると、アイウエオ順と文字コードの順番は一致しています。

　

変換の大原則にしたがう場合

拗音の「ァ」から「カ」までは「変換の大原則」にしたがって変換することができます。これは文字コードが12449から12459までです。

同様に拗音の「ッ」の場合は文字コードが12483であり、「ツ」の場合は文字コードが12484であり、「ナ」から「ノ」の場合は文字コードが12490から12494であり、「マ」から「ン」の場合は文字コードが12510から12531です。

いずれの場合も、文字コードの数値から96を引いた数値をUNICHAR関数の引数にして、ひらがなの文字をもとめます。

　

濁音を清音に変換する場合

濁音の文字コードが偶数の場合と奇数の場合で処理がことなります。

　

濁音の文字コードが偶数の場合

「ガ」から「ヂ」までは、濁音を清音に変換してから、ひらがなに変換します。

その前に「ガ」の文字コードをみると12460であり、「か」の文字コードをみると12459です。

ほかの文字も同様であり、濁音の場合の文字コードは「偶数」であり、清音の場合の文字コードは「奇数」です。

したがって、文字コードの数値を「2」で割ってあまりが「0」の場合は濁音の文字コードですので、1を引いてやると、清音の文字コードになります。

割り算をしてあまりを求めるには、MOD演算子をつかいます。

Private Function F_TwoMod_1(ByVal myCode As Long) As Long
    If myCode Mod 2 = 0 Then myCode = myCode - 1
    
    F_TwoMod_1 = myCode
End Function

このファンクション・プロシージャをかましてやることによって、「変換の大原則」にしたがって変換することができます。

　

濁音の文字コードが奇数の場合

「ヅ」から「ド」までの場合、「ヅ」の文字コードをみると12485であり、ほかの文字も同様であり、濁音の場合の文字コードは「奇数」であり、清音の場合の文字コードは「偶数」です。

したがって、文字コードの数値を「2」で割ってあまりが「1」の場合は濁音の文字コードですので、1を引いてやると、清音の文字コードになります。

Private Function F_TwoMod_0(ByVal myCode As Long) As Long
    If myCode Mod 2 = 1 Then myCode = myCode - 1
    
    F_TwoMod_0 = myCode
End Function

このファンクション・プロシージャをかましてやることによって、「変換の大原則」にしたがって変換することができます。

　

濁音・半濁音を清音にする場合

例として、「ハ」と「バ」と「パ」の文字コードの関係を整理してみましょう。

「ハ」の文字コードは12495であり、「バ」の文字コードは12496であり、「パ」の文字コードは12497です。

この場合、文字コードの数値を「3」で割ってあまりが「0」の場合は清音の文字コード、「3」で割ってあまりが「1」の場合は濁音の文字コード、「3」で割ってあまりが「2」の場合は半濁音の文字コードだということです。

したがって、「3」で割ってあまりが「0」の場合は、そのままで清音の文字コード。

つぎに、「3」で割ってあまりが「1」の場合は、「1」を引くと清音の文字コード。

さらに、「3」で割ってあまりが「2」の場合は、「2」を引くと清音の文字コード。

以上のことをマクロにするには、Select文とMod演算子をくみあわせます。

Private Function F_ThreeMod(ByVal myCode As Long) As Long
    Select Case myCode Mod 3
        Case 1
            myCode = myCode - 1
            
        Case 2
            myCode = myCode - 2
            
    End Select
    
    F_ThreeMod = myCode
End Function

　

カタカナの「ヴ」を変換する場合

カタカナの「ヴ」の文字コードは12532なので、その場合にHiraganaの値を「う」にします。

　

まとめ

以上をまとめると、次のソースコードになります。

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case True
        Case Not Intersect(Target, Range("J7")) Is Nothing
            Dim Katakana As Range
            Dim Hiragana As Range
            
            Set Katakana = Range("J7")
            Set Hiragana = Range("B8")
    
            If Len(Katakana.Value) = 0 Then
                Hiragana.Value = ""
            
            Else
                Dim myCode As Long
                myCode = _
                WorksheetFunction.Unicode(Katakana.Value)
                
                Select Case myCode
                    Case 12449 To 12459, 12483 To 12484, _
                         12490 To 12494, 12510 To 12531
                        Hiragana.Value = _
                        F_NormalConverter(myCode)
                    
                    Case 12460 To 12482
                        Hiragana.Value = _
                        F_NormalConverter(F_TwoMod_1(myCode))
                    
                    Case 12485 To 12489
                        Hiragana.Value = _
                        F_NormalConverter(F_TwoMod_0(myCode))
                    
                    Case 12495 To 12509
                        Hiragana.Value = _
                        F_NormalConverter(F_ThreeMod(myCode))
                        
                    Case 12532
                        Hiragana.Value = "う"

                End Select
            End If
            
            Set Hiragana = Nothing
            Set Katakana = Nothing
    End Select
End Sub

Private Function F_NormalConverter(ByVal myCode As Long) _
    As String
    
    F_NormalConverter = _
        WorksheetFunction.Unichar(myCode - 96)
End Function

Private Function F_TwoMod_1(ByVal myCode As Long) As Long
    If myCode Mod 2 = 0 Then myCode = myCode - 1
    
    F_TwoMod_1 = myCode
End Function

Private Function F_TwoMod_0(ByVal myCode As Long) As Long
    If myCode Mod 2 = 1 Then myCode = myCode - 1
    
    F_TwoMod_0 = myCode
End Function

Private Function F_ThreeMod(ByVal myCode As Long) As Long
    Select Case myCode Mod 3
        Case 1
            myCode = myCode - 1
            
        Case 2
            myCode = myCode - 2
            
    End Select
    
    F_ThreeMod = myCode
End Function

　

おわりに

今回のリクエストがあるまで、カタカナとひらがなの文字コードの関係、清音と濁音と半濁音の関係などについて、しらべたこともありませんでしたが、しらべてみたら明確な数値の関係があり、意外とおもしろかったですね。

もっとも、文字コードの世界は深いので、これ以上掘るよりはほかのことを深掘りしようと思います。

　

セルの入力規則をVBAで設定する - 不渡届その3

VBA

f:id:m-eye:20181019094618p:plain

　

はじめに

  • ファイルはYahoo! ボックスに公開しているので、ここからダウンロードして自由につかってください。
  • このファイルには「不渡届その1・その2」で作成したマクロを含みます。
  • 動作確認は、Offie 365 Solo + Windows 10でおこなっています。
  • 動作は無保証です。

　

セルの入力規則をVBAで設定したい

セルの入力規則はいちど設定すると変更することはあまりないと思いますが、うっかり設定を変更してしまうこともあるとおもいます。

そういうときに困るのはユーザなので、そういうことがないようにVBAでファイルの起動時に毎回入力規則を設定するのがいいでしょう。

　

設定するには

  • 標準モジュールに入力規則を設定するマクロを書きます。
  • ThisWorkbookモジュールにそのマクロを起動する命令文を追加します。

　

セルの入力規則を設定するマクロ

　

ソースコード

Sub S_SetIMEMode()
    Application.ScreenUpdating = False
    
    Worksheets("NoticeOfDishonor").Unprotect
    
    Range("J8, B8, D19, O19, N10, O12, D16").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly
        .IMEMode = xlIMEModeHiragana
    End With
    
    Range("J7, P11").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly
        .IMEMode = xlIMEModeKatakana
    End With
    
    Range("G7, AD10, I13, S16, W16").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly
        .IMEMode = xlIMEModeAlpha
    End With
    
    Range("G8, Y8").Select
    With Selection.Validation
        .Add Type:=xlValidateList, Formula1:="=$B$30:$B$33"
        .IMEMode = xlIMEModeHiragana
    End With
    
    Range("AD9").Select
    With Selection.Validation
        .Add Type:=xlValidateList, Formula1:="=$B$42:$B$46"
        .IMEMode = xlIMEModeHiragana
    End With
    
    Range("AE15").Select
    With Selection.Validation
        .Add Type:=xlValidateList, Formula1:="=$B$56:$B$58"
        .IMEMode = xlIMEModeHiragana
    End With
    
    Range("G10").Select
    With Selection.Validation
        .Add Type:=xlValidateList, Formula1:="=$B$36:$B$39"
        .IMEMode = xlIMEModeHiragana
    End With
    
    Range("AG3").Select
    
    Worksheets("NoticeOfDishonor").Protect _
        AllowFormattingCells:=True
    
    Application.ScreenUpdating = True
End Sub

　

解説

　

画面のちらつきを抑えたい

Application.ScreenUpdating = False

'セルに入力規則を設定する処理

Application.ScreenUpdating = True

いくつものセルを選択するので、画面がちらつきます。

それを抑えるために、Application.ScreenUpdatingプロパティにFalseを設定して、画面の再描画を停止して、ちらつきを抑えます。

入力規則を設定しおえたら、Application.ScreenUpdatingプロパティにTrueを設定して、画面の再描画を再開します。

　

シートの保護

ふだんはシートを保護していますが、セルの入力規則を設定するときだけ、シートの保護を解除します。

Worksheets("NoticeOfDishonor").Unprotect

'セルに入力規則を設定する処理

Worksheets("NoticeOfDishonor").Protect _
    AllowFormattingCells:=True

シートの保護をするときには、ロックしているセルの選択をできないようにして、ロックのかかっていない、これから値を入力したり、変更したりするセルだけを選択できるようにします。

また、文字の大きさを変更できるように、AllowFormattingCells:=Trueでセルの書式設定をできるようにしておきます。

　

セルの入力規則

Excelの標準機能で設定している入力規則をVBAで設定します。

　

入力規則の設定方法

    Range("入力規則を設定するセル").Select
    With Selection.Validation
        .Delete
        .Add Type:="入力規則の種類"
        .IMEMode = "日本語の入力規則の内容"
    End With

マクロの自動記録をとると、いったん入力規則をDeleteしてから、再度設定していますので、このとおりやりましょう。

また、いったんセルを選択しないとうまくいかないようです。

　

よくつかう入力規則の種類
入力規則の種類 引数
xlInputOnly 引数はとくに不要。自由に入力できます
xlValidateList 引数 Formula1 を必ず指定します。引数 Formula1 には、コンマで区切った値の一覧またはこの一覧へのシート参照を指定する必要があります

　

よくつかう日本語の入力規則の内容
定数 内容
xlIMEModeAlpha 半角英数字
xlIMEModeHiragana ひらがな
xlIMEModeKatakana カタカナ

　

入力規則の種類と日本語の入力規則の内容をくみあわせる

どんな文字でも入力してもいいかどうか、リストから選択するか。

半角英数字を入力するのか、ひらがなからかな漢字変換をつかうのか、カタカナを入力するのか。

それぞれのセルによって、設定はちがうので、まずセルを選択して、そのセルにあう内容を設定します。

　

おわりに

ノンプログラマにとっては、入力規則やシート保護、書式設定などもVBAとおなじか、それ以上に重要です。

VBAをつかって特殊なことを実現することよりも、標準の機能を便利につかえるようにするためにVBAを利用することのほうが優先順位がたかいことはよくあります。

ブックやシートやセルに関する設定を操作することは、VBAの独壇場です。

ほかの言語でもできることをムリにVBAですることよりも、VBAにしかできない、ブックやシートやセルといったExcel固有のオブジェクトを自由に操作できるようにすることが重要だと思います。

　

特定のセルだけを「Enter」キーで巡回するマクロ - 不渡届その2

VBA

f:id:m-eye:20181019094618p:plain

　

はじめに

  • ファイルはYahoo! ボックスに公開しているので、ここからダウンロードして自由につかってください。
  • このファイルには「不渡届その1」で作成したマクロを含みます。
  • 動作確認は、Offie 365 Solo + Windows 10でおこなっています。
  • 動作は無保証です。

　

キーボードだけで入力作業を完結したい

不渡届を作成する際に、マウスをつかいたくない、というリクエストがありました。

キーボードから手をはなさずに作業したいけど、さすがにホームポジションをまったく崩さずに作業するのはムリなので、Enterキーや、Altキーなどの修飾キー、矢印キーなどはつかってよいことにして、特にEnterキーでセルの移動をできるようにしたい、といわれました。

　

実現するには

まず、特定のセルを巡回するマクロを作成します。

たとえば、セルAG3からセルG8に、セルG8からセルJ8に、セルJ8からセルAG3へと巡回します。

ついでに、逆順に巡回するマクロも作成します。

そのつぎに、その巡回マクロをEnterキーに登録するマクロ・解除するマクロを作成します。

さいごに、ブックモジュールに、ブックを開いたときと閉じるときに自動的に登録・解除するマクロを作成します。

　

特定のセルを巡回するマクロ

セルを巡回する順番をきめます。

AG3→G8→J8→Y8→J7→B8→AD9→AD10→AE15→D19→O19→G10→
N10→I13→O12→P11→D16→S16→W16→AG3→以下、おなじ

これをマクロに落とし込みます。AG3がアクティブのときはG8を選択、G8がアクティブのときはJ8を選択、というマクロをつくります。

マクロ名のS_IndicateEnterDirectionは「Enterを押したときのセルの移動方向を決める」という意味ですが、自分がわかればなんでもいいと思います。

わたしはこのマクロをEnterキーに登録するつもりなので、このマクロ名にしました。

　

ソースコード

Sub S_IndicateEnterDirection()
    Select Case ActiveCell.Address(False, False)
        
        Case "AG3"
           Range("G8").Activate
           
        Case "G8"
           Range("J8").Activate
           
        Case "J8"
           Range("Y8").Activate
           
        Case "Y8"
           Range("J7").Activate
           
        Case "J7"
           Range("B8").Activate
           
        Case "B8"
           Range("AD9").Activate
           
        Case "AD9"
           Range("AD10").Activate
           
        Case "AD10"
           Range("AE15").Activate
           
        Case "AE15"
           Range("D19").Activate
           
        Case "D19"
           Range("O19").Activate
           
        Case "O19"
           Range("G10").Activate
           
        Case "G10"
           Range("N10").Activate
           
        Case "N10"
           Range("I13").Activate
           
        Case "I13"
           Range("O12").Activate
           
        Case "O12"
           Range("P11").Activate
           
        Case "P11"
           Range("D16").Activate
           
        Case "D16"
           Range("S16").Activate
           
        Case "S16"
           Range("W16").Activate
           
        Case "W16"
           Range("AG3").Activate
           
        Case Else
        
            On Error Resume Next
            
            With ActiveCell
                
                Application.MoveAfterReturn = True
            
                Select Case Application. _
                    MoveAfterReturnDirection
                    
                    Case xlDown
                        .Offset(1, 0).Select
                        
                    Case xlUp
                        .Offset(-1, 0).Select
                        
                    Case xlToRight
                        .Offset(0, 1).Select
                        
                    Case xlToLeft
                        .Offset(0, -1).Select
                        
                End Select
            
            End With
            
    End Select
    
End Sub

　

解説

ActiveCell.Address(False, False)でアクティブセルのアドレスを相対参照で取得します。

それをさきほど決めた順番にあてはめてコーディングします。

ただし、順番のなかにないセルの場合、Enterをおしても全くうごかなくなってしまいます。

ですので、Case Elseのなかで他のセルの挙動を規定しています。

Application.MoveAfterReturn = Trueで、Enterキーを押したあとにアクティブセルを移動するように設定します。

Application.MoveAfterReturnDirectionは、「ファイル」メニュー→「オプション」→「詳細設定」→「Enterキーを押したら、セルを移動する(M)」という項目の「方向」オプションで選んだ「下」、「右」、「上」、「左」のどれが選択されているかを取得します。

xlDown、xlUp、xlToRight、xlToLeftのそれぞれのとき、「下」、「上」、「右」、「左」に1つセルを移動します。

　

特定のセルを「逆順で」巡回するマクロ

自分できめたセルを巡回する順番を逆に移動するだけですね。

マクロ名のS_IndicateShiftEnterDirectionは「Shift + Enterを押したときのセルの移動方向を決める」という意味ですが、自分がわかればなんでもいいと思います。

わたしはこのマクロをShift + Enterキーに登録するつもりなので、このマクロ名にしました。

　

ソースコード

Sub S_IndicateShiftEnterDirection()
    Select Case ActiveCell.Address(False, False)
        Case "AG3"
           Range("W16").Activate
           
        Case "W16"
           Range("S16").Activate
           
        Case "S16"
           Range("D16").Activate
           
        Case "D16"
           Range("P11").Activate
           
        Case "P11"
           Range("O12").Activate
           
        Case "O12"
           Range("I13").Activate
           
        Case "I13"
           Range("N10").Activate
           
        Case "N10"
           Range("G10").Activate
           
        Case "G10"
           Range("O19").Activate
           
        Case "O19"
           Range("D19").Activate
           
        Case "D19"
           Range("AE15").Activate
           
        Case "AE15"
           Range("AD10").Activate
           
        Case "AD10"
           Range("AD9").Activate
           
        Case "AD9"
           Range("B8").Activate
           
        Case "B8"
           Range("J7").Activate
           
        Case "J7"
           Range("Y8").Activate
           
        Case "Y8"
           Range("J8").Activate
           
        Case "J8"
           Range("G8").Activate
           
        Case "G8"
           Range("AG3").Activate
           
        Case Else
        
            On Error Resume Next
            
            With ActiveCell
                
                Application.MoveAfterReturn = True
            
                Select Case Application. _
                    MoveAfterReturnDirection
                    
                    Case xlDown
                        .Offset(1, 0).Select
                        
                    Case xlUp
                        .Offset(-1, 0).Select
                        
                    Case xlToRight
                        .Offset(0, 1).Select
                        
                    Case xlToLeft
                        .Offset(0, -1).Select
                        
                End Select
            
            End With
            
    End Select
    
End Sub

解説

解説はとくにありませんが、気をつけるとしたら、スペルミスくらいですかね。

だれも指摘してくれないと、けっこうな時間、気がつかないことがあります。

　

巡回マクロを「Enter」キーに登録・解除するマクロ

　

登録

巡回マクロをEnterキーに登録するときは、Application.OnKeyメソッドを使います。

Application.OnKey "登録するキー", "登録するマクロ名"

登録するキーは、Enterキーはエディタに表示されないので、特別なコードを使います。

キー コード
Enter（メインキーボード） ~（チルダ）
Enter（テンキー） {ENTER}

「逆順」で巡回するマクロはShift + Enterに登録するのですが、Shiftも特別なコードをつかいます。

キー コード
Shift +（プラス記号）

以上をくみあわせると次のようなマクロになります。

Sub S_AddKeysToMacro()
    Application.OnKey "~", "S_IndicateEnterDirection"
    Application.OnKey "{Enter}", "S_IndicateEnterDirection"
End Sub

逆順のマクロを登録するのは、次のようなマクロになります。

Sub S_AddKeysToMacro2()
    Application.OnKey "+~", _
        "S_IndicateShiftEnterDirection"
    Application.OnKey "+{Enter}", _
        "S_IndicateShiftEnterDirection"
End Sub

　

解除

巡回マクロをEnterキーから解除するときも、Application.OnKeyメソッドを使います。

Application.OnKey "解除するキー" 'マクロ名はなにも書かない

マクロ名を省略することで、登録していたマクロを解除する意味になります。

次のマクロは、Enterキーからマクロを解除するものです。

Sub S_RemoveKeysFromMacro()
    Application.OnKey "~"
    Application.OnKey "{Enter}"
End Sub

次のマクロは、Shift + Enterキーからマクロを解除するものです。

Sub S_RemoveKeysFromMacro2()
    Application.OnKey "+~"
    Application.OnKey "+{Enter}"
End Sub

　

「ThisWorkbook」モジュールに登録するマクロ

「セルを巡回するマクロ」を登録・解除するマクロを実行するタイミングは、このブックを開いたとき・閉じるときです。

次のマクロで「セルを巡回するマクロ」を登録・解除します。

Private Sub Workbook_Open()
    Call S_AddKeysToMacro
    Call S_AddKeysToMacro2
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call S_RemoveKeysFromMacro
    Call S_RemoveKeysFromMacro2
End Sub

Workbook_Openプロシージャで、このブックをひらくときに正順・逆順のセル巡回マクロを登録します。

Workbook_BeforeCloseプロシージャで、このブックをとじるときにマクロを解除します。

　

おわりに

実際にマウスなしで操作するには、「データの入力規則の設定」や「シートの保護」など、ほかにやることがありますが、とりあえず特定のセルを移動することは、今回のマクロでできるようになります。

　

郵便番号から住所を検索するマクロ（データ更新機能つき） - 不渡届その1

VBA

f:id:m-eye:20181019094618p:plain

　

郵便番号を入力すると住所の一部を表示するマクロ

セルに郵便番号を入力してEnterを押すと、該当する住所の一部とそのフリガナを表示するマクロを考えました。

たとえば、郵便番号のセルに「154-0001」とセルに入力してEnterを押すと、住所のセルに「東京都世田谷区池尻」と表示されるようなものです。

VLOOKUPのようなワークシート関数をつかうと超高速で表示されるのですが、実務上は「東京都世田谷区池尻」のあとに地番などを入力する必要があるので、関数はムリです。ちゃんと文字列が入力される必要があります。

そこでマクロをつかうことになりました。VLOOKUPと同じスピードを確保して、セルには検索の結果が数式ではなく、文字列で入力されるという条件は、マクロを使わないと満たせませんね。

  • ファイルはYahoo! ボックスに公開しているので、ここからダウンロードして自由につかってください。
  • 動作確認は、Offie 365 Solo + Windows 10でおこなっています。
  • 動作は無保証です。

　

この記事は

VBAの命令の1行1行はわかるけれども、いったいどう組み合わせたら実際に使えるマクロが書けるかわからない、と悩んでいる方にむけて書きました。

1つ1つはカンタンな命令しかないかもしれませんが、どう組み合わせるか、なにに配慮するかの例として読んでいただけたらと思います。

この記事は、かなり長いです。いろいろ説明を詰め込みすぎたかもしれません。

　

郵便番号データ

約15万件あり、日々更新されているのですが、便利なサイトがあったので、その公開データをもとに更新元となる郵便番号データを更新するマクロも同時に作成して、郵便番号データの陳腐化をふせぎます。

jusyo.jp

無料で郵便番号データを公開してくださっているありがたいサイトはいくつもありますが、このサイトもそのひとつです。ここにCSVファイルがありましたので、それをつかわせていただきます。ほかにもMDBファイルなどもありますが、ExcelであつかうにはCSVファイルがいいですね。

　

ワークシートの例として不渡届をえらびました

f:id:m-eye:20181019094618p:plain

ノンプログラマが実際の業務であつかうExcelの帳票にはいろいろあるとおもいますが、これもそのひとつです。

わたしの会社でもいままで手書きだった不渡届がようやくExcelのファイルになりました。もちろん、手形交換所にはいまでも手書きで提出しますが。

ワークシートはなんでもいいと思いますが、ただセルに入力するだけでは自分がおもしろくないので、Web上に公開されている一般財団法人静岡県銀行協会のこのPDF文書の不渡届をもとに自分でエクセルファイルを作成しました。

実際の業務でつかっている不渡届は社外に出せないので、作成したのはその一部で、例として入力してあるデータはまっかなウソです。架空のデータを入力しています。実在の方とはなんの関係もありません。

　

マクロの構成

f:id:m-eye:20181019174731p:plain

　

シートモジュールの郵便番号検索マクロ

実際に業務で使うマクロなので、パソコンにあまりくわしくないユーザへの気配りはかかせません。自分だけが使うわけではないので。

郵便番号を検索するマクロは、マクロを起動するトリガーが必要ですね。今回はボタンやショートカットにマクロを登録するのではなく、セルに郵便番号を入力して（セルの内容を変化させて）、Enterを押すとマクロが起動して、住所とフリガナを表示するようにします。

だから、そのマクロはセルの内容の変化を検知するイベントプロシージャになり、「NoticeOfDishonor」という名前のシートのシートモジュールに書きます。

　

ThisWorkbookモジュールのマクロ

パソコンにあまりくわしくないユーザは、ブックをひらいたときに、まずどのセルを操作するのか探すのが苦痛です。それをやわらげるために、このブックをひらいたときに、不渡届のシートを選択し、郵便番号のセルを選択しておきます。

　

標準モジュールのデータ更新マクロ

これは自分で使うか、インターネット参照権限を持った管理者が使う（わたしの部署のほとんどの人はインターネットの参照権限がないばかりか、自分専用のパソコンもありません）ので、イベントプロシージャにする必要はありません。シート上にボタンを配置するか、ショートカットを設定すればじゅうぶんです。今回はほかの管理者にも便利なようにシート上にボタンを配置することにしました。

　

シートモジュールの郵便番号検索イベントプロシージャ

ここでは、Worksheet_Changeイベントプロシージャをつかいます。Worksheet_SelectionChangeではセルの値に変化がなくても別のセルをクリックしただけでプロシージャが起動してしまいますので適切ではないですね。

　

ソースコード

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range
    Dim myLf As Long
    Dim my1stValue As String, my2ndValue As String
    
    Select Case True
        Case Not Intersect(Target, Range("I13")) Is Nothing
            Set myCell = Range("I13")
            myLf = InStr(myCell.Value, vbLf)
            
            Select Case Len(myCell.Value)
                Case 8
                    If F_CheckUpZip(myCell.Value) = False _
                        Then Exit Sub
                    If F_CheckUpSheet = False _
                        Then Exit Sub
                    
                    Call S_DisplayAddress(myCell.Value)
                
                Case 17
                    If myLf > 0 Then
                        my1stValue = _
                            Left(myCell.Value, myLf - 1)
                        my2ndValue = _
                            Mid(myCell.Value, myLf + 1)
                        
                        If F_CheckUpZip(my1stValue) = _
                            False Then Exit Sub
                        If F_CheckUpZip(my2ndValue) = _
                            False Then Exit Sub
                        If F_CheckUpSheet = _
                            False Then Exit Sub
                        
                        Call S_DisplayAddress2( _
                            my1stValue, my2ndValue)
                            
                    Else
                        Call S_ClearWorksheet
                        
                    End If
                
                Case 0
                    Range("O12, P11").Value = ""
                    myCell.Activate
                 
                Case Else
                    Call S_ClearWorksheet
                    
            End Select
            
            Set myCell = Nothing
            
    End Select
    
End Sub

　

ソースコードの解説

　

「Worksheet_Change」プロシージャの使い方

　

「Intersect」プロパティの使い方

郵便番号を入力するセル「I13」の値が変化したときだけ、住所とフリガナを入力したいので、Intersectメソッドを使って、「Worksheet_Change」プロシージャの引数Targetにセル「I13」が含まれるかどうか判断します。

　

「Intersect」プロパティの基本
Intersect(Target, Range("I13"))

Intersectプロパティは「2つのRange型オブジェクトの引数TargetとRange("I13")との間の共通範囲があるかどうか」、共通範囲があればそのRange型オブジェクトを、なければNothingを返します。

　

「Nothing」との比較
Intersect(Target, Range("I13")) Is Nothing

これは「2つのRange型オブジェクトの引数TargetとRange("I13")との間の共通範囲がないこと（Nothing）」をしめしています。

　

接頭辞「Not」をつけると全体の否定になる
Not Intersect(Target, Range("I13")) Is Nothing

一方、先頭にNotをつけると、全体の意味を否定することになります。

ですから、これは「2つのRange型オブジェクトの引数TargetとRange("I13")との間の共通範囲があること」をしめしています。

これがマクロを起動するトリガーになります。If文やSelect文といった条件文に、この条件をかけば、そのときマクロが起動します。

　

「If」文ではなく「Select」文をつかう

今回の条件は、「Range("I13")の値が変化すること」1つですから、次のように書いてもまちがいではありません。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("I13")) Is Nothing
       'ここに実行する処理を書く
    End If
End Sub

でも、使わないのには理由があります。

2つ以上の条件になったときにElseIfを使いたくないからです。ほかにもっといい方法があるからなんです。

　

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case True
        Case Not Intersect(Target, Range("I13")) Is Nothing
           'ここに実行する処理を書く
    End Select
End Sub

ネストはIf文よりもSelect文を使ったほうが1つ深くなりますが、ソースは読みやすくなりますよね。とくに条件が3つ以上のときは積極的にSelect文を使用したほうがいいと思います。

このプログラムも最終的には3つ以上の条件を設ける予定なので、Select文で書いています。

　

'ふつうのSelect文
Select Case Not Intersect(Target, Range("I13")) Is Nothing
    Case True

'今回のSelect文
Select Case True
    case  Not Intersect(Target, Range("I13")) Is Nothing

ふつうのSelect文では、TrueかFalseかの２種類しか判断できません。でも、今回のSelect文なら、判断したいセルの数だけ、条件をふやすことができ、複雑な判断が可能になっています。

　

変数の宣言

変数の宣言は、その変数を使用するまえであれば、プログラムの先頭でも、使用する直前でも、どちらでもかまいません。PythonなどのVBA以外の言語では使用する直前に宣言するのがふつうです。そこらへんについては、VBA界隈で有名な「t-hom」さんが次のような記事を書かれています。

thom.hateblo.jp

thom.hateblo.jp

できるだけ変数を使用する直前で宣言したほうがいいということですね。

Dim myCell As Range
Dim myLf As Long
Dim my1stValue As String, my2ndValue As String

でも、Select文を使って、判断条件を3つ以上にふやす予定なので、プロシージャの先頭で変数を宣言しています。

myCellは対象となるセルを格納するRange型変数です。

郵便番号は2段書きになることも予想されるので、それに対応するための変数も用意します。

myLfは2段書きになる場合の改行文字の位置を格納します。

my1stValueとmy2ndValueは2段書きになった場合の、それぞれ、1段目の郵便番号と2段目の郵便番号の値を格納します。

　

郵便番号を入力するセルを変数「myCell」に代入

Set myCell = Range("I13")

このプログラムの場合、わざわざ郵便番号を入力するセルを変数に代入しなければならないほど複雑ではありませんが、判断条件であるCase節が3つ以上にふえることを見越して、リテラルであるRange("I13")は変数に代入して利用します。

気をつけるのは、使い終わったらなるべくはやくNothingを代入してかたづけることでしょう。

Set myCell = Nothing

これに関しては、わざわざNothingを格納する必要はないとおっしゃる方もいます。

　

郵便番号が2段書きになっているかを確認

myLf = InStr(myCell.Value, vbLf)

セル内で改行する場合、Excelの標準機能ではAlt + Enterで改行をしますが、そのときの改行文字はラインフィードvbLfです。

2段書きになっているときは必ず改行文字vbLfがつかわれていますので、その位置をInStr関数で取得します。

もし、2段書きになっているなら、vbLfが何番目の文字かを返します。もし、2段書きになってないなら、0を返します。

　

セル内の文字列の長さによって条件分岐させる

セル内には郵便番号を入力します。123-4567のような形で入力します。

ですので、セル内に1つだけ郵便番号を入力するなら、その文字列の長さは8文字です。

郵便番号を2段書きするなら、その文字列の長さは「郵便番号8文字」＋「改行文字1文字」＋「郵便番号8文字」の計17文字です。

もしセル内の文字列を削除したなら、文字列の長さは0になります。

文字列の長さがそのほかの数値になる場合は、入力ミスがあったと判断します。

Select Case Len(myCell.Value)
    Case 8
        '住所とフリガナを1段書きで表示する
    Case 17
        If myLf > 0 Then
           '住所とフリガナを2段書きで表示する
        Else
            'エラー対応をする
        End If
    Case 0
        '住所欄とフリガナ欄を空欄にする
    Case Else
        'エラー対応をする
End Select

　

文字列の長さが8文字の場合

If F_CheckUpZip(myCell.Value) = False Then Exit Sub
If F_CheckUpSheet = False Then Exit Sub

Call S_DisplayAddress(myCell.Value)

郵便番号のセルの文字列の長さが8文字の場合は、「123-4567」のようなかたちで郵便番号が入力されているはずです。

ここではそれを確認するために、郵便番号かどうかを判定する関数F_CheckUpZipにセルの文字列を引数としてわたして、正当性を確認しています。

　

文字列が郵便番号かどうかを判定するファンクションプロシージャ「F_CheckUpZip」

文字列が郵便番号かどうかを判定するファンクションプロシージャは、8文字の文字列を引数にとり、Boolean型の値を返します。

　

ソースコード（再掲）
Private Function F_CheckUpZip(ByVal myZip As String) _
    As Boolean
    
    F_CheckUpZip = True

    Dim i As Long

    For i = 1 To 8
        Dim myDigit As String: myDigit = Mid(myZip, i, 1)

        Select Case i
            Case 4
                If myDigit <> "-" Then
                    F_CheckUpZip = False
                    Call S_ClearWorksheet
                    Exit For
                    
                End If
                
            Case Else
                Dim myNumber As Long: myNumber = Asc(myDigit)

                If myNumber < 48 Or myNumber > 57 Then
                    F_CheckUpZip = False
                    Call S_ClearWorksheet
                    Exit For
                    
                End If
                
        End Select
        
    Next i
    
End Function

まず、プロシージャ名F_CheckUpZipにTrueを格納しておきます。

これは感覚的なものかもしれませんが、このファンクションプロシージャでしらべた結果、「ダメ」＝文字列が郵便番号でない、とわかったら、TrueではなくFalseを返したい。

しかし、Boolean型のデフォルト値はFalseなので、最初にTrueを入れておくことにしました。

　

For文を使って1文字目から8文字目までを調べる

郵便番号は「123-4567」という形の8文字の文字列で4文字目にハイフン-を含みます。

そこで、4文字目はハイフン-かどうかをしらべ、それ以外のときは半角の数字かどうかをしらべます。

For i = 1 To 8
    Dim myDigit As String: myDigit = Mid(myZip, i, 1)
   'なんらかの処理
Next i

郵便番号の文字列から、Mid関数でi番目の文字列をを取り出して、変数myDigitに格納します。

　

        Select Case i
            Case 4
               '4文字目がハイフンでないときの処理
            Case Else
               '4文字目以外が数字でないときの処理
        End Select

Select文をつかって4文字目とそれ以外で処理を分岐します。

　

If myDigit <> "-" Then
    F_CheckUpZip = False
    Call S_ClearWorksheet
    Exit For
End If

4文字目がハイフン-でないとき、プロシージャ名F_CheckUpZipにFalseを格納して、S_ClearWorksheetというサブプロシージャを呼び出して処理をさせ、For文を抜けます。

S_ClearWorksheetというサブプロシージャにはセルの内容をクリアする処理をさせます。何回もくりかえし使う処理なので独立したサブプロシージャにしています。

　

Dim myNumber As Long: myNumber = Asc(myDigit)
If myNumber < 48 Or myNumber > 57 Then
    F_CheckUpZip = False
    Call S_ClearWorksheet
    Exit For
End If

i番目の文字を格納した変数myDigitをAsc関数の引数としてつかって、その文字コード (Shift_JIS) を取得し、変数myNumberに格納します。

0から9の数字の文字コード (Shift_JIS) は48から57です。

i番目の文字（4文字目以外）の文字コード (Shift_JIS) が48から57以外のとき、プロシージャ名F_CheckUpZipにFalseを格納して、S_ClearWorksheetというサブプロシージャを呼び出して処理をさせ、For文を抜けます。

このように、1文字目から8文字目までをしらべて、条件に該当しないときはプログラムを終了します。

条件にあったときだけ、次の処理を続行します。

実現したいことをいっきにおこなうのではなく、すこしずつ条件をせばめて希望する処理にちかづけるのはよくある処理方法ですね。

　

郵便番号データが入ったシートが存在するかどうかをを判定するファンクションプロシージャ「F_CheckUpSheet」

郵便番号データが入ったシートが存在するかどうかをを判定するファンクションプロシージャは引数をとらず、Boolean型の返り値をかえします。

　

ソースコード（再掲）
Private Function F_CheckUpSheet() As Boolean
    F_CheckUpSheet = True
    
    On Error Resume Next
    
    Dim wsZip As Worksheet
    Set wsZip = Worksheets("zenkoku")
    
    If wsZip Is Nothing Then
        F_CheckUpSheet = False
        MsgBox """zenkoku""シートがありません。", _
            vbExclamation
        Exit Function
        
    End If
    
    On Error GoTo 0
    
    Set wsZip = Nothing
End Function

　

On Error Resume Nextステートメント
On Error Resume Next

On Error Resume Next ステートメントはエラーが発生した行を無視して、次の行の命令文を実行したいときに使います。

ここでは、次の行にエラーが発生したとき、それを無視して、次の行の命令を実行します。

Dim wsZip As Worksheet: Set wsZip = Worksheets("zenkoku")

Worksheet型の変数wsZipにzenkokuシートを格納してます。

ここで、Dim文は変数の宣言だけなのでエラーは発生しませんが、Set文はブック内にzenkokuシートがないとエラーが発生しますね。

エラーが発生すると、wsZipにはなにも格納されず、中身は初期値のNothingのままです。

If wsZip Is Nothing Then
    F_CheckUpSheet = False
    MsgBox """zenkoku""シートがありません。", vbExclamation
    Exit Function
End If

wsZipの中身がNothingのままの場合、プロシージャ名のF_CheckUpSheetにFalseを格納して、「"zenkoku"シートがありません。」というメッセージを表示して、プログラムを終了します。

    On Error GoTo 0
    Set wsZip = Nothing

この2つの文は、「おかたづけ」ですね。

On Error GoTo 0は、これを書くと、On Error Resume Nextを無効にして、エラーが発生した場合に特別な処理をせず、ふつうにエラーメッセージを表示します。

On Error GoTo 0はOn Error Resume Nextとセットだと思っておいたほうがいいですね。

エラー発生時の特別扱いをおわったら、すぐに通常処理にもどすことが大切だと思います。

　

郵便番号を検索して、該当する住所とフリガナを表示するサブプロシージャ「S_DisplayAddress」

このプロシージャがこのプログラムの本題ですね。

でも、実際につかうプログラムでは、ユーザに対する配慮をおこなう「前置き」の部分もかかせません。

このプロシージャでは、入力した郵便番号をzenkokuワークシート 上で探して、その住所とフリガナをセルに表示します。

ワークシート関数ではなく、マクロをつかって処理しているので、セルに入力されるのは数式ではなく、文字列です。

　

ソースコード（再掲）
Private Sub S_DisplayAddress(ByVal myZip As String)
    On Error GoTo HandleError

    Dim wsZip As Worksheet
    Set wsZip = Worksheets("zenkoku")

    With wsZip
        Dim myRow As Long
        myRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim myCell As Range
        Set myCell = _
            .Cells(WorksheetFunction.Match(myZip, _
            .Range(.Cells(2, 1), .Cells(myRow, 1)), 0) _
            + 1, 1)
                     
        myRow = myCell.Row
    End With

    With Worksheets("NoticeOfDishonor")
        .Range("O12").Value = wsZip.Cells(myRow, 2).Value
        .Range("P11").Value = wsZip.Cells(myRow, 3).Value
    End With
    
    Set wsZip = Nothing

    Exit Sub
    
HandleError:
    Call S_ClearWorksheet
    
End Sub

　

「On Error GoTo 行ラベル」
Private Sub S_DisplayAddress(ByVal myZip As String)
    On Error GoTo HandleError

    'エラーが発生する可能性がある処理
    
    Exit Sub
    
HandleError:
    Call S_ClearWorksheet
    
End Sub

HandleErrorとは「行ラベル」のことで、エラーが発生したときの移動先です。

On Error Resume Nextはエラーが発生すると、その行を無視して、次の行を実行しますが、On Error GoTo HandleErrorはエラーが発生すると、その行を無視して、HandleErrorに記述している処理を実行します。

On Error GoTo HandleErrorを使ううえで気をつける点は、かならずExit Subを記述することですね。

これがないと、毎回HandleError行ラベルの内容を実行してしまいます。それを避けるために、Exit Subでプロシージャを抜けます。

On Error GoTo 行ラベルは、ただしく使うことがむずかしい文です。多用すると、プログラムが読みにくくなるので、使いすぎないことですね。

ここでは、Worksheet型変数にワークシートを格納したり、Range型変数にセルを格納したり、エラーが発生する可能性が2つ以上あるため、On Error GoTo 行ラベルをつかってます。

　

ワークシート関数「MATCH」をマクロで使って郵便番号を検索する
        Dim myRow As Long
        myRow = .Cells(.Rows.Count, 1).End(xlUp).Row

zenkokuシートには約15万件のデータがありますが、途中に空白のセルはありません。

Rows.Countプロパティでシートの最終行の番号を取得します。

.Cells(.Rows.Count, 1)は、A列の最終行のセルを表します。わたしの環境では、Range("A1048576")を指します。これは、Excelのバージョンによって変わるので注意が必要です。

End(xlUp)プロパティは、今いるセルから上方向に移動します。ちょうどCtrlキーと上方向キーを同時におしたときに移動する場所へと移動します。

.Cells(.Rows.Count, 1).End(xlUp)と書くと、zenkokuシートでRange("A1048576")からRange("A149213")へと移動します。

Rowプロパティは、そのセルの行番号を取得します。.Cells(.Rows.Count, 1).End(xlUp).Row全体では、A列の郵便番号がはいったセルの最終行の行番号149213を返します。

.Cells(.Rows.Count, 1).End(xlUp)はとてもよく使われるので、このまま覚えるといいと思います。

　

Dim myCell As Range
Set myCell = .Cells(WorksheetFunction.Match(myZip, _
             .Range(.Cells(2, 1), .Cells(myRow, 1)), 0) _
             + 1, 1)

ここでワークシート関数MATCHを使って、郵便番号を検索しています。Findを使わないのは遅いからです。

excel-ubara.com

この記事にも書いてあるとおり、たしかに遅いです。

ためしにzenkokuシート上でCtrl + Fで検索をおこなってみると、一瞬のタイムラグがあります。

実務上では、このタイムラグがきらわれるので、より高速なを使うことにしました。

WorksheetFunction.Match(検査値,範囲,方法)

検査値は、変数myZipに格納された郵便番号です。

範囲は.Range(.Cells(2, 1), .Cells(myRow, 1))であり、Range("A2")からRange("A149213")のことです。

方法は次のいずれかを指定するのですが、ここでは0を指定して、完全に一致する値をさがします。

数値 内容
-1 「検査値」以上の最小値
0 「検査値」に完全一致する値
1 「検査値」以下の最大値

ワークシート関数MATCHの返り値に関してひとつ大切なことは、あたえられたセル範囲のなかで上から何番目かを返す、ということです。

このプログラムの場合、ほしいのは範囲のなかで何番目かではなく、セルの行番号です。

zenkokuシートは1行目が見出しになっていて、範囲がRange("A2")からRange("A149213")ですから、ワークシート関数MATCHの返り値に1をたすと、セルのただしい行番号を得ることができます。

myRow = myCell.Row

最後に変数myRowを再利用して、得られた答えのセルの行番号を格納します。

　

住所とフリガナをセルに入力
With Worksheets("NoticeOfDishonor")
    .Range("O12").Value = wsZip.Cells(myRow, 2).Value
    .Range("P11").Value = wsZip.Cells(myRow, 3).Value
End With

変数myRowにもとめたい郵便番号のセルの行番号が格納されたので、あとは、住所のセル「O12」とフリガナのセル「P11」に代入するだけです。

代入するのは、=演算子をつかって代入するのがいいでしょう。 VBAでコピペするより代入した方がスピードがあると思います。

　

文字列の長さが17文字の場合

Case 17
    If myLf > 0 Then
       '1段目と2段目の郵便番号の住所とフリガナを格納
    Else
       'エラー処理
    End If

変数myLfの値が0より大きいということは改行文字が郵便番号のセルにふくまれるということです。改行文字がある場合は、1段目の郵便番号と2段目の郵便番号を順番にさがして、住所のセルとフリガナのセルにそれぞれ2段書きで格納します。

　

1段目と2段目の郵便番号の住所とフリガナを格納
my1stValue = Left(myCell.Value, myLf - 1)
my2ndValue = Mid(myCell.Value, myLf + 1)

If F_CheckUpZip(my1stValue) = False Then Exit Sub
If F_CheckUpZip(my2ndValue) = False Then Exit Sub
If F_CheckUpSheet = False Then Exit Sub

Call S_DisplayAddress2(my1stValue, my2ndValue)

my1stValueは1段目の郵便番号、改行文字よりも前の7文字、my2ndValueは2段目の郵便番号、改行文字よりも後の7文字です。

どちらの文字列もF_CheckUpZipで調べて、郵便番号の形（123-4567）になっていなかったら、プログラムを終了します。

そして、F_CheckUpSheetで調べて、zenkokuシートがなかったときもプログラムを終了します。

　

2つの郵便番号を検索して、該当する住所とフリガナを表示するサブプロシージャ「S_DisplayAddress2」

引数が2つにふえただけで、基本的な処理は、郵便番号が1つの場合のS_DisplayAddressサブプロシージャとかわりません。

　

ソースコード（再掲）
Private Sub S_DisplayAddress2( _
    ByVal my1stValue As String, ByVal my2ndValue As String)
    
    On Error GoTo HandleError

    Dim wsZip As Worksheet
    Set wsZip = Worksheets("zenkoku")

    With wsZip
        Dim myRow As Long
        myRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim myRange1 As Range, myRange2 As Range
        Set myRange1 = _
            .Cells(WorksheetFunction.Match(my1stValue, _
            .Range(.Cells(2, 1), .Cells(myRow, 1)), 0) _
            + 1, 1)
        Set myRange2 = _
            .Cells(WorksheetFunction.Match(my2ndValue, _
            .Range(.Cells(2, 1), .Cells(myRow, 1)), 0) _
            + 1, 1)
        
        Dim myRow1 As Long, myRow2 As Long
        myRow1 = myRange1.Row
        myRow2 = myRange2.Row
        
    End With

    Set myRange1 = Nothing: Set myRange2 = Nothing
    
    With Worksheets("NoticeOfDishonor")
        .Range("O12").Value = _
            wsZip.Cells(myRow1, 2).Value & vbLf & _
            wsZip.Cells(myRow2, 2).Value
        .Range("P11").Value = _
            wsZip.Cells(myRow1, 3).Value & vbLf & _
            wsZip.Cells(myRow2, 3).Value
                              
    End With

    Set wsZip = Nothing

    Exit Sub
    
HandleError:
    Call S_ClearWorksheet
    
End Sub

サブプロシージャ「S_DisplayAddress」と本質的におなじことをしているので、説明は割愛します。

　

文字列の長さが0文字の場合

文字列の長さが0文字ということは、何も入っていないということです。DELETEキーでセルの内容を削除した場合などがそれにあたります。

Case 0
    Range("O12, P11").Value = ""
    myCell.Activate

郵便番号のセルだけでなく、住所とフリガナのセルも内容を削除します。

　

文字列の長さがその他の場合

文字列の長さが8文字（郵便番号1つ）、17文字（郵便番号2つ）、0文字（セルに何も文字列がはいっていない）の場合以外は、メッセージを表示して、プログラムを終了します。ここにメッセージを直接書いてもかまいませんが、プログラムの見通しをよくするために独立したプロシージャを書いています。

Case Else
    Call S_ClearWorksheet

　

セルの内容をクリアするサブプロシージャ「S_ClearWorksheet」

エラーメッセージを表示して、郵便番号のセル・住所のセル・フリガナのセルの文字列をクリアするサブプロシージャです。

Private Sub S_ClearWorksheet()
    MsgBox "郵便番号が正しくありません。" & vbCrLf & _
           "正しい形式「123-4567」（ハイフンあり）で" & vbCrLf & _
           "入力してください。", vbExclamation
           
    Range("I13, O12, P11").Value = ""
    
    Range("I13").Activate
End Sub

　

ThisWorkbookモジュールの「Workbook_Open」プロシージャ

このブックをひらいたときにNoticeOfDishonorシートを選択して、セル「AG3」を選択するマクロです。

こうしておけば、入力担当者がいちいち郵便番号のセルを探す手間がはぶけます。

Private Sub Workbook_Open()
    Worksheets("NoticeOfDishonor").Activate
    Range("AG3").Activate
End Sub

　

標準モジュールのデータ更新マクロ

「住所.jp」さんからダウンロードしたCSVファイルはそのままではわたしの希望のかたちにはなっていません。そこでダウンロードしたCSVファイルに対してプログラムを実行して、セルの内容を希望のかたちに変更します。

ソースコード

Option Explicit

'http://jusyo.jp/downloads/new/csv/csv_zenkoku.zip

Sub S_CreateZIPToAddressTable_Main()
    Switch = True
    
    Dim myWB As Workbook
    
    Select Case F_CheckUpFile
        Case 0
            Exit Sub
        Case 1
            Set myWB = Workbooks("zenkoku.csv")
        Case 2
            Set myWB = _
                Workbooks.Open(ThisWorkbook.Path & _
                    "\zenkoku.csv")
    End Select
    
    If F_CheckUpSheet(myWB) = False Then Exit Sub
    
    Call S_CreateZIPToAddressTable_Core(myWB)
    
    Set myWB = Nothing
    
    Switch = False
    
    MsgBox "データ更新終了！", vbInformation
End Sub

Private Property Let Switch(ByVal Flag As Boolean)
  With Application
    .ScreenUpdating = Not Flag
    .EnableEvents = Not Flag
    .DisplayAlerts = Not Flag
    .Calculation = _
        IIf(Flag, xlCalculationManual, xlCalculationAutomatic)
    .PrintCommunication = Not Flag
  End With
End Property

Private Function F_CheckUpFile() As Long
    If Dir(ThisWorkbook.Path & "\zenkoku.csv") = "" Then
        MsgBox "郵便番号・住所データのCSVファイルが" & _
               "同じフォルダに存在しません。", vbExclamation
        F_CheckUpFile = 0
        Exit Function
    End If
    
    Dim myFile As Workbook
    
    For Each myFile In Workbooks
        If myFile.Name = "zenkoku.csv" Then
            F_CheckUpFile = 1
            Exit For
        Else
            F_CheckUpFile = 2
        End If
    Next myFile
End Function

Private Function F_CheckUpSheet(ByVal myWB As Workbook) _
    As Boolean
    
    With myWB
        Dim myWS As Worksheet
        
        For Each myWS In .Worksheets
            If myWS.Name = "zenkoku" Then
                F_CheckUpSheet = True
                Exit For
            End If
        Next myWS
    End With
    
    If F_CheckUpSheet = False Then
        MsgBox "シート名が""zenkoku""になっていません" & _
            vbCrLf & _
           "シート名を""zenkoku""にしてください。", _
           vbExclamation
    End If
End Function

Private Sub S_CreateZIPToAddressTable_Core _
    (ByVal myWB As Workbook)
    
    With myWB.Worksheets("zenkoku")
        .Columns(22).Delete
        .Columns(18).Delete
        .Range(.Columns(14), .Columns(15)).Delete
        .Range(.Columns(1), .Columns(4)).Delete
        
        Dim myRow As Long
        myRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Cells(2, 15).Formula = _
            "=IF(B2=0,D2&F2&H2&J2, _
              IF(B2=1,D2&F2&N2&"" ""&L2,""""))"
        .Cells(2, 16).Formula = _
            "=IF(B2=0,E2&G2&I2&K2, _
              IF(B2=1,E2&G2&"" ""&M2,""""))"
        
        .Range(.Cells(2, 15), .Cells(2, 16)).Copy _
            Destination:=.Range(.Cells(3, 15), _
                         .Cells(myRow, 16))
            
        With Application
            .Calculation = xlCalculationAutomatic
            .Calculation = xlCalculationManual
        End With
        
        With .Range(.Cells(2, 15), .Cells(myRow, 16))
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
        
        .Range(.Columns(2), .Columns(14)).Delete
        .Range(.Columns(2), .Columns(3)).ColumnWidth = 70
        .Cells(1, 2).Value = "住所"
        .Cells(1, 3).Value = "住所カナ"
    End With
    
    Call S_ReplaceZeroToNothing(myWB)
    
    Call S_FreezePanes(myWB)
    
    Call S_CopyTable(myWB)
End Sub

Private Sub S_ReplaceZeroToNothing(ByVal myWB As Workbook)
    With myWB.Worksheets("zenkoku")
        Dim myRow As Long
        myRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        With .Range(.Cells(2, 3), .Cells(myRow, 3))
            .Replace What:="０１", _
                     Replacement:="１", LookAt:=xlPart
            .Replace What:="０２", _
                     Replacement:="２", LookAt:=xlPart
            .Replace What:="０３", _
                Replacement:="３", LookAt:=xlPart
            .Replace What:="０４", _
                Replacement:="４", LookAt:=xlPart
            .Replace What:="０５", _
                Replacement:="５", LookAt:=xlPart
            .Replace What:="０６", _
                Replacement:="６", LookAt:=xlPart
            .Replace What:="０７", _
                Replacement:="７", LookAt:=xlPart
            .Replace What:="０８", _
                Replacement:="８", LookAt:=xlPart
            .Replace What:="０９", _
                Replacement:="９", LookAt:=xlPart
        End With
    End With
End Sub

Private Sub S_FreezePanes(ByVal myWB As Workbook)
    With myWB
        .Activate
        
        With .Worksheets("zenkoku")
            .Activate
            .Cells(2, 2).Activate
            ActiveWindow.FreezePanes = True
        End With
    End With
End Sub

Private Sub S_CopyTable(ByVal myWB As Workbook)
    With ThisWorkbook
        Dim myWS As Worksheet
        
        For Each myWS In .Worksheets
            If myWS.Name = "zenkoku" Then
                myWS.Delete
                Exit For
            End If
        Next myWS
    End With
    
    With myWB
        .Worksheets("zenkoku").Copy _
            Before:=ThisWorkbook.Worksheets("UpdateTable")
        .Close
    End With
    
    ThisWorkbook.Save
End Sub

ソースコードの解説

　

データ更新マクロの流れを確認

  • プロパティ・プロシージャSwitchでマクロの高速化・自動化の設定をして
  • ファンクション・プロシージャF_CheckUpFileでCSVファイルが存在しているか、開いているかいないかをチェックして
  • ファンクション・プロシージャF_CheckUpSheetでzenkokuシートの有無を確認して
  • サブプロシージャS_CreateZIPToAddressTable_Coreでセルの内容を変更します。

　

プロパティ・プロシージャ「Switch」

Property Letプロシージャは、プロパティの値を設定するプロシージャです。ここでは、プロシージャの高速化・自動化に関するプロパティをまとめて設定するためにつかってます。

高速化・自動化の「切り替え」をするという意味でSwitchという名前にしています。

ほかの言語では、VBAのSelect文と同じ意味でSwitch文がありますが、それとは関係ありません。

Private Property Let Switch(ByVal Flag As Boolean)
  With Application
    .ScreenUpdating = Not Flag
    .EnableEvents = Not Flag
    .DisplayAlerts = Not Flag
    .Calculation = _
        IIf(Flag, xlCalculationManual, _
            xlCalculationAutomatic)
    .PrintCommunication = Not Flag
  End With
End Property

画面再描画・イベント発生・警告自動表示・ブック自動計算・プリンタ通信を最適化します。

t-homさんのサイトにくわしい説明があります。

thom.hateblo.jp

　

CSVファイルの状態を確認する「F_CheckUpFile」ファンクション・プロシージャ

まず、Dir関数でCSVファイルが存在するかどうかを確認して、存在しなければ、プロシージャ名の変数F_CheckUpFileに0を格納します。

If Dir(ThisWorkbook.Path & "\zenkoku.csv") = "" Then
    MsgBox "郵便番号・住所データのCSVファイルが" & _
           "同じフォルダに存在しません。", vbExclamation
    F_CheckUpFile = 0
    Exit Function
End If

　

ファイルが存在するとわかったら、開いているすべてのファイルを確認して、CSVファイルの名前があったら、プロシージャ名の変数F_CheckUpFileに1を格納し、なかったら、2を格納します。

Dim myFile As Workbook

For Each myFile In Workbooks
    If myFile.Name = "zenkoku.csv" Then
        F_CheckUpFile = 1
        Exit For
    Else
        F_CheckUpFile = 2
    End If
Next myFile

ここは改善の余地があるかもしれません。For Each文で全部のブックをしらべるのではなく、Set文でmyFileにCSVを格納してみて、エラーが発生するかどうかをしらべたり、プロシージャのデータ型をLongではなく、String型にして、返り値をNoExistence、Opened、NoOpenedなどの自分にとってわかりやすい文字列を返り値に設定するのもいいかもしれません。

　

CSVファイルのシート名を確認する「F_CheckUpSheet」

シート名をすべてしらべて、zenkokuになっていれば、処理を続行。

With myWB
    Dim myWS As Worksheet
    
    For Each myWS In .Worksheets
        If myWS.Name = "zenkoku" Then
            F_CheckUpSheet = True
            Exit For
        End If
    Next myWS
End With

　

zenkokuになっていなければ、メッセージをだして、シート名を変更するよう促して、プログラムを終了します。

If F_CheckUpSheet = False Then
    MsgBox "シート名が""zenkoku""になっていません" & _
        vbCrLf & _
       "シート名を""zenkoku""にしてください。", _
       vbExclamation
End If

　

実際にセル内のデータを操作する「S_CreateZIPToAddressTable_Core」サブプロシージャ

CSVファイルのデータは、いろいろな人のニーズを考えて、分割して掲載されているので、「郵便番号」「住所（漢字）」「住所（カナ）」の3つに集約します。

必要としていない列を削除

分割してあるデータの中で、結合したいデータではない列を削除します。

このとき、いちばん右の列から削除すると、自分で混乱することがありません。左の列から削除すると、その都度、列番号が変わるので、やめたほうがいいですね。

With myWB.Worksheets("zenkoku")
    .Columns(22).Delete
    .Columns(18).Delete
    .Range(.Columns(14), .Columns(15)).Delete
    .Range(.Columns(1), .Columns(4)).Delete
End With

　

住所（漢字）と住所（カナ）のデータを結合

データの中に事業所フラグ（0と1）があって、事業所の住所データと事業所以外の住所データではデータの構造がちがうので、結合のしかたもちがいます。

ですので、IFで分岐させることにして、漢字のデータとカナのデータのそれぞれのための結合用関数を用意します。

そして、一番上のデータのセルにワークシート関数を埋め込みます。

With myWB.Worksheets("zenkoku")
    .Cells(2, 15).Formula = _
    "=IF(B2=0,D2&F2&H2&J2,IF(B2=1,D2&F2&N2&"" ""&L2,""""))"
    .Cells(2, 16).Formula = _
    "=IF(B2=0,E2&G2&I2&K2,IF(B2=1,E2&G2&"" ""&M2,""""))"
End With

ワークシート関数を埋め込むのは、VBAでFor文を回して処理すると非常に遅くて、いったんワークシート関数を埋め込んで、その数式をコピーしてExcelに計算させたほうが圧倒的に速いからです。

　

挿入した数式をコピー

Copyメソッドで数式をコピーするのですが、注意点がひとつ。

Switchプロパティ・プロシージャのなかで自動計算をオフにしているので、数式をコピーしたあと、いったん自動計算をオンにします。

すると、自動計算がはじまり、それぞれの行の住所のデータを結合してくれます。

自動計算をオンにしたらすぐに計算（結合）してくれるので、次の命令文で自動計算をすぐにオフにしてかまいません。

With myWB.Worksheets("zenkoku")
    .Range(.Cells(2, 15), .Cells(2, 16)).Copy _
        Destination:=.Range( _
        .Cells(3, 15), .Cells(myRow, 16))
        
    With Application
        .Calculation = xlCalculationAutomatic
        .Calculation = xlCalculationManual
    End With
End With

　

数式を文字列に変換

これもExcelの機能で処理するのがいいでしょう。

というか、ほかに処理方法はあるのでしょうか。

With myWB.Worksheets("zenkoku")
    With .Range(.Cells(2, 15), .Cells(myRow, 16))
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
End With

　

余分なデータを削除

挿入した数式を文字列に変換したことで、数式の元データとなっていた列を削除することが可能になりました。

With myWB.Worksheets("zenkoku")
    .Range(.Columns(2), .Columns(14)).Delete
End With

　

さらなるデータの整形

住所（漢字）データのセルと住所（カナ）データのセルの横幅をひろげて見やすくします。

S_ReplaceZeroToNothingサブプロシージャで、「０１」〜「０９」という丁目の表記を「１」〜「９」に変更します。実際の書類上では「０１丁目」という表記は変なので「１丁目」にします。

S_FreezePanesサブプロシージャで、ウィンドウ枠を固定して、常に見出しが見えるようにしておきます。

    With myWB.Worksheets("zenkoku")
        .Range(.Columns(2), .Columns(3)).ColumnWidth = 70
        .Cells(1, 2).Value = "住所"
        .Cells(1, 3).Value = "住所カナ"
    End With
    
    Call S_ReplaceZeroToNothing(myWB)
    
    Call S_FreezePanes(myWB)

　

CSVファイルのシートをコピー

S_CopyTableサブルーチンを呼んで、CSVファイルの整形済みのzenkokuシートをブックにコピーします。

Call S_CopyTable(myWB)

　

「０１」を「１」に置換する「S_ReplaceZeroToNothing」サブプロシージャ

Excelの標準機能の「置換」機能をVBAから利用して「０１丁目」を「１丁目」にします。

これも、標準機能をつかわずに、For文で回したりすると、おそろしく時間がかかります。

        With .Range(.Cells(2, 3), .Cells(myRow, 3))
            .Replace What:="０１", _
                Replacement:="１", LookAt:=xlPart
            .Replace What:="０２", _
                Replacement:="２", LookAt:=xlPart
            .Replace What:="０３", _
                Replacement:="３", LookAt:=xlPart
            .Replace What:="０４", _
                Replacement:="４", LookAt:=xlPart
            .Replace What:="０５", _
                Replacement:="５", LookAt:=xlPart
            .Replace What:="０６", _
                Replacement:="６", LookAt:=xlPart
            .Replace What:="０７", _
                Replacement:="７", LookAt:=xlPart
            .Replace What:="０８", _
                Replacement:="８", LookAt:=xlPart
            .Replace What:="０９", _
                Replacement:="９", LookAt:=xlPart
        End With

　

ウィンドウ枠を固定する「S_FreezePanes」サブプロシージャ

FreezePanesプロパティは、Windowオブジェクトに対しておこなうものですが、ActiveWindowに対しておこなうのが安全です。

そのため、まず、ブックをアクティブにして、シートをアクティブにして、セルを選択してから、FreezePanesプロパティにTrueを設定します。

With myWB
    .Activate
    
    With .Worksheets("zenkoku")
        .Activate
        .Cells(2, 2).Activate
        ActiveWindow.FreezePanes = True
    End With
End With

　

CSVファイルのシートをコピーする「S_CopyTable」サブプロシージャ

まず、ブックの中にzenkokuシートがあったら、それは古いデータなので、削除します。

With ThisWorkbook
    Dim myWS As Worksheet
    
    For Each myWS In .Worksheets
        If myWS.Name = "zenkoku" Then
            myWS.Delete
            Exit For
        End If
    Next myWS
End With

　

準備がととのったら、zenkokuシートをCSVファイルからブックにコピーして、CSVファイルをとじます。

最後にブックを保存して終了です。

With myWB
    .Worksheets("zenkoku").Copy _
        Before:=ThisWorkbook.Worksheets("UpdateTable")
    .Close
End With

ThisWorkbook.Save

　

おわりに

最後まで読んでいただき、ありがとうございました。

長いかなあとも思いますが、せっかく書いたので、このままアップします。

　

ノンプログラマにとってのヒューリスティクスの重要性

プログラミングの学び方

f:id:m-eye:20181006102226p:plain

　

ヒューリスティクスってなに

ヒューリスティクスっていうのは、ひとことでいえば「経験則」、あるいは「臨機応変」のことです。「その場しのぎ」という意味でつかわれることもあります。

わたしのだいすきなマンガに「ドカベン」がありますが、ヒューリスティクスによれば、山田太郎がキャッチャーになるのは当然です。

山田太郎のように太っていればキャッチャーをやらせて、中西球道のようにスリムならピッチャーをやらせるのがヒューリスティクスです。

ふとっているほうがしっかりボールを受けとめてくれそうだと思いますよね。それに山田太郎にはピッチャーはむりだろうと思いますよね。

　

また、「激変する環境変化に柔軟に対応し、生き残る能力」のことを指すこともあります。

山でけものに襲われたら逃げるのがふつうですよね。それが経験則です。わたしたちは生き残るための知恵としてヒューリスティクスを身につけています。

　

ヒューリスティクスには、判断がただしいかどうか保証はありませんが、すぐに判断できるというメリットがあります。

飛行機は落ちたら死ぬから自動車で移動しよう、というのもヒューリスティクスです。本当はきちんとデータを検証し、過去の事例から死亡率を算出しないと正解はわかりません。

でも、すぐに飛行機の予約をとるかどうか決めるしかないときは、ヒューリスティクスにたよるしかありません。

　

また、月に10日勤務する予定のアルバイトの方から来月の勤務予定表をもらったときに9日分しか勤務予定がはいっていなかったら、「ああ、1日分予定を書きわすれたんだな」と判断します。これもヒューリスティクスです。

　

専門的な用語をつかうと

専門的にいえば、かならず正しい答えを導けるかどうかわからないけれど、ある程度のレベルで正解に近い解をもとめることができる方法で、答え（正解とは限らない）にたどりつく時間が短いという特徴があります。

ヒューリスティクスはコンピュータの世界と心理学の世界の両方でつかわれます。

どちらも同じ意味でつかわれていて、どちらもノンプログラマにとっては重要です。

　

コンピュータの世界では

プログラミングの方法（戦略）を指します。

プログラミングの戦略には、3つあるとおもいます。

　

1つめは、アルゴリズム（手順の言語化）にもとづいて、正解を導きだす「超最適化戦略」。

これが理想です。というかふつうです。プログラミングをするひとは、まずこれをめざします。

　

2つめは、近似アルゴリズムにもとづいて、答えをあるていどの誤差の範囲内におさめる「近似解戦略」。

プログラムの種類によっては、正解ではなくても近い答えでじゅうぶんなことがあります。ふつうは超最適化戦略か近似解戦略をとります。

　

3つめは、ヒューリスティクスにたよった「その場しのぎ戦略」です。

とにかくはやくプログラミングをおえなければならない状況で有効です。ノンプログラマは（専門家でも？）納期が10分とか15分とかいうときはあります。「とりあえずforループでまわしとけ」とか、そういうことです。

　

とくに重要なのは

　

「その場しのぎ戦略」の重要性と有効性です。ノンプログラマには「本業」があります。それを助けるためにプログラミングをおこないます。逆はありません。

　

本業をおこたってプログラミングに没頭するようでは、いくら難解な問題をエレガントな解法で解決したとしても、評価はされません。

上司は30分後に答えがほしいのに、完璧な答えにこだわって3日後に答えをだしてもまったく意味がありません。

本業をがんばって、上司の期待にこたえるための1つの方法として、ノンプログラマはプログラミングをするのです。

　

一般に、「その場しのぎ戦略」は「近似アルゴリズム戦略」よりも正解に近いことが多いのです。それを知らないと、「近似アルゴリズム戦略」で（その場ではむだとわからないが）むだな努力をしたり、よけいな時間をくったり、しなくてもいい遠回りをしてしまうことになります。

　

ヒューリスティクスの重要性は

　

ここですべてを伝えきるのは不可能ですが、むだなことをしないためヒューリスティクスを知っておいたほうがいいのはたしかです。

「経験則」にしたがって「臨機応変」にその場その場をしのぎましょう。

　

Copyright 2018 m-eye blog All Rights Reserved.