VBA ユーザー関数の作成 （配列を利用 ?)

質問

文字列から特定文字「:」を見つけて
その前後の数値（計5文字）を抜き出すVBAが一応できました。

使用列が多く無駄が多いので
これをユーザー関数にしたいのですがどのようになりますか　？

セルに書き込む代わりに配列に書き込めば出来そうな気がしますが
やり方がわかりません。

例えば、
分秒抜き出し(指定文字列 as string)
で結果が5分50秒の場合
350.000
と表示させたい。

’------------------------------------------------------------------
Option Explicit

Sub test()

Dim 位置 As Integer
Dim name As String
Dim i As Long
Dim LastNo As Long

LastNo = Cells(Rows.Count, "A").End(xlUp).Row

'B列(特定文字の位置)
For i = 2 To LastNo
name = Range("A" & i).Value
Range("B" & i).Value = InStr(name, ":")

Next

'C列(特定文字から前の文字列）
'D列(特定文字から後ろの文字列
For i = 2 To LastNo
name = Range("A" & i).Value
Range("C" & i).Value = Left(name, Range("B" & i) - 1)
Range("D" & i).Value = Mid(name, Range("B" & i) + 1)
Next

'E列(分を抽出)
For i = 2 To LastNo
’10分以下の場合、半角のカッコ又は半角のスペースが前にある　（必要なら）追加
If Mid(Range("C" & i), Range("B" & i) - 2, 1) = "(" Or _
Mid(Range("C" & i), Range("B" & i) - 2, 1) = " " Then '9分以下
Range("E" & i) = Right(Range("C" & i), 1)
Else '１0分以上
Range("E" & i) = Right(Range("C" & i), 2)
End If
Next
'F列(秒を抽出)
For i = 2 To LastNo
Range("F" & i) = Left(Range("D" & i), 2)
Next
'G列（分：秒)
For i = 2 To LastNo
Range("G" & i) = "0:" & Range("E" & i) & ":" & Range("F" & i)
Range("G" & i).NumberFormatLocal = "h:mm:ss"
Cells(i, "H").Value = Cells(i, "G").Value
Cells(i, "H").NumberFormatLocal = "[s].000" '小数点以下で表示
Next

End Sub

回答No.2

kkkkkm · ベストアンサー

配列というのがよく分かりませんが、他から呼び出すFunctionでしたら

Sub Test()
Dim i As Long
Dim LastNo As Long

LastNo = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastNo
Call 他から呼び出す分秒抜き出し(Cells(i, "A"))
Next
End Sub

Function 他から呼び出す分秒抜き出し(セル As Range)
Dim 位置 As Integer
Dim temp As String, temp2 As String
Dim mMinutes As Long, mSeconds As Long

位置 = InStr(セル.Value, ":")

temp = Left(セル.Value, 位置 - 1)
temp2 = Mid(セル.Value, 位置 + 1)

If Mid(temp, 位置 - 2, 1) = "(" Or _
Mid(temp, 位置 - 2, 1) = " " Then '9分以下
mMinutes = Right(temp, 1)
Else '１0分以上
mMinutes = Right(temp, 2)
End If
mSeconds = Left(temp2, 2)

セル.Offset(0, 3).NumberFormatLocal = "[s].000" '小数点以下で表示
セル.Offset(0, 3).Value = "0:" & mMinutes & ":" & mSeconds

End Function

お礼

kkkkkmさん、毎回の回答感謝します。

やりたいことはNO1.回答のユーザー関数(Function）の事で
セルを利用せずに解がでるしコードがすごく短くなって嬉しいです。
結果、No2回答のような方法で利用したいと思います。

>配列というのがよく分かりませんが、

これは、セルに計算結果や（一時）表示を入れないで
　Dim　HAI＿B(1 to 100) のように定義して
配列をセルの代わりに利用出来ないと考えましたが
その必要は無いのが判りました。

これで現在進めているマクロの見直しに明かりがみえました。

回答No.3

kkkkkm

> 結果、No2回答のような方法で利用したいと思います。

For i = 2 To LastNo
Cells(i, "E").NumberFormatLocal = "[s].000" '小数点以下で表示
Cells(i, "E").Value = 分秒抜き出し(Cells(i, "A"))
Next

こんな感じですね。書き出すセルの融通が利くのでいいですね。

回答No.1

kkkkkm

ユーザー関数というのがセルに
=分秒抜き出し(A2)
みたいに利用したいというのでしたら
ただし、書式設定は関数ではできないので手動で設定してください。
他のプロシージャから呼び出すタイプのFunctionだとしても同じような感じでできると思います。

Function 分秒抜き出し(セル As Range) As Double
Dim 位置 As Integer
Dim temp As String, temp2 As String
Dim mMinutes As Long, mSeconds As Long

位置 = InStr(セル.Value, ":")

temp = Left(セル.Value, 位置 - 1)
temp2 = Mid(セル.Value, 位置 + 1)

If Mid(temp, 位置 - 2, 1) = "(" Or _
Mid(temp, 位置 - 2, 1) = " " Then '9分以下
mMinutes = Right(temp, 1)
Else '１0分以上
mMinutes = Right(temp, 2)
End If
mSeconds = Left(temp2, 2)
分秒抜き出し = CDbl(TimeValue("0:" & mMinutes & ":" & mSeconds))
End Function