@ MountainMan,
Thnks ... I was in a hurry and didn't notice that the lOldAddr variable was also declared inside the HookCOMFunction procedure ... Actually I also wrongly left the lpfnAddr variable inside

I have now sucessfully managed to override the CalculateFull Method of the excel application object AND to unhook it back ti its original as shown in the code below :
Code:
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
Destination As Any, _
Source As Any, _
ByVal length As LongPtr _
)

Private Declare PtrSafe Function VirtualProtect Lib "kernel32" _
(ByVal lpAddress As LongPtr, ByVal dwSize As Long, _
ByVal flNewProtect As LongPtr, lpflOldProtect As LongPtr) As Long


Private Const PAGE_EXECUTE_READWRITE As Long = &H40&
Private Const lFuncOffset = 319  'Excel Application.calculateFull Method VTable Offset
Private pVTable As LongPtr
Private bHooked As Boolean
Private lOldAddr As LongPtr
Private lpfnAddr As LongPtr

Sub HookCOMFunction()
    Dim lpVtableHead As LongPtr
    Dim lOldProtect As LongPtr
    Dim pObj As LongPtr
    pObj = CLngPtr(ObjPtr(Application))

    CopyMemory lpVtableHead, ByVal pObj, 8
    lpfnAddr = lpVtableHead + (lFuncOffset) * 8
    CopyMemory lOldAddr, ByVal lpfnAddr, 8

    Call VirtualProtect(lpfnAddr, 8, PAGE_EXECUTE_READWRITE, lOldProtect)
    CopyMemory ByVal lpfnAddr, AddressOf OverrideFunction, 8
    Call VirtualProtect(lpfnAddr, 8, lOldProtect, lOldProtect)
End Sub


Private Function OverrideFunction(ByVal voObjPtr As Long, ByVal Param As Long) As Long
    MsgBox "Excel 'CalculateFull' Method has been Hooked !!"
End Function


Sub Test()
    Application.CalculateFull
End Sub

Sub UnHookComFunction()
    CopyMemory ByVal lpfnAddr, lOldAddr, 8
End Sub
Unfortunately, this only works when yielding the CalculateFull Method via code as per the Test Procedure above ... Can this also work when executing the CalculateFull Method via the excel user interface ?!!