Asked by:
VBA  optimize beyond all limits (If ... Then VS Select ... Case)
General discussion

good morning, how can I optimize the calculation time, this code? to be noted that I have to run this cycle for billions of times...
the functions that I have applied to Pz = nm and Pz = Sin (nm) are random never mind.
Public Sub xTest() Dim Tm As Single, nm As Double, x1 As Long, x2 As Double Tm = Timer nm = 7 For x1 = 1 To 10 ^ 7 x2 = Pz(Int(32 * Rnd + 1), nm) Next x1 Debug.Print Timer  Tm End Sub Public Function Pz(o As Byte, nm As Double) As Long If o = 1 Then Pz = nm: Exit Function If o = 2 Then Pz = Sin(nm): Exit Function If o = 3 Then Pz = Cos(nm): Exit Function If o = 4 Then Pz = nm / 1000: Exit Function If o = 5 Then Pz = Sqr(nm): Exit Function If o = 6 Then Pz = Sin(nm): Exit Function If o = 7 Then Pz = Cos(Pz): Exit Function If o = 8 Then Pz = Tan(nm): Exit Function If o = 9 Then Pz = nm * 10: Exit Function If o = 10 Then Pz = nm * 100: Exit Function If o = 11 Then Pz = nm + 7: Exit Function If o = 12 Then Pz = nm ^ 2: Exit Function If o = 13 Then Pz = nm / 7: Exit Function If o = 14 Then Pz = nm * 2.5: Exit Function If o = 15 Then Pz = 7 / nm: Exit Function If o = 16 Then Pz = 5 / nm: Exit Function If o = 17 Then Pz = Sqr(nm / 2): Exit Function If o = 18 Then Pz = Sin(Cos(nm)): Exit Function If o = 19 Then Pz = Sin(Sqr(nm / 2)): Exit Function If o = 20 Then Pz = Tan(nm * 2): Exit Function If o = 21 Then Pz = Cos(nm / 3): Exit Function If o = 22 Then Pz = nm * 3: Exit Function If o = 23 Then Pz = nm * 7: Exit Function If o = 24 Then Pz = 9 / nm: Exit Function If o = 25 Then Pz = Cos(nm * 3): Exit Function If o = 26 Then Pz = Sin(7 / nm): Exit Function If o = 27 Then Pz = nm * 10 ^ 3: Exit Function If o = 28 Then Pz = nm: Exit Function If o = 29 Then Pz = nm / 10: Exit Function If o = 30 Then Pz = Cos(Sin(nm / 2)): Exit Function If o = 31 Then Pz = Cos(nm / Sin(2)): Exit Function If o = 32 Then Pz = nm * 4: Exit Function End Function
All replies

I chose the If ... Then ... Exit Function because it is faster than the other (Selec Case, If Else, ...):
If: 14,38281 sec
If_Exit: 12,14063 sec
Case: 13,21875 sec
Case_Exit: 12,48438 secPrivate Sub Test() Dim Tm As Single, x1 As Long, x2 As Variant Tm = Timer: For x1 = 1 To 10 ^ 8: x2 = P_If(4): Next x1: Debug.Print "If: " & Timer  Tm Tm = Timer: For x1 = 1 To 10 ^ 8: x2 = P_If_Exit(4): Next x1: Debug.Print "If_Exit: " & Timer  Tm Tm = Timer: For x1 = 1 To 10 ^ 8: x2 = P_Case(4): Next x1: Debug.Print "Case: " & Timer  Tm Tm = Timer: For x1 = 1 To 10 ^ 8: x2 = P_Case_Exit(4): Next x1: Debug.Print "Case_Exit: " & Timer  Tm End Sub Private Function P_If(o As Byte) As Byte If o = 1 Then P_If = 7 If o = 2 Then P_If = 9 If o = 3 Then P_If = 5 If o = 4 Then P_If = 3 If o = 5 Then P_If = 2 If o = 6 Then P_If = 1 If o = 7 Then P_If = 4 End Function Private Function P_If_Exit(o As Byte) As Byte If o = 1 Then P_If_Exit = 7: Exit Function If o = 2 Then P_If_Exit = 9: Exit Function If o = 3 Then P_If_Exit = 5: Exit Function If o = 4 Then P_If_Exit = 3: Exit Function If o = 5 Then P_If_Exit = 2: Exit Function If o = 6 Then P_If_Exit = 1: Exit Function If o = 7 Then P_If_Exit = 4: Exit Function End Function Private Function P_Case(o As Byte) As Byte Select Case o Case 1: P_Case = 7 Case 2: P_Case = 9 Case 3: P_Case = 5 Case 4: P_Case = 3 Case 5: P_Case = 2 Case 6: P_Case = 1 Case 7: P_Case = 4 End Select End Function Private Function P_Case_Exit(o As Byte) As Byte Select Case o Case 1: P_Case_Exit = 7: Exit Function Case 2: P_Case_Exit = 9: Exit Function Case 3: P_Case_Exit = 5: Exit Function Case 4: P_Case_Exit = 3: Exit Function Case 5: P_Case_Exit = 2: Exit Function Case 6: P_Case_Exit = 1: Exit Function Case 7: P_Case_Exit = 4: Exit Function End Select End Function
 Edited by makexcel Sunday, March 5, 2017 11:16 AM


yeah sure, I know, even if the javascript V8, yet is absolute fastest, for programs that provide many cycles FOR / DO / WHILE and IF statements. what interests me is to optimize the algorithm used. i an example. to optimize a sequence of IF statements, as well as to write EXIT FUNCTION you could do this:
insert intermediate IF so as to reduce the steps. everything is figure out how to insert intermediate IF to get the fewest steps possible
Public Function Pz(o As Byte, nm As Double) As Long If o < 5 Then If o = 1 Then Pz = nm: Exit Function If o = 2 Then Pz = Sin(nm): Exit Function If o = 3 Then Pz = Cos(nm): Exit Function If o = 4 Then Pz = nm / 1000: Exit Function End If If o = 5 Then Pz = Sqr(nm): Exit Function If o = 6 Then Pz = Sin(nm): Exit Function If o = 7 Then Pz = Cos(Pz): Exit Function If o = 8 Then Pz = Tan(nm): Exit Function End Function
 Edited by makexcel Sunday, March 5, 2017 3:47 PM

for o = 1 must execute 2 instructions IF
for o = 2 must execute 3 instructions IF
for o = 3 must execute 4 instructions IF
for o = 4 must execute 5 instructions IF
for o = 5 must execute 2 instructions IF
for o = 6 must execute 3 instructions IF
for o = 7 must execute 4 instructions IF
for o = 8 must execute 5 instructions IFwithout that intermediate IF instructions in total would be (1+2+3+4+5+6+7+8) = 36 instead of (2+3+4+5+2+3+4+5) = 28
I'm interested in the algorithm or the programming logic


the best solution I've found is the following: I have optimized the speed of the code 528 = (1,2,3,4,5,6,7,8, ..., 32) to 184 = (3,4, 5,6,7,4,5,6,7,4,5,6,7,3,4,5,6,7,8,4,5,6,7,5,6,7,8, 6,7,6,7,8)
managed to get a better result?Public Function Pz(o As Byte, nm As Double) As Long If o < 14 Then If o < 6 Then If o = 1 Then Pz = nm: Exit Function If o = 2 Then Pz = Sin(nm): Exit Function If o = 3 Then Pz = Cos(nm): Exit Function If o = 4 Then Pz = nm / 1000: Exit Function If o = 5 Then Pz = Sqr(nm): Exit Function End If If o = 6 Then Pz = Sin(nm): Exit Function If o = 7 Then Pz = Cos(Pz): Exit Function If o = 8 Then Pz = Tan(nm): Exit Function If o = 9 Then Pz = nm * 10: Exit Function End If If o = 10 Then Pz = nm * 100: Exit Function If o = 11 Then Pz = nm + 7: Exit Function If o = 12 Then Pz = nm ^ 2: Exit Function If o = 13 Then Pz = nm / 7: Exit Function Else If o < 20 Then If o = 14 Then Pz = nm * 2.5: Exit Function If o = 15 Then Pz = 7 / nm: Exit Function If o = 16 Then Pz = 5 / nm: Exit Function If o = 17 Then Pz = Sqr(nm / 2): Exit Function If o = 18 Then Pz = Sin(Cos(nm)): Exit Function If o = 19 Then Pz = Sin(Sqr(nm / 2)): Exit Function End If If o < 24 Then If o = 20 Then Pz = Tan(nm * 2): Exit Function If o = 21 Then Pz = Cos(nm / 3): Exit Function If o = 22 Then Pz = nm * 3: Exit Function If o = 23 Then Pz = nm * 7: Exit Function End If If o < 28 Then If o = 24 Then Pz = 9 / nm: Exit Function If o = 25 Then Pz = Cos(nm * 3): Exit Function If o = 26 Then Pz = Sin(7 / nm): Exit Function If o = 27 Then Pz = nm * 10 ^ 3: Exit Function End If If o < 30 Then If o = 28 Then Pz = nm: Exit Function If o = 29 Then Pz = nm / 10: Exit Function End If If o = 30 Then Pz = Cos(Sin(nm / 2)): Exit Function If o = 31 Then Pz = Cos(nm / Sin(2)): Exit Function If o = 32 Then Pz = nm * 4: Exit Function End If End Function