How to dimension and initialize a 2D array of strings (variants?)

[Grug.Crood]

Hi,

I've been "using" Excel VBA for a few dozen years off-and-on, but I don't normally need arrays beyond one dimension..

The below code works, but I had to resort to some "fixes" that left me puzzled.

I first tried to declare and fill the 2d array on the same line (tried with and without keyword New), no luck, so ended up doing it C-style, one at a time.

In the nested loop, tried "country = ar(2, indx)". Didn't work?!? Got the "mess" you see below from the variable in the locals window...

At this point I'm more than willing to feel stupid - can someone enlighten please :-)

Thanks!
GC

Please Login or Register  to view this content.

[Marc L]

Your VBA procedure revamped :

Sub fixCountryNames(Ws As Worksheet) 
    With Ws.Range("B2", Ws.[B1].End(xlDown)) 
            V = .Value2 
        For R& = 1 To UBound(V) 
            Select Case V(R, 1) 
                   Case "Czechia":  V(R, 1) = "Czech Republic" 
                   Case "USA":      V(R, 1) = "United States" 
                   Case "UK":       V(R, 1) = "United Kingdom" 
                   Case "UAE":      V(R, 1) = "United Arab Emirates" 
                   Case "S. Korea": V(R, 1) = "South Korea" 
            End Select 
        Next 
           .Value2 = V 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Marc L]

Maybe a better way :

Sub fixCountryNames(Ws As Worksheet) 
        V = [{"Czechia","Czech Republic";"USA","United States";"UK","United Kingdom";"UAE","United Arab Emirates";"S. Korea","South Korea"}] 
        Application.ScreenUpdating = False 
    With Ws.Range("B2", Ws.[B1].End(xlDown)) 
        For R& = 1 To UBound(V) 
            .Replace V(R, 1), V(R, 2), xlWhole 
        Next 
    End With 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jolivanes]

Or this.
See attached.

Column A, from the first Row on down, has the values to be changed.
Column B, from the first Row on down, has the values to be changed into.

Column C has the data to be checked and changed

Column E gets the result

Change all refrences where required.

It goes through the whole list in case of multiple instances of the same values.

Please Login or Register  to view this content.

[jindon]

In the nested loop, tried "country = ar(2, indx)". Didn't work?!? Got the "mess" you see below from the variable in the locals window...

This is a typical example.
Since you didn't declare country, it is Variant.
So it is only assigning the value ar(2, index) to country.

If you declare country as range, or country.value = ar(2, index), it should fix it.

[Grug.Crood]

Jindon,

Per following code window, adding .value (country.value) or prior declaration (dim country as Range) both worked, thank you! I am still, however, puzzled due to:

? vartype(country)
8
? vartype(country.value)
8

Also, I reckon seeing varType(countries.Cells) report as an array kind of surprised me because the locals window shows "variant/object/range".

If you can clarify where my thought process went South that'd be great, but I am satisfied I can take it from here (topic closed).

Thanks again!
GC

Please Login or Register  to view this content.

[Grug.Crood]

Marc,

Much appreciated, I don't recall ever having that form in assignments (brackets), but I do have a mind like a cast iron trap -- a little rain and rusted solid.

Of the below snippit you inspired, is there a way to combine the two lines and initialize on the declaration line? I tried several times, but no luck.

Thanks for the help!!!

GC

Please Login or Register  to view this content.

[Grug.Crood]

Marc,

It didn't occur to me to use the with statement. I find them harder to read, but I will try to work through that one (only caveat is the list could change and editing or moving the list to a separate function seems like it would be more work?).

It's eloquent , thanks again!

GC

Your VBA procedure revamped :

Sub fixCountryNames(Ws As Worksheet) 
    With Ws.Range("B2", Ws.[B1].End(xlDown)) 
            V = .Value2 
        For R& = 1 To UBound(V) 
            Select Case V(R, 1) 
                   Case "Czechia":  V(R, 1) = "Czech Republic" 
                   Case "USA":      V(R, 1) = "United States" 
                   Case "UK":       V(R, 1) = "United Kingdom" 
                   Case "UAE":      V(R, 1) = "United Arab Emirates" 
                   Case "S. Korea": V(R, 1) = "South Korea" 
            End Select 
        Next 
           .Value2 = V 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jolivanes]

Did the attachment in Post #4 work at all?

[rorya]

If country is a Variant, then country = ar(2, indx) means that you want to replace the current contents of the variable country (a cell) with whatever is in the array. So the country variable simply switches from referring to a cell to referring to a string at that point (which you don't then do anything with). If you declare it as a range, then you can't replace the variable's contents with a Let assignment (you'd need a Set statement), so it assigns the array value to the default range property (value).

[Grug.Crood]

If country is a Variant, then country = ar(2, indx) means that you want to replace the current contents of the variable country (a cell) with whatever is in the array. So the country variable simply switches from referring to a cell to referring to a string at that point (which you don't then do anything with). If you declare it as a range, then you can't replace the variable's contents with a Let assignment (you'd need a Set statement), so it assigns the array value to the default range property (value).

That (the underlined) is exactly what I was trying to do. The variable changed, but not the spreadsheet cell value.

I have three ways to make that work, but still don't know how to declare and initialize the array of values (on one line).

CG

[rorya]

What I mean is that it goes from:

country -> refers to a cell

to:

country -> refers to a string variable


You can't declare and assign an array in one statement in VBA.

[Grug.Crood]

@Jolivanes, it probably does, but I can't do the work on the sheet itself, so needed to work the list in the VBA coding.

I don't suppose you know how to declare and initialize a 2 dimensional array of strings (1 to 2, 1 to 5) on one line of code?

GC

[Grug.Crood]

What I mean is that it goes from:

country -> refers to a cell

to:

country -> refers to a string variable


You can't declare and assign an array in one statement in VBA.

Thanks Rorya

[jolivanes]

Re: "but I can't do the work on the sheet itself"
There are other sheets. Hidden if need be. External workbooks is another possibility.
I jus can't see me, or anybody, typing in or changing a whole bunch of names in code.
What are you going to do if you use similar code for an extended amount like 40 pieces of data?

[Grug.Crood]

Re: "but I can't do the work on the sheet itself"
There are other sheets. Hidden if need be. External workbooks is another possibility.
I jus can't see me, or anybody, typing in or changing a whole bunch of names in code.
What are you going to do if you use similar code for an extended amount like 40 pieces of data?

Long story short, I don't expect it will change. If it does then there are options and as you suggest, it could be easier to do it using a worksheet. My biggest surprise was seeing disparity between the local's window vs varType, and needing to add the brackets to initialize the array. Oh well, I'll retire soon and trade excel for a fishing pole and some good cigars...

[Grug.Crood]

@Marc,

I said I'd look … well, just did. Maybe eloquent was an understatement --- VERY impressive.

Love it!!!
GC

Your VBA procedure revamped :

Sub fixCountryNames(Ws As Worksheet) 
    With Ws.Range("B2", Ws.[B1].End(xlDown)) 
            V = .Value2 
        For R& = 1 To UBound(V) 
            Select Case V(R, 1) 
                   Case "Czechia":  V(R, 1) = "Czech Republic" 
                   Case "USA":      V(R, 1) = "United States" 
                   Case "UK":       V(R, 1) = "United Kingdom" 
                   Case "UAE":      V(R, 1) = "United Arab Emirates" 
                   Case "S. Korea": V(R, 1) = "South Korea" 
            End Select 
        Next 
           .Value2 = V 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jindon]

Grug.Crood,

What I meant to say was that this is a typical case when you don't properly declare the variable type and not specifying the property.

Look at the type in local window when step through the code.
country = Variant/Empty at start
country = Variant/Object/Range when loop commence
country = Variant/String when ar(2, indx) is Text value. Varaint/Integer or Double etc. depends on ar(2, indx)
So, it will never change the value in cell

As I mentioned earlier, if you declare country as range or specify the property, it stays as Range/Range, Variant.Object/Range, so that you can change the value in cell.

HTH

[Marc L]

Thanks for the rep' !

I don't recall ever having that form in assignments (brackets)

It's the short syntax of the Evaluate method, a VBA help must read !

is there a way to combine the two lines and initialize on the declaration line?

    Dim V:  V = [{"Czechia","Czech Republic";"USA","United States";"UK","United Kingdom";"UAE","United Arab Emirates";"S. Korea","South Korea"}]  

[Grug.Crood]

@jindon

Ah hah. I did not realize once a type is assigned on a variant it could be changed.

Thanks!
GC

Grug.Crood,
...

Look at the type in local window when step through the code.

country = Variant/Object/Range when loop commence
...
country = Variant/String when ar(2, indx) is Text value.

HTH