check variable or cell is blank or not

[pmich]

I have posted the following in this forum in the link given below.
Since, SOLVED is mentioned in the caption, I am not getting any reply from Experts. hence I amposting my two queries again here. Please reply.
https://www.mrexcel.com/forum/excel...er-explanation-null-empty-nothing-solved.html
................. .............. .............. .........
Kindly explain the fault in my code given below. Though DOUBLE QUOTES
is assigned to variable, IsEmpty returns FALSE. Why?

Private sub DoubleQuotesChckr
Dim varMyValue As Variant
Dim bEmptyCheck As Boolean
varMyValue = ""
  bEmptyCheck = IsEmpty(varMyValue)
 If varMyValue = "" Then
  MsgBox "Double Quotes is assigned to var." & vbCrLf & _
  "Var value is Double Quotes" & vbCrLf & _
  "Empty status := " & bEmptyCheck
 Else
  MsgBox "Double Quotes is assigned to var." & vbCrLf & _
  "Var value is NOT Double Quotes" & vbCrLf & _
  "Empty status := " & bEmptyCheck
 End If
End Sub

................. .............. .............. .........
Excel Experts, Kindly suggest me which code to use to find whether a cell is blank in an used range.

Private Sub WhichEmptyDoubleQuotesNothingEntered()
 'Which code to use to check if a cell is blank?
  Dim AsoNo As Range
  Dim bEmptyCheck As Boolean
  
  Sheets("Sheet1").Select
  Range("A10").Value = ""
  Set AsoNo = Range("A10")
  If Application.CountA(AsoNo) <> 0 Then
   MsgBox "Range 10 has double quotes.[" & Range("A10").Value & "]" & vbCrLf & _
    "CountA(AsoNo) is not zero = " & Application.CountA(AsoNo)
  Else
   MsgBox "Range 10 has double quotes.[" & Range("A10").Value & "]" & vbCrLf & _
    "CountA(AsoNo) gets zero = " & Application.CountA(AsoNo)
  End If
  
  MsgBox "range a20=[" & Range("a20").Value & "]" 'blank in used range in sheet1


  If Application.CountA(AsoNo) <> 0 Then
   MsgBox "Range 20 is blank in used range.[" & Range("A20").Value & "]" & vbCrLf & _
    "CountA(AsoNo) not zero = " & Application.CountA(AsoNo)
  Else
   MsgBox "Range 20 is blank in used range.[" & Range("A20").Value & "]" & vbCrLf & _
    "CountA(AsoNo) gets zero = " & Application.CountA(AsoNo)
  End If
  
  If Trim(AsoNo.Value) = "" Then 'YES
   MsgBox "IF. AsoNo = Range 10 is double quotes.[" & Range("A10").Value & "]"
  Else
   MsgBox "ELSE. AsoNo = Range 10 is double quotes.[" & Range("A10").Value & "]"
  End If
       
  If Trim(Range("a20").Value) = "" Then 'YES
   MsgBox "IF. Range 20 is not used.[" & Range("A20").Value & "]"
  Else
   MsgBox "ELSE. Range 20 is not used.[" & Range("A20").Value & "]"
  End If
       
  Range("A10").Value = ""
  bEmptyCheck = IsEmpty(Range("A10").Value)
  If bEmptyCheck = True Then 'YES
   MsgBox "Range is double quotes. So, blank." & vbCrLf & _
    "Empty status := " & bEmptyCheck
   bEmptyCheck = False
  Else
   MsgBox "Range is double quotes. So, not blank." & vbCrLf & _
    "Empty status := " & bEmptyCheck
  End If
 
  Range("A10").Value = ""
  Range("A10").Value = Trim(Range("A10").Value)
  bEmptyCheck = IsEmpty(Range("A10").Value)
  If bEmptyCheck = True Then 'YES
   MsgBox "Range has double quotes & also Trimmed.[" & _
    Range("A10").Value & "]" & vbCrLf & _
    "IsEmpty is TRUE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
   bEmptyCheck = False
  Else
   MsgBox "Range has double quotes & Trimmed.[" & _
    Range("A10").Value & "]" & vbCrLf & _
    "IsEmpty is FALSE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
  End If
  
  AsoNo.Value = ""
  AsoNo.Value = Trim(AsoNo.Value)
  bEmptyCheck = IsEmpty(AsoNo.Value)
  If bEmptyCheck = True Then 'YES
   MsgBox "Range has double quotes & also Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is TRUE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
   bEmptyCheck = False
  Else
   MsgBox "Range has double quotes & Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is FALSE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
  End If
  
  AsoNo.Value = "TEXT "
  AsoNo.Value = Trim(AsoNo.Value)
  bEmptyCheck = IsEmpty(AsoNo.Value)
  If bEmptyCheck = True Then 'YES
   MsgBox "Range has a string & also Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is TRUE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
   bEmptyCheck = False
  Else
   MsgBox "Range has a string & Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is FALSE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
  End If
    
  AsoNo.Value = "TEXT "
  bEmptyCheck = IsEmpty(Trim(AsoNo.Value))
  If bEmptyCheck = True Then 'YES
   MsgBox "Range has a string & also Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is TRUE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
   bEmptyCheck = False
  Else
   MsgBox "Range has a string & Trimmed.[" & _
    AsoNo.Value & "]" & vbCrLf & _
    "IsEmpty is FALSE." & vbCrLf & _
    "Empty status := " & bEmptyCheck
  End If
End Sub

Thanks in advance for the suggestions of any expert.

 

[pgc01]

Kindly explain the fault in my code given below. Though DOUBLE QUOTES
is assigned to variable, IsEmpty returns FALSE. Why?

...
Dim varMyValue As Variant
Dim bEmptyCheck As Boolean
varMyValue = ""
bEmptyCheck = IsEmpty(varMyValue)
...

Hi

The IsEmpty() returns False because you just assigned it a text value, in this case an empty string.

The IsEmpty() will only return True when, like the name of the function says, the variable is empty, it has no value assigned.

For ex., when you declare a Variant variable the default value in Emtpy, like

...
Dim varMyValue As Variant
Dim bEmptyCheck As Boolean

bEmptyCheck = IsEmpty(varMyValue)
...

Or, if the variable has some value and you want to clear it

...
Dim varMyValue As Variant
Dim bEmptyCheck As Boolean

varMyValue = "Some value"

' ... other code

varMyValue = Empty
bEmptyCheck = IsEmpty(varMyValue)
...

 

[pmich]

Thanks, pgc01, for making it clear that double quotes is treated as string.
I saw the usage assigning 'EMPTY' to variable in the link that I have given here. But. I was not sure where and how to use it. You have suggested to use it to clear the value in a variable. Thanks.
I am waiting for suggestion on my second query regarding which code to use in VB to know whether a cell is empty.

 

[pgc01]

I am waiting for suggestion on my second query regarding which code to use in VB to know whether a cell is empty.

To see if a cell is empty try checking the cell value, like:

'...
Dim bEmptyCheck As Boolean

bEmptyCheck = IsEmpty(Range("A1").Value)

'...

 

[pmich]

Thank you for the simple solution.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[pmich]

On the net I searched and found that COUNTA is WorkSheet Function.
But I am able to use it in VB.
Similarly I found that ISBLANK is also WorkSheet Function.
But I am unable to use it in VB.
Kindly ghuide me.

Dim AsoNo As Range
Sheets("Sheet1").Select
Set AsoNo = Range("A10")
AsoNo.Value = ""
Range("A11").Value = " "
 MsgBox "Range 10 has double quotes.[" & Range("A10").Value & "]" & vbCrLf & _
  "CountA(AsoNo) is zero = " & Application.CountA(AsoNo)
MsgBox "Range 11 has one space.[" & Range("A11").Value & "]" & vbCrLf & _
  "CountA(Range(A11).Value) is not zero = " & Application.CountA(Range("A11").Value)
MsgBox "IsBlank(Range(A11)) is not zero = " & Application.IsBlank(Range("A11").Value)

 

[pgc01]

Check the vba help for the object WorksheetFunction object and click on WorksheetFunction object members. You'll get a list of all its methods.

You can also use evaluate() to execute a worksheet function.

 

[pmich]

Oh my God. There are so many object members. Only today I saw that list. Thanks for directing me there.
It will surely take a long period for me to learn most of the objects.
Even without knowing them, with whatever I know I could write a programme of about 7000 lines of code for my friend's office to retrieve data from his database. I am working on data entry portion of it.
Moreover I am providing some solutions in this forum to the enquirers. Though this is a help to them but it is actually increasing my skills in VB.
Thanks for suggesting EVALUATE. I will try.
I like this forum. One expert directs me to one area in VB and another expert takes me to another area.
Quite interesting and also useful.
Thanks once again for your suggestions.
By the way, I have some useful codes, such as checking whether EMAIL ID is correct, Label Printing etc., Can you suggest me a THREAD in this forum to post such codes. Or if there is such a thread in some other Forum, if suggesting that is permitted by this forum, can you suggest me. If this query is irrelevant to this thread, sorry and please ignore my request.
I dont want my name to be blocked by this forum. I want to continue here. Thanks.