• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Testing for "Blank" Cells with VBA

ErikG

New Member
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:
Code:
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
 
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
 
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
 
Back
Top