前回にVBAの高速化は作業効率上で大事な事と書きました。
①セルの書式を文字列指定にする
②With句で出来るだけ参照範囲を絞る
実は、VBAの高速化って
・Application.ScreenUpdatingや、Application.Calculationのおまじない
これは確かに大事なのですけど（まずやっとけば間違いない）
上記①②も結構効くんです。
■論より証拠
実験してみたので、エビデンスを。
セルの書式を文字列、且つWith句での絞り込みは、実に処理時間を25%まで圧縮できる事がこれで分かると思います。
テスト用ソースは以下のとおりです（雑ですみません）
Sub WithTest()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim stTime As Double
Dim edTime As Double
Dim ttlTime As Double
Dim loopCnt As Long
Dim i As Long
Dim j As Long
Dim y As Long
loopCnt = InputBox("ループ回数を入力してください", "Input LoopCnt")
y = 1
With ThisWorkbook.Sheets("Sheet1").Cells(y, 1)
.Offset(0, 0) = "テストケース(書式表示=G/標準)"
.Offset(0, 1) = "テスト" & vbLf & "文字列"
For i = 2 To 11
.Offset(0, i) = "Test" & i - 1
Next i
.Offset(0, 12) = "平均処理時間"
.Offset(1, 0) = "With指定なし"
.Offset(2, 0) = "With Thisworkbook"
.Offset(3, 0) = "With Thisworkbook.Sheets(""Sheet1"")(Cellsで座標指定)"
.Offset(4, 0) = "With Thisworkbook.Sheets(""Sheet1"").Cells(y,2)(Offsetで座標指定)"
End With
'NumberFormatLocal = "G/標準"
ThisWorkbook.Sheets("Sheet1").Cells.NumberFormatLocal = "G/標準"
'With指定なし
ttlTime = 0
y = y + 1
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
ThisWorkbook.Sheets("Sheet1").Cells(y, 2) = "gao1"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook
ttlTime = 0
y = y + 1
With ThisWorkbook
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Sheets("Sheet1").Cells(y, 2) = "gao2"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook.sheets("Sheet1")
ttlTime = 0
y = y + 1
With ThisWorkbook.Sheets("Sheet1")
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Cells(y, 2) = "gao3"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook.sheets("Sheet1").cells(y,2)
ttlTime = 0
y = y + 1
With ThisWorkbook.Sheets("Sheet1").Cells(y, 2)
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Offset(0, 0) = "gao4"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
y = y + 2
With ThisWorkbook.Sheets("Sheet1").Cells(y, 1)
.Offset(0, 0) = "テストケース(書式表示=""@""(文字列))"
.Offset(0, 1) = "テスト" & vbLf & "文字列"
For i = 2 To 11
.Offset(0, i) = "Test" & i - 1
Next i
.Offset(0, 12) = "平均処理時間"
.Offset(1, 0) = "With指定なし"
.Offset(2, 0) = "With Thisworkbook"
.Offset(3, 0) = "With Thisworkbook.Sheets(""Sheet1"")(Cellsで座標指定)"
.Offset(4, 0) = "With Thisworkbook.Sheets(""Sheet1"").Cells(y,2)(Offsetで座標指定)"
End With
'NumberFormatLocal = "@"
ThisWorkbook.Sheets("Sheet1").Cells.NumberFormatLocal = "@"
'With指定なし
ttlTime = 0
y = y + 1
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
ThisWorkbook.Sheets("Sheet1").Cells(y, 2) = "gao1"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook
ttlTime = 0
y = y + 1
With ThisWorkbook
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Sheets("Sheet1").Cells(y, 2) = "gao2"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook.sheets("Sheet1")
ttlTime = 0
y = y + 1
With ThisWorkbook.Sheets("Sheet1")
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Cells(y, 2) = "gao3"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
'With Thisworkbook.sheets("Sheet1").cells(y,2)
ttlTime = 0
y = y + 1
With ThisWorkbook.Sheets("Sheet1").Cells(y, 2)
For j = 0 To 9
stTime = Timer
For i = 1 To loopCnt
.Offset(0, 0) = "gao4"
Next i
endtime = Timer
ThisWorkbook.Sheets("Sheet1").Cells(y, 3 + j) = WorksheetFunction.Round(endtime - stTime, 3)
ttlTime = ttlTime + (endtime - stTime)
Next j
End With
ThisWorkbook.Sheets("Sheet1").Cells(y, 13) = ttlTime / 10
ThisWorkbook.Sheets("Sheet1").Cells.NumberFormatLocal = "G/標準"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub