magelan
10-29-2012, 07:29 AM
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.
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
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]
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.
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
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]