400 error - can't figure out why it is happening

[mcomp72]

I made a macro-enabled workbook for my Dad, for keeping track of golf scores for a senior’s group he plays with.

I created it on Windows and he is using it on Windows.

He has two computers, a desktop and a laptop, and would like to be able to use the file on either of them.

He is receiving a 400 error on his desktop when pressing a certain button, but the same issue does not occur on his laptop. The issue also never occurred for me as I was creating & testing the file on my computer.

I was able to step through the code and figure out what line of code is causing the error. It is:

.Range("T" & Row).Formula2 = FormulaToUse

(This line is within a WITH block, which is why it starts with a period.)

When the error occurs, the variable Row equals 7 and the variable FormulaToUse equals the following:
"=VLOOKUP(1,'Allen, Ed'!$A$3:$F$5000,5)"

There is definitely a worksheet named "Allen, Ed" in the file.

On my computer, I have the following version of Excel:
Microsoft Excel for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20188) 32-bit

On his laptop, where the file is working fine, he has this version of Excel:
Microsoft Excel for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20070) 64-bit

On his desktop, where the 400 error is occurring, he has this version of Excel:
Microsoft Excel 2019 MSO (Version 2210 Build 16.0.15726.20188) 64-bit

I had him try to update Excel on his desktop, but it said he already had the latest version, so that does not seem to be the problem.

I notice that his desktop does not have the Microsoft 365 version on it like the other two computers do, but I have no idea why that might make a difference.

Does anyone have any idea why this error is happening? I am at a loss.

 

[mcomp72]

There are some other options here

VBA Error 400: What Are the Causes and How to Fix It? - VBA and VB.Net Tutorials, Education and Programming Services

Contents Introduction It is often said that debugging is part of programming, and through error messages, the programmer can easily identify where there are problems in his/her code. But as you can see from the above screenshot the VBA Error 400 is one of those

software-solutions-online.com

But I'd also try and setup a new workbook on Dads computer and copy data and macros across as required.
You could post ALL of the code in case there is another issue as well.

Thanks for posting this link. I will tell my Dad to try a couple of these things. Maybe doing a Repair of Excel will take care of it. Seems too good to be true, but we'll certainly try it. Also the Trusted Access thing might be the issue. Will check both and report back.

 

[mcomp72]

I was just able to take control of my Dad's computer and try some things. I turned on Trusted Access of VBA, but that didn't do it. Then I tried replacing all instances of "Formula2" with "Formula". That did it. I didn't get any 400 errors after that. I could have sworn I had tried that already (in one spot) and it had no affect. But it does now.

So, mystery solves. However, one thing I am not clear on. Why does the retail version of Excel 2019 not like Formula2? The Microsoft 365 version does, do I don't understand why it would be different in the retail version.

 

[Michael M]

I believe it is a backwards compatibility problem....
See here.

Backward Compatibility of Office 365 Dynamic Arrays (Updated 23 Sep 2020)

Office 365 Excel Dynamic Arrays are great, but: What happens when you create a workbook with Dynamic Array Excel (Excel DA) and send it to someone who does not have Excel DA, and how do you write …

fastexcel.wordpress.com

 

[Alex Blakenburg]

one thing I am not clear on. Why does the retail version of Excel 2019 not like Formula2? The Microsoft 365 version does, do I don't understand why it would be different in the retail version.

My understanding it that Formula2 only works if your version of Excel supports Dynamic Arrays which is MS365, Excel 2021 and later.
You can test this out by typing some data into A1 to A4 and then in B1 enter the formula =A1:A4
If it supports Dynamic arrays it will "Spill" and populate B1:B4 if not it will only return the value from A1.