ユーザー関数を作ろう(Fanctionプロシージャ)

ユーザー関数を作ろう(Fanctionプロシージャ)-電柱NoでGoogleMap表示-

サンプルソフト(電柱NoでGoogleMap表示)

前へ　　　　メニューに戻る　　　　　次へ

シートやVBA上で使えるユーザー関数、Fanctionプロシージャを使うとかなり高度な計算ができます。

M1.png (56135 バイト)

開発メニューからVisualBasicを選びます。

M3.png (36846 バイト)

各シートから利用できるようにするため「標準モジュール」を追加します

M4.png (36313 バイト)

新しく作られた「Module1」を選択し、メニュー「挿入」「プロシージャ」をクリック

M7.png (17968 バイト)

名前に作りたい関数名をいれ「Functionプロシージャ」を選択し、「OK」をクリック

ここで、少し説明

「Functionプロシージャ」は、戻り値を持ったプロシージャで関数のように扱えます。

「Subプロシージャ」は戻り値を持たず処理のみを行ないます。

「Public」は広い範囲で利用できるのに対して、「Private」は限定された範囲のみで利用可能

M8.png (5964 バイト)

プロシージャの最初と最後が自動的にできたので、この中に計算式を入れていきます。

M9.png (6352 バイト)

入力値と戻り値の形式を指定します。

この例では電柱Noから経度を求める計算をさせます。

DNoは電柱Noで12桁あるので「Double」(倍精度変数)を指定します。
入力する変数が複数ある場合は「,」で区切ります。

戻り値は「Single」(単精度変数)を指定します。(Doubleでもかまいません)

(変数指定は「as」のあとスペースを入れると一覧が出てきます)

M6.png (15112 バイト)

計算式を書き加えていきます。

「'」以降はコメントで、処理はされません。(緑に文字が変わります)
SNoは電柱Noを文字列として扱うための変数
「&」(アンパサンド)は文字列をつなげるための記号
「Mid」関数は文字列の何番目から何文字取り出すかの関数
「Mod」は割り算の余りを求める数式
電柱Noの2桁目は9だと139、その他は0,1,2,3は140,141,142となっています。
そのため、2桁目に2を足し、10で割ったあまりを138に足すと、経度の整数分が求められます。
「Val」は文字を数値に変換する関数

「Functionプロシージャ」では、最後にプロシージャ名(Dx)に戻り値を与えます。(途中で変数として使うことも可能)

'電柱No⇒座標変換経度(日本測地系)
Public Function Dx(DNo As Double) As Single '関数の戻り値を単精度、入力値を倍精度に定義
'経度計算関数
　Dim SNo As String '「SNo」を文字列として宣言
　SNo = "" & DNo '空文字「""」をたすことにより文字列に変換(数字の「+」は文字列では「&」になる
　Dx = (Val(Mid(SNo, 2, 1)) + 2) Mod 10 + 138 '文字列SNoの２番目から１文字を取り出しMid(SNo, 2, 1)数値に変換VAL()、２を足し10で割った余りを求めMod 10、138を足す
　Dx = Dx + (Val(Mid(SNo, 4, 1)) * 10000 + Val(Mid(SNo, 6, 1)) * 1000 + Val(Mid(SNo, 8, 1)) * 100 + Val(Mid(SNo, 11, 2))) / 80000
End Function

'電柱No⇒座標変換緯度(日本測地系)
Public Function Dy(DNo As Double) As Single
　Dim SNo As String
　SNo = "" & DNo
　Dy = Val(Mid(SNo, 1, 1)) * 2 / 3 + 40
　Dy = Dy + (Val(Mid(SNo, 3, 1)) * 10000 + Val(Mid(SNo, 5, 1)) * 1000 + Val(Mid(SNo, 7, 1)) * 100 + Val(Mid(SNo, 9, 2))) / 120000
End Function

'電柱No⇒座標変換(日本測地系)
Public Function Dxy(SW As Integer, DNo As Double) As Single
'ちょっと高度な扱い。SWに「0」か「1」を入れることにより経度と緯度の計算に切り替わる。
　Dim SNo As String
　SNo = "" & DNo
　Select Case SW
　Case 0
　　Dxy = (Val(Mid(SNo, 2, 1)) + 2) Mod 10 + 138
　　Dxy = Dxy + (Val(Mid(SNo, 4, 1)) * 10000 + Val(Mid(SNo, 6, 1)) * 1000 + Val(Mid(SNo, 8, 1)) * 100 + Val(Mid(SNo, 11, 2))) / 80000
　Case 1
　　Dxy = Val(Mid(SNo, 1, 1)) * 2 / 3 + 40
　　Dxy = Dxy + (Val(Mid(SNo, 3, 1)) * 10000 + Val(Mid(SNo, 5, 1)) * 1000 + Val(Mid(SNo, 7, 1)) * 100 + Val(Mid(SNo, 9, 2))) / 120000
　End Select
End Function

'座標(日本測地系)⇒電柱No変換
Public Function DNo(X As Single, Y As Single) As Double '入力x,yが単精度、戻り値が倍精度宣言
'電柱Noへ逆変換
　Dim Xn(4) As Integer, Yn(4) As Integer '整数型の配列を宣言
　Xn(0) = Int(X) Mod 10 'Xの端数を切り捨てInt(X)、10で割った余りを求めるMod 10
　X = (X - Int(X)) * 80000 'XからXの整数部を引き小数部のみを求める(X-Int(X))、これを80000倍して新たなXとする
　'区は約80kmで画を1/8にしたもののため
　Xn(1) = X \ 10000 'Xを10000で割った整数を求める。a\bはInt(a/b)と同じ
　Xn(2) = (X \ 1000) Mod 10 '同じく1000で割った整数を10で割った余りを求める。(第一位の取り出し)
　Xn(3) = (X \ 100) Mod 10 '同じく100で割った整数を10で割った余りを求める。(第一位の取り出し)
　Xn(4) = X Mod 100 '100で割った余りを求める
　Y = (Y - 40) * 3 / 2 '緯度の1ﾟの2/3を画としているため。
　Yn(0) = Int(Y) Mod 10
　Y = (Y - Int(Y)) * 80000
　Yn(1) = Y \ 10000
　Yn(2) = (Y \ 1000) Mod 10
　Yn(3) = (Y \ 100) Mod 10
　Yn(4) = Y Mod 100
　DNo = Val(Yn(0) & Xn(0) & Yn(1) & Xn(1) & Yn(2) & Xn(2) & Yn(3) & Xn(3) & Format(Yn(4), "00") & Format(Xn(4), "00"))
　'電柱Noとなる緯度と経度の数字を順に文字としてつなげ、最後に数値に変換する。最後の2桁の数字は「03」が「3」とならないようにFormat関数で「00」のように表すことを宣言
End Function

'日本測地系⇒世界測地系　変換
Public Function WConv_Y(Y As Double, X As Double) As Double
　WConv_Y = Y - 0.00010695 * Y + 0.000017464 * X + 0.0046017
End Function

'日本測地系⇒世界測地系　変換
Public Function WConv_X(Y As Double, X As Double) As Double
　WConv_X = X - 0.000046038 * Y - 0.000083043 * X + 0.01004
End Function

'世界測地系座標を与えるとGoogle MapのURLを作成します。
Public Function GMap(E As Double, N As Double) As String
　GMap = "https://www.google.co.jp/maps/search/" & N & "," & E & "/data=!4m2!2m1!4b1"
End Function

'緯度経度から直接GogleMapURL
Public Function EN_GMap(E As Double, N As Double) As String
EN_GMap = "https://www.google.co.jp/maps/search/" & N & "," & E & "/data=!4m2!2m1!4b1"
End Function

'緯度経度から直接ストリートビューURL
Public Function EN_Veu(E As Double, N As Double) As String
EN_Veu = "http://maps.google.com/maps?q=&layer=c&cbll=" & N & "," & E
End Function

'電柱Noを与えるとGoogle MapのURLを作成します。
Public Function D_GMap(DNo As Double) As String
　Dim JX As Double, JY As Double, GX As Double, GY As Double
　JX = Dx(DNo): JY = Dy(DNo) '日本測地系座標計算
　GX = WConv_X(JY, JX): GY = WConv_Y(JY, JX) '世界測地系座標計算
　D_GMap = "https://www.google.co.jp/maps/search/" & GY & "," & GX & "/data=!4m2!2m1!4b1"
End Function

'電柱Noから直接ストリートビューURLを作成します。
Public Function D_Veu(DNo As Double) As String
Dim JX As Double, JY As Double, GX As Double, GY As Double
JX = Dx(DNo): JY = Dy(DNo)
GX = WConv_X(JY, JX): GY = WConv_Y(JY, JX)
D_Veu = "http://maps.google.com/maps?q=&layer=c&cbll=" & GY & "," & GX
End Function

サンプルソフトに使われている関数(「Functionプロシージャ」)です。

これらの関数を使うことによって、電柱NoからGoogleMapを表示させることができます。

M2.png (7675 バイト)

M5.png (8887 バイト)

4行目が入力された計算式、3行目が結果を表しています。

「Functionプロシージャ」は関数と同じように扱うことができます。

また、「Functionプロシージャ」内で「Functionプロシージャ」を使うこともできます。

「=HYPERLINK()」はハイパーリンクへ変換する関数です。これで、ここをクリックするとGoogleMapを表示します。

電柱Noに「-」が入っていたり全角文字が入っていた場合の変換

これまでは電柱Noが数値の時は変換できましたが文字列の場合は変換できませんてした

数値でも文字でもなんでも入る変数型として『As Variant』として入力を宣言します。

『StrConv(D_No, 8)』で全角文字を半角文字に変換します。この関数の詳しい説明はこちら

『Replace(D_No, "-", "")』で「-」を削除。この関数の詳しい説明はこちら

『Val(D_No)』で文字を数値に変換。この関数の詳しい説明はこちら

'電柱Noから直接GogleMapURL 電柱Noが数値でも文字列でも扱える。
Public Function DN_GMap(D_No As Variant) As String
Dim DNo As Double
D_No = StrConv(D_No, 8) '全角文字を半角文字に変換
D_No = Replace(D_No, "-", "") '「-」を削除
DNo = Val(D_No) '数値に変換
DN_GMap = D_GMap(DNo) '電柱Noから直接GogleMapUR
End Function

'電柱Noから直接ストリートビューURL　電柱Noが数値でも文字列でも扱える。
Public Function DN_Veu(D_No As Variant) As String
Dim DNo As Double
D_No = StrConv(D_No, 8) '全角文字を半角文字に変換
D_No = Replace(D_No, "-", "") '「-」を削除
DNo = Val(D_No) '数値に変換
DN_Veu = D_Veu(DNo) '電柱Noから直接ストリートビューURL
End Function

さらにこんな使い方も

「その3」シートではアクティブセルに電柱Noが入っていると「GoogleMap表示」ボタンを押すとジャンプします。

M10.png (9270 バイト)