Variant - Empty vs vbNullstring vs Nothing

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
I am running into an intricate problem to do with that apparently an empty Variant is not the same as one filled with vbNullstring and neither are the same (logically) as one set to Nothing

I have a variant which i set to a range in the worksheet.
When looking at the variant in the Watch window, then the empty cells in the sheet show up as 'Empty' in the Watch window

I have a test somewhere in my code which checks if a value in a column (of the array vProj) is larger than some variable:
Code:
               If vProj(lSnxt, 4) > iRL Then Exit For

This works fine until I insert a row in my array.
I do this with a modification of one of Chip Pearson's array functions, by adding a row at the bottom of the array and then moving all the rows from the insertion point down. The inserted row values I then set to vbNullstring.

Now the above code doesn't work anymore because (for some reason) if vProj(lSnxt, 4) contains vbNullstring the comparison is TRUE.

I could do something like
Code:
               If CInt(vProj(lSnxt, 4)) > iRL Then Exit For
This works with vbNullstring but not with 'Empty' !

Anybody have a solution to this? I guess I could use a double if:
Code:
  if isnumeric(Myvariant) then
           if Myvariant > i then
but it becomes kind of dirty.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
OK, I found a solution after some digging on a thread about Empty and isEmpty():
apparently you can set a variant to a special value 'Empty' which means it is not allocated a value (different from setting it to Nothing, which is the same as saying that the variant is an unallocated Object)
So in my function to insert a row in the array, I set each of the values to 'Empty':
Code:
    For j = LB2 To UB2
        InputArr(lRow, j) = Empty
    Next j

Now the simple check works.
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
A Variant can also have a value of Null, which apparently is different again.

a variant having value Null or Nothing has some implications:

When you are trying to use the worksheet function Transpose on an array where one or more values are either Null or Nothing, then VBA will complain that it cannot do this.
Code:
        OutputArr = Application.WorksheetFunction.Transpose(InputArr)

I have had a few instances of this, for example when using an inline if (IIf) together with a Format() - see this thread: http://www.mrexcel.com/forum/excel-questions/749102-strange-behaviour-iif-inline-if.html

happy debugging!
 

Forum statistics

Threads
1,136,354
Messages
5,675,301
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top