Empty array element - test not consistent.

[Arkadi]

Hi all,

This is more a search for an explanation than a solution... though the explanation may help me avoid having to do multiple tests.

The basic problem is this:
-I read some Excel worksheet data into an array, which is 2 dimensional. Since I use it to update some tables in an Access table. I also update another software. I won't bore you too much with the details.
-Then need to test the 2nd and 3rd columns to see if there was data in the cell, since the code takes different routes depending on whether there is a value specified.
-I have to make sure the data is not just a space, so I also trim the value... my test looks like this:

Please Login or Register  to view this content.

This works... when the cell has no value, the array element returns empty, and all is well. Similar checks are performed on (i,1) and (i,2) along the way, but then later, the EXACT SAME test fails to detect the element is empty. I never modify the array in my code at all, yet a few lines of code further down, on the same iteration of the i-loop, rolls_array(i,3) is neither empty nor contains a null string, instead the element is NULL. So suddently I have to test for that too:

Please Login or Register  to view this content.

Not the end of the world, but it seems like something odd is going on. I realize I can work around this either by the null test, or just checking once at the start and using some boolean variables to control the flow, but most of all I wonder if anyone would know why an empty array element would return null later on, without modification of any kind?

TIA for any insight!

Arkadi

[Arkadi]

Bump... no thoughts on this at all? If not then I'll just close this.

[xlnitwit]

I have to say that I have never seen that happen. It's actually quite rare to get a Null with Excel, unless you try and apply a property that doesn't return an array to a multiple cell range.

[Arkadi]

Thanks xlnitwit... I know.. this is so strange, especially because the array is not manipulated in any way by the code... I read sheet data into the array for faster access to the info, and only use it to check values.
Anyway, appreciate your reply!

[xlnitwit]

Out of interest, can you post the code? And how did you determine the value was Null? (were there any Watches involved?)

[Arkadi]

Yes I put a watch on it, and also stepped through, cursor over the element usually pops up the value. Yeah I can post the code:
Note: At line 480 I need to check for NULL because that what it returns at that point... but prior to that it is not NULL, it is "Empty"

Please Login or Register  to view this content.

[xlnitwit]

I can't see how that could happen unless your watch accidentally altered the value, which is unlikely. Does it happen regularly, without any watches set?

I do note that your line 170 is unnecessary, but that's not related!

[Arkadi]

I do note that your line 170 is unnecessary, but that's not related!

Point taken! I had originally not Dimmed it as a simple variant, and forgot to remove that line, thanks

As for "does it happen regularly?" Yes, every iteration of the loop... I noticed that even without a DOM specified on the sheet, which is what that array element refers to, the code would still put in DOM followed by blank when updating the "description". (line 490 was being executed that is) That is how I found that at that check, yet not before, IsEmpty no longer worked because it had become NULL.

I know the code is a bit messy... and there are some redundant checks, but given this is used to track lot numbers for our raw materials, I would rather over-validate than have errors. 2 different systems are being updated by this workbook, which is used at the receiving stage. Our MRP system does not support lot numbers, and our manufacturing requires it. To build systems that control production inputs, I decided to build a database to track the numbers, which I can then use to validate raw material entries.

[xlnitwit]

What kind of watch did you put on it- one to detect a change in value?

[Arkadi]

simply selected the array element, right-click, add watch. Then put stops in the code at the various checks and checked the watches window to see what it returned. Initially I didn't even have a watch, I just hovered the mouse over the array element.

[xlnitwit]

Do you not have an issue with line 420 trying to insert ## into your (presumably) date field?

[Arkadi]

Funny you should ask... no because what that line does is check if the array element we are discussing is empty. If it is, it inserts NULL, if it is not, then it inserts the date value. BUT! It is that line specifically, where rolls_array(i,3) changes to NULL from EMPTY (assuming it is empty to begin with of course), if there was a date on the sheet in that column, then there are no problems at all.

Looks like somehow the IIf statement is triggering the change.

[xlnitwit]

Is it null prior to line 430 executing? There is nothing in either part of the IIf (since both parts are always executed) that ought to change anything in the array.

[Arkadi]

I fully agree, it is not logical, hence my frustration/confusion.
But before line 430 comes into context the watch goes from Empty to NULL. I even put in a random if statement so that 430 was not even highlighting as the "next" line when I use F8 to step through the code.

[xlnitwit]

This is very odd- it's the Format function that is doing it. Change the Format part of line 420 to this to fix it

Please Login or Register  to view this content.

[Arkadi]

Weird! Just an extra set of brackets... which on the face of it are redundant... thanks! Oddly the IIf statement in the empty scenario should not even get to the Format stage... but as you said, that fixes it. Wouldn't have thought to try that...THANKS!!!

[xlnitwit]

IIf always evaluates both the True and False parts, which is one reason I tend to avoid it.

It seems that the variable is passed ByRef to Format and it converts Empty to Null for its internal processing. Adding the extra parentheses creates a copy of the variable to pass to the Format function, so the original is unaffected.

[Arkadi]

Fantastic! Glad I learned something new today
Your help is very much appreciated xlnitwit, once I have added rep to someone else, I can give you another for your solution

[xlnitwit]

Glad I learned something new today

Same here! I had never come across that issue before in all the years I've been using VBA.