1

I have a problem with value detection in excel. When it doesn't have value in cell i want excel show me message warning rather than zero value and i have code below.

Public Function MySum(a As Range, b As Range) As String

    If IsNull(a) And IsNull(b) Then
        MySum = "No value sir"
    Else
        MySum = a.Value + b.Value
    End If

End Function
Community
  • 1
  • 1
nara son
  • 39
  • 5

2 Answers2

0

Here are a few ways to validate parameters

Option Explicit

Public Function MySum(a As Range, b As Range) As String
    Dim itmsOK As Boolean

    MySum = "No value"  'default return value, if a or b are not valid

    itmsOK = (Not a Is Nothing And Not b Is Nothing)    'validate the Range objects

    If itmsOK Then itmsOK = (Not IsNull(a.Value2) And Not IsNull(b.Value2))       'DB vals
    If itmsOK Then itmsOK = (Len(Trim(a.Value2)) > 0 And Len(Trim(b.Value2)) > 0) 'Empty
    If itmsOK Then itmsOK = (IsNumeric(a.Value2) And IsNumeric(b.Value2))         'Numbers

    If itmsOK Then MySum = Val(a.Value2) + Val(b.Value2)   'if both valid, perform math

End Function

(checking just for Nulls is more specific to importing data from a database)

paul bica
  • 10,557
  • 4
  • 23
  • 42
  • 1
    Thank you. But what does value2 use for? – nara son Sep 30 '15 at 05:47
  • When working with cells (ranges) there are 3 properties used to get the actual data from the Range object: `rng.Value`, `rng.Value2`, and `rng.Text`. `rng.Value2` is the "raw data", extracted without formatting, for example if A1 contains NOW(): .Text: **#######** (when column is too narrow to show entire value), .Value: **9/28/2015 6:43:43 PM**, .Value2: **42275.7803555556**. [This answer](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) has more info about the differences between them – paul bica Sep 30 '15 at 12:06
0

CountA function returns value "0" when when the cell is empty. SO you can use CountA function in this case to check whether there is any data in the cell.

If CountA("a") = 0 and CountA("b") = 0 Then
MySum = "No value sir"
    Else
MySum = a.Value + b.Value
Else IF
END
Isu
  • 127
  • 4
  • 15