VBA Gets Empty Value from Not-Empy Value Cell

[BrianAll]

Hi,

sounds really weird.
Please see attachment.
If I read the cell value troough VBA the variable aaa results Empty while it has a specific value (7AJF138.T250).
The weird thing is that while aaa is Empty, if i I print it with the Msg Box, it shows the right value.

How do I fix it?

Thanks!

[TMS]

Please post the code in the thread. I can't see it on an iPad.

[6StringJazzer]

the variable aaa results Empty

Can you be more specific what you mean by this? When I use IsEmpty it returns False (see below). However, when I mouseover aaa during execution it just says

aaa=

That is very strange because if it were the null string or unassigned it would be

aaa=""

I checked the character string you are using and it is all just printable ASCII characters, no control characters. But it seems to be specific to your character string. When I put anything else in the same cell it works normally.

Everything else in the code seems to be OK. I modified your code to diagnose:

Please Login or Register  to view this content.

and got these results, which indicate nothing unusual:

Please Login or Register  to view this content.

Then I tried removing one character at a time. These two caused no problems, but all the other ones still had the problem:

7JF138.T250
7AF138.T250

So there is something about the specific character combination causing this.

blank.jpg

[BrianAll]

You got exactly the problem.
I can see it in the Immediate Window, I can even print it in a sheet.
But I need to store it in array, which seems impossible.

Any hint for a possible workaround?

[6StringJazzer]

I have not gotten far enough to look at the issue when assigning to an array, but I'll follow up.

I have a suspicion this is a bug in Excel/VBA.

[Marc L]

Hi Jeff !

It can not be a bug as a String variable can not be 'Empty' so just a bad logic not using an appropriate variable type
like in this VBA beginner demonstration according to the empty active cell :

Sub Demo1() 
    Dim S$, V 
        S = ActiveCell.Value2 
        V = ActiveCell.Value2 
        Debug.Print IsEmpty(S), IsEmpty(V), S = "" 
End Sub  

[6StringJazzer]

Marc, I'm not sure what you are trying to demonstrate. The original question shows a text value from a cell assigned to a String variable. IsEmpty returns False, and Debug.Print shows the expected value. But the value cannot be seen in the debugger on mouseover of the variable, and it cannot be assigned to an array element. If you look at the screenshot I attached, in particular the mouseover pop-up

aaa =

that should not be possible with a String variable. The issue also correlates to the content of the text string. Only certain values cause this problem.

[Marc L]

From VBA basics : a String variable never forever can ever be 'Empty' ‼

So never use VBA function IsEmpty with a String variable, I'm clear enough ?

[rorya]

I can see the value being assigned to the array.

[dangelor]

I can see it in the array...

[Marc L]

Hi Dangelor,

your aaa variable is obviously Variant instead of String like in the initial VBA procedure, as yet demonstrated in post #6 …

[dangelor]

The snap was taken directly from the workbook provided in post #1.

[Marc L]

Ok : on my side under Excel 2010 aaa does not contain the cell value but "item" ! And I can not select any other cell within the sheet,
when I select another workbook after less than a second the active window comes back to the Test_Doesn_Get_Value workbook automatically,
so weird ! So for some reason there is some glitch within this workbook …

[Marc L]

Information : I can select any cell before running the VBA procedure but not anymore after !
The same after I closed the workbook and re-opened it …

If I add any cell property like Value, Text or Value2 whatever,
if I run the same VBA procedure with aaa as a String variable the value returned is "15" !
With a come back as Variant sometimes aaa may return the correct cell value but sometimes it's still "15" …

I tried with a brand new variable S : exactly the same behaviour …

[Marc L]

When I try to close the workbook I get a message if I want to save another one !
Hoping there is no hidden virus within the post #1 workbook …

[6StringJazzer]

Just to rule out a file corruption I created a new file from scratch and typed in the content for the cell, and got the same result.

[6StringJazzer]

Also, I had been focused on the variable aaa and did not even look at the array. I am getting the value correctly assigned to the array element, and the correct value in aaa, even though the mouseover for aaa doesn't look right. So the code runs as expected, it's just that the debugger has a hiccup.

[Marc L]

On my side, I got the same result too on another workbook but after rebooting the computer,
entering the same value on a brand new workbook no issue, all works as usual !
So the issue comes from the post #1 workbook …