How to debug a VBA Runtime Error 1004 "Application-defined or Object-defined error"

Question

The reason for the question, seemingly duplicate with many others concerning the VBA Runtime Error 1004, is that as far as I can tell other SO questions deal with very specific cases or occurences of the error, rather than helping with diagnosing/debugging the underlying issue. So in the spirit of teaching a man how to fish rather than handing him the fish:

How to properly debug the VBA Runtime Error 1004 "Application-defined or Object-defined error"

This doc link gives me some hint that there is a way to get to the underlying error:

"It may be that in accessing objects from other applications, an error was propagated back to your program that can't be mapped to a Visual Basic error."

So it seems to me that there is a way to get more information about what's actually going wrong, regardless of what application context you're using VBA in. However, I can't find a way to dive deeper.

Some context is given below, but do mind I'm not specifically looking for the solution to these errors, only on how to get more information on them.

Context 1

I built a script to do some simple file operations on a batch of Excel files. When I run the script, with identical data as the target machine, running Excel as part of Office 365, everything goes fine (disregarding Context 2). However, when I send the script over to the one running the script on the target machine, also running Excel as part of Office 365 we get the error on the following line:

Workbooks(newFileName).Worksheets("rekenblad").Range("G7").Value = "=" & adminCosts & "-$I$7"

Now the adminCosts is a Double, and my machine and target machine run different locales, but I'd implemented some in an earlier iteration of the script to make sure any decimal symbol issues would be resolved:

If Application.International(xlDecimalSeparator) = "," Then
    adminCosts = Replace(adminCosts, ".", ",")
ElseIf Application.International(xlDecimalSeparator) = "." Then
    adminCosts = Replace(adminCosts, ",", ".")

However, this shouldn't be a problem anyway, as the value of the variable is assigned by copying a cell value on the host machine, thus any decimal separator should be correct (and is, as I can see from debugging). However, the line of code won't run.

So now I'm at a loss, considering it works on my machine, I can manually assign the identical value to G7 in Excel on the target machine and all assignments of the range using the Immediate Window on the target machine work as expected. I just can't get to the root of this problem.

Context 2

After giving up on the script on the target machine we decided to move the data to my machine, running the script there and just be done with it. After two thirds of the batch, I run into the 1004 on the same line (hello darkness my old friend). Turns out the sheet contained a String rather than a Double in the cell assigned to adminCosts. Now this was a simply spotted issue, sadly not the case for Context 1 above.

Answer 1

About all you can find out about the error is whatever information the error object has in it, which is a little limited:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/err-object

You can set up an error handler in the routine, something like this:

Sub test()
    
    On Error GoTo errTrap
    buffer = 1 / 0
    
Exit Sub
errTrap:
    Debug.Print Err.Number
    Debug.Print Err.Description
    Debug.Print Err.LastDllError
    Debug.Print Err.HelpContext
    Debug.Print Err.HelpFile
    Debug.Print Err.Source
    Err.Clear
End Sub

and dump that information out so you can read it, but it only helps in limited situations. The first thing to do is to look at the observations from freeflow and Shrotter: make sure your code is written in such a way that variables are checked for type and match their use, and that the property or method you are using is being used in accordance with its documentation.

After that it's really just educated guessing.