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