VBA: Output "blank" from empty array element rather than zero

Question

I'm currently handling a lot of data with arrays in Excel VBA. Until now source data containing blank cells have been treated as zero, but now I'll have to try and distinguish between blank cells and cells with zeroes in them.

Is it possible to make the array output nothing rather than zero? Such that...

aArray(5) as double
Range("A1:E5") = aArray()

Would only output values in the cells of the range that correspond to array elements that have been filled with something? If the array is empty I'd still get [0,0,0,0,0]

You have declared aArray as Double so empty gets converted to zero. You should declare it as Variant. — Tony Dallimore
– Tony Dallimore, Commented Apr 13, 2012 at 9:13

Tony Dallimore · Accepted Answer · 2012-04-13 13:53:32Z

3

Converted from comment as kindly suggested by psubsee2003.

You have declared aArray as Double so empty elements gets converted to zero. You should declare it as Variant.

answered Apr 13, 2012 at 13:53

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

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

3 Comments

psubsee2003 Over a year ago

I was actually typing this exact answer until I saw your comment. As much as people hate the lack of type safety of Variant, it is the best way to handle some of the features of excel, specifically identifying 0 and blank fields as being different.

beerskij Over a year ago

Wow, it didn't even cross my mind to use the variant type since I try to avoid it as much as possible. But it's the obvious answer in this situation. Thanks a lot!

Tony Dallimore Over a year ago

You are right to avoid data type Variant; there is a overhead with something that can be an integer then a string then an array and there is no type protection. However, a cell has almost as much flexibility as a variant and it is the only data type to which any cell can be transferred without error or lost. You can use function VarType to tell you what data type a Variant is holding.

Collectives™ on Stack Overflow

VBA: Output "blank" from empty array element rather than zero

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related