#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    313
    Rep Power
    30

    Excel Macro - How To Find Out If Cell Is Null?


    My cousin asked me to write a fairly simple macro for him in visual basic to use in excel. I do not have any experience with visual basic, but I've programmed in several other languages.

    Is there a way to know if a variable has a value or not? I'm not sure on the data he will put in the cell (integer, float, or string I assume).

    After reading a couple quick tutorials, I can access a cell. Let's say Cell(i,j) is the cell I want to know the value for.

    Is there a quick way to see if it is null?

    I just found that the keyword NOTHING is used for the default value of the given reference type. I thought maybe

    if cell(i,j) != nothing THEN
    //do something else
    END IF

    would work...but I'm also thinking maybe it might not. For example, if we have integers, then I assume the default is 0. But then I assume the cell may have the number 0 in it - the cell is not empty. But at the same time it would appear to be empty if nothing works as I think it might...

    Anyways, just wondering if anyone knows about this or has a little experience creating macros in excel (uses visual basic). The main concepts I will need is to loop through a column until the end of the data in that column, if a cell is NOT empty, do something to a nearby cell. If it is empty, do nothing. That's pretty much it. Excel provides objects for accessing the values of the cells or ranges over cells and things of this nature. I just need to figure out if they are null and figure out how to loop through until the end of the set (not just until we hit one that is empty)
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Originally Posted by Superman859
    Is there a way to know if a variable has a value or not? I'm not sure on the data he will put in the cell (integer, float, or string I assume).
    integer and float will both be 0 and string will be a VbNullString ("") once declared and not used. None of these will have Null

    Some things, especially recordset values, however, will have null. To check this

    If IsNull(MyRecordset![SomeField]) Then
    ' ...
    End If

    The following will show 0, 0 and "is vbNullstring"

    Code:
    Sub ExampleDeclaredValues()
        Dim myInteger As Integer
        Dim myFloat As Single
        Dim myString As String
        If IsNull(myInteger) Then
            MsgBox "myInteger is null"
        Else
            MsgBox "myInteger is " & myInteger ' will display 0
        End If
        If IsNull(myFloat) Then
            MsgBox "myFloat is null"
        Else
            MsgBox "myFloat is " & myFloat ' will display 0
        End If
        If IsNull(myString) Then
            MsgBox "myString is null"
        ElseIf myString = vbNullString Then
            MsgBox "myString is vbNullString" ' will display this message
        Else
            MsgBox "myString is " & myString
        End If
    End Sub
    After reading a couple quick tutorials, I can access a cell. Let's say Cell(i,j) is the cell I want to know the value for.

    Is there a quick way to see if it is null?
    The vbNullString thing applies here too

    Assuming nothing is in cell A1 this will display the "is vbNullString" message

    Code:
    Sub CheckACell()
        If IsNull(ActiveSheet.Cells(1, 1).Value) Then
            MsgBox "A1 is null"
        ElseIf ActiveSheet.Cells(1, 1).Value = vbNullString Then
            MsgBox "A1 is vbNullString" ' will display this message
        Else
            MsgBox "A1 is " & ActiveSheet.Cells(1, 1).Value
        End If
    End Sub
    I just found that the keyword NOTHING is used for the default value of the given reference type. I thought maybe

    if cell(i,j) != nothing THEN
    //do something else
    END IF

    would work...but I'm also thinking maybe it might not.
    [Is] Nothing applies to objects.

    Originally Posted by Help
    The Nothing keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:

    Set MyObject = Nothing

    Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.
    For example, if we have integers, then I assume the default is 0. But then I assume the cell may have the number 0 in it - the cell is not empty. But at the same time it would appear to be empty if nothing works as I think it might...
    vbNullString again (or "")

    Anyways, just wondering if anyone knows about this or has a little experience creating macros in excel (uses visual basic). The main concepts I will need is to loop through a column until the end of the data in that column, if a cell is NOT empty, do something to a nearby cell. If it is empty, do nothing. That's pretty much it. Excel provides objects for accessing the values of the cells or ranges over cells and things of this nature. I just need to figure out if they are null and figure out how to loop through until the end of the set (not just until we hit one that is empty)
    Yeah a little experience. Actually I code the Excel application from Access most of the time.

    Now for looping through the valid range of a sheet.

    Code:
    Sub LoopThroughEachWorksheetRowWithData()
        Dim i As Integer
        With ActiveSheet
            For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
                Debug.Print .Cells(i, 1).Value
            Next
        End With
    End Sub
    Note the .SpecialCells(xlCellTypeLastCell)
    The same applies to .Column property of this if you need to loop through columns.

    The above example prints the value of column A for each row from 1 through the end of the sheet with data to the immediate pane.

    Comments on this post

    • zynder agrees
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

IMN logo majestic logo threadwatch logo seochat tools logo