Code optimization - WorksheetFunction.SumIfs() function

[nfuids]

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

for each phonenumber in phonenumbers
    has_voice = ForfaitVoix(phonenumbers, [USOC1, USOC2, USOC3])
next

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

How could I optimize this to run a LOT faster?

' Accept a phone number and an array of USOC code to look for and sum up the quantities
Private Function QuantiteCodesUsocs(numeroCellulaire As Variant, codesUsocs() As Variant)
    Dim total As Double
    Dim i As Integer
    
    ' Range that should be added (the quantity of the option represented by the USOC), for the SumIfs function
    Dim plageAddition As Range
    Set plageAddition = Range("DonneesForfait!I:I")
    
    ' Range containing the phone numbers, used in a condition for the SumIfs function
    Dim plageNumeroCellulaire As Range
    Set plageNumeroCellulaire = Range("DonneesForfait!C:C")
    

    ' Range containing the Usoc code, used in a condition in the SumIfs function
    Dim plageCodeUsoc As Range
    Set plageCodeUsoc = Range("DonneesForfait!G:G")
    
    ' initialisation
    total = 0
    
    ' Determine how many of the USOC the phone number has
    For i = 1 To UBound(codesUsocs)
        total = total + WorksheetFunction.SumIfs(plageAddition, plageNumeroCellulaire, numeroCellulaire, plageCodeUsoc, codesUsocs(i))
    Next i
    
    ' retourne la quantité
    QuantiteCodesUsocs = total
End Function

' Sert à déterminer si "numeroCellulaire" devrait avoir la voix activée
Private Function ForfaitVoix(numeroCellulaire) As String
    Dim usocs(2) As Variant
    
    usocs(1) = "RVOIX"
    usocs(2) = "BVOIX"
    
    If (QuantiteCodesUsocs(numeroCellulaire, usocs) > 0) Then
        ForfaitVoix = "True"
    Else
        ForfaitVoix = "False"
    End If
End Function