Variant - Empty vs vbNullstring vs Nothing

[sijpie]

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:

               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

               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:

  if isnumeric(Myvariant) then
           if Myvariant > i then

but it becomes kind of dirty.

 

[sijpie]

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':

    For j = LB2 To UB2
        InputArr(lRow, j) = Empty
    Next j

Now the simple check works.

 

[sijpie]

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.

        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!