# 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```

Sunday, March 5, 2017 10:45 AM

### 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 sec

```Private 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 Sunday, March 5, 2017 11:16 AM
Sunday, March 5, 2017 10:54 AM
• If execution speed is essential, don't use VBA but a compiled application, generated for example by one of the Visual Studio languages. Compiled code is much faster than interpreted code.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Sunday, March 5, 2017 1:43 PM
• 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 Sunday, March 5, 2017 3:47 PM
Sunday, March 5, 2017 3:33 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 IF

without 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

Sunday, March 5, 2017 3:39 PM
• however, I made a lot of mathematical algorithms in VBA, and to be an interpreted programming language, is very fast and can be changed for convenience of anywhere on any PC that has MS Excel.
Sunday, March 5, 2017 3:42 PM
• 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```

Monday, March 6, 2017 8:23 AM