return array (as string) element to it's value when originally dimensioned

[mchac]

Does anyone have a suggestion of the best way to return an array (as string) element from a string to what that element was when first dimensioned?

I'm assuming when an array (as string) is created all the values are null (please confirm or correct this). When I input a string into an element then later want to erase the string in that element, should I replace it with "" or something else?

Thanks in advance.

 

[Marcelo Branco]

Or (guessing also)

As a Variant variable behaves as a "chameleon" in

If myArr(1) = "" Then MsgBox "Null String"

i think it behaves as a String because the right side is a Null String

But in
If myArr(1) = Empty Then MsgBox "Empty"

If IsEmpty(myArr(1)) Then MsgBox "Is Empty"

it behaves as something unknown (at least for me)

M.

 

[shg]

Empty is a value reserved to Variants (as is Null). Somewhere buried in the overhead bytes of a Variant is a flag for Empty.

 

[Marcelo Branco]

Empty is a value reserved to Variants (as is Null). Somewhere buried in the overhead bytes of a Variant is a flag for Empty.

Hi shg,

Ok, but what about this

Sub aTest()
    Dim myArr() As Long
    
    ReDim myArr(1 To 5)
    
    If myArr(1) = 0 Then MsgBox "Zero"
    
    If myArr(1) = Empty Then MsgBox "Empty"
    
    If IsEmpty(myArr(1)) Then MsgBox " Is Empty"
    
End Sub

I receveid the 2 first messages (???)
So myArr(1) = 0 and Empty at the same time...

I can't understand this...

M.

 

[Marcelo Branco]

Now, i think, i understand the meaning of Empty

Empty is equal to both 0 (zero) and zero length string ("")

If you try in the Immediate Window
? Empty = ""

and

? Empty = 0

you get TRUE in both cases.

M.

 

[mchac]

I suspect you declared it as a Variant (which initializes to Variant/Empty), and then ReDim'd it as String, which changes it to Variant/String

Yes I made a mistake in my comment just above. Immediately after declaring the array I'm not able to see its elements so I can't see what value they are given. The way I've written the VBA is that after my Dim statements, I calculate how large the array needs to be then use ReDim to specify that size.

As you suggested, I had declared the array as Variant and the Value after ReDim was Empty. When I declare the array as String, the Value after ReDim is "".

Sorry for the confusion.

 

[shg]

That's because of implicit coercion.

? Empty = ""

Returns True because CStr(Empty) = ""

? Empty = 0

Returns True because CInt(Empty) = 0

 

[mchac]

I think that's right.
I replied to shg's post but my comment didn't get posted here.

I made a mistake in my comment above. I originally declared the array as Variant (and dynamic) not String. But [I think] since it wasn't initialized I actually couldn't see the elements in the Watch window immediately after Dim (my mistake in saying I could see them) so I couldn't see the values.

After my Dims the next part of my VBA determines the size the array needs to be, then I ReDim at that size but had done so as Variant as shg had suggested. Immediately after the ReDim (as Variant) I can see the array elements in the Watch window and the values are all set to 'Empty' which contradicts what JoeMo said above. When I wrote strings to the array then overwrote with "" the Watch window showed "" as expected.

I just Dim'ed the array as String and immediately after the ReDim as String the Watch window shows the element values as "".

So it seems, as JoMo posted immediately above, that declaring as Variant sets the array elements to Empty.

I got curious as to what would happen if you dimmed an array as dynamic as Integer. ReDimmed as Integer, assigned a value to one of the elements then tried to 'erase' it by setting that element to ''".

Sub test()
Dim myarray() As Integer
ReDim myarray(5, 1) As Integer
myarray(1, 0) = 5
myarray(1, 0) = ""
End Sub

After the Dim you can't see the elements. After ReDim the elements are all set to 0. After setting (1,0) to 5, I can't erase by setting it to "" which makes sense that I can't set a an element that accepts integers to a string, even a null string. So it also makes sense that the error is Type mismatch. (not surprisingly, same results if set to Long or Double, but i was curious).

Thanks everyone for helping me work through this.