How to edit this VBA line to an array for better optimization?

[Indominus]

This full code pulls data from a work website into Excel. Can be up to 55000 rows. Currently on Excel 2019 it takes forever to run. Also uses between 33%-50% of cpu. On 2016 it worked fine. I use a code already that turns off all things like calculations, events, etc.

I am thinking an array would make it faster but not sure how to do that. When I press ESC during execution when it’s taking forever it’s always on one of the CallByName lines. Any help would be very appreciated. Thank you in advance!

For Each key In keyring
   .Cells(Row, 1) = haul_id
   .Cells(Row, 2) = CallByName(CallByName(Json, key, VbGet), "ID", VbGet)
   .Cells(Row, 3) = CallByName(CallByName(Json, key, VbGet), "state", VbGet)
   .Cells(Row, 4) = CallByName(CallByName(Json, key, VbGet), "size", VbGet)
   .Cells(Row, 6) = CallByName(CallByName(Json, key, VbGet), "section", VbGet)
   .Cells(Row, 7) = CallByName(CallByName(Json, key, VbGet), "date", VbGet) 
   .Cells(Row, 8) = CallByName(CallByName(Json, key, VbGet), "cycle", VbGet)
 
   If CallByName(CallByName(Json, key, VbGet), "Area", VbGet) <> "" Then
   .Cells(Row, 5) = Split(CallByName(CallByName(Json, key, VbGet), "Area", VbGet), ".")(0)

    End If
    Row = Row + 1
    Next key

  Next line_haul
  End With

  Call LudicrousMode(False)
  End Sub

 

[RoryA]

Does keyring, whatever that is, have any sort of count property?

I'm not sure why you're using a nested callbyname so much. I'd have thought something like:

Dim o as Object
Set o = CallByName(Json, key, VbGet)

then you can probably use:

o.ID

instead of

CallByName(CallByName(Json, key, VbGet), "ID", VbGet)

and similarly for the other properties you need.

 

[Indominus]

Does keyring, whatever that is, have any sort of count property?

I'm not sure why you're using a nested callbyname so much. I'd have thought something like:

Dim o as Object
Set o = CallByName(Json, key, VbGet)

then you can probably use:

o.ID

instead of

CallByName(CallByName(Json, key, VbGet), "ID", VbGet)

and similarly for the other properties you need.

Hi. So the last line before the cut off cut off I posted is

set keyring = s.Run(“keys”, Json)

So I changed up the code like you said. However, I am getting the error 438, “object doesn’t support this property or method.”

Here it is.

Dim o As Object

Set o = CallByName(Json, key, VbGet)



.Cells(Row, 1) = o.haul_id

.Cells(Row, 2) = o.id

.Cells(Row, 3) = o.state

.Cells(Row, 4) = o.size

.Cells(Row, 6) = o.section

.Cells(Row, 7) = o.date

.Cells(Row, 8) = o.cycle

 

[RoryA]

Based on your original code, the first two calls should actually be:

.Cells(Row, 1) = haul_id

.Cells(Row, 2) = o.Id

not:

.Cells(Row, 1) = o.haul_id

.Cells(Row, 2) = o.trackingId

 

[Indominus]

Based on your original code, the first two calls should actually be:

.Cells(Row, 1) = haul_id

.Cells(Row, 2) = o.Id

not:

.Cells(Row, 1) = o.haul_id

.Cells(Row, 2) = o.trackingId

Yes, that was my bad. I ended up just changing some names. Corrected. I am still seeing that error though. Using o as an object is not working for some reason

 

[RoryA]

Which line gives you the error?

 

[Indominus]

Which line gives you the error?

So I finally fixed it. First line I added
.Cells(Rows, 1) = o.haul_id when it should have been just haul_id.

However doing this did not increase the performance of the code at all. Been running for 7 minutes and it is still not done. Fastest I have seen it run is just under 1 minute on Excel 2016. Any other optimization that could be potentially done? Thank you by the way!

 

[RoryA]

Back to my original question: Does keyring, whatever that is, have any sort of count property? If it does, you can create an array of the appropriate size, populate that, then write it to the sheet in one hit, rather than going cell by cell which is always slow.

 

[Indominus]

Back to my original question: Does keyring, whatever that is, have any sort of count property? If it does, you can create an array of the appropriate size, populate that, then write it to the sheet in one hit, rather than going cell by cell which is always slow.

So not in the code. But I just set a
Debug.Print keyring after and the count is how many times the line_haul is in the data. So basically how many rows will be used for each line_haul. This can vary. For clarification, this code pulls the data for each shipment on an incoming vehicle. So if there are 2000 shipments on it, there will be 2000 ids, states, sizes, cycles, ead. And that is the number of keyring. Each vehicle can have a varying number of shipments on them. So this code loops through each line_haul to get the shipment data.

The debug.print keyring looks like
0,1,2,3,4,5,….

Debug.print key looks like
0
1
2
3
4
5
…