Testing for "Blank" Cells with VBA

[ErikG]

I'm trying to write a summation function (for use in a worksheet) that sums two numbers. The twist is that I want the function to add the numbers only if both numbers actually exist. If either of the passed variables are blank/missing I want the function to return a #N/A error.
Thus far, my function looks like this:

Public NoBlankSum(NumOne As Double, NumTwo As Double) As Variant

   If NumOne = "" OR NumTwo = "" Then
     NoBlankSum = CVErr(xlErrNA)
     Exit Function
   Else
     NoBlankSum = NumOne + NumTwo
   End If
End Function

The problem I've found is that if one of the variables comes from a blank cell Excel assigns a value of zero to the variable. So if I put the formula in a cell like: =NoBlankSum(A1,B1), and A1 is blank then Excel assigns a value of zero to the NumOne variable in the user-defined function. The function then executes with the statement
NoBlankSum = 0 + NumTwo = NumTwo.

So the question is, How do I test for blank/missing values so that I can return an error value when necessary? There is an ISBLANK() worksheet function, but it is not available to VBA. Any help is appreciated!!

ErikG

 

[Khalid NGO]

There is an ISBLANK() worksheet function, but it is not available to VBA.

Hi Erik,

Have you tried IsEmpty in vba?

Regards,

 

[Hui]

If len(NumOne) = 0 OR Len(NumTwo) = 0 Then

you also might be better to pass the NumOne and Numtwo in as Ranges rather than as Doubles

 

[ErikG]

Thanks very much! Problem solved!!!
I passed cells into the function as ranges and then used the IsEmpty function. Thanks again for your help!

ErikG