0

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 Nov 28 '20 at 21:49
  • 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. –  Nov 28 '20 at 21:51
  • 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 Nov 28 '20 at 21:53
  • Not a good explanation but now I know I need to check if len is > 0. –  Nov 28 '20 at 21:59
  • The length will always be >0 since the minimum value the variable can hold is 0, thus a length of 1. – Andreas Nov 28 '20 at 22:01
  • Is num1 a variant? If so then `num1 = ““` is not incorrect. – RBarryYoung Nov 28 '20 at 22:02
  • I just need to check is the value of the cell the variable draws from is > 0. Not the variable itself. –  Nov 28 '20 at 22:02
  • @RBarryYoung first sentence of the question ***I have an if statment checking for equality of a variable cast as a double***. It's not variant. – Andreas Nov 28 '20 at 22:05
  • @RBarryYoung true but then this error wouldn't been thrown. Saying something is casted to double is probably a "translation error" from other programming language. Even if you would cast a variant to double then this error would not occur. Only reason for this error is that the variable is declared as a double. – Andreas Nov 28 '20 at 22:19

2 Answers2

2

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://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/double-data-type
The default value of Double is 0.

Andreas
  • 23,610
  • 6
  • 30
  • 62
0

Answer is instead of checking if num1 is blank, check is len of num1 is greater than 0.