Type Mismatch on VBA if Statement

Question

I have an if statment checking for equality of a variable cast as a double. It checks if the variable contains anything at all and if the number is greater is 0.

However, when I check to see if the variable (num1) = 0 or num1 = "", I get a Type Mismatch Error.

num1 is a number take from a worksheet as a double:

num1 = loc.Cells(6, 5).Value

This currently assigns the value of 7800 to num1: num1 = 7800

However the code errors on the ElseIf statement stating there is a Type Mismatch:

ElseIf num1 = 0 Or num1 = "" Then
    GoTo nocurrentnumber
        If Not makenumber Then
            makenumber = NumberMaker(startdate, enddate)
            Set numbersheet = ThisWorkbook.Worksheets("Current Number")
        Else
        If PnL_Sheet.Cells(Startrow, i).Value > StartDate And PnL_Sheet.Cells(Startrow, i).Value <= EndDate Then
            PnL_Sheet.Cells(Startrow, numberLine).Value = number
        Else
        End If
        End If
nocurrentnumber:
    Else
        PnL_Sheet.Cells(Startrow, numberLine).Value = 0

Shouldn't the variables set to double be able to check for these types of equality? Or am I missing something else?

I have read the following posts, each of which provide an answer I don't think I can use:

Excel VBA Run-time error '13': Type mismatch

Excel VBA Run-time error '13' Type mismatch

Excel VBA: Type Mismatch

If it's a double then it can't be ""? Double is numeric only if I recall correctly — Andreas
Yes but num1 may be empty, so I need to check if is empty as well. Ill edit my post. What your seeing is an equality check, not assignment. — askquestion
But not string empty. That is the difference. What you are doing is equivalent to me holding a ball in my hand and asking what color the car is. The question does not make sense to the interpreter — Andreas
Not a good explanation but now I know I need to check if len is > 0. — askquestion
The length will always be >0 since the minimum value the variable can hold is 0, thus a length of 1. — Andreas

Andreas Andreas · Accepted Answer · 2020-11-28T21:56:48

A double data type can't hold strings, thus = "" is incorrect.

A numeric data type (int, double, long etc.) in VBA that is empty will hold the value 0.

ElseIf num1 = 0 then

Remove the string comparison.

Reference:

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/double-data-type
The default value of Double is 0.

Type Mismatch on VBA if Statement

2 Answers