| |
Sample です。
Const END_ROW As Long = 1000&
' これを実行
Sub Main()
Dim i As Long
For i = 1 To 5
Debug.Print F_CallTest("test" & i)
Next
End Sub
' Test呼び出し
Function F_CallTest(strProcedure As String) As String
Dim curTime As Currency
Dim i As Long
curTime = Timer
For i = 1 To END_ROW
Application.Run strProcedure, Cells(i, 1)
Next
F_CallTest = strProcedure & vbTab & CCur(Timer) - curTime
End Function
' Data作成
Sub SetData()
Dim lngData() As Long
Dim i As Long
ReDim lngData(1 To END_ROW, 1 To 1)
For i = 1 To END_ROW
lngData(i, 1) = "hogehoge" & i
Next
Cells(1, 1).Resize(END_ROW).Value = lngData
End Sub
' For 〜 Next Loop 順次検索
Sub test1(rngTarget As Excel.Range)
Dim strTarget As String
Dim i As Long
strTarget = rngTarget.Value
For i = 1 To END_ROW
If Cells(i, 1).Value = strTarget Then Exit For
Next
End Sub
' For Each 〜 Next Loop 順次検索
Sub test2(rngTarget As Excel.Range)
Dim objTarget As Excel.Range
Dim strTarget As String
Dim i As Long
strTarget = rngTarget.Value
For Each objTarget In Cells(1, 1).Resize(END_ROW)
If objTarget.Value = strTarget Then Exit For
Next
End Sub
' 配列 順次検索
Sub test3(rngTarget As Excel.Range)
Dim strTarget As String
Dim vntData As Variant
Dim vntbuf As Variant
Dim strBuf As String
Dim i As Long
vntData = Cells(1, 1).Resize(END_ROW).Value
strTarget = rngTarget.Value
For Each vntbuf In vntData
If vntbuf = strTarget Then Exit For
Next
End Sub
' Find Method 順次検索
Sub test4(rngTarget As Excel.Range)
Dim strTarget As String
Dim rngResult As Excel.Range
strTarget = rngTarget.Value
With Cells
Set rngResult = .Item(1).Resize(END_ROW).Find( _
What:=strTarget, After:=.Item(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
End With
End Sub
' Match 2分探索
Sub test5(rngTarget As Excel.Range)
Dim vntResult As Variant
vntResult = Application.Match( _
rngTarget, Cells(1, 1).Resize(END_ROW), 1)
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
