It's because you use Trim.
Excel's IsBlank is very restrictive and will only ever give True if the cell is completely empty (not even has a formula that returns empty result). So if that returns True, IsEmpty on the cell's value should also return True.
But what IsEmpty(cell) actually does is detecting whether the cell's .Value contains the special value Variant/Empty. The raw cell value does indeed contain it, and IsEmpty(ThisWorkbook.ActiveSheet.Cells(31, 1)) would return True, but Trim() turns the Variant/Empty into a Variant/String, and a string is never IsEmpty, even when it's zero length.
If for you "empty" means "contains zero length content", then you should check for Len(Trim(ThisWorkbook.ActiveSheet.Cells(31, 1))) = 0.
Videos
Title says it all. I have a number of formulas that I only want I run if certain cells have data. I have historically used the a2=“” return blank, but am wondering if it is better to use the isblank function instead. Most looking for ways to make workbooks more efficient as they are getting rather large
A variant is the only type of variable that can store an empty value or a Null value, and is declared thus:
Dim aVar as Variant
Dim aVar2 ' if no type is given then it's declared as a variant
Immediately after declaration a variant stores no value and it is empty.
Also you can assign empty to a variant using aVar = Empty and it will be empty again.
When a variant stores empty these are both true:
aVar = Empty
IsEmpty(aVar)
You can also set the value of a Variant variable to be Null
aVar = Null
These would now be false
aVar = Empty
IsEmpty(aVar)
However, IsNull(aVar) would be true.
Null is particularly useful when you use VBA variables to store data that has come from a database table that allows NULL values to be stored in it's fields. In this case it is generally advisable that all the variables need to accommodate storing NULL. So they all need to be variant as this is the only data type that stores NULL.
This is very unfortunate as it would be better to have more strongly typed variables in use.
A variant storing Null is not the same as a variant being Empty. Null indicates that a value was assigned to a variable and the value was Null.
This gets confusing as Null is used by databases to indicate no value has been stored in a field, and most databases allow fields with any datatype to be Null. When a database field stores Null, it is kind of the equivalent to a VBA variant variable having just been declared as “receptacle” for a value and not yet being given a value. The variable, just like the table field is an receptacle without anything in it.
However, if a VBA variant variable is given a Null value from a database table, then it stores the fact that it is Null, as this is different to it never having been given a value and is information your program might want to treat differently.
Also note that a variant storing and empty string "" is not the same as being empty.
ie "" does not equal Empty (and it is not the same as Null either!)
When using MS Access tables to store text, I would advise against setting the "Allow Zero Length" field property to true, which is the default setting, as this means your database field will be able to store "" (ie an empty string) as well as a Null value. Any code you write then has to work with the possibility that the field will store a "" or a Null. It's easier just to work with a Null.
(It's very rare to need to store an empty string in a database table).
Another useful technique is to use MyString = Nz(MyStringDatabaseField) to convert any nulls to be an empty string. At least then your code only has to test for empty strings and not for Nulls as well. This technique will also simplify code working with access tables that store empty strings. Sometimes it may be appropriate to use `MyInteger=Nz(MyIntegerDatabaseField) to convert any nulls to 0, but I am very uncomfortable with this as 0 has a more meaning than an empty string and really Null <> 0!
Note that SQL statements which use an OUTER JOIN between their tables, can result in the returned recordset containing NULL values in fields where the underlying table field is defined to prevent NULLs being stored.
Note that if you do not use a variant the data types then default values may be used unexpectedly. Eg
Dim aInt As Integer
aInt = Empty
Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"
The code below helped me to understand the difference between the various functions that can be used to inspect variant variables
Sub ExperimentsWithVariants()
Dim aInt As Integer
aInt = Empty
Debug.Print aInt, " This will print 0, as 0 is the default value for integer variables"
Dim avar As Variant ' The results shown as comments below were created when aVar was declared as a variant
Debug.Print "-----------------------"
Debug.Print "NOT SET:"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Empty
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
If avar = Empty Then
Debug.Print " "
Debug.Print "avar = Empty so the above would be the same if you set avar = Empty explicitly"
Debug.Print " """
Else
avar = Empty
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO Empty"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Empty
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
End If
avar = Null
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO NULL"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Null
Debug.Print "aVar = Empty ", (avar = Empty) ' Null
Debug.Print "aVar", avar ' Null
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' True
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' False
Debug.Print "aVar = """"", (avar = "") ' Null
Debug.Print "aVar = 0", (avar = 0) ' Null
avar = ""
Debug.Print " "
Debug.Print "-----------------------"
Debug.Print " SET TO EMPTY STRING ie """""
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) '
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' False
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' String
' Note
' Is empty returns false, whereas ="" returns NULL
avar = 1.23
Debug.Print "-----------------------"
Debug.Print "SET to 1.23:"
Debug.Print "-----------------------"
Debug.Print "TypeName(avar)", TypeName(avar) ' Double
Debug.Print "aVar = Empty ", (avar = Empty) ' True
Debug.Print "aVar", avar ' '' ie blank
Debug.Print "IsNull(aVar)", (IsNull(avar)) ' False
Debug.Print "IsEmpty(aVar)", (IsEmpty(avar)) ' True
Debug.Print "aVar = """"", (avar = "") ' True
Debug.Print "aVar = 0", (avar = 0) ' True
' You can test for both an IsEmpty AND an empty string (ie "" ) AND a null value with:
' IIf(Len(avar & vbNullString)
Debug.Print "-----------------------"
Debug.Print "Using IIf(Len(avar & vbNullString) "
Debug.Print "-----------------------"
avar = ""
Debug.Print """""=", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty, or Empty String", "NOT")
avar = "1"
Debug.Print "1 = ", IIf(Len(avar & vbNullString) = 0, "Null IsEmpty,or Empty String", "NOT")
avar = Null
Debug.Print "Null = ", IIf(Len(avar & vbNullString) = 0, "Null, IsEmpty or Empty String", "NOT")
avar = Empty
Debug.Print "Empty = ", IIf(Len(avar & vbNullString) = 0, "Null or Empty String", "NOT")
Debug.Print "-----------------------"
Debug.Print "using TypeName"
Debug.Print "-----------------------"
Dim dbl1 As Double
Debug.Print "TypeName(dbl1) ", TypeName(dbl1) ' Double
Dim int1 As Integer
Debug.Print "TypeName(int1) ", TypeName(int1) ' Integer
Dim str1 As String
Debug.Print "TypeName(str1) ", TypeName(str1) ' String
End Sub
Sub ExperimentsWithNz()
Debug.Print " "
Debug.Print "---------------------------------------------------------------------- "
Debug.Print "---------------------------------------------------------------------- "
Debug.Print "1a Nz(Null)="""" =", Nz(Null) = ""
Debug.Print "1b IsNull(Nz(Null)) =", IsNull(Nz(Null)) ' False
Debug.Print "---------------------------------------------------------------------- "
Dim aVar As Variant
Debug.Print "2a Nz(aVar) Unassigned =", Nz(aVar) ' Null
aVar = Empty
Debug.Print "2b Nz(aVar) Empty =", Nz(aVar) ' Null
aVar = Null
Debug.Print "2c Nz(aVar) Null =", Nz(aVar) ' Null
Debug.Print "2d IsNull(Nz(aVar)) Null=", (IsNull(Nz(aVar))) ' Null
aVar = ""
Debug.Print "2e Nz(aVar) """" =", Nz(aVar) ' ' ie an empty string
Debug.Print "---------------------------------------------------------------------- "
Dim str1 As String
Debug.Print "3a Nz(str1) Unassigned =", Nz(str1) ' 0
str1 = Empty
Debug.Print "3b Nz(str1) Empty =", Nz(str1) ' 0
Debug.Print "---------------------------------------------------------------------- "
Dim int1 As Integer
Debug.Print "4a Nz(int1) Unassigned =", Nz(int1) ' 0
int1 = Empty
Debug.Print "5b Nz(int1) Empty =", Nz(int1) ' 0
' The following line cannot run as a string cannot be assigned Null
' str1 = Null
End Sub
Sub DealingWithEmptyStringsInADatabaseTable()
Dim aVar As Variant
Debug.Print "UNdeclared: ", Nz(aVar, 1)
aVar = Empty
Debug.Print "aVar=Empty ", Nz(aVar, 1)
aVar = Null
Debug.Print "aVar=Null ", Nz(aVar, 1)
aVar = ""
Debug.Print "aVar="""" ", Nz(aVar, 1)
Debug.Print " -------------------------------------------------------"
Debug.Print "Dealing with empty string in a database table"
aVar = ""
Debug.Print "IIf(aVar = "", 1, 0) ", IIf(aVar = "", 1, 0)
Debug.Print " "
Debug.Print " "
Debug.Print "-------------------------------------------------------"
Debug.Print "Dealing with a table field that can have Null or an Empty string"
Debug.Print "leads to more complex code than if is just stores NULL."
Debug.Print " "
Debug.Print "The code below shows WHY you should set the ""; Allow Zero Length "" property of access tables to false"
Debug.Print " "
aVar = Null
Debug.Print "1 Null : IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)
aVar = ""
Debug.Print "2 Empty String: IIf(Nz(aVar & """" ,"""") = """", 1, 0) ", IIf(aVar & "" = "", 1, 0)
Debug.Print " "
Debug.Print "Both lines 1 and 2 above work."
Debug.Print " "
Debug.Print " "
aVar = Null
Debug.Print "3 Null : Nz(aVar, 1) ", Nz(aVar, 1)
aVar = ""
Debug.Print "4 Empty String: Nz(aVar, 1) ", Nz(aVar, 1)
Debug.Print " "
Debug.Print "however, line 4 does not work for empty string."
Debug.Print "3 & 4 are much simpler than 1 & 2, but if your field can store """" and Null"
Debug.Print "you have to use 1 & 2. Which is a shame as 3 & 4 are simpler."
Debug.Print "Queries and code accessing this data can get messy"
End Sub
Adding to HarveyFrench's answer...
The Variant of VBA gracefully handles NULL's, and it also handles Empty's. The concept of Empty is of debatable merit, and not represented in many database systems.
NULL means an unobtained or unobtainable value. It was a concept made essential to the proper handling of OUTER JOIN's. An OUTER JOIN begins with a table (or subquery) that will have all of its rows selected. The condition of the JOIN is then used to link to another table (or subquery). In the case of an INNER JOIN, if the condition cannot be satisfied (i.e. resolved to TRUE) between the tables (or subqueries), then the rows of such situations do not get selected. However, in the case of an OUTER JOIN, the unsatisfied JOIN condition will still result in rows from the first table (or subquery) being selected alongside a set of NULL's for each column of the other table (or subquery). These NULL's would signal the failure of the JOIN; these would be the unobtainable values.
An "Empty" isn't the same as a NULL. NULL means unobtained. "Empty" means "this field is intentionally blank." It's a subtle difference, but an "Empty" is somthing definitive, whereas a NULL is something left unknown. An intentional blank is deliberate, not a mistake, unambiguously there specifically to mean there is nothing to find in the first place.
Some folks may intentionally employ NULL to indicate an "Empty" for a column; or they may use NULL to indicate something else other than the unobtainable. There are some folks who would say using NULL in this way is bad practice. The reasoning to this is that now there isn't a clear way to distinguish the intentionally blank from the unknown, and you may confuse yourself in complicated queries. I would say to that "just be careful, and are you sure that NULL is the best or only way to make it all work?"
As for the empty string, the "", that is very much a value. As in something definitively known. It is also a specific datatype - namely that of a string (or text or varchar or whatever an array of characters may be taken as). Call it an "Empty" is you wish, although really it's limited to where strings are expected. In practice it may amount to the same thing. But it's not really "Empty", and it is most certainly not NULL.
Personally I regard NULL as more of a signal, like "not-a-number" conditions you may encounter in floating point numbers. In other words, NULL isn't a particular value. It's the signal of not having a value. When you look up three-valued logic (3VL), particularly where SQL and NULL get involved, you'll better understand why the condition NULL=NULL simply isn't TRUE.
"Empty" is also a signal, but a signal of "whatever amounts to the same as a blank." Hence, the conditions EMPTY="" and EMPTY=0 will both be TRUE - even though ""=0 probably won't compute.