How to re-execute same function during run time

We have created few excel macros which we use as a script for performing certain actions against application. Each script has multiple function calls and during execution some of these function calls fails due to application related performance issues (like window did not load properly/window took more time than expected to load etc.)

Since these application perf 开发者_如何学Pythonrelated issues occurs quiet frequently and our execution is getting failed all the time, we are planning to implement some code in script using which during execution we could be able to re-execute the failed function again (function from macro).

Can you let me know how should I code to achieve my objective of executing same function again.

For example,

If in macro there are below function calls:

Function 1

Function 2

Function 3

In this case if function 2 fails then I would like to re-execute function 2 during run time.

Note: Here we do not know which function call will fail so I need to implement a capability to re-execute any function which fails during execution so it could be Function 1/Function 2/Function 3.

Store all the functions in a dictionary object.

set a reference to Microsoft Scripting Runtime Library

public Sub MasterFunction()

Public Dict as Dictionary
Set dict = New Dictionary

Dict.add "Function1"
Dict.add "Function2"
Dict.add "Function3"

call Function1
call Function2
call Function3

When a function is run...at the end of the function, erase the functions name from the dictionary. ie,

public Function Function1()

dict.remove "Function1"

End Function

The last step is to add a loop through the dictionary to see if any items are still remaining. If there are no items in the dictionary then your functions executed successfully. If there are items, then call the function again with application.run "Function1", substituting "Function1" for your function name. Full Example below, Copy and paste into a module and run "MasterFunction". The third function I am not calling to simulate that it did not run. If you step through the code you will see that the only item remaining is Function3 which was not called.

Public dict As Dictionary
Public Function MasterFunction()

Set dict = New Dictionary
dict.Add "Function1", "Function1"
dict.Add "Function2", "Function2"
dict.Add "Function3", "Function2"

Call Function1
Call Function2

Dim DictItem

For Each DictItem In dict
Application.Run DictItem
MsgBox DictItem & " has run again because it didn't execute last time"
Next

Set DictItem = Nothing
Set dict = Nothing
End Function

Function Function1()
     dict.Remove "Function1"
End Function

Function Function2()
     dict.Remove "Function2"
End Function

Function Function3()
    dict.Remove "Function3"
End Function

You can make all of your functions return a Boolean indicating success or failure, then test that to determine whether to rerun. Here's an example that also includes a run counter to avoid endless loops.

Sub Master()

    Dim lRunCount As Long

    Const lRUNMAX As Long = 5

    lRunCount = 0
    Do
        lRunCount = lRunCount + 1
    Loop Until Function1 And lRunCount <= lRUNMAX

    lRunCount = 0
    Do
        lRunCount = lRunCount + 1
    Loop Until Function2 And lRunCount <= lRUNMAX

    lRunCount = 0
    Do
        lRunCount = lRunCount + 1
    Loop Until Function3 And lRunCount <= lRUNMAX


End Sub

Function Function1() As Boolean

    Dim bReturn As Boolean

    On Error GoTo ErrHandler
    bReturn = True

    Debug.Print "function 1 did stuff"


ErrExit:
    Function1 = bReturn
    Exit Function

ErrHandler:
    bReturn = False
    Resume ErrExit

End Function

Function Function2() As Boolean

    Dim bReturn As Boolean

    On Error GoTo ErrHandler
    bReturn = True

    'simulate error
    If Rnd < 0.5 Then Err.Raise 9999

    Debug.Print "function 2 did stuff"


ErrExit:
    Function2 = bReturn
    Exit Function

ErrHandler:
    bReturn = False
    Resume ErrExit

End Function

Function Function3() As Boolean

    Dim bReturn As Boolean

    On Error GoTo ErrHandler
    bReturn = True

    Debug.Print "function 3 did stuff"


ErrExit:
    Function3 = bReturn
    Exit Function

ErrHandler:
    bReturn = False
    Resume ErrExit

End Function