Help with new Run-Time Error PLEASE!!

[logandiana]

The error is a Run-time error 21473219767 (80028029)
Automation error
Invalid forward reference, or reference to uncompiled type

Dim DD As Workbook
Dim Data, Exp As Worksheet
Dim LR1 As Long
Set DD = ThisWorkbook
Set Data = DD.Sheets("Data")
Set Exp = DD.Sheets("export")
LR1 = Data.Cells(Rows.Count, 5).End(xlUp).Row     '''<<<<<<< ERRORS here
...

THIS CODE HAS BEEN RUNNING STABLE FOR YEARS!!
I run this code several times each day. The code hasn't been changed in years and has always run just fine.
This morning was a different story. Several users are getting the same message when they try so that tells me its not just my machine.

What is causing this error? What changed between yesterday and today?

 

[Yongle]

Strange
But try these amendments

    Dim DD As Workbook
    Dim Data As Worksheet, Exp As Worksheet
    Dim LR1 As Long
    Set DD = ThisWorkbook
    Set Data = DD.Sheets("Data")
    Set Exp = DD.Sheets("export")
    LR1 = Data.Cells(Data.Rows.Count, 5).End(xlUp).Row

 

[logandiana]

Strange
But try these amendments

Yes! Just separating the declarations worked, but... WHY!!??!???

Why does code work thousands of times untouched for years, then all of a sudden decide, "You know I should have said something years ago, but I think you are going to need to separate those or I am going to refuse to work"

What could have caused this?

 

[Yongle]

Never mind why it has changed recently.
The reason it works now is that your code is declared correctly
In your original code variable Data is type VARIANT and should be type WORKSHEET
- for some reason VBA is no longer happy with type VARIANT (I have no idea why!)

Regardless of any code you may have seen elsewhere VBA requires that EVERY variable type is assigned separately otherwise VBA treats it as a variant
That is not the case, I believe, with some similar languages

 

[logandiana]

I understand now, and more importantly that I have been declaring variables wrong all these years. I have always declared stuff like Dim LR1, LR2, LR3 as long. And only the LR3 is long while LR1 and LR2 are variants.
I need to go back and change a bunch of my previous work, even though it works, it’s not correct. I do learn something new every time though!

 

[Yongle]

I am glad that fixed it for you

 

[Duane]

Never mind why it has changed recently.
The reason it works now is that your code is declared correctly
In your original code variable Data is type VARIANT and should be type WORKSHEET
- for some reason VBA is no longer happy with type VARIANT (I have no idea why!)

Regardless of any code you may have seen elsewhere VBA requires that EVERY variable type is assigned separately otherwise VBA treats it as a variant
That is not the case, I believe, with some similar languages

Yet, if someone like me uses the macro recorder (and it is provided to help people who are not programmers), it generates the code the same way it always has which, apparently now, is (still) incorrect.

If it is incorrect, why is the macro recorder still producing it?

 

[thunderin]

Yet, if someone like me uses the macro recorder (and it is provided to help people who are not programmers), it generates the code the same way it always has which, apparently now, is (still) incorrect.

If it is incorrect, why is the macro recorder still producing it?

The problem was with user's invalid variables declaration. The macro recorder never produces that part of the code.