Fastest Way to Build a List

[magelan]

Hey All,

Whats the fastest way to build a list out of excel?
I've got an array of 20,000 or so classobjects and some code that looks like this
Entrylist(x) is my array of classobjects, with properties pay as bool, file as bool, entity as string, day as string, juris as string, and preparer as string.

[vba]
Sub buildList()

Dim wsheet As Worksheet
Set wsheet = ActiveWorkbook.Worksheets("List")
wsheet.Activate 'select the list sheet and make it active.
wsheet.Range("a1").Activate
wsheet.Range("a1").Select

ActiveCell.value = "Date"
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = "Jurisdiction"
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = "Entity"
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = "Preparer"
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = "Action"
ActiveCell.Offset(1, -4).Activate

Dim c As Integer
Dim r As Integer
Dim dinger As Variant
Dim percomplete As Long


For r = 1 To (UBound(entryList) - 1)
ActiveCell.value = entryList(r).day
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).juris
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).entity
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = Trim(entryList(r).preparer)
ActiveCell.Offset(0, 1).Activate

If (entryList(r).file) And (entryList(r).pay) Then
ActiveCell.value = "File"
ActiveCell.Offset(1, -4).Activate
ActiveCell.value = entryList(r).day
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).juris
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).entity
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = Trim(entryList(r).preparer)
ActiveCell.Offset(0, 1).Activate
ActiveCell.value = "Pay"
ActiveCell.Offset(1, -4).Activate
Else
If (entryList(r).file) Then
ActiveCell.value = "File"
ActiveCell.Offset(1, -4).Activate
Else
ActiveCell.value = "Pay"
ActiveCell.Offset(1, -4).Activate
End If
End If
Next
end sub
[/vba]
so basically im selecting a cell, putting in its value, and then offsetting to select all the other cells. I know there's gotta be a better and MUCH faster way to do this [the buildlist subroutine takes the longest out of my code - without it, the code is around 5 seconds, with it is 17]

[Aflatoon]

There is very rarely any need to select or activate cells to manipulate them - you can simply assign a value to them:
[vba] ActiveCell.Offset(0, 1).value = entryList(r).juris[/vba]
rather than
[vba] ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).juris[/vba]

It's also very slow to read or write data one cell at a time so you would be better off creating an array sized to fit the output required and then populate that in code before writing it to a range in one go.

[magelan]

There is very rarely any need to select or activate cells to manipulate them - you can simply assign a value to them:
[vba] ActiveCell.Offset(0, 1).value = entryList(r).juris[/vba] rather than
[vba] ActiveCell.Offset(0, 1).Activate
ActiveCell.value = entryList(r).juris[/vba]
It's also very slow to read or write data one cell at a time so you would be better off creating an array sized to fit the output required and then populate that in code before writing it to a range in one go.

Well I currently have an array of all the classobjects

Do you mean creating a two dimensional array thats basically (1 to amt-of-fields, 1 to length of list)?

How would you output that array to the list?

EDIT

New Code - Removed the Activates - shaved a second off my time.

[vba]
Sub buildList()

Call clearlists
Application.ScreenUpdating = False
Dim wsheet As Worksheet
Set wsheet = ActiveWorkbook.Worksheets("List")
wsheet.Activate 'select the list sheet and make it active.
wsheet.Range("a1").Activate
wsheet.Range("a1").Select

ActiveCell.value = "Date"
ActiveCell.Offset(0, 1).value = "Jurisdiction"
ActiveCell.Offset(0, 2).value = "Entity"
ActiveCell.Offset(0, 3).value = "Preparer"
ActiveCell.Offset(0, 4).value = "Action"
ActiveCell.Offset(1, 0).Activate

Dim r As Integer

Dim percomplete As Long


For r = 1 To (UBound(entryList) - 1)
ActiveCell.value = entryList(r).day
ActiveCell.Offset(0, 1).value = entryList(r).juris
ActiveCell.Offset(0, 2).value = entryList(r).entity
ActiveCell.Offset(0, 3).value = Trim(entryList(r).preparer)

If (entryList(r).file) And (entryList(r).pay) Then
ActiveCell.Offset(0, 4).value = "File"
ActiveCell.Offset(1, 0).value = entryList(r).day
ActiveCell.Offset(1, 1).value = entryList(r).juris
ActiveCell.Offset(1, 2).value = entryList(r).entity
ActiveCell.Offset(1, 3).value = Trim(entryList(r).preparer)
ActiveCell.Offset(1, 4).value = "Pay"
ActiveCell.Offset(2, 0).Activate
Else
If (entryList(r).file) Then
ActiveCell.Offset(0, 4).value = "File"
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(0, 4).value = "Pay"
ActiveCell.Offset(1, 0).Activate
End If
End If
percomplete = ((r / UBound(entryList)) * 100)
Application.StatusBar = "(3/4) Building the List... (" & percomplete & "%)"
Next

End Sub
[/vba]

[Aflatoon]

Yes that's the idea - you output it using
[vba]Range("A2").resize(ubound(myarray, 1) + 1, ubound(myarray, 2) + 1).value = myarray[/vba]

BTW you still have activate and selects in that code.