That is because a variant variable can be null, but a string variable cannot.  They are not really compatible data types, hence the "argument type mismatch" error.

The following code will run with no errors:

    Dim v As Variant
    v = "string"
    v = 1
    v = Null
    v = #6/28/2024#
    v = Null
    v = "string again"

This is why it's a variant -- its data type can vary.

But the code below will error on the third line, because a string variable can't be null:

    Dim s As String
    s = "string"
    s = Null
    s = "string"

Nor any typed variable -- integer, boolean, date, etc.

Okay, don't we have Nulls all the time in data fields that are typed as strings in a table? So, why make VBA different?

Data types in tables aren't exactly the same thing as data types in VBA.  You can enter nulls in a textbox because the underlying table allows nulls to be entered in that field, with the field's "Required" property set to No.  If "Required" is Yes, and if you enter a blank in a textbox, a zero-length string will be entered into the table instead, not null.  Tables have this flexibility, because that's part of a database's function.  But VBA, like other programming languages, deals with variables, not tables, and has to be strict with data types.

I've worked in other databases that have a language more tightly coupled to the way the database works. Yes, I know that VBA is used for other MS and Office applications. The fact that MS decided to allow a variant type of variable shows that one really doesn't have to be that strict with typing (as other languages or implementations have shown). Some languages that compile down to assembly code worked around this limitation some thirty years ago (I don't know how as they did it after I worked in O/S assembly, but still). As I said, a little more design work with VBA could (or can) eliminate these compile and runtime problems.

It's not really a problem if it functions per its design.  A variant is simply not compatible with a string.  In the example in Richard's video, it just *happens* that a variant has a string value.  That doesn't mean it should be compatible to an actual string variable, because a variant variable could very well be any other types of value.

Then how do we explain this strange way that incompatible variables work?
?"2024" = Year(now)
True
?vartype(Year(now))
2

Regarding "2024" = 2024 returning true, it has more to do with how VBA handles comparisons.  Here is Microsoft's documentation of it:

     https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/comparison-operators

which says:

     "When either expression is not a variant, if one expression is a numeric data type and the other is a Variant that is, or can be, a number, then perform a numeric comparison."

For instance, the code below:

    Dim s As String, i As Integer
    s = "2024"
    i = 2024
    Debug.Print s = i

will return True, because s and i are both not variant, and s can be numeric.

But the code below:

    Dim v1 As Variant, v2 As Variant
    v1 = "2024"
    v2 = 2024
    Debug.Print v1 = v2

will return False instead.  As the Microsoft doc above says, if *both* variables are variant, the variable with string value is greater than the one with the numeric value, even if they appear equal.  In fact, any string value in v1 would be "greater" than any integer value in v2 (e.g. "0" > 2024).

Some of these "rules" may be a little arcane, but that's what the Microsoft doc says.  And "Option Compare Database/Binary/Text" adds even more rules to how values are compared.

In the immediate window, the use of Dim is not allowed, so all variables are variant.  And when one variant variable is "2024" and the other 2024, they are not equal, just as the Microsoft doc says.  See picture below.

Hi Kevin Y. The point was that VBA CAN compare dislike variables. You did a more thorough job explaining it. Back to the original point, why can't a variable string be Null? it was an ARBITRARY decision that conflicts with how Access tables work.

How many hundreds or thousands of times have you had to deal with something as silly as this? (image below)

That's just the way VBA is designed: none of the typed variables (string, integer, boolean, etc.) can be null.  If you need a null to be stored in a variable, you need to declare it as a totally different data type, like variant.  Many other programming languages are designed that way.  But some do allow nullable variables.  I believe C# lets you declare a typed variable that can also be nullable (i.e. an integer variable that can be either an integer or null).

I can "design" a bicycle to be low to the ground like a child's tricycle. That doesn't mean it will be very functional.

Don't get me wrong, I like many of the features in VBA and the editor, especially when compared to the BASIC I used twenty years before MS started on VBA. However, I would have expected better. Especially in the thirty years they've had to fix some of the oddities (bad design?). Or why would there be so much excitement around Twin Basic?

What other variable types, beside variant can have a Null? Since "real programmers" don't use variants (I don't say that, but many of my professors did), what does that say?

I am a real programmer and I use variants ergo your professors are invalid. Lol.