The Excel IsEmptyFunction is used to determine whether a variable has ever been initialized i.e. has a value ever been assigned to the variable.
It is important to remember that the
IsEmptyExcel Function is only effective to use on Variant variables. The reason for that is that other variables already have a default value assigned on variable declaration. Now. let us look at an example.
Dim VariableToEvaluate As Variant
If IsEmpty(VariableToEvaluate) Then MsgBox “Message 1: Correct no value has yet been assigned to variable VariableToEvaluate”
VariableToEvaluate = “Test value”
If IsEmpty(VariableToEvaluate) Then MsgBox “Message 2: Correct no value has yet been assigned to variable VariableToEvaluate”
An effective way to evaluate if a value has been assigned to any variable is to use the following command len(trim(Variable))>0. No matter what type of variable is used if it contains a value this function will identify it.
Dim StringToProcess As String’Variable string to process
Dim arrValues() As String’String Array
Dim A, B As Long’Value to use as a counter
Dim tmpValue As Variant
Dim Check As Boolean
StringToProcess = ActiveSheet.Cells(2, 1).Value ‘Assign the information to process to our variable
arrValues() = Split(StringToProcess, ” “) ‘Parse the string into a string array using a space delimiter
Counter = 5
For A = LBound(arrValues) To UBound(arrValues)
Check = IsNumeric(arrValues(A))
If Check Then
ActiveSheet.Cells(Counter, 1).Value = CDbl(arrValues(A))
Counter = Counter + 1