Code optimization - WorksheetFunction.SumIfs() function

Hi there,

I have this data I need to work with. In the first worksheet, it's a list of 20k phone numbers with basic account info (owner's name, client id, subdivision id, model of phone, etc.)
In the second worksheet, I have one row per USOC per phone number. So say the number 1234567890 has a voice data plan, and a data plan, the second worksheet would have 1 row for the voice plan, and one row for the data plan.

There are about 75k rows in this second worksheet.


Now, I need to create this worksheet with these columns:
customer_id, phone_number, carrier, has_voice, has_data, has_callerid, has_voicemail, has_email2sms

So I created a VBA macro that would grab basic info from worksheet(1) and do some work to find the info from worksheet(2). This is where it gets complicated. Since we're migrating to a new system, we need to migrate to new USOC code (USOC code is simply a unique id for each cellphone plan options). Basically, it works like this:

If you had any voice plan, I need to set the column 'has_voice' to true. So if number 1234567890 has USOC "RVOICE", "TVOICE" or "BVOICE", set 'has_voice' to true.

I created a function that does just that and it works well. The thing is that it takes for ever to run through all records. I set a break point to stop every 1000 rows (of the 20k rows) and it takes a few minutes between each break. I'm disabling screen updating, manually update formula, etc.

There must be a better way that to call the SumIfs function as many time as USOC for every phone number.

So basically, the peudo-code is like this

Please Login or Register  to view this content.

So you can see the function ForfaitVoix() and the underlying QuantityCodesUsocs() function.

How could I optimize this to run a LOT faster?

Please Login or Register  to view this content.

Looping like that is very very slow. Do you need VBA? It would be much faster to put the formulas in the sheet.
Or you can have VBA put the formulas in the sheet all at once, no loops.

I tried a basic lookup formula:

Formula:

Please Login or Register  to view this content.

It takes about 1.5 seconds to fill down and calculate for 200 000 rows.
Then I tried the same thing in VBA and it takes about 16 seconds.

Please Login or Register  to view this content.

I would expect a greater difference.
However, at one point I ran it from the VB editor with a Debug.print and then i took 70 seconds. Apparently small things make a big difference. I used timer to check the time as you can see in my code. Maybe if you put Timer in different sections of your code you will find the bottleneck. Maybe there is an issue with the array.

I don't know anymore than this, it's out of my range of knowledge.
Anyone else?