Improving VBA execution speed when writing to a Excel worksheet

[cappy2112]

As a follow-on to this thread:
https://www.mrexcel.com/forum/excel...ing-crazy-trying-get-cell-offset-working.html

Now that I've got my VBA code working, I'm wondering if there is a way to speed it up.
My code reads data from a small (12KB) log file, parses it into VBA collections, then iterates over those collections while writing the
data values to a worksheet. My worksheet has two sets of data, each is 17 rows * 16 columns. Very small compared to some worksheets I've seen. However small, it takes ~ 36 seconds to populate that data.

Is there anything I can do to my code to improve the execution speed? Given that I'm mostly new to VBA scriptiing, I wouldn't be surprise if I'm using a very slow way of populating the worksheet.

My code is below.

Public Sub PopulateWorksheet(PerformanceData As Collection, Sheetname As String)


Const TESTDESCRITEM As Integer = 1
Const DATAITEM As Integer = 2
Const TESTNAMEITEM As Integer = 1
Const IOPSITEM As Integer = 2
Const MBSITEM As Integer = 3


Dim IOPs As String
Dim MBs As Variant
Dim BlkSizeCount As Integer
Dim TestNumberCount As Integer
Dim IOPSRange As Range
Dim MBSRange As Range
Dim RowOffset As Integer
Dim ColOffset As Integer
Dim TestName As String
Dim TestNameFlag As Boolean


' clear the old data
Set IOPSRange = Worksheets("My Try").Range("J8:Y24")
IOPSRange.Clear

' point to the IOPs starting cell
Set IOPSRange = Worksheets("My Try").Range("J8")


'clear the old MB/S data
Set MBSRange = Worksheets("My Try").Range("Z8:AO24")
MBSRange.Clear

' point to the MB/S starting cell
Set MBSRange = Worksheets("My Try").Range("Z8")


BlkSizeCount = 1

For ColOffset = 0 To 15

TestNumberCount = ColOffset + 1

For RowOffset = 0 To 16
'TestName = PerformanceData.Item(TestNumberCount).Item(TESTNAMEITEM)
'If TestNameFlag = False Then
' TestNameFlag = True
' 'Debug.Print TestNumberCount, TestName
'End If


'Populate IOPs
IOPs = PerformanceData.Item(TestNumberCount).Item(DATAITEM).Item(RowOffset + 1).Item(IOPSITEM)
IOPSRange.NumberFormat = "General"
IOPSRange.Offset(RowOffset, ColOffset) = CStr(IOPs)

'Populate MB/s
MBs = PerformanceData.Item(TestNumberCount).Item(DATAITEM).Item(RowOffset + 1).Item(MBSITEM)
MBSRange.Offset(RowOffset, ColOffset) = CStr(MBs)
MBSRange.NumberFormat = "General"

Next RowOffset

Next ColOffset


End Sub

Thanks in advance.

 

[cappy2112]

Thank you!

That works- and blazing fast too.

 

[cappy2112]

BTW, I've just purchased a copy of your book on Excel Tables.
Eveb though I'm not an Excel guy, I'll probably give it to someone I work with.

thanks for your help.

 

[Zack Barresse]

Thanks! If you send it to me I'll sign it and send it back. PM me if you're interested.