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.

 

[Michael M]

Have you tried reverting to "Formula" rather than "Formula2"
Have a loook at the attached thread

Redirecting

 

[mcomp72]

Have you tried reverting to "Formula" rather than "Formula2"
Have a loook at the attached thread

Redirecting

Yep, tried that. The error still occurred.

 

[Michael M]

How have you declared the formulatouse variable
AND
Have you tried inserting the actual formula rather than the variable.
Notwithstanding that I'd suggest posting the entire code.
Have you also checked the OP'sVBA reference library to make sure the Object library is checked.

 

[mcomp72]

How have you declared the formulatouse variable
AND
Have you tried inserting the actual formula rather than the variable.
Notwithstanding that I'd suggest posting the entire code.
Have you also checked the OP'sVBA reference library to make sure the Object library is checked.

FormulaToUse is a string variable.

Nope, haven't tried inserting the actual formula into that line of code. I will give that a try.

Regarding the last question: when I open the file on my computer (where I created the file) the following are the references that are checked.

But does my Dad need to check this on his computer once he opens the file? Or should these boxes always be checked because that's how they were in the original file?

 

[Michael M]

He needs the same boxes checked !

 

[mcomp72]

He needs the same boxes checked !

I just checked on my Dad's computer. All four of those boxes were checked under References, so that wasn't the problem. Any other ideas?

 

[Michael M]

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.

 

[Fluff]

Did you change every instance of Formula2 to Formula?
Also rather than running the code from a button in the VB editor use F8 to step through & see where the error occurs & what the error message/number is.

 

[mcomp72]

Did you change every instance of Formula2 to Formula?
Also rather than running the code from a button in the VB editor use F8 to step through & see where the error occurs & what the error message/number is.

I only changed Formula2 to Formula in the one instance that was causing the 400 error. At least, the only one I knew of at the time. Making that change made no difference.

The way I found out what line of code was causing the 400 error was by pressing F8 and stepping through the code. When I did that, I can't remember now if it actually gave me a specific error message not. It may have still only shown a 400 error.

Last night, my Dad tried pressing several other buttons in the workbook, and some of them also caused a 400 error. I haven't been able to look into it yet. He lives in another state, so I have to take control of his computer remotely to be able to look into it. I'm not sure yet what was causing it in these other instances, but there are other places in the code where I have Formula2, so I guess it's possible it's that. I'll try to look within the next couple of days, and I will report back with any updates. Worst case, I can paste the full code here. But what's odd is that it's working fine on two other computers with Microsoft 365 installed, but on his computer with the retail version of Excel 2019, it has this problem.