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

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
If you want to clear a single element of a String array, then yes, set that element equal to the empty string (""). If, on the other hand, you want to clear all of the elements of the array... that depends on how the array was Dim'med. If like this (a fixed array)...

Dim SomeArray(6) As String

or like this (also a fixed array)...

Dim SomeArray(0 To 6) As String

then you can simply Erase the array like this...

Erase SomeArray

and all the element will be returned to empty strings. If you Dim'med the array as a dynamic array like this...

Dim SomeArray() As String

and ReDim'med it later on, there simply ReDim it again (without using the Preserve keyword) and each element of the array will be returned to empty strings. You cannot use Erase with dynaminc arrays as it will destroy the structure of the array (leaving you with exactly what your Dim statement created... an arrays that must be ReDim'med before it can be used.
 

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
Great. Thanks Rick.
I am only clearing specific elements in the array and need to leave the other data untouched.

The bit that confuses me is that if I put the array as a Watch expression and look at the array elements immediately after the array is created, each element is shown as "Empty". If I then write data to the array, later erase the data of specific array elements by setting them to "" and again look at those array elements in the Watch table, the value is "". So does this mean that "" = Empty? I'm not sure that it does. Ideally I'd like to set an element back to 'Empty'.

Thanks again for your post.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
The bit that confuses me is that if I put the array as a Watch expression and look at the array elements immediately after the array is created, each element is shown as "Empty". If I then write data to the array, later erase the data of specific array elements by setting them to "" and again look at those array elements in the Watch table, the value is "". So does this mean that "" = Empty? I'm not sure that it does. Ideally I'd like to set an element back to 'Empty'.
First off, "" is called the "empty string" meaning it is a text string consisting of no characters.... Empty on the other hand usually means a Variant has not been assigned anything yet. I am not 100% sure why the array name lists as Empty before an array is ReDim'med, but once it is ReDim'med (as String), I believe it stop being Empty and instead becomes a String array (and it elements become Strings)... at that point, I am pretty sure its elements all contain the Empty String.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
Interesting discussion

i tried this

Code:
Sub aTest()
    Dim myArr() As String
    
    ReDim myArr(1 To 5)
    
    If myArr(1) = "" Then MsgBox "Null String"
    
    If myArr(1) = Empty Then MsgBox "Empty"
    
    If IsEmpty(myArr(1)) Then MsgBox "Is Empty"
    
End Sub

and received the two first messages, "Null String" and "Empty", but NOT the third "Is Empty".

Curious..

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409

ADVERTISEMENT

But if i use

Dim myArr() as Variant

I receive the 3 messages!

M.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
But if i use

Dim myArr() as Variant

I receive the 3 messages!

M.
When you redim (as in your first test), you initialize the array (all elements are initialized to ""). According to VBA Help:
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
When you redim (as in your first test), you initialize the array (all elements are initialized to ""). According to VBA Help:
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False.

Hi JoeMo,

Ok, i understand it, but if i Dim as Variant IsEmpty returns TRUE. Do you know why?

M.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi JoeMo,

Ok, i understand it, but if i Dim as Variant IsEmpty returns TRUE. Do you know why?

M.
I guess because its a variant the act of redim explicitly sets it to Empty.
Try this:
Code:
Sub aTest()
    Dim myArr() As Variant
    
    ReDim myArr(1 To 5)
    
    If myArr(1) = "" Then MsgBox "Null String"
    
    If myArr(1) = Empty Then MsgBox "Empty"
    
    If IsEmpty(myArr(1)) Then MsgBox "Is Empty"
    If Empty = "" Then MsgBox "OK"
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,813
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top