none
VBA - optimize beyond all limits (If ... Then VS Select ... Case) RRS feed

  • 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 makexcel 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 makexcel 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