If you close everything and open a brand new instance of Excel, then open a 2nd workbook in that same instance of Excel, and place a formula in the 1st workbook that references the 2nd workbook, you will notice the formula takes a certain format when referencing the 2nd.
Now, if you repeat that same experiment, but somehow make sure to have each workbook (1st and 2nd) open in a SEPARATE instance of Excel, (it's tricky nowadays, Microsoft has made it harder, I forgot the most reliable way to do it but has to do with a Shift key or something - it's NOT just the old way of going to Start menu > Excel > hit enter again).....in this second case, you should notice the formula takes a certain different format.
(If necessary, use vbscript or something to guarantee you that there are 2 separate instances of excel, to do this, create a notepad that ends in .vbs and type something like set xl1=createobject("excel.application") [newline] set xl2=createobject("excel.application")
Another way to 'see' the difference in this format is to perform Scenario 1 that I said ..... then save and close both workbooks, then only open the 1st one. At this point, the 1st workbook's formula will reference the 2nd workbook in a way that is more inclusive of the Path, etc.
I have a feeling that however your code is handling things like Instances of Excel Applications (etc. etc), and the question of which workbook belongs to which Application instance in any given second of time, is playing (perhaps) into the format you need your formula to be in.
That is all I can think of, assuming your formula is generally otherwise correct, I have always found Range.formula="formula" to be correct.
To be safe, just use the longer format all the time.
This is partly why I asked about the xlApp. It will never cease to be surprising how often the "post all your code" is relevant - including for myself - when I least expect it, it tends to be
