The idea of the loop is to iterate for an n number of foods, calculate the calories of each element and calculate the total sum of all (it's a sum loop). The loop has an (n) time complexity, I believe... is there any possibility of multi-threading this loop to decrease the time to complete the calculation or any other solution possible? I'm open to all. I'm using a dictionary with the row number to retrieve the food data from an array, I've performed many tests and recall the data is not the "slow" part. I took only 1s to create and fill the dictionary (and array) with >30.000k of food and it retrieves the data in (1) time complexity.....

Below is the code of the function, calcMacros :)

Function calcMacros()

Dim coor As Coordinates
coor = Utils.findCoordinates(diet, "D")

sumEnergy = 0
sumCarbs = 0
sumFat = 0
sumProtein = 0

totalsumEnergy = 0
totalsumCarbs = 0
totalsumFat = 0
totalsumProtein = 0

Dim arr As Variant  'dimensioning and array equals to the range
arr = diet.Range("a1:x" & coor.aa)


wantLoop = Array(coor.a + 4, coor.d + 4, coor.g + 4, coor.j + 4, coor.m + 4, coor.p + 4, coor.s + 4, coor.v + 4)

'coor.a....coor.d is the coordinates for where the foods are, there are 8 meal groups, so I iterate
' only the food with these two nested loops...

For j = 0 To 7

    For i = wantLoop(j) To coor.aa
        If arr(i, 4) = "x1" Then  'x1 means it is a food
                arr(i, 21) = 0
                arr(i, 22) = 0
                arr(i, 23) = 0
                arr(i, 24) = 0
            If (arr(i, 5) <> "") Then
              
                If dict(arr(i, 5)) = "" Then 'non recorded food
                    arr(i, 21) = 0
                    arr(i, 23) = 0
                    arr(i, 23) = 0
                    arr(i, 24) = 0
                    Application.ScreenUpdating = True
                    Application.EnableEvents = True
                    Exit Function
                Else
               'mainDataBase is also an array        
               Carbs = mainDataBase(dict(arr(i, 5)), 9) * arr(i, 19)
               Protein = mainDataBase(dict(arr(i, 5)), 7) * arr(i, 19)
               Fat = mainDataBase(dict(arr(i, 5)), 8) * arr(i, 19)
               energy = mainDataBase(dict(arr(i, 5)), 6) * arr(i, 19)
               
                       
                           arr(i, 21) = Carbs 'carboidratos na coluna 20
                           arr(i, 22) = Protein ' Proteínas na coluna 21
                            arr(i, 23) = Fat 'Gordura na coluna 22
                            arr(i, 24) = energy 'energia na coluna 23
                            
                            sumEnergy = energy + sumEnergy
                            sumProtein = Protein + sumProtein
                            sumFat = Fat + sumFat
                            sumCarbs = Carbs + sumCarbs
                            
                            
                            totalsumEnergy = energy + totalsumEnergy
                            totalsumCarbs = Carbs + totalsumCarbs
                            totalsumProtein = Protein + totalsumProtein
                            totalsumFat = Fat + totalsumFat
                        
                       
                End If
            End If
        End If
                     
        
        If arr(i, 3).value = "y1" Then 'y1 is the end of a food group 
            arr(i, 24).value = sumEnergy
            arr(i, 23).value = sumFat
            arr(i, 22).value = sumProtein
            arr(i, 21).value = sumCarbs
            
            arr(14, 22).value = sumCarbs
            arr(14, 23).value = sumProtein
            arr(14, 24).value = sumFat
            arr(14, 25).value = sumEnergy                                
            sumEnergy = 0
            sumCarbs = 0
            sumFat = 0
            sumProtein = 0              
             t = t + 1
             Exit For
        End If
    Next

Next

    arr(10, 22) = totalsumCarbs
    arr(10, 23) = totalsumProtein
    arr(10, 24) = totalsumFat
    arr(10, 25) = totalsumEnergy

'from array back to the worksheet
 diet.Range("a1:x" & coor.aa) = arr

End Function 

0

There are 0 best solutions below