VBA Check if variable is empty

Question

I have an object and within it I want to check if some properties are set to False, like:

If (Not objresult.EOF) Then
  'Some code 
End if

But sometimes, objresult.EOF is Empty; how can I check for this?

IsEmpty function is for excel cells only
objresult.EOF Is Nothing - returns Empty
objresult.EOF <> null - returns Empty as well!

Please provide some real code - show us what type of object is objresult. And IsEmpty is not specific for excel cells, it is for Variant variables. — Doc Brown
– Doc Brown, Commented Jul 25, 2010 at 12:21

Matt Handy · Accepted Answer · 2013-04-26 11:21:05Z

112

How you test depends on the Property's DataType:

| Type                                 | Test                            | Test2
| Numeric (Long, Integer, Double etc.) | If obj.Property = 0 Then        | 
| Boolen (True/False)                  | If Not obj.Property Then        | If obj.Property = False Then
| Object                               | If obj.Property Is Nothing Then |
| String                               | If obj.Property = "" Then       | If LenB(obj.Property) = 0 Then
| Variant                              | If obj.Property = Empty Then    |

You can tell the DataType by pressing F2 to launch the Object Browser and looking up the Object. Another way would be to just use the TypeName function:MsgBox TypeName(obj.Property)

edited Apr 26, 2013 at 11:21

Matt Handy

30.1k3 gold badges91 silver badges116 bronze badges

answered Jul 25, 2010 at 22:13

Oorang

6,7901 gold badge38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hope Over a year ago

obj.Property = Empty works for Numeric values as well.

Axel Over a year ago

I tried it with an unset worksheets variable. A check was only possible like this: If ws Is Nothing Then

rleir Over a year ago

Thanks Axel. This applies to Workbook variables too.

Timo Over a year ago

@Axel did not work for me for a workbook var: if var is empty then it throws a runtime error 424 object needed.

Valentin Despa · Accepted Answer · 2013-12-11 11:33:59Z

23

To check if a Variant is Null, you need to do it like:

Isnull(myvar) = True

or

Not Isnull(myvar)

answered Dec 11, 2013 at 11:33

Valentin Despa

42.8k18 gold badges85 silver badges108 bronze badges

Comments

deasa · Accepted Answer · 2015-05-20 14:53:29Z

13

For a number, it is tricky because if a numeric cell is empty VBA will assign a default value of 0 to it, so it is hard for your VBA code to tell the difference between an entered zero and a blank numeric cell.

The following check worked for me to see if there was an actual 0 entered into the cell:

If CStr(rng.value) = "0" then
    'your code here'
End If

answered May 20, 2015 at 14:53

deasa

6069 silver badges25 bronze badges

1 Comment

Laurent Over a year ago

Your code sample is a great workaround for distinguishing 0 and Empty

Galanor · Accepted Answer · 2019-04-07 11:19:30Z

-2

I had a similar issue with an integer that could be legitimately assigned 0 in Access VBA. None of the above solutions worked for me.

At first I just used a boolean var and IF statement:

Dim i as integer, bol as boolean
   If bol = false then
      i = ValueIWantToAssign
      bol = True
   End If

In my case, my integer variable assignment was within a for loop and another IF statement, so I ended up using "Exit For" instead as it was more concise.

Like so:

Dim i as integer
ForLoopStart
   If ConditionIsMet Then
      i = ValueIWantToAssign
   Exit For
   End If
ForLoopEnd

answered Apr 7, 2019 at 11:19

Galanor

1

Collectives™ on Stack Overflow

VBA Check if variable is empty

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related