The issue is with the macros and could be just about anything. Automation errors can occur at runtime for a variety of reasons. Without any knowledge of the macros then we would have no way of diagnosing this. These are runtime errors (network blips, odd system behavior, etc) and is unlikely to be noticeable just by looking at the code.
You're going to need to enable more verbose logging when an error occurs. Unfortunately it depends where the error is occurring as to how you do that. I might start by looking in the Event Viewer to see if it logged any useful error messages. That would be the ideal situation.
If not then I'd modify each macro to wrap the errors and display useful information (or log it somewhere) so you can review it. I'm assuming here the default of "break on unhandled error" is already set and that is why you're seeing the message box. But it doesn't tell you anything useful. Therefore you need to capture the error and display it instead. There is a lot involved in doing this so I'm going to link to this article on how you might do that. Given the choices I'd lean toward the on error goto label
approach. Then use Err.Description
to get the error but I suspect that it is that description that you're already seeing so it is not useful.
This is where you need to understand your macro. If your macro is making calls into Excel then that is likely where the error is occurring. You could wrap each separate call into Excel with a custom label but that seems like overkill. I might recommend that you create a simple step variable in the macro and update the step variable as your code executes. If an error occurs then include the step in the message so you can figure out where things are failing. A pseudo example (not valid VBA).
Dim step As String
On Error GoTo OnError
Set step = "Getting range"
Get range from Excel...
Set step = "Calculating value"
Calculating value from Excel...
Set step = "Updating spreadsheet"
Updating spreadsheet...
OnError:
MsgBox.Err.Description & vbCrLf & "Step: " & step