Empty vba excel что это
The IsEmpty VBA function can be used to check if a cell is blank, just like the Excel ISBLANK worksheet function. But, there’s more!
Not only can IsEmpty check if a cell or range is empty, it can check if a variable is uninitialized. In other words, you can use it to see if your macro has assigned a value to a variable.
We’ll get to some examples shortly, but first, let’s start this tutorial with some IsEmpty Basics.
IsEmpty accepts one argument:
It gives you your result as a Logical Boolean. Recall, a Logical Boolean returns either True or False .
The “Expression” argument IsEmpty accepts is a Variant, so it can be pretty much anything - a variable, an object, a range and more.
As I mentioned in my IsNumeric VBA Tutorial, it’s a good practice to check if your cells are empty before applying certain VBA functions.
Let’s take a look at some examples to see what I mean.
IsEmpty Examples
Check if a Cell is Blank
Make powerful macros with our free VBA Developer Kit
Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
This is a basic macro that checks whether or not you have entered content in cell A2 . You should use IsEmpty If statements like this one near the top of your macros to protect against errors.
Not recognizing that a required cell is empty is one of the most common causes of VBA runtime errors and undesirable outcomes.
Capturing empty cells gives you control over your macro and lets you handle these exceptions.
Check if All Cells in a Range are Blank
The comments in this VBA example explain what’s going on. This macro checks all the values in range A1:B5 and looks for empty cells. If one is found, the macro recognizes it and gives you the opportunity to program two different flow paths for the two scenarios.
Check if a Variable is Initialized
The first MsgBox returns True because the variable str1 has not been initialized - it’s empty. In other words, even though it’s been declared a variable of type Variant, it hasn’t been assigned a value.
Once you assign it the value of Hello there! , the variable is no longer empty so the IsEmpty function returns False. It’s been initialized!
Only when you set the variant back to Empty will the IsEmpty command once again return True. That’s what we did right before our 3rd MsgBox.
Now it’s time for the shocker. This doesn’t work for all data types! It only works for the Variant in this example because the default value for a variant is Empty . If we declare the variable as a string, the IsEmpty function will always return False. That’s because the default value for a string is the empty string "" . It comes pre-initialized.
An empty string is not the same as an uninitialized variable. An empty string is NOT considered empty, as defined by the IsEmpty function. Try it yourself:
You can prove the default value is an empty string by right clicking str1 and clicking “Add Watch.” By stepping into your macro (pressing the F8 button once), you can see see the default value is "" . Keep running your macro, and you’ll see all three MsgBoxes return False. VBA is a funny thing, like that.
Final Thoughts
Although it can be used to check whether or not variables have been initialized, the most common use of IsEmpty is to check for blank cells. By learning these examples, you can now perform IsEmpty checks for individual cells, and for entire ranges!
Remember, when applied to a cell, the VBA function IsEmpty is the same as the Excel Worksheet Function ISBLANK. If you’re familiar with using =ISBLANK() in your worksheet cells, learning the IsEmpty VBA function will be a breeze.
I know you want to learn more VBA. To see more VBA tips, tricks and techniques, subscribe using the form below.
Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.
Оператор представляет собой комбинацию ключевых слов, констант, переменных, символов, действий, объектов, свойств и методов.
Язык VBA допускает компоновку одного или нескольких операторов в единый блок, называемый процедурой.
Типами данных называются типы значений, которые могут содержать переменные.
Основные типы данных в VBA
Основные типы данных
Наименование | Размер | Описание |
Byte | 1 байт | Целые числа от 0 до 255 |
Integer | 2 байта | Целые числа от –32768 до 32767 |
Long | 4 байта | Целые числа от –2 147 483648 до 2 147 483 647 |
Single | 4 байта | Вещественные числа одинарной точности |
Double | 8 байт | Вещественные числа двойной точности |
Decimal | 12 байт | Целые без знака, умноженные на степень числа 10 |
Currency | 8 байт | Масштабированные целые с фиксированной точкой |
String (перемен- ной длины) | 10 байт +длина строки | Строка от 0 до 2 млрд. символов |
String (постоянной длины) | Длина строки | Строка от 1 до 65400 символов. Переменная должна быть объявлена Private |
Boolean | 2 байта | Имеет два значения: False и True |
Date | 8 байт | Дата и время |
Object | 4 байта | Хранит адрес любого объекта |
Variant число строка | 16 байт/ 22 байта + длина строки | Универсальный тип данных для числа как Double, для строки- от 0 до 2 млрд. символов |
Преобразование типов данных
В состав Access входит набор функций преобразования типов, которые можно использовать для преобразования строк и числовых выражений к конкретному типу данных.
Функции преобразования данных
Функция | Описание |
Asc(выражение) | Возвращает ANSI-код первой буквы в строке |
Cbool(выражение) | Возвращает False. если значение =0 |
Cbyte(выражение) | Преобразует к типу Byte. |
Ccur(выражение) | Преобразует выражение к типу Currency. |
Cdate(выражение) | Преобразует литералы даты и времени и некоторые числа к типу Date. |
CDbl(выражение) | Преобразует выражение к типу Double. |
CDec(выражение) | Преобразует выражение к типу Decimal. |
CInt(выражение) | Преобразует выражение к типу Integer. |
CLng(выражение) | Преобразует выражение к типу Long. |
CSng(выражение) | Преобразует выражение к типу Single. |
CStr(выражение) | Преобразует выражение к типу String. |
CVar(выражение) | Преобразует выражение к типу Variant. |
CVErr | Преобразует номер ошибки к типу Variant. |
Fix | Отбрасывает дробную часть числа и возвращает ближайшее большее число. |
Int | Отбрасывает дробную часть числа и возвращает ближайшее меньшее число. |
Значения NULL и EMPTY
Данные типа Variant могут приобретать два значения Null и Empty.
Null означает, что данные отсутствуют, неизвестны или неприменимы. Можно определить, содержит ли значение поля или элемент управления значение Null. Для этого можно использовать функцию IsNull.
Значение Empty служит для резервирования памяти под переменную типа Variant, которой не присвоено значение. При создании переменной типа Variant её значение равно Empty до тех пор, пока ей не будет присвоено конкретное значение. Для определения содержания переменной значения Empty служит функция IsEmpty. Значение Empty -это не нуль, не строка нулевой длины и не Null, но так VBA автоматически преобразует переменные типа Variant к типу, необходимому для дальнейшей обработки; значение Empty преобразуется в нуль, если требуется числовое значение, и в строку нулевой длины, если требуется строковое значение.
to iterate through columns (with Double/Integer values) in Excel. Then I found a case where the test evaluates to False whenever the value of the cell is 0. I have no idea what the difference is between this case and the working ones.
If I change the test to:
it works fine. So my question is: What is the difference between how IsEmpty() and =Empty is evaluated? In what cases will =Empty evaluate to True for cells with value 0?
Range("A2")=0 ?Range("A2")=Empty True ?IsEmpty(range("A2")) False I'd like to see a case where a cell value of 0 = Empty returns False!
You are right. I see now that the series I have looked at before do not contain zeros (I just assumed they did), so the behaviour is consistent.
3 Answers 3
Empty refers to a variable being at its default value. So if you check if a cell with a value of 0 = Empty then it would return true.
IsEmpty refers to no value being initialized.
In a nutshell, if you want to see if a cell is empty (as in nothing exists in its value) then use IsEmpty . If you want to see if something is currently in its default value then use Empty .
Yes use IsEmpty to see if a cell is empty. But Empty is just a constant with a value of zero: it does not refer to a variable being at its default value: its a shortcut to to vbEmpty which is part of an Enum and has a value of zero. The default value of an Excel cell is that it has no value (its IsEmpty): if you assign this to a numeric variable it gets converted to zero.
@Jean-Francois: you are correct: from VBA Help - The Empty keyword is used as a Variant subtype. It indicates an uninitialized Variant variable value.
From the Help:
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable.
IsEmpty only returns meaningful information for variants.
To check if a cell is empty, you can use cell(x,y) = "" .
You might eventually save time by using Range("X:Y").SpecialCells(xlCellTypeBlanks) or xlCellTypeConstants or xlCellTypeFormulas
cell(x,y) = "" will return True for a non-empty cell containing ="" . Thus it is more consistent to use IsEmpty(cell(x,y)) . BTW cell(x,y) returns a Variant, so it's a textbook case for IsEmpty.
I believe IsEmpty is just method that takes return value of Cell and checks if its Empty so: IsEmpty(.Cell(i,1)) does ->
Unfortunately, this cannot be the case on my system. When I do Do While Not IsEmpty(Sheets("Results").Cells(i, 1)) Sheets("Results").Cells(i, 2).Value = (Sheets("Results").Cells(i, 1) <> Empty) Loop I get an adjacent column of booleans, where 0 corresponds to False, while other values corresponds to True.
Not IsEmpty() should be logically equivalent to <>Empty, don't you think? I.e. if the <>Empty statement ever evaluates to false, then the loop with the Not IsEmpty() condition should terminate.
IsEmpty() = False is logically identical to Not IsEmpty() , so there is no point switching from the latter to the former.
By J.A. Gomez
In this VBA Tutorial, you learn how to check if a cell or range is empty.
This VBA Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Use the following Table of Contents to navigate to the section you're interested in.
Table of Contents
Related VBA and Macro Tutorials
The following VBA and Macro Tutorials may help you better understand and implement the contents below:
-
Learn about commonly-used VBA terms here.
You can find additional VBA and Macro Tutorials in the Archives.
VBA Code to Check if Cell is Empty
Process Followed by VBA Code to Check if Cell is Empty
VBA Statement Explanation
-
VBA Construct: Opening statement of If… Then… Else statement.
-
The If… Then… Else statement tests the specified condition (IsEmpty(Cell)).
-
VBA Construct: IsEmpty function.
The IsEmpty function:
-
Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (Cell).
You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties. If you explicitly declare an object variable to represent Cell, use the Range object data type.
-
VBA Construct: Condition of If… Then… Else statement.
- Item: StatementsIfCellIsEmpty.
-
VBA Construct: Statements within If… Then… Else statement.
-
VBA Construct: Else clause of If… Then… Else statement.
- Item: StatementsIfCellIsNotEmpty.
-
VBA Construct: Else Statements within If… Then… Else statement.
-
VBA Construct: Closing statement of If… Then… Else statement.
Macro Example to Check if Cell is Empty
The following macro example checks if cell A5 of the worksheet named “Check if Cell is Empty” (myCell) is empty and displays a message box confirming whether the cell is empty or not empty.
Effects of Executing Macro Example to Check if Cell is Empty
The following GIF illustrates the results of executing the macro example. Cell A5 (This cell isn't empty) is not empty and the message box displayed confirms that this is the case.
VBA Code to Check if Active Cell is Empty
Process Followed by VBA Code to Check if Active Cell is Empty
VBA Statement Explanation
-
VBA Construct: Opening statement of If… Then… Else statement.
-
The If… Then… Else statement tests the specified condition (IsEmpty(ActiveCell)).
-
VBA Construct: IsEmpty function.
The IsEmpty function:
-
Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (ActiveCell).
-
VBA Construct: Application.ActiveCell property.
-
VBA Construct: Condition of If… Then… Else statement.
- Item: StatementsIfActiveCellIsEmpty.
-
VBA Construct: Statements within If… Then… Else statement.
-
VBA Construct: Else clause of If… Then… Else statement.
- Item: StatementsIfActiveCellIsNotEmpty.
-
VBA Construct: Else Statements within If… Then… Else statement.
-
VBA Construct: Closing statement of If… Then… Else statement.
Macro Example to Check if Active Cell is Empty
The following macro example checks if the active cell is empty and displays a message box confirming whether the active cell is empty or not empty.
Effects of Executing Macro Example to Check if Active Cell is Empty
The following GIF illustrates the results of executing the macro example. The active cell (A6) is empty and the message box displayed confirms that this is the case.
VBA Code to Check if Range is Empty
Process Followed by VBA Code to Check if Range is Empty
VBA Statement Explanation
-
VBA Construct: Opening statement of If… Then… Else statement.
-
The If… Then… Else statement tests the specified condition (WorksheetFunction.CountA(CellRange) = 0).
-
VBA Construct: WorksheetFunction.CountA method.
You can usually return a Range object with constructs such as the Worksheet.Range property. If you explicitly declare an object variable to represent CellRange, use the Range object data type.
-
VBA Construct: = comparison operator.
-
The expression to the left of the = comparison operator (WorksheetFunction.CountA(CellRange)).
-
VBA Construct: Condition of If… Then… Else statement.
-
True: If WorksheetFunction.CountA(CellRange) returns 0. This occurs when CellRange is empty.
- Item: StatementsIfRangeIsEmpty.
-
VBA Construct: Statements within If… Then… Else statement.
-
VBA Construct: Else clause of If… Then… Else statement.
- Item: StatementsIfRangeIsNotEmpty.
-
VBA Construct: Else Statements within If… Then… Else statement.
-
VBA Construct: Closing statement of If… Then… Else statement.
Macro Example to Check if Range is Empty
The following macro example checks if the range composed of cells A7 through A11 of the worksheet named “Check if Cell is Empty” (myCellRange) is empty and displays a message box confirming whether the range is empty or not empty.
Effects of Executing Macro Example to Check if Range is Empty
The following GIF illustrates the results of executing the macro example. Cells A7 through A11 (with fill) are empty and the message box displayed confirms that this is the case.
VBA Code to Check if Any Cell in Range is Empty
Process Followed by VBA Code to Check if Any Cell in Range is Empty
VBA Statement Explanation
-
VBA Construct: Opening statement of If… Then… Else statement.
-
The If… Then… Else statement tests the specified condition (WorksheetFunction.CountA(CellRange) < CellRange.Count).
-
VBA Construct: WorksheetFunction.CountA method.
You can usually return a Range object with constructs such as the Worksheet.Range property. If you explicitly declare an object variable to represent CellRange, use the Range object data type.
-
VBA Construct: Range.Count property.
-
VBA Construct: Condition of If… Then… Else statement.
-
True: If WorksheetFunction.CountA(CellRange) returns a value smaller than the value returned by CellRange.Count. Within this macro structure, this occurs when (i) the number of non-empty cells in CellRange (returned by WorksheetFunction.CountA(CellRange)) (ii) is less than (iii) the number of cells in CellRange (returned by CellRange.Count). This occurs when CellRange contains at least 1 empty cell.
- Item: StatementsIfAnyCellInRangeIsEmpty.
-
VBA Construct: Statements within If… Then… Else statement.
-
VBA Construct: Else clause of If… Then… Else statement.
- Item: StatementsIfNoCellInRangeIsEmpty.
-
VBA Construct: Else Statements within If… Then… Else statement.
-
VBA Construct: Closing statement of If… Then… Else statement.
Macro Example to Check if Any Cell in Range is Empty
The following macro example checks if the range composed of cells A13 through A17 of the worksheet named “Check if Cell is Empty” (myCellRange) contains any empty cells and displays a message box confirming whether the range contains or not any empty cells.
Effects of Executing Macro Example to Check if Any Cell in Range is Empty
The following GIF illustrates the results of executing the macro example. Cell A15 is empty. The message box displayed confirms that the cell range containing cells A13 to A17 (with fill) contains at least one empty cell (A15).
References to VBA Constructs Used in this VBA Tutorial
Use the following links to visit the appropriate webpage within the Microsoft Developer Network:
In excel vba we often refer to an Empty variable, ZLS (zero-length string) or null string or vbNullString, Null value, Missing Argument, or using the Nothing keyword with an object variable. It is important to differentiate and understand these terms and expressions while using them in your vba code. In this section, we will also understand using the VarType Function to determine the subtype of a variable, using the IsEmpty & IsNull Functions to check for Empty & Null values, and using the IsMissing Function to check whether optional arguments have been passed in the procedure or not.
Empty
When you declare a variable in your code using a Dim statement, you are setting aside sufficient memory for the variable (viz. 2 bytes for a Boolean or Integer variable, 4 bytes for a Long variable, and so on), and that the information being stored in the variable has an allowable range (of True or False for a Boolean variable, a whole number between -32,768 to 32,767 for an Integer variable, a whole number between -2,147,483,648 to 2,147,483,647 for a variable subtype of Long, and so on). You will receive a run-time error if trying to assign a string value to a variable declared as Integer.
While declaring a variable if you do not specify its data type, or if you do not declare a variable at all it will default to Variant data type that can hold any type of data (string, date, time, Boolean, or numeric values) & can automatically convert the values that it contains. However, the disadvantage is that this makes Excel reserve more memory than is required (at least 16 bytes), and could also result in mistyping a variable name and not knowing it viz. you might type rowNumbre instead of rowNumber.
When you run a macro, all variables are initialized to a default value. The initial default value: for a numeric variable is zero; for a variable length string it is a zero-length or empty string (""); a fixed length string is initialized with the ASCII code 0, or Chr(0); an object variable defaults to Nothing; a Variant variable is initialized to Empty. In numeric context, an Empty variable denotes a zero while in a string context an Empty variable is a zero-length string ("") . A zero-length string ("") is also referred to as a null string. However, it is advised to explicitly specify an initial value for a variable instead of relying on its default initial value.
Empty indicates that no beginning value has been assigned to a Variant variable ie. a variable which has not been initialized. An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context. Empty is not the same as Null which indicates that a variable contains no valid data.
The Empty keyword indicates an uninitialized variable value. It is used as a Variant subtype. You can assign the Empty keyword to explicitly set a variable to Empty.
IsEmpty Function
Use the IsEmpty Function to check whether a variable has been initialized. The function returns a Boolean value - returns True for an uninitialized variable or if a variable is explicitly set to Empty, otherwise the function returns False. Syntax: IsEmpty(expression), where expression is a Variant variable which you want to check. See below example(s) where we use this function to check if a variant variable is empty.
Empty, Blank, ZLS (zero-length string), null string & vbNullString
ZLS means a zero-length string (""), is also referred to as a null string, and has a length of zero (0). For all practical purposes using vbNullString is equivalent to a zero-length string ("") because VBA interprets both in a similar manner, though both are actually not the same - a 'zero length string' actually means creating a string with no characters, whereas vbNullString is a constant used for a null pointer meaning that no string is created and is also more efficient or faster to execute than ZLS. You can use "" or vbNullString alternatively in your code and both behave similarly. Note that there is no Blank keyword in vba, but we can refer to 'blank cells' or "empty cells" in Excel spreadsheet. There are Excel worksheet functions for empty cells: (i) the COUNTA function counts the number of cells that are not empty, and also counts or includes a cell with empty text ("") - also referrred to as empty string or zero length string - which is not counted as an empty cell; and (ii) the ISBLANK function returns True for an empty cell, and does not treat a zero-length string ("") as a blank (empty cell) similarly as in COUNTA. Both the worksheet functions of ISBLANK and COUNTA distinguish between an empty cell and a cell containing a zero-length string (ie. "" as formula result).
VarType Function
Example - Empty variable:
Sub EmptyVar()
'Empty variable
'variable var1 has not been declared, hence it is a Variant data type:
'returns 0, indicating variable subtype Empty:
'returns True, indicating variable subtype Empty:
'returns False - is an Empty variable, not a Null variable - no beginning value has been assigned to a Variant variable:
'Empty indicates a Variant variable for which you do not explicity specify an initial value, which by default gets initialized in VBA to a value that is represented as both a zero and a zero-length string.
'returns both messages as below:
If var1 = 0 Then
MsgBox "Empty Variable represented as Zero"
MsgBox "Empty Variable represented as a Zero-Length (Null) String"
End Sub
Example - Testing for Empty:
Sub EmptyCheck()
'testing for Empty
Dim var1 As Variant
'variable not initialized - returns 0, indicating variable subtype Empty:
'returns True, indicating variable subtype Empty:
'initialize the variable by specifying a string value:
'returns 8, indicating variable subtype String:
'returns False, indicating variable is not Empty:
'assign Empty keyword to set variable to Empty:
'returns 0, indicating variable subtype Empty:
'returns True, indicating variable is Empty:
'returns True for an empty worksheet cell, otherwise False:
End Sub
Example - Initialize a Variant variable:
Sub VarInitialized()
'initialized variable
Dim var1 As Variant
'variable has been initialized to a zero-length string (""):
'returns False, indicating variable is NOT Empty:
'returns 8, indicating variable subtype String:
'returns - "Variable value is a Zero-Length String"
MsgBox "Variable value is a Zero-Length String"
MsgBox "Variable value is NOT a Zero-Length String"
'returns - "Variable value is NOT Zero"
If var1 = 0 Then
MsgBox "Variable value is Zero"
MsgBox "Variable value is NOT Zero"
End Sub
Example - Check a zero-length string:
Sub CheckZLS()
'check a zero-length string
Dim var1 As Variant
'variable not initialized - returns 0, indicating variable subtype Empty - represented both as Zero (0) and a Zero-Length (Null) String:
'returns "True" for all If statements below:
If var1 = vbNullString Then
If Len(var1) = 0 Then
End Sub
Null
In VBA, Null keyword is used to indicate that a variable contains no valid data. A value indicating that a variable contains no valid data. Null is the result - (i) if you explicitly assign Null to a variable, or (ii) if you perform any operation between expressions that contain Null. The Null keyword is used as a Variant subtype ie. only a Variant variable can be Null, and and variable of any other subtype will give an error. Null is not the same as a zero-length string (""), and neither is Null the same as Empty, which indicates that a variable has not yet been initialized.
If you try to get the value of a Null variable or an expression that is Null, you will get an error of 'Invalid use of Null' (Run-time Error 94). You will need to ensure the variable contains a valid value. Refer Image 1.
IsNull Function
The IsNull Function returns a Boolean value - True for an expression that is Null (containing no valid data), or else False for an expression that contains valid data. Syntax: IsNull(expression). The expression argument is a variant that contains a numeric or string value, and is necessary to specify.
Example - Integer variable:
Sub VarInteger()
'no beginning value assigned to a variable of subtype Integer
Dim intVar As Integer
'returns False (intVar is not Null & neither is it Empty) - no beginning value has been assigned to a variable of subtype Integer:
'returns 2, indicating variable subtype Integer:
'returns - "Variable value is Zero" (The initial default value for a numeric variable is zero)
If intVar = 0 Then
MsgBox "Variable value is Zero"
MsgBox "Variable value is NOT Zero"
End Sub
Example - Evaluate Empty / Null variable, use IsNull & VarType vba functions:
Sub EmptyNullVar()
'evaluate Empty / Null variable, use IsNull & VarType vba functions.
Dim var1 As Variant
'returns False, var1 is not Null but an Empty variable (no beginning value has been assigned to a Variant variable):
'variable not initialized - returns 0, indicating variable subtype Empty:
'returns the message because var1 is an Empty variable:
If var1 = 0 And var1 = vbNullString Then
MsgBox "Empty Variable represented both as Zero (0) and a Zero-Length (Null) String"
'variable is initialized to a zero-length string ("") or vbNullString:
'returns False - var1 is not a Null variable:
'returns 8, indicating variable subtype String:
'explicitly assigning Null to a variable:
'returns True, for a Null variable, containing no valid data:
'returns 1, indicating variable subtype Null:
'explicitly assigning valid data to a variable:
'returns False, for a variable containing valid data:
'returns 2, indicating variable subtype Integer:
'returns False, for an expression containing valid data:
End Sub
Example - Check a Null variable:
Sub CheckNull()
'check a Null variable
'explicitly assigning Null to a variable:
'returns 1, indicating variable subtype Null:
'returns the message, indicating variable subtype Null:
If VarType(var1) = vbNull Then
MsgBox "Null variable"
'an expression containing Null also evaluates to Null:
'returns 1, indicating variable subtype Null:
End Sub
Example - Check worksheet cell for Empty, ZLS, Null:
Sub WorksheetCell_ZLS_Empty_Null()
'check worksheet cell for Empty, ZLS, Null
Dim var1 As Variant
'In the case where ActiveCell is Blank:
'returns True for a Blank cell:
MsgBox ActiveCell.Value = vbNullString
MsgBox ActiveCell.Value = 0
'assign Active Cell value to variable:
MsgBox var1 = vbNullString
MsgBox VarType(var1) = vbNull
'returns 0, indicating variable subtype Empty:
'If you enter "" in the Active Cell ie. the active cell contains the value: = ""
'returns True:
MsgBox ActiveCell.Value = vbNullString
MsgBox ActiveCell.Value = 0
End Sub
Nothing
Assigning the Nothing keyword to an object variable disassociates the variable from an actual object. Nothing is assigned to an object variable by using the Set statement. You can assign the same actual object to multiple object variables in vba code, and this association uses your system resources and memory. The system resources and memory get released only either after you assign Nothing to all object variables using the Set statement which disassociates these variables from the actual object, or when all object variables go out of scope and get destroyed. It is advisable to explicity set all object variables to Nothing at the end of your procedure or even earlier while running your code when you finish using them, and this will release memory allocated to these variables.
Determine if the object variable is initialized - use Is Nothing for objects: To check if an object has been assigned or set, use the Is keyword with Nothing, viz. If object_variable Is Nothing. For objects, you cannot test if an object_variable is equal to something, and using = instead of Is will give an error.
Example - Using the Nothing keyword with an object variable:
Sub ObjNothing()
'using the Nothing keyword with an object variable
Dim objVar As Object
'returns True, because you have not yet assigned an actual object to the object variable:
MsgBox objVar Is Nothing
Set objVar = ActiveSheet
'returns False, because you have assigned an actual object (Sheet) to the object variable:
MsgBox objVar Is Nothing
Set objVar = Nothing
'returns "Variable not associated with an actual object", because you have disassociated the object variable from an actual object:
If objVar Is Nothing Then
MsgBox "Variable not associated with an actual object"
MsgBox "Actual object is assigned to an Object variable"
End Sub
Missing
Passing Arguments to Procedures: When an external value is to be used by a procedure to perform an action, it is passed to the procedure by variables. These variables which are passed to a procedure are called arguments. An argument is the value supplied by the calling code to a procedure when it is called. When the set of parentheses, after the procedure name in the Sub or Function declaration statement, is empty, it is a case when the procedure does not receive arguments. However, when arguments are passed to a procedure from other procedures, then these are listed or declared between the parentheses.
Optional Arguments: Arguments can be specified as Optional by using the Optional keyword before the argument to its left. When you specify an argument as Optional, all other arguments following that argument to its right must
also be specified as Optional. Note that specifying the Optional keyword makes an argument optional otherwise the argument will be required.
Check if an argument is Missing, using the IsMissing function: The Optional argument should be (though not necessary) declared as Variant data type to enable use of the IsMissing function which works only when used with variables declared as Variant data type. The IsMissing function is used to determine whether the optional argument was passed in the procedure or not and then you can adjust your code accordingly without returning an error. If the Optional argument is not declared as Variant in which case the IsMissing function will not work, the Optional argument will be assigned the default value for its data type which is 0 for numeric data type variables (viz. Integer, Double, etc) and Nothing (a null reference) for String or Object data type variables.
IsMissing function: The IsMissing function is used to check whether optional Variant arguments have been passed in the procedure or not. Syntax: IsMissing(argname). The function returns a Boolean value - True if no value is passed for the optional argument, and False if a value has been passed for the optional argument. If the IsMissing function returns True for an argument, using the missing argument in the code will cause an error, and thus using this function will help in adjusting your code accordingly.
Example of using the IsMissing function to check if an argument is Missing:
Function FullName(strFirstName As String, Optional strSecondName As Variant) As String
'The declaration of the procedure contains two arguments, the second argument is specified as Optional. Declaring the Optional argument as Variant data type will enable use of the IsMissing function.
'The IsMissing function is used to determine whether the optional argument was passed in the procedure, and if not, you can adjust your code accordingly without returning an error.
If IsMissing(strSecondName) Then
FullName = strFirstName & " " & strSecondName
End Function
Sub GetName()
Dim strGivenName As String
strGivenName = InputBox( "Enter Given Name" )
'specifying only the first argument & omitting the second argument which is optional:
Читайте также: