Assistance in a code to clear excel caches in VBA

[Shuvam Senapati]

Hello,

I have a Macro that runs a solver when called. My requirement is to call the macro multiple times so that we can run the solver for particular number of times. But after executing the VBA code to run the solver, the code runs for 4 to 5 times but then throws this error : " pywintypes.com_error: (-2147023170, 'The remote procedure call failed.', None, None) ".
We suspect it might be because the cache fills up after running the solver 5 times. So I want a code that can be appended in the end of my VBA code which clears the cache everytime upon its execution.

How to Clear Excel Cache

There’s no doubt that cache memory is handy and that every computer program relies on it. It helps software remember the most used functions and values and stores frequently used files. However, if you don’t clear the cache

www.alphr.com

I found this on internet, but this only clears pivot table and other handful of excel objects but not the entire cache.

Can someone please help me with this ?

 

[Micron]

Maybe your problem is in how the code runs and what it actually does. AFAIK, most cache is used for files, including web browser cache. There only seems to be cache properties for slicer and pivot so if you're not using either in your code, then perhaps the message is misleading. If you're using a COM object here with something other than vba (e.g. Python) I'm clueless - except to say that possibly this can be caused by using ByRef in procedures instead of ByVal. The COM object might be trying to access/change variables stored in memory to get the variable value, and sometimes that's not permitted. AFAIK, vba default is ByRef whereas in VB and others, it is ByVal, so if you're not specifying one way or the other, it might be something to check out. While I'm at it, I seem to recall that using parentheses around variables will coerce to ByVal so that is another thing to watch out for. In other words, if you test these two procedures you will see that wrapping the variable in the call statement with ( ) you will get you different results.

Function TestByRefVal_1()
Dim lngIn As Long

lngIn = 2
Debug.Print "(1) lngIn is " & lngIn

TestByRefVal_2 (lngIn)
Debug.Print "(2) lngIn is now " & lngIn

TestByRefVal_2 lngIn
Debug.Print "(3) lngIn is now " & lngIn

End Function
Function TestByRefVal_2(lngIn As Long)
lngIn = 4

End Function

Results:
(1) lngIn is 2
(2) lngIn is now 2
(3) lngIn is now 4

So you should be able to see from the results that not specifying the argument in vba defaults to ByRef (else the 2nd function would not alter the value) BUT not if you wrap the variable ( ).