September 11th, 2008, 06:16 PM
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
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)
September 11th, 2008, 06:35 PM
integer and float will both be 0 and string will be a VbNullString ("") once declared and not used. None of these will have Null
Originally Posted by Superman859
Some things, especially recordset values, however, will have null. To check this
If IsNull(MyRecordset![SomeField]) Then
The following will show 0, 0 and "is vbNullstring"
The vbNullString thing applies here too
Dim myInteger As Integer
Dim myFloat As Single
Dim myString As String
If IsNull(myInteger) Then
MsgBox "myInteger is null"
MsgBox "myInteger is " & myInteger ' will display 0
If IsNull(myFloat) Then
MsgBox "myFloat is null"
MsgBox "myFloat is " & myFloat ' will display 0
If IsNull(myString) Then
MsgBox "myString is null"
ElseIf myString = vbNullString Then
MsgBox "myString is vbNullString" ' will display this message
MsgBox "myString is " & myString
Assuming nothing is in cell A1 this will display the "is vbNullString" message
[Is] Nothing applies to objects.
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
MsgBox "A1 is " & ActiveSheet.Cells(1, 1).Value
vbNullString again (or "")
Originally Posted by Help
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.
Note the .SpecialCells(xlCellTypeLastCell)
Dim i As Integer
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print .Cells(i, 1).Value
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
“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss